DBMS SQL 서브쿼리

테이블 조회 서브쿼리

1. 하나의 sql문 안에 있는 또다른 Select절.
2. 연산자의 오른쪽에 위치하며 괄호로 묶여져 사용
3. 질의가 지정되지 않은 값을 근거로 할 때 유용
4. 서브쿼리에는 ORDER BY를 쓸 수 없다
5. 그룹함수를 사용할 수 없다
6. SELECT, WHERE, HAVING 절 등에 사용 가능

SELECT *
FROM temp
WHERE emp_id = (SELECT MIN(emp_id) FROM temp);

 

 

--질문 temp에서 직급별로 최소연봉을 가진 직원의 사번과 연봉을 읽어보자
SELECT emp_id, salary
FROM temp
WHERE salary = (
    SELECT MIN(salary)
    FROM temp
    GROUP BY lev
    HAVING MIN(salary)<=(
        SELECT MIN(salary)
        FROM temp
    )
);

 

--질문 Martin과 같은 직무를 가진 사원 정보를 사원 테이블에서 검색하는 결과를 얻고자 할 때
SELECT empno, ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='MARTIN');

 

 

SELECT ename, sal
FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp));

 

--emp 테이블에서 가장 입사일이 오래된 사원을 출력
SELECT ename, hiredate
FROM emp
WHERE hiredate = (SELECT MIN(hiredate) FROM emp);

 

 

 

--각 업무 (job)별 평균 급여 중 가장 적은 급여를 받는 업무와 그 업무의 평균 급여를 출력하라
SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) = (SELECT MIN(AVG(sal) FROM emp GROUP BY job);

 

 

-- emp 테이블과 dept 테이블에서 부서 중 평균 급여가 가장 높은 부서의 이름과 평균 급여를 출력
SELECT dname, AVG(sal)
FROM emp,dept
WHERE emp.deptno = dept.deptno
GROUP BY dname
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);

 

 

--부서별 최고 연봉자의 이름과 급여 출력
SELECT ename, sal
FROM emp
WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

 

 

 

-- emp 테이블에서 가장 최근에 입사한 5명의 사원번호, 사원명, 입사날짜를 출력하시오.
SELECT empno, ename, hiredate
FROM
(SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate DESC)
WHERE ROWNUM <= 5;

-- emp 테이블에서 급여를 가장 많이 받는 사람순으로 4명의 사원번호, 사원명, 급여를 출력하시오.
SELECT empno, ename, sal
FROM (
    SELECT empno, ename, sal
    FROM emp
    ORDER BY sal DESC)
WHERE ROWNUM <=4;

 

---

WITH

여러 개의 서브쿼리가 하나의 메인 쿼리문에 사용될 때 생기는 복잡성을 간결하게 정의할 수 있다.

 

WITH
    dept_costs AS
    (SELECT d.deptno, SUM(e.sal)
    AS dept_total
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
    GROUP BY d.deptno),
    
    avg_cost AS
    (SELECT SUM(dept_total)/COUNT(*)
    AS dept_avg
    FROM dept_costs)
    
    SELECT *
    FROM dept_costs
    WHERE dept_total>
        (SELECT dept_avg
        FROM avg_cost)
        ORDER BY deptno;

 

 

 

 

 

---

테이블 생성 서브쿼리

--질문 emp테이블로부터 업무가 salesman인 사원의 사원번호, 이름, 업무, 급여만을 검색하여 새로운 테이블인 salesman1 테이블을 생성
CREATE TABLE salesman1
AS
SELECT empno, ename, job, sal
FROM emp
WHERE job = 'SALESMAN';

DROP TABLE salesman1;


--문제 emp 테이블에서 업무가 manager인 사원의 사원번호, 이름, 업무, 급여, 부서번호를 갖는 manager2라는 table을 생성하고 테이블 구조를 확인하시오.
CREATE TABLE manager2
AS 
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE job='manager';

DESC manager2;

DROP TABLE manager2;


--문제 Division10이라는 테이블을 만들고, EMP 테이블에서 부서번호가 10번인 부서의 급여의 합 정보를 가져오라
CREATE TABLE Division10
AS
SELECT SUM(sal) salary
FROM emp
WHERE deptno=10
GROUP BY deptno;

DESC Division10;

SELECT * FROM division10;

DROP TABLE division10;