Which three statements are true regarding sub queries? (Choose three.)
A.
Multiple columns or expressions can be compared between the main query and sub query
B.
Main query and sub query can get data from different tables
C.
Sub queries can contain GROUP BY and ORDER BY clauses
D.
Main query and sub query must get data from the same tables
E.
Sub queries can contain ORDER BY but not the GROUP BY clause
F.
Only one column or expression can be compared between the main query and subqeury
A,B,C IS correct ,its wonderful website
A,B,C
I don’t think C is the right answer. If you perform following:
SQL> SELECT * FROM EMP WHERE deptno IN (SELECT deptno FROM DEPT WHERE deptno > 10 ORDER BY deptno);
SELECT * FROM EMP WHERE deptno IN (SELECT deptno FROM DEPT WHERE deptno > 10 ORDER BY deptno)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
if I remove ORDER BY from nested SELECT:
SQL> SELECT * FROM EMP WHERE deptno IN (SELECT deptno FROM DEPT WHERE deptno > 10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
I’d personally go for right answers A,B and F, but not 100% sure about F.
Nope, sorry
There is another type of nesting, which allows ORDER BY:
SQL> SELECT emp.empno, sq.deptno FROM emp, (SELECT deptno FROM dept ORDER BY deptno) sq WHERE emp.deptno = sq.deptno;
EMPNO DEPTNO
———- ———-
7782 10
7934 10
7839 10
So indeed the right answer is A,B,C.
In example gave by you it is about correlated query. The question it is about query and subquery.
Subquery:
SELECT
field1
,field2
, (SELECT field3 from table2 t2 where t2.field3 = t1.filed1)
FROM table1 t1;
Correlated query is as you wrote;
Regards,