[하위질의(SubQuery)]
: 하나의 쿼리에 다른 쿼리가 포함되는 구조, ()로처리
1) 단일행 서브쿼리(단일행반환) : > , < , >=, <= , <>
Main Query
Sub Query -----> 1 개결과
2) 다중행 서브쿼리(여러행반환) : in, any, all
Main Query
Sub Query -----> 여러개의 결과
< any : 비교대상중 최대값보다 작음
> any : 비교대상중 최소값보다 큼
(ex. 과장직급의 급여를 받는 사원조회)
= any : in연산자와 동일
< all : 비교대상중 최소값보다 작음
> all : 비교대상중 최대값보다 큼
(ex. 모든과장들의 직급보다 급여가 많은 사원조회)
3) 상관쿼리(correlated subquery)
: 메인쿼리에서 고려된 각 후보 행들에 대해 서브쿼리가 다른 결과를 반환해야하는경우
(메인쿼리에서 처리되는 각 행들의 값에 따라 응답이 달라져야하는 경우)에 유용하다
exists, not exists : 존재 여부에 따라 true,false을 반환
--------------------------------------------------------------
ex1) Neena사원의 부서명을 알아내시오
select department_id from employees where first_name='Neena';
select department_name from departments where department_id=90;
select department_name from departments
where department_id = (select department_id
from employees
where first_name='Neena');
ex2) Neena사원의 부서에서 Neena사원보다 급여를 많이 받는 사원들을 구하시오
(90) (17000)
last_name department_id salary
---------------------------------------
select last_name, department_id, salary
from employees
where department_id = (select department_id
from employees
where first_name='Neena')
and
salary > (select salary from employees where first_name='Neena');
[문제1] 최저급여를 받는 사원들의 이름과 급여를 구하시오
[문제2] 부서별 급여 합계 중 최대급여를 받는 부서의 부서명과 급여합계를 구하시오(group by)
DEPARTMENT_NAME SUM(SALARY)
------------------------- -----------
Sales 304500
ex3) Austin과 같은 부서이면서 같은 급여를 받는 사원들의
이름, 부서명, 급여를 구하시오(60부서, 4800달러)
select last_name, department_name, salary
from employees
left join departments using(department_id)
where department_id = (select department_id
from employees
where last_name='Austin')
and
salary = (select salary from employees where last_name='Austin');
ex4) 'ST_MAN' 직급보다 급여가 많은 'IT_PROG' 직급 직원들을 조회하시오
last_name job_id salary
-----------------------------
select last_name, job_id, salary
from employees
where job_id = 'IT_PROG'
and
salary >any(select salary from employees where job_id='ST_MAN');
[문제3] 'IT_PROG' 직급 중 가장 많이 받는 사원의 급여보다 더 많은 급여를 받는 'FI_ACCOUNT' 또는 'SA_REP' 직급 직원들을 조회하시오
조건1) 급여 순으로 내림차순 정렬하시오
조건2) 급여는 세자리 마다 콤마(,) 찍고 화폐단위 '원’을 붙이시오
조건3) 타이틀은 사원명, 업무ID, 급여로 표시하시오
사원명 업무ID 급여
--------------------------
:
ex5) 'IT_PROG'와 같은 급여를 받는 사원들의 이름,업무ID,급여를 전부 구하시오
select last_name, job_id, salary
from employees
where salary in(select salary from employees where job_id='IT_PROG');
ex6) 전체직원에 대한 관리자와 직원을 구분하는 표시를 하시오(in, not in이용)
사원번호 이름 구분
-------------------------------------
100 King 관리자
★ 방법1(in 연산자)
select employee_id as 사원번호, last_name as 이름,
case
when employee_id in(select manager_id from employees)
then '관리자'
else '직원'
end as 구분
from employees
order by 3,1;
★ 방법2(union, in, not in 연산자)
select employee_id as 사원번호, last_name as 이름, '관리자' as 구분
from employees
where employee_id in(select manager_id from employees)
union
select employee_id 사원번호, last_name 이름, '직원' as 구분
from employees
where employee_id not in(select manager_id from employees where manager_id is not null)
order by 3,1;
★ 방법3(상관쿼리이용)
-- 메인쿼리 한행을 읽고 해당값을 서브쿼리에서 참조하여 서브쿼리결과에 존재하면 true를 반환
select employee_id as 사원번호, last_name as 이름, '관리자' as 구분
from employees e
where exists(select null from employees where e.employee_id=manager_id)
union
select employee_id as 사원번호, last_name as 이름, '직원' as 구분
from employees e
where not exists(select null from employees where e.employee_id=manager_id)
order by 3,1;
[문제4] 자기 업무id(job_id)의 평균급여를 받는 직원들을 조회하시오
조건1) 평균급여는 100단위 이하 절삭
급여는 세자리마다 콤마, $표시
조건2) 사원이름(last_name), 업무id(job_id), 직무(job_title), 급여(salary) 로 표시하시오
조건3) 급여순으로 오름차순 정렬하시오
ex7) group by rollup : a,b별 집계(Subtotal 구하기)
부서별, 직무ID별 급여평균구하기(동일부서에 대한 직무별 평균급여)
조건1) 반올림해서 소수 2째자리까지 구하시오
조건2) 제목은 Job_title, Department_name, Avg_sal로 표시하시오
select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by rollup(department_name, job_title);
ex8) group by cube : a별 집계 또는 b별 집계
부서별, 직무ID별 급여평균구하기(부서를 기준으로 나타내는 평균급여)
select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by cube(department_name, job_title);
ex9) group by grouping sets
직무별 평균급여와 전체사원의 평균급여를 함께 구하시오
select job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by grouping sets((job_title),()); ← () All Rows의 역활
'JAVA 개발 공부 정보' 카테고리의 다른 글
SQL-수업4 (0) | 2018.03.23 |
---|---|
SQL-수업자료3 (0) | 2018.03.20 |
SQL 연습 (0) | 2018.03.15 |
Chapter04-스프링JDBC (0) | 2018.03.15 |
Oracle-SQL (0) | 2018.03.14 |