Oracle 1z0 047 Exam

Exam Number/Code : 1z0-047
Exam Name : Oracle Database SQL Expert
Questions and Answers : 278 Q&As
Update Time: 2010-04-15
1. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: AD
2. View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the
company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual
salary:
SELECT last_name, department_id, salary+50*12 "Annual Compensation"
from employees
WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you
make to the query to calculate the annual compensation correctly?
A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
C. Change the SELECT clause to SELECT last_name, department_id, (salary+50)*12 "Annual Compensation".
D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
Answer: C
3. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in
relation to the EMPDET table?
A. UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;
B. DELETE from empdet
WHERE ename LIKE 'J%';
C. CREATE VIEW empvu
AS
SELECT * from empdept;
D. CREATE INDEX empdet_idx
ON empdet(empno);
Answer: C
4. Evaluate the following SQL statement:
ALTE