23일차 SQL
23일차
220901
Review
GRANT
권한을 부여한다. / ON TO
GRANT 권한 ON Table_name TO Account;
REVOKE
부여된 권한을 회수한다. / ON FROM
REVOKE 권한 ON Table_name FROM Account
Set Operator :
집합 연산자
UNION (합집합)
중복값을 제거 후 병합
select * from t1 -- {1, 2, 3}
union -- 합집합(중복 값을 합쳐버림)
select * from t2; -- {2, 3, 4}
RESULT
중복값을 제거하지 않고 병합
select * from t1 --{1, 2, 3}
union all -- 중복값을 합치지 않고 전체 출력
select * from t2; -- {2, 3, 4}
RESULT
주의사항
- UNION 병합시에 컬럼의 수가 동일하지 않다면 에러가 발생한다.
- query block has incorrect number of result columns
- 두 테이블의 병합하려는 컬럼의 데이터 타입이 동일해야 한다.
- expression must have same datatype as corresponding expression
INTERSECT (교집합)
중복값만 병합
select * from t1 -- {1, 2, 3}
intersect -- 교집합
select * from t2; -- {2, 3, 4}
RESULT
MINUS (차집합)
첫번째 테이블 조회결과에서 두 번째 테이블 조회 결과와 중복되는 값을 제외하고 남는 결과만 생성
select * from t1 -- {1, 2, 3}
minus -- 차집합 (t1 - t2)
select * from t2; -- {2, 3, 4}
RESULT
SubQuery
쿼리 내부에서 사용되는 쿼리
- 단일행 단일열 서브쿼리
- 다중행 서브쿼리
- 다중열 서브쿼리
- 다중행 다중열 서브쿼리
단일행 단일열 SubQuery
예시
전지연의 매니저(사수) 정보 출력
SELECT *
FROM employee
WHERE emp_id = (SELECT manager_id
FROM employee
WHERE emp_name = '전지연'); -- 서브쿼리의 결과물
평균 급여보다 월급을 많이 받는 직원의 사번, 이름, 급여 출력
select emp_id,
emp_name,
salary
from employee
where salary >= (select avg(salary)
from employee);
QUIZ
윤은해와 급여가 같은 사원들을 검색하여 사원번호 이름 급여를 출력 (윤은해 제외)
select emp_id,
emp_name,
salary
from employee
where salary = (select salary
from employee
where emp_name = '윤은해')
AND emp_name Not like '윤은해';
employee 테이블에서 급여가 가장 많은 직원과 가장 적은 직원의 이름과 급여를 출력하십시오
SELECT emp_name,
salary
FROM employee
WHERE salary IN ( (SELECT Max(salary)
FROM employee), (SELECT Min(salary)
FROM employee) );
D1, D2 부서에서 근무하는 직원들 중 급여가 D5 부서 평균 급여보다 많은 사람들 출력
select emp_id,
emp_name,
salary,
dept_code
from employee
where dept_code in ('D1', 'D2')
and salary > (select avg(salary)
from employee
where dept_code = 'D5');
다중 행 서브쿼리
결과물(튜플, 행)이 두 개 이상 나옵니다. ‘=’ 기호 사용을 지양하고 in , not in 사용
송종기 또는 박나라 속한 부서에 속해있는 모든 직원의 정보
SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE IN (SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME IN ('박나라','송종기'));
차태연, 전지연 직원의 급여 등급과 동일한 등급을 가진 직원들의 사원명, 직급명, 급여 등급
SELECT emp_name,
job_name,
sal_level
FROM employee e
LEFT JOIN job j
ON e.job_code = j.job_code
WHERE sal_level IN (SELECT sal_level
FROM employee
WHERE emp_name IN ( '차태연', '전지연' ))
AND emp_name NOT IN ( '차태연', '전지연' )
ORDER BY 3,1;
직급명이 대표, 부사장도 아닌 모든 직원의 이름, 부서명, 직급코드를 부서명으로 정렬하여 출력
-- USE MULTIPLE JOIN
SELECT emp_name,
dept_title,
e.job_code
FROM employee e
LEFT JOIN job j
ON e.job_code = j.job_code
LEFT JOIN department
ON dept_code = dept_id
WHERE job_name NOT IN ( '대표', '부사장' );
-------------
-- USE SUBQUERY
SELECT emp_name,
dept_title,
job_code
FROM employee
LEFT JOIN department
ON dept_code = dept_id
WHERE job_code NOT IN (SELECT job_code
FROM job
WHERE job_name IN ( '대표', '부사장' ))
ORDER BY 2;
다중행 다중열
비교 하려는 두 개의 컬럼을 괄호로 묶어준다.
박나라 차태연의 부서가 같은 동기 구하기
SELECT emp_name,
dept_code,
job_code
FROM employee
WHERE ( dept_code, job_code ) IN (SELECT dept_code,
job_code
FROM employee
WHERE emp_name IN ( '박나라', '차태연'));
직급별로 급여가 가장 작은 사람의 이름과 급여를 출력하기
SELECT emp_name,
salary,
job_code
FROM employee
WHERE ( job_code, salary ) IN (SELECT job_code,
Min(salary)
FROM employee
GROUP BY job_code);
Correlated Subquery (상관 서브쿼리)
Subquery 에서 Mainquery 요소를 가져가 사용한 후 결과를 반환하는 상태
컬럼에서 서브쿼리를 사용
select emp_name,
(select dept_title
from department
where dept_id = dept_code) 부서명 -- DEPT_CODE = mainquery
from employee;
Inline View
from 절에서 subquery 를 사용한 경우
select emp_name,
email
from (select emp_id,
emp_name,
phone,
email
from employee);
TOP - N 분석 (순위 기법)
rank() over(정렬 기준(ORDER BY))
사원 급여 순위 만들기.
select emp_id,
emp_name,
salary ,
rank() over(order by salary desc) 급여순위
from employee;
-- 공동순위시 한 순위를 스킵한다.
dense_rank() -- 순위를 스킵시키지 않고 밀집시킴
select emp_id,
emp_name,
salary ,
dense_rank() over(order by salary desc) 급여순위
from employee;
---
row_number() -- 행에 번호 부여 (공동순위 존재하지않음)
select emp_id,
emp_name,
salary ,
row_number() over(order by salary desc) 급여순위
from employee;
rank() over()
dense_rank()
row_number()
Inline-view 에 top-n 분석 사용하기
- 급여순위 5위에서 10위까지 출력
select *
from (select emp_name,
emp_id,
salary,
row_number() over(order by salary desc) 급여순위
from employee)
where 급여순위 between 5 and 10;
SQL 실행순서에 유의해야 하는 문제.
과제
--문제1
--기술지원부에 속한 사람들의 사람의 이름,부서코드,급여를 출력하시오
select emp_name,
dept_code,
salary
from employee
where dept_code = (select dept_id
from department
where dept_title = '기술지원부');
--문제2
--기술지원부에 속한 사람들 중 가장 연봉이 높은 사람의 이름,부서코드,급여를 출력하시오
select emp_name,
dept_code,
salary
from employee
where (dept_code, salary) in (select dept_code,
max(salary)
from department join employee
on dept_id = dept_code
where dept_title = '기술지원부'
group by dept_code);
--문제3
--매니저가 있는 사원중에 월급이 전체사원 평균을 넘긴 직원의
--사번,이름,매니저 이름,월급(만원단위부터)을 구하시오
-- * 단, JOIN을 이용하시오
select a.emp_id 사번,
a.emp_name 이름,
b.emp_name "매니저 이름",
a.salary 월급
from employee a join employee b
on a.manager_id = b.emp_id
where a.salary > (select avg(salary) from employee);
--문제4
--각 직급마다 급여 등급이 가장 높은 직원의 이름, 직급코드, 급여, 급여등급 조회
select emp_name,
job_code,
salary,
sal_level
from employee
where (job_code,sal_level) in (select job_code,
min(sal_level)
from employee
group by job_code)
order by 2,4;
-- 문제5
-- 부서별 평균 급여가 2200000 이상인 부서명, 평균 급여 조회
-- 단, 평균 급여는 소수점 버림
SELECT 부서명,
To_char("평균급여", 'L999,999,999') "평균급여"
FROM (SELECT dept_title 부서명,
Floor(Avg(salary)) 평균급여
FROM department JOIN employee
ON dept_code = dept_id
GROUP BY dept_title)
WHERE 평균급여 >= 2200000;
--문제6
--직급의 연봉 평균보다 적게 받는 여자사원의
--사원명,직급코드,부서코드,연봉을 이름 오름차순으로 조회하시오
--연봉 계산 => (급여+(급여*보너스))*12
SELECT emp_name,
job_code,
dept_code,
( salary + ( salary * Nvl(bonus, 0) ) ) * 12 연봉
FROM employee a
WHERE ( salary + ( salary * Nvl(bonus, 0) ) ) * 12 < (SELECT Avg(( salary + ( salary * Nvl(bonus, 0) ) ) * 12)
FROM employee b
WHERE a.job_code = b.job_code)
AND Substr(emp_no, 8, 1) = 2
order by 1;