[バックエンド]SQL#4

Sub Query

queryのquery

select単行単列(生成されたデータは1つのみ、または1つのみ)

複数行の複数列から

whereマルチラインマルチカラム

select

 -- 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;

from

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;

where

-- 평균 급여보다 많이 받는 사원
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;

特殊Query
switch case

-- #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;

decode

select employee_id, first_name, phone_number,
	decode( substr(phone_number, 1, 3),
		'515', '서울',
		'590', '부산',
		'650', '광주'
	)
from EMPLOYEES;

rownum

-- 급여 랭킹 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