[Oracle] DB 서브쿼리

 

서브쿼리(하위 질의문)

-WHERE, HAVING절 하위질의문
-FROM절 하위질의문 => (n-tier)

사원의 평균급여보다 많이 받는 직원의 내역을 출력

->SELECT AVG(salary) FROM employees;

->SELECT last_name, salary 
        FROM employees
        WHERE salary > 6461.831775700934579439252336448598130841;
 
==> 2번의 질의문이 필요하다.

 

서브쿼리 작성 순서

1.서브쿼리문 먼저 작성
2.메인 쿼리문 작성

->SELECT last_name, salary 
     FROM employees
     WHERE salary > (SELECT AVG(salary) FROM employees);

'Chen' 사원보다 salary를 많이 받는 사원의 목록을 출력

->SELECT last_name, salary
     FROM employees
     WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Chen');



'정의찬'과 부서(dept)가 다르지만 동일한 업무(job)을 수행하는 사원 목록 출력

-서브쿼리
SELECT job FROM emp WHERE ename = '정의찬'
SELECT dno FROM emp WHERE ename = '정의찬'

SELECT eno, ename, dno, job
     FROM emp 
     WHERE dno != (SELECT dno FROM emp WHERE ename = '정의찬')
     AND  job = (SELECT job FROM emp WHERE ename = '정의찬')

'관우'보다 일반화학과목의 학점이 낮은 학생의 명단 출력

-서브쿼리
SELECT grade FROM student s, course c, score r, scgrade g
     WHERE s.sno = r.sno
     AND c.cno = r.cno
     AND cname = '일반화학'
     AND sname = '관우'
     AND result BETWEEN loscore AND hiscore


SELECT s.sno, s.sname, grade
     FROM student s, course c, score r, scgrade g
     WHERE s.sno = r.sno
     AND c.cno = r.cno
     AND cname = '일반화학'
     AND grade > (SELECT grade FROM student s, course c, score r, scgrade g
     WHERE s.sno = r.sno
     AND c.cno = r.cno
     AND cname = '일반화학'
     AND sname = '관우'
     AND result BETWEEN loscore AND hiscore)

---

HAVING절 서브쿼리

부서 중 가장 급여를 많이 받는 부서를 검색

-서브쿼리
SELECT MAX(AVG(salary)) FROM emp
            GROUP BY dno

SELECT dno FROM emp
            GROUP BY dno
            HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp
                                        GROUP BY dno)

학생 인원수가 가장 많은 학과를 검색.

-서브쿼리
SELECT MAX(count(*)) FROM student
            GROUP BY major

SELECT major FROM student
            GROUP BY major
            HAVING count(*) = (SELECT MAX(count(*)) FROM student
            GROUP BY major)

학생중 기말고사 평균성적이 가장 낮은 학생의 정보를 검색

-서브쿼리
SELECT MIN(AVG(result)) FROM score
            GROUP BY sno

SELECT s.sno, s.sname
FROM student s, score r
WHERE s.sno = r.sno
GROUP BY s.sno, s.sname
HAVING AVG(result) = (SELECT MIN(AVG(result)) FROM score
                               GROUP BY sno)
 

화학과 1학년 학생중에 평점이 평균이하인 학생을 검색

-서브쿼리
SELECT AVG(avr) FROM student
           WHERE major = '화학'
           AND syear = 1

SELECT * FROM student
            WHERE major = '화학'
            AND syear = 1
            AND avr < (SELECT AVG(avr) FROM student
                               WHERE major = '화학'
                               AND syear = 1)

---

다중컬럼 다중로우

직무(job_id)별 최대급여자의 사원내역을 출력

-서브쿼리
->SELECT MAX(salary), job_id FROM employees 
                GROUP BY job_id

->SELECT employee_id, last_name, salary, job_id
                FROM employees
                WHERE salary IN (SELECT MAX(salary), job_id FROM employees 
                                            GROUP BY job_id)        

==> 값(MAX(salary))만 넘겨주게 되면 최대값 이외에 값도 출력되게 된다.
         다중으로 컬럼(job_id)을 명시해주어야 한다.

WHERE 컬럼 = (SELECT  ~) => 단일로우
WHERE 컬럼 IN (SELECT ~) => 다중로우

IN : 검색된 값 중에 하나만 일치하면 참
ANY : 검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참
ALL : 검색된 값 중에 조건에 모두 일치해야 참

*컬럼 > max   →   컬럼 > ALL(서브쿼리) : 가장 큰 값 보다 크다.
*컬럼 < min   →   컬럼 < ALL(서브쿼리) : 가장 작은 값 보다 작다.

*컬럼 > min   →   컬럼 > ANY(서브쿼리) : 가장 작은 값 보다 크다.
*컬럼 < max   →  컬럼 < ANY(서브쿼리) : 가장 큰 값 보다는 작다.



10번 부서에 가장 작은 급여자 보다 작게 받는 급여자를 출력

SELECT eno, ename, sal, dno
      FROM emp
      WHERE sal < (SELECT MIN(sal) FROM emp
      WHERE dno = 10)


SELECT eno, ename, sal, dno
      FROM emp
      WHERE sal < ALL(SELECT sal FROM emp
      WHERE dno = 10)

부서번호 30번 최대 급여자 보다 급여가 높은 사원을 출력

SELECT department_id, last_name, salary FROM employees
WHERE salary > ALL(SELECT salary FROM employees WHERE department_id = 30)

부서번호 30번 최대급여자 보다 급여가 작은 사원을 출력

SELECT department_id, last_name, salary FROM employees
WHERE salary < ANY(SELECT salary FROM employees WHERE department_id = 30)

'손하늘'과 동일한 관리자(mgr)의 관리를 받으면서 업무도 같은 사원을 검색

SELECT mgr,job FROM emp
WHERE ename = '손하늘'

SELECT ename, mgr, job FROM emp
WHERE (mgr,job) IN(SELECT mgr,job FROM emp
WHERE ename = '손하늘')

화학과 학생과 평점이 동일한 학생을 검색

SELECT avr FROM student
WHERE major = '화학'

SELECT sname, major, avr FROM student
WHERE avr IN(SELECT avr FROM student
WHERE major = '화학')

기말고사 성적이 '핵화학' 과목 평균 성적보다 우수한 과목번호, 과목명, 담당교수 검색

SELECT c.cno, c.cname, p.pname, AVG(result)
FROM score r, professor p, course c
WHERE r.cno = c.cno
AND p.pno = c.pno
GROUP BY c.cno, c.cname, p.pname
HAVING AVG(result) > (SELECT AVG(result) FROM score WHERE cno =
(SELECT cno FROM course WHERE cname = '핵화학'))

---

FROM절 서브쿼리(TOP-N SQL)

입사순서 5명을 출력

SELECT employee_id, last_name, hire_date
            FROM employees
            ORDER BY hire_date

SELECT ROWNUM, alias.*
            FROM (SELECT employee_id, last_name, hire_date
                        FROM employees
                        ORDER BY hire_date) alias
            WHERE ROWNUM <= 5;

 

급여를 많이 받는 순서 3명의 사원정보를 출력

SELECT employee_id, last_name, salary            
            FROM employees
            ORDER BY salary desc


SELECT ROWNUM, alias.*           
            FROM (SELECT employee_id, last_name, salary            
            FROM employees
            ORDER BY salary desc) alias
    WHERE ROWNUM <= 3