 معلومات العضو
|
|
|
|
إحصائية العضو
|
|
|
|
QUESTION NO: 30
Which three SELECT statements displays 2000 in the format “$2,000.00”? (Choose
three)
A. SELECT TO CNAR(2000, ‘$#,###.##’)
FROM dual;
B. SELECT TO CNAR(2000, ‘$0,000.00’)
FROM dual;
C. SELECT TO CNAR(2000, ‘$9,999.00’)
FROM dual;
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 30 -
D. SELECT TO CNAR(2000, ‘$9,999.99’)
FROM dual;
E. SELECT TO CNAR(2000, ‘$2,000.00’)
FROM dual;
F. SELECT TO CNAR(2000, ‘$N,NNN.NN’)
FROM dual;
Answer: B, C, D
Explanation:
Only queries in answers B, C and D will show result as in the format “$2,000.00”.
Incorrect Answers
A: Oracle error “ORA-01481: invalid number format model” will be generated.
E: Oracle error “ORA-01481: invalid number format model” will be generated.
F: Oracle error “ORA-01481: invalid number format model” will be generated.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access Control in Oracle
QUESTION NO: 31
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHARD2(25)
LAST_NAME VARCHARD2(25)
HIRE_DATE DATE
NEW EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET name = (Select last_name||
first_name
FROM employees
Where employee_id
=180)
WHERE employee_id =180;
B. UPDATE new_employees SET name = (SELECT
last_name||first_name
FROM employees)
WHERE employee_id =180;
C. UPDATE new_employees SET name = (SELECT last_name||
first_name
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 31 -
FROM employees
WHERE employee_id
=180)
WHERE employee_id =(SELECT employee_id
FROM new employees);
D. UPDATE new_employees SET name = (SELECT last name||
first_name
FROM employees
WHERE employee_id=
(SELECT employee_id
FROM new_employees))
WHERE employee_id
=180;
Answer: A
Explanation:
Sub-query in this answer will return one row value, concatenated first and last name for the
employee with ID 180, so update will be successful. When sub-queries are linked to the
parent by equality comparisons, the parent query expects only one row of data from the subquery.
Incorrect Answers
B: Sub-query will return concatenated first and last name for ALL records from the table
EMPLOYEES. It will cause an error for the parent query, because it expects one only one
row.
C: Last WHERE statement in this query will generate error because sub-query returns multirow
result.
D: SELECT statement for the NEW_EMPLOYEES table in this query will generate error
because sub-query returns multi-row result.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-156
Chapter 4: Subqueries
QUESTION NO: 32
Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the
DEPARTMENTS table
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 32 -
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2
(30)
MGR_ID NUMBER References NGR_ID column of the
EMPLOYEES table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the
LOCATIONS table
LOCATIONS
LOCATION_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2 |30)
Which two SQL statements produce the name, department name, and the city of all the
employees who earn more then 10000? (Choose two)
A. SELECT emp_name, department_name, city
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations 1
USING (location_id)
WHERE salary > 10000;
B. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
JOIN ON (e.department_id = d.department id)
AND (d.location_id =1.location_id)
AND salary > 10000;
C. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE salary > 10000;
D. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE e.department_id = d.department_id
AND d.location_id = 1.location_id
AND salary > 10000;
E. SELECT emp_name, department_name, city
FROM employees e
NATURAL JOIN departments, locations
WHERE salary > 10000;
Answer: B, D
Explanation:
These statements show correct syntax and semantics to receive correct results.
Incorrect Answers
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 33 -
A: JOIN ON keywords need to be used to build correct query.
C: This query will built Cartesian product because there is no join conditions in WHERE
clause to join tables.
E: NATURAL JOIN is a join between two where Oracle joins the tables according to the
column(s) in the two tables sharing the same name. It is required to add one more
NATURAL JOIN clause to join additional table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 98-118
Chapter 3: Advanced Data Selection in Oracle
QUESTION NO: 33
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid
in that department, only of the minimum salary is less then 5000 and the maximum
salary is more than 15000?
A. SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: D
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 34 -
Explanation:
This SELECT statement shows correct result.
Incorrect Answers
A: To provide correct data statement needs also GROUP BY clause.
B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause.
E: You need only grouping by department, not by salary.
QUESTION NO: 34
Examine the structure if the EMPLOYEES table:
Column name Data Type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID
column of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the user to insert rows through
the view. Which SQL statement, when used to create the EMP_VU view, allows the user
to insert rows?
A. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
B. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id
department_id
FROM employees
WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_Vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id;
D. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees;
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 35 -
Answer: B
Explanation:
This statement will create view that can be used to change tables in underlying table through
simple views. It includes primary key, NOT NULL column and foreign key to avoid
constraint restrictions.
Incorrect Answers
A: This statement does not include JOB_ID column that cannot be NULL. In general, all
constraint restrictions defined on the underlying table also apply to modifying data via the
view. For example, you can’t add data to an underlying table via a view that violates the
table’s primary key constraint.
C: You cannot update a column of an underlying table if the simple view use a single-row
function to define the column.
D: You may not insert, update, or delete records data on the table underlying the simple view
if the SELECT statement creating the view contains a GROUP BY clause, GROUP
function, or DISTINCT clause.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 298-299
Chapter 7: Creating Other Database Objects in Oracle |