11. Chapter6 Advanced Query(Subquery, Set Operation)(20081030)

 Subquery (P6-1)

• Subquery는 쿼리안에 또 쿼리가 들어가는 것으로 괄호안에 써준다.
• P6-2의 Example의 쿼리문은 'row의 개수에 따라 operator의 종류를 맞게 써준다'의 특징에 안맞음
• ename이 SCOTT인 경우가 여러개인 경우가 있으므로 '=' 대신에 'IN'을 써줘야한다. (P6-4처럼~)
  ('WHERE deptno = ' -> 'WHERE deptno IN(~)'으로 변경)

1. SQL> SELECT ename
     2  FROM emp
     3  WHERE deptno =
     4  (SELECT deptno
     5  FROM emp
     6  WHERE ename = 'SCOTT');
2.  
3. ENAME
   ----------
   SMITH
   JONES
   SCOTT
   ADAMS
   FORD

 

 

Multiple-Row Subquery (P6-4)

• 부서별 최대봉급을 받는 사원이름 (test00계정)

1. SQL> SELECT dept_id, last_name, salary
     2  FROM s_emp
     3  WHERE(dept_id, salary) IN (
     4  SELECT dept_id, MAX(salary)
     5  FROM s_emp
     6  GROUP BY dept_id
     7  );
2.  
3.    DEPT_ID LAST_NAME                     SALARY
   ---------- ------------------------- ----------
           10 Quick-To-See                    1450
           31 Magee                           1400
           31 Nagayama                        1400
           32 Giljum                          1490
           33 Sedeghi                         1515
           34 Nguyen                          1525
           35 Dumas                           1450
           41 Ngao                            1450
           42 Menchu                          1250
           43 Biri                            1100
           44 Catchpole                       1300
           45 Havel                           1307
           50 Velasquez                       2500
4. 13 rows selected.

 

 

Subquery in the FROM Clause (P6-5)

1. SQL>  SELECT e.ename, e.sal
     2   FROM emp e, (SELECT deptno, MAX(sal) msal
     3   FROM emp GROUP BY deptno) g
     4   WHERE e.deptno=g.deptno AND e.sal = g.msal;
2.  
3. ENAME             SAL
   ---------- ----------
   BLAKE            2850
   SCOTT            3000
   KING             5000
   FORD             3000

이 글은 스프링노트에서 작성되었습니다.