서브쿼리 subquery

Q) Daniel이 근무하는 부서의 평균급여를 구하시오

--1. Daniel의 부서를 알아낸다
--2. 해당 부서의 평균 급여를 알아낸다
SELECT department_id FROM employees WHERE first_name='Daniel';
SELECT avg(salary) FROM employees WHERE department_id=100;
--이걸 합쳐야됨
SELECT avg(salary) FROM employees WHERE department_id=(SELECT department_id FROM employees WHERE first_name='Daniel');
--이렇게 두번에 걸쳐 구하는것을 한번에 구하는 것이 서브쿼리

 

- where절 서브쿼리

Q) 급여를 8800 받는 직원의 부서아이디를 찾아 부서 아이디와 부서명을 출력

SELECT department_id 부서아이디, department_name 부서명
FROM departments
WHERE department_id=(SELECT department_id FROM employees WHERE salary=8800);

- 값이 2개 이상일때 IN 사용

Q) 급여를 8200 받는 직원의 부서아이디를 찾아 부서 아이디와 부서명을 출력

SELECT department_id 부서아이디, department_name 부서명
FROM departments
WHERE department_id IN (SELECT department_id FROM employees WHERE salary=8200);
--부서가 2개 나옴, 그러면 =으로 비교 안됨, 이떄는 IN 사용(결과가 하나 이상일때)

 

- 두개의 조건 비교하기 where (컬럼1, 컬럼2) IN [서브쿼리]

Q) 각부서별로 최저급여를 받는 직원의 이름과 부서아이디, 급여를 조회하시오. 단 출력은 부서아이디 오름차순

SELECT first_name 이름 , department_id 부서아이디 , salary 급여
FROM employees
WHERE salary IN (SELECT  MIN(salary) FROM employees GROUP BY department_id)
ORDER BY 2;
--위에꺼는 오류, pair wise되었다고함(이것저것 다됐다), 밑에처럼 해야됨
SELECT first_name 이름 , department_id 부서아이디 , salary 급여
FROM employees
WHERE (salary,department_id )IN (
    SELECT  MIN(salary) ,department_id
    FROM employees
    GROUP BY department_id)
ORDER BY 2;
-- join 사용
SELECT e.first_name 이름, d.department_id 부서아이디, d.m 급여
FROM employees e, (SELECT department_id,MIN(salary) m FROM employees GROUP BY department_id) d
WHERE e.department_id=d.department_id AND e.salary=d.m
ORDER BY 2;

Q) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라. 단 업무별로 정렬하여라.

SELECT employee_id 사번,first_name 이름, job_id 업무, department_id 부서번호
FROM employees
WHERE (job_id,salary) IN (SELECT job_id , min(salary)
                            FROM employees
                            GROUP BY job_id)
ORDER BY 3;

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.JOB_ID, E.SALARY, E.DEPARTMENT_ID
FROM EMPLOYEES E
WHERE E.SALARY = ( SELECT MIN(SALARY)
                   FROM EMPLOYEES G
                   WHERE E.JOB_ID = G.JOB_ID
                   GROUP BY JOB_ID )
ORDER BY JOB_ID;

 

 

Q) IT 부서에서 근무하는 직원들의 이름, 급여, 입사일을 조회

--서브쿼리사용
SELECT first_name 이름 , salary 급여 , hire_date 입사일
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
where department_name = 'IT');
--join으로 풀기
SELECT e.first_name 이름 , e.salary 급여 , e.hire_date 입사일, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id=d.department_id AND d.department_name = 'IT';

Q) 30번 부서의 사원 평균급여보다 많은 급여를 받고 그러면서 Seattle 에 근무하는 사원의 최대급여보다 급여를 적게 받는 사원에 대해 사원의 이름, 급여, 부서명을 출력

SELECT e.first_name 이름 , e.salary 급여, d.department_name 부서명
FROM departments d, employees e
WHERE e.department_id=d.department_id(+)
AND e.salary>(SELECT avg(salary) FROM employees WHERE department_id=30 GROUP BY department_id)
AND e.salary<(SELECT max(e.salary)
                FROM locations l,departments d,employees e
                WHERE e.department_id=d.department_id(+) AND d.location_id=l.location_id(+)
                AND l.city = 'Seattle' 
                GROUP BY l.city)
ORDER BY 2 DESC;

 

- select절 서브쿼리

- 서브쿼리가 select절에 있으면 scala subquery이며 1행만 반환(무조건 한열만 반환돼야됨)

Q) 사원의 이름,부서id,급여를 출력, 사원이 속한 해당 부서의 최소급여를 부서id 다음에 넣어 출력하시오

