query 내부의 query
select 단일 row 단일 column(산출되는 데이터는 한 개, 컬럼도 하나)
from 다중 row 다중 column
where 다중 row 다중 column
-- employee_id가 100인 사람의 이름을 함께 출력 select employee_id, first_name, (select first_name from EMPLOYEES where employee_id = 100) from EMPLOYEES; -- 전체 인원수를 같이 출력 select employee_id, first_name, (select count(*) from EMPLOYEES) from EMPLOYEES;
-- Error : 단일 행이 아니면 안 됨 select employee_id, first_name, (select first_name from EMPLOYEES where salary > 10000) from EMPLOYEES; -- Error : 단일 컬럼이 아니면 안 됨 select employee_id, first_name, (select first_name, salary from EMPLOYEES where employee_id = 100) from EMPLOYEES; -- Error : 단일 그룹의 그룹함수가 아닙니다. select employee_id, first_name, count(*) from EMPLOYEES;
select employee_id, salary from (select employee_id, first_name, salary from EMPLOYEES where department_id = 80) where salary >= 10000;
-- 부서번호 50번, 급여가 6000이상인 사원 select * from EMPLOYEES where salary >= 6000 and department_id = 50; -- 위와 동일 select employee_id from (select * from EMPLOYEES where department_id = 50) where salary >= 6000;
-- 업무별 급여의 합계, 인원수, 사원명, 급여 select e.employee_id, e.salary, e.job_id, j.job_id, j."급여의 합계", j.인원수 from EMPLOYEES e, (select job_id, sum(salary) as "급여의 합계", count(*) as 인원수 from EMPLOYEES group by job_id) j where e.job_id = j.job_id;
-- 평균 급여보다 많이 받는 사원 select first_name, salary from EMPLOYEES where salary > (select avg(salary) from EMPLOYEES)
-- 부서별로 가장 급여를 많이 받는 사원만을 출력 select department_id, salary, first_name from EMPLOYEES where (department_id, salary) in (select department_id, max(salary) from EMPLOYEES group by department_id) order by department_id;
-- #1 select employee_id, first_name, phone_number, case substr(phone_number, 1, 3) when '515' then '서울' when '590' then '부산' when '650' then '광주' else '기타' end from EMPLOYEES; -- #2 select employee_id, first_name, phone_number, case when substr(phone_number, 1, 3) = '515' then '서울' when substr(phone_number, 1, 3) = '590' then '부산' when substr(phone_number, 1, 3) = '650' then '광주' else '기타' end as 지역 from EMPLOYEES;
select employee_id, first_name, phone_number, decode( substr(phone_number, 1, 3), '515', '서울', '590', '부산', '650', '광주' ) from EMPLOYEES;
-- 급여 랭킹 top10을 구하라 -- 1. 급여의 순위 -> 정렬 -- 2. 번호 지정 -> rownum -- 3. 범위 지정 select * from (select rownum as rnum, employee_id, first_name, salary from (select employee_id, first_name, salary from EMPLOYEES order by salary desc) ) where rnum <= 20 and rnum >= 11