P.431

단일행 Sub Query 연습문제 1

Student 테이블과 department 테이블을 사용하여 'Anthony Hopkins' 학생과 1 전공(deptno1)이 동일한 학생들의 이름과 1전공 이름을 출력하세요.

결과 화면

STUD_NAME            DEPT_NAME
-------------------- -------------------------
Sandra Bullock       Software Engineering
Anthony Hopkins      Software Engineering

SQL문

SELECT name "STUD_NAME", dname "DEPT_NAME"
FROM student s, department d
WHERE s.deptno1 = d.deptno AND s.deptno1 = ( SELECT deptno1
                                             FROM student
                                             WHERE name = 'Anthony Hopkins');

단일행 Sub Query 연습문제 2

Professor 테이블과 department 테이블을 조회하여 'Meg Ryan' 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.

결과 화면

PROF_NAME           HIREDATE    DEPT_NAME
------------------- ----------- -------------------------------
Jessica Lange       1998-03-22  Computer Engineering
Angela Bassett      1987-01-30  Computer Engineering
Michelle Pfeiffer   1985-11-30  Multimedia Engineering
Winona Ryder        2001-09-01  Multimedia Engineering
Sharon Stone        2002-02-24  Software Engineering
Julia Roberts       1997-07-01  Software Engineering
Susan Sarandon      2009-08-30  Electronic Engineering
Holly Hunter        2009-01-28  Mechanical Engineering
Nicole Kidman       1999-12-01  Mechanical Engineering
Jodie Foster        2001-05-23  Library and Information science
Andie Macdowell     2010-06-28  Library and Information science

SQL문

SELECT name "PROF_NAME", TO_CHAR(hiredate, 'YYYY-MM-DD') "HIREDATE", dname "DEPT_NAME"
FROM professor p, department d
WHERE p.deptno = d.deptno AND p.hiredate > ( SELECT hiredate
                                             FROM professor
                                             WHERE name = 'Meg Ryan');

단일행 Sub Query 연습문제 3

Student 테이블에서 1 전공(deptno1)이 201번인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요.

결과 화면

NAME                     WEIGHT
-------------------- ----------
James Seo                    72
Demi Moore                   83
Danny Glover                 70
Richard Dreyfus              72
Tim Robbins                  70
Wesley Snipes                82
Christian Slater             69
Charlie Sheen                81

SQl문

SELECT s.name, s.weight
FROM student s
WHERE s.weight > ( SELECT AVG(weight)
                   FROM student
                   WHERE deptno1 = 201);

P.446

다중 행 Sub Query 연습문제1

Emp2 테이블을 사용하여 전체 직원 중 'Section head' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요. 단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호와 $표시를 하세요.

결과 화면

NAME                 POSITION                       SALARY
-------------------- ------------------------------ ------------------
Kurt Russell         Boss                            $100,000,000
Kevin Bacon          Department head                  $75,000,000
AL Pacino            Department head                  $72,000,000
Val Kilmer           Department head                  $68,000,000
Tommy Lee Jones      Deputy department head           $60,000,000
Gene Hackman         Section head                     $56,000,000
Hugh Grant           Section head                     $51,000,000
Woody Harrelson      Section head                     $50,000,000

SQL문

SELECT e.name, e.position, TO_CHAR(e.pay, '$999,999,999') "SALARY"
FROM emp2 e
WHERE e.pay >ANY ( SELECT pay
                   FROM emp2
                   WHERE position = 'Section head')
ORDER BY 3 DESC;

다중 행 Sub Query 연습문제 2

Student 테이블을 조회하여 전체 학생 중에서 체중이 2학년 학생들의 체중에서 가장 적게 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.

결과 화면

NAME                      GRADE     WEIGHT
-------------------- ---------- ----------
Billy Crystal                 3         48
Danny Devito                  1         48
Nicholas Cage                 3         42

SQL문

SELECT s.name, s.grade, s.weight
FROM student s
WHERE s.weight <ALL ( SELECT weight
                      FROM student
                      WHERE grade = 2);

다중 행 Sub Query 연습문제 3

Emp2 테이블과 dept2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

출력 결과

DNAME                NAME                 SALARY
-------------------- -------------------- ------------------------
S/W Support Team     Clint Eastwood        $20,000,000
H/W Support Team     Harrison Ford         $20,000,000
Sales4 Team          Tom Cruise            $20,000,000
Sales3 Team          Sly Stallone          $22,000,000
Sales2 Team          Robert De Niro        $22,000,000
Sales1 Team          JohnTravolta          $22,000,000

SQL문

SELECT dname, name, TO_CHAR(e.pay, '$99,999,999') "SALARY"
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode AND e.pay <ALL ( SELECT MIN(AVG(pay))
                                          FROM emp2
                                          GROUP BY deptno)
ORDER BY 3;

P.448

다중 컬럼 Sub Query 연습문제 1

Professor 테이블과 department 테이블을 조회하여 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요(입사일 순으로 오름차순 정렬하세요).

결과 화면

    PROFNO NAME                 hiredate    DNAME
---------- -------------------- ----------- -----------------------------------
      1001 Audie Murphy         1980-06-23  Computer Engineering
      4001 Meryl Streep         1981-10-23  Electronic Engineering
      3001 Emma Thompson        1981-10-23  Software Engineering
      2003 Whoopi Goldberg      1982-04-29  Multimedia Engineering
      4005 Meg Ryan             1985-09-18  Chemical Engineering
      4003 Nicole Kidman        1999-12-01  Mechanical Engineering
      4007 Jodie Foster         2001-05-23  Library and Information science

SQL문

SELECT profno, name "PROF_NAME", hiredate, dname "DEPT_NAME"
FROM professor p, department d
WHERE p.deptno = d.deptno
AND (p.deptno, hiredate) IN (SELECT deptno, MIN(hiredate) 
                               FROM professor 
                               GROUP BY deptno)
ORDER BY 3;

다중 컬럼 Sub Query 연습문제 2

Emp2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름과 직급, 연봉을 출력하세요. 연봉 순으로 오름차순 정렬하세요.

결과 화면

NAME                 POSITION                       SALARY
-------------------- ------------------------------ ---------------------
Keanu Reeves         Deputy Section chief             $35,000,000
Gene Hackman         Section head                     $56,000,000
Tommy Lee Jones      Deputy department head           $60,000,000
Kevin Bacon          Department head                  $75,000,000
Kurt Russell         Boss                            $100,000,000

SQL문

SELECT name, position, TO_CHAR(pay, '$999,999,999') "SALARY"
FROM emp2
WHERE (pay, position) IN ( SELECT MAX(pay), position 
                           FROM emp2 
                           GROUP BY position)
ORDER BY 3;

출처