- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
86.
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. |
87.
The COUNTRY table has the following data:
- A.INDIA
- B.65
- C.91
- D.SINGAPORE
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The subquery returns 91 to the main query. |
88.
Which line in the following query contains an error?
Line 1 SELECT deptno, ename, sal
Line 2 FROM emp e1
line 3 WHERE sal = (SELECT MAX(sal) FROM emp Line 4
Line 5 ORDER BY deptno);
Line 1 SELECT deptno, ename, sal
Line 2 FROM emp e1
line 3 WHERE sal = (SELECT MAX(sal) FROM emp Line 4
Line 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. |
89.
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?
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 the 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. |
90.
The COUNTRY table has the following data:
- A.One row will be inserted into the COUNTRY table.
- B.WITH CHECK OPTION is missing in the subquery.
- C.The query will fail because the VALUES clause is invalid.
- D.The WHERE clause cannot appear in the subqueries used in INSERT statements.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Because only one column is selected in the subquery to which you are doing the insert, only one column value should be supplied in the VALUES clause. The VALUES clause can have only CNT_CODE value (971). |
|
|
||||||||||||||||||||||||||||