SELECT first_name 이름 , department_id 부서id,(SELECT min(salary) FROM employees WHERE department_id=e.department_id GROUP BY department_id) 부서최소급여 ,salary 급여
FROM employees e
ORDER BY 2;

 

- from절 서브쿼리 (inline view)

Q) 사원의 급여가 자신이 속한 부서의 평균 급여보다 많은 사원 조회출력(from 절에 서브쿼리 사용)

SELECT e.first_name 이름,e.department_id 부서id, d.a 평균급여, e.salary 급여
FROM employees e, (SELECT department_id,AVG(salary) a FROM employees GROUP BY department_id) d
WHERE e.department_id=d.department_id AND e.salary>d.a
ORDER BY 2,4;

Q) 급여를 가장 많이 받는 상위 10명의 이름, 급여 출력(from 서브쿼리, rownum 사용)

SELECT first_name 이름, salary 급여
FROM (SELECT first_name, salary
        FROM employees
        ORDER BY salary desc)
WHERE rownum <= 10;

 

- HAVING 절 서브쿼리

Q) 80번 부서의 평균급여보다 평균급여가 작은 부서의  부서아이디와 해당 부서의 평균급여를 조회하시오

SELECT department_id 부서아이디,avg(salary) 평균급여 
FROM employees 
GROUP BY department_id
HAVING avg(salary)<(SELECT avg(salary) FROM employees WHERE department_id=80 GROUP BY department_id);

Q) 직책에 대한 평균급여 중 가장 큰 평균급여에 대한 직책과 해당 평균 급여를 조회 출력하시오

SELECT job_id,avg(salary) 
FROM employees 
GROUP BY job_id
HAVING avg(salary)=(SELECT max(avg(salary)) FROM employees GROUP BY job_id);

 

- create 절 서브쿼리

Q) 부서 아이디가 80번인 사원의 사원아이디와 급여가 들어있는 테이블을 작성하시오

CREATE TABLE employee_deptid_80 AS 
SELECT employee_id 사원아이디, salary 급여
FROM employees 
WHERE department_id=80;

SELECT * FROM employee_deptid_80;

 

- employees 테이블의 구조만 추출한 테이블 만들기

CREATE TABLE employees_2 as 
SELECT *
FROM employees 
WHERE 1=2;
--일부러 조건에 무조건 거짓 조건 주기
SELECT * FROM employees_2;

 

- insert 절 서브쿼리

Q) 앞에서 만든employee_deptid_80 테이블에 부서 아이디가 100인 사원을 insert 하기

SELECT * FROM employee_deptid_80; --추가하기전 34명
INSERT INTO employee_deptid_80 SELECT employee_id, salary FROM employees WHERE department_id=100; --VALUES 빼고 바로 서브쿼리
SELECT * FROM employee_deptid_80; --추가하고 40명

 

- update 절 서브쿼리

Q) Alexander 를 Daniel이 있는 부서로 옮기기

UPDATE employees SET department_id=(SELECT department_id FROM employees WHERE first_name='Daniel') WHERE first_name='Alexander';

 

- ALL, ANY

1. ALL

컬럼값 > ALL(서브쿼리) : 최대값보다 크면
컬럼값 >= ALL(서브쿼리) : 최대값보다 크거나 같으면
컬럼값 < ALL(서브쿼리) : 최소값보다 작으면
컬럼값 <= ALL(서브쿼리) : 최대값보다 작거나 같으면

 

Q) 부서 ID가 100인 부서 직원들의 급여(예: 12000이 최대라면) 보다 많은 급여를 받는 직원들 조회

SELECT first_name 이름, salary 급여
FROM employees
WHERE salary>ALL (SELECT salary FROM employees WHERE department_id=100);
--아래와 똑같음
SELECT first_name 이름, salary 급여
FROM employees
WHERE salary>(SELECT max(salary) FROM employees WHERE department_id=100);

 

2. ANY

컬럼값 > ANY : 최소값보다 크면
컬럼값 >= ANY : 최소값보다 크거나 같으면
컬럼값 < ANY : 최대값보다 작으면
컬럼값 <= ANY : 최대값보다 작거나 같으면
컬럼값 = ANY : IN과 같은 효과
컬럼값 != ANY : NOT IN과 같은 효과

 

Q) 부서 ID가 100인 부서 직원 중 급여가 최대인 직원보다 급여가 같거나 적은 직원 조회

SELECT first_name 이름, salary 급여
FROM employees
WHERE salary <= ANY (SELECT salary FROM employees WHERE department_id=100);
--아래와 같음
SELECT first_name 이름, salary 급여
FROM employees
WHERE salary<=(SELECT max(salary) FROM employees WHERE department_id=100);