46.
The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Which
query will show you the top five highest-paid employees in the company?
- [A]
SELECT last_name, salary FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;
- [B]
SELECT last_name, salary FROM (SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC ) WHERE ROWNUM <= 5;
- [C]
SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary) WHERE ROWNUM <= 5;
- [D]
SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| To find the top n rows, you can select the necessary columns in an inline view with an ORDER BY DESC clause. An outer query limiting the rows to n will give the result. ROWNUM returns the row number of the result row. |
47.
Review the SQL code, and choose the line number that has an error.
1 SELECT DISTINCT department_id 2 FROM employees 3 ORDER BY department_id 4 UNION ALL 5 SELECT department_id 6 FROM departments 7 ORDER BY department_id
- [A]1
- [B]3
- [C]6
- [D]7
- [E]No error
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| When using set operators, the ORDER BY clause can appear only on the SQL at the very end. You can use the column names (or aliases) appearing in the top query or use positional columns. |
48.
Consider the following query:
SELECT deptno, ename, salary salary, average, salary-average difference FROM emp, (SELECT deptno dno, AVG(salary) average FROM emp GROUP BY deptno) WHERE deptno = dno ORDER BY 1, 2;Which of the following statements is correct?
- [A]The query will fail because no alias name is provided for the subquery.
- [B]The query will fail because a column selected in the subquery is referenced outside the scope of the subquery.
- [C]The query will work without errors.
- [D]GROUP BY cannot be used inside a subquery.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The query will work fine, producing the difference between the employee's salary and average salary in the department. You do not need to use the alias names, because the column names returned from the subquery are different from the column names returned by the parent query. |
49.
Which line in the following query contains an error?
1 SELECT deptno, ename, sal 2 FROM emp e1 3 WHERE sal = (SELECT MAX(sal) FROM emp 4 WHERE deptno = e1.deptno 5 ORDER BY deptno);
- [A]Line 2
- [B]Line 3
- [C]Line 4
- [D]Line 5
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| You cannot have an ORDER BY clause in the subquery used in a WHERE clause. |
50.
Which of the following is a correlated subquery?
- [A]
select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE' and city.cnt_code = state.cnt_code);
- [B]select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE');
- [C]
select cty_name from city, state where city.st_code = state.st_code and city.cnt_code = state.cnt_code and st_name = 'TENNESSEE';
- [D]
select cty_name from city, state where city.st_code = state.st_code (+) and city.cnt_code = state.cnt_code (+) and st_name = 'TENNESSEE';
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| A subquery is correlated when a reference is made to a column from a table in the parent statement. |
|
|
||||||||||||||||||||||||||||
