-- SUB QUERY
-- Query 안의 Query
-- 한개의 행(row)에서 결과 값이 반환되는 Query
-- SELECT 단일 ROW 단일 COLUMN SELECT FIRST_NAME, SALARY FROM EMP
-- SELECT 단일 ROW 단일 COLUMN (나오는 데이터가 한개, 컬럼 한개)
-- FROM 다중 ROW 다중 COLUMN
-- WHERE 다중 ROW 다중 COLUMN --> 제일 많이 사용
/*
안된다
select employee_id, (select first_name, last_name from employees) -- 다중컬럼 사용X
select employee_id, (select first_name from employees) -- 다중 ROW 사용X
*/
-- select
--반드시 값은 하나가 나와야하는게 select문의 다중 쿼리 규칙
SELECT employee_id, first_name,
(Select salary from employees where employee_id = 100),
-- (SELECT sum(salary)
-- FROM employees)
(SELECT count(*)
FROM employees)
FROM employees;
-- From
-- 왠만큼 사용한다.
SELECT employee_id, first_name, department_id
FROM (SELECT employee_id, first_name, department_id
FROM employees
where department_id = 20);
-- 부서번호가 20번, 급여가 6000이상의 사원
SELECT
e.employee_id, e.salary
FROM (SELECT
employee_id , salary
FROM employees e
where department_id = 20 ) e
where salary > 6000;
SELECT a.employee_id, a.first_name,
a.job_id, b.job_id,
b."급여합계", b."인원수"
FROM employees a, (SELECT job_id, sum(salary) as "급여합계", count(*) as "인원수"
FROM employees
GROUP BY job_id) b
where a.job_id = b.job_Id;
-- WHERE
-- 가장 많이 사용
SELECT first_name, sal
FROM employees
where salary > ( select avg(salary)
from employees );
SELECT first_name, job_id, department_id
FROM employees
--where job_id IN('IT_PROG');
where job_id IN(SELECT job_id FROM employees
where department_id = 90);
-- 부서별로 가장 급여를 적게 받는 사원과 같은 급여를 받는 사원
SELECT first_name, salary, department_id, job_id
FROM employees
where salary in(select min(salary)
from employees
GROUP BY department_id); --최하 급여
-- 부서별로 가장 급여를 적게 받는 사원의 정보
SELECT department_id, first_name, salary
FROM employees
where (salary, department_id) /*부서에서 이 급여*/ IN (SELECT min(salary), department_id
from employees
group by department_id)
order by department_id asc;