0230324 SQL
SQL hr 데이터 언락 방법
- dba > alter user hr account unlock identified by 1234;
금요일이다아!
[문제75] 월별 연도별 급여 총액을 아래 결과처럼 출력해주세요.
월 2001년 2002년 2003년 2004년
--- -------------------------------
01
02
...
12
SELECT to_char(hire_date, 'mm') 월,
sum(decode(to_char(hire_date, 'yyyy'),'2001',salary))"2001년",
sum(decode(to_char(hire_date, 'yyyy'),'2002',salary))"2002년",
sum(decode(to_char(hire_date, 'yyyy'),'2003',salary))"2003년",
sum(decode(to_char(hire_date, 'yyyy'),'2004',salary))"2004년",
sum(decode(to_char(hire_date, 'yyyy'),'2005',salary))"2005년",
sum(decode(to_char(hire_date, 'yyyy'),'2006',salary))"2006년",
sum(decode(to_char(hire_date, 'yyyy'),'2007',salary))"2007년",
sum(decode(to_char(hire_date, 'yyyy'),'2008',salary))"2008년"
FROM employees
GROUP BY to_char(hire_date, 'mm')
ORDER BY 1;
SELECT *
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, sum(salary) sumsal
FROM employees
GROUP BY to_char(hire_date, 'mm'), to_char(hire_date, 'yyyy'))
PIVOT(max(sumsal) FOR 연도 IN (‘2001’as“2001”,2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1;
SELECT *
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1;
SELECT 월, "2001"
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1;
SELECT 월, nvl("2001",0)
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1;
SELECT
FROM
(SELECT
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1)
UNPIVOT(급여총액 FOR 연도 IN ("2001","2002","2003","2004","2005","2006","2007","2008"));
=> 컬럼 이름 숫자일 경우 큰 따옴표로 표현
SELECT 연도, 월, 급여총액
FROM
(SELECT *
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1)
UNPIVOT(급여총액 FOR 연도 IN ("2001","2002","2003","2004","2005","2006","2007","2008"))
ORDER BY 1;
SELECT 연도, 월, 급여총액
FROM
(SELECT *
FROM
(SELECT to_char(hire_date, 'mm') 월, to_char(hire_date, 'yyyy') 연도, salary
FROM employees)
PIVOT(sum(salary) FOR 연도 IN ('2001'as"2001",2002,2003,2004,2005,2006,2007,2008))
ORDER BY 1)
UNPIVOT INCLUDE NULLS(급여총액 FOR 연도 IN ("2001","2002","2003","2004","2005","2006","2007","2008"))
ORDER BY 1;
다중열 서브쿼리
-쌍비교
-비쌍비교
SELECT절 서브쿼리
ORDER BY절 서브쿼리
집합 연산자
■ 다중열 서브쿼리
-
쌍비교
SELECT *
FROM employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
FROM employees
WHERE first_name = 'John'); -
비쌍비교
SELECT *
FROM employees
WHERE manager_id IN (SELECT manager_id
FROM employees
WHERE first_name = 'John')
AND department_id IN (SELECT department_id
FROM employees
WHERE first_name = 'John');
=> 두 개 결과 다름(쌍비교 18개, 비쌍비교 23개)
[문제76] commission_pct null이 아닌 사원들의 department_id, salary와 일치하는 사원들의 정보를 출력해주세요.
1) 쌍비교
SELECT *
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, salary
FROM employees
WHERE commission_pct IS NOT NULL);
=> 서브쿼리 수행시 35개 나옴, 전체 쿼리문 수행시 34개 나옴(부서_id NULL인 1개 누락)
SELECT *
FROM employees
WHERE (nvl(department_id,0), salary) IN (SELECT nvl(department_id,0), salary
FROM employees
WHERE commission_pct IS NOT NULL);
2) 비쌍비교
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE commission_pct IS NOT NULL)
AND salary IN (SELECT salary
FROM employees
WHERE commission_pct IS NOT NULL);
SELECT *
FROM employees
WHERE nvl(department_id,0) IN (SELECT nvl(department_id,0)
FROM employees
WHERE commission_pct IS NOT NULL)
AND salary IN (SELECT salary
FROM employees
WHERE commission_pct IS NOT NULL);
[문제77] location_id가 1700 위치에 있는 사원들의 salary, commission_pct가 일치하는 사원들의 정보를 출력해주세요.
1) 쌍비교
SELECT *
FROM employees
WHERE (salary, commission_pct) IN (SELECT e.salary, e.commission_pct
FROM employees e, departments d
WHERE d.location_id = 1700
AND e.department_id = d.department_id);
SELECT *
FROM employees
WHERE (salary, nvl(commission_pct,0)) IN
(SELECT salary, nvl(commission_pct,0)
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700));
=> 위치id 1700 18(21??)개 데이터, null값 있어서 비교불가, 공집합 나옴
SELECT *
FROM employees
WHERE (salary, nvl(commission_pct,0)) IN (SELECT e.salary, nvl(e.commission_pct,0)
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700);
=> 36개 나옴
2) 비쌍비교
SELECT *
FROM employees
WHERE salary IN (SELECT e.salary
FROM employees e, departments d
WHERE d.location_id = 1700
AND e.department_id = d.department_id)
AND commission_pct IN (SELECT e.commission_pct
FROM employees e, departments d
WHERE d.location_id = 1700
AND e.department_id = d.department_id);
SELECT *
FROM employees
WHERE salary IN (SELECT e.salary
FROM employees e, departments d
WHERE d.location_id = 1700
AND e.department_id = d.department_id)
AND nvl(commission_pct,0) IN (SELECT nvl(e.commission_pct,0)
FROM employees e, departments d
WHERE d.location_id = 1700
AND e.department_id = d.department_id);
=> 36개 나옴
SELECT e.employee_id, e.department_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY 2,3;
■ SELECT절의 서브쿼리(SCALAR SUBQUERY)
- SELECT절의 서브쿼리
- 전제조건 : 한 행에서 정확히 하나의 열 값만 반환하는 서브쿼리를 의미함
- 동일한 입력값이 들어오면 수행 회수를 최소화할 수 있는 로직을 구현함(캐쉬)
- 키 값이 없는 데이터가 입력되면 NULL값으로 리턴함(OUTER JOIN에 대한 기법처럼 결과가 출력됨)
SELECT employee_id, department_id, (SELECT department_name
FROM departments
WHERE department_id = e.department_id)
FROM employees e └----미지수----┘
ORDER BY 2;
=> 조인은 107번 IO(?) 돌리는 데 반해 스칼라 서브쿼리는 department_id 12번만 돌림
SELECT employee_id, department_id, (SELECT department_name||manager_id
FROM departments
WHERE department_id = e.department_id“부서이름 부서장”)
FROM employees e
ORDER BY 2;
[문제78] 부서이름별 급여의 총액, 평균을 구하세요.
1) 조인 이용
SELECT d.department_name, sum(e.salary), avg(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
2) (과정; INLINE VIEW 이용)
SELECT department_id, salary
FROM employees
ORDER BY 1;
SELECT department_id, sum(salary), avg(salary)
FROM employees
GROUP BY department_id
ORDER BY 1; => 12개
SELECT d.department_name, e.sumsal, e.avgsal
FROM (SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
FROM employees
GROUP BY department_id
ORDER BY 1) e, departments d
WHERE e.department_id = d.department_id; => 11개
SELECT d.department_name, e.sumsal, e.avgsal
FROM (SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
FROM employees
GROUP BY department_id
ORDER BY 1) e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id; => 12개
SELECT d.department_name, e.sumsal, e.avgsal
FROM (SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
FROM employees
GROUP BY department_id
ORDER BY 1) e, departments d
WHERE e.department_id = d.department_id(+); => 12개
3) SCALAR SUBQUERY 이용
SELECT department_name, (SELECT sum(salary)
FROM employees
WHERE department_id = d.department_id) sumsal,
(SELECT avg(salary)
FROM employees
WHERE department_id = d.department_id) avgsal
FROM departments d;
=> 서브쿼리 단일행 단일식만 넣어야 함
=> 이 경우 동일 부서 id 입력될 시 두 번 돌아가야 함(큰 테이블의 경우 오래 걸림)
▽▽▽SELECT department_name, (SELECT '총액 : '||sum(salary)||' 평균 : '||avg(salary)
FROM employees
WHERE department_id = d.department_id) sum_avg_sal
FROM departments d;
(결과 테이블)Administration 총액 : 4400 평균 : 4400
Marketing 총액 : 19000 평균 : 9500
Purchasing 총액 : 24900 평균 : 4150
Human Resources 총액 : 6500 평균 : 6500
Shipping 총액 : 156400 평균 : 3475.55555555555...
IT 총액 : 28800 평균 : 5760
Public Relations 총액 : 10000 평균 : 10000
Sales 총액 : 304500 평균 : 8955.88235294117...
=> 여기서 총액, 평균을 다른 열로 만들고 싶다면? LPAD, RPAD 이용
SELECT department_name, (SELECT lpad(sum(salary),10))||lpad(avg(salary),10)
FROM employees
WHERE department_id = d.department_id) sum_avg_sal
FROM departments d;
SELECT department_name, substr(sum_avg_sal,1,10) sum_sal,substr(sum_avg_sal,11) avg_sal
FROM (SELECT department_name, (SELECT lpad(sum(salary),10))||lpad(avg(salary),10)
FROM employees
WHERE department_id = d.department_id) sum_avg_sal
FROM departments d)
WHERE sum_avg_sal IS NOT NULL;
[문제79] 사원들의 last_name, salary, grade_level을 출력해주세요.
1) join 이용
1-1) 오라클 전용
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
1-2) ANSI 표준
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
2) scalar subquery 이용
SELECT last_name, salary, (SELECT grade_level
FROM job_grades
WHERE e.salary BETWEEN lowest_sal AND highest_sal) gd_lvl
FROM employees e
ORDER BY 2;
SELECT last_name, salary, (SELECT grade_level
FROM job_grades
WHERE e.salary >= lowest_sal
AND e.salary <= highest_sal) gd_lvl
FROM employees e
ORDER BY 2;
(예제) 사원들의 employee_id, last_name을 출력해주세요. 단, department_name을 기준으로 오름차순 정렬해주세요.
SELECT e.employee_id, e.last_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY d.department_name;
● 서브쿼리 만들 수 있는 절
1) select
2) from
3) where
4) having
5) order by
■ ORDER BY절 서브쿼리(SCALAR SUBQUERY)
- 단일컬럼
SELECT e.employee_id, e.last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments
WHERE e.department_id = department_id) desc;
■ 집합 연산자
- 합집합(UNION, UNION ALL), 교집합(INTERSECT), 차집합(MINUS)
1) SELECT절의 컬럼의 개수가 일치해야 함
2) 첫 번째 SELECT절에 대응이 되는 두 번째 SELECT절 컬럼의 데이터 타입이 일치해야 함
3) UNION, INTERSECT, MINUS 연산자는 중복을 제거함(정렬이 발생함)
4) 집합 연산자에서 ORDER BY절은 무조건 제일 마지막에 기술해야 함(위치 표기법 지향)
5) ORDER BY절에는 첫 번째 SELECT절의 컬럼이름, 별칭, 위치표기법 사용 가능
- 합집합(UNION, UNION ALL)
- UNION: 합치되 동일한 원소를 하나로 출력(중복 제거)
- UNION ALL: 중복 포함하여 합집합 출력
①
SELECT employee_id, job_id, salary
FROM employees;
②
SELECT employee_id, job_id
FROM job_history
①+②
SELECT employee_id, job_id, salary => SALARY는 숫자임!(UNION시 타입 일치)
FROM employees
UNION
SELECT employee_id, job_id, NULL => 이 자리는 0이라고 해도 됨(숫자여야 함)
FROM job_history;
SELECT employee_id, job_id, to_char(salary) => 문자로 변형
FROM employees
UNION
SELECT employee_id, job_id, '무급' => 문자로 표현
FROM job_history;
SELECT employee_id, job_id, salary
FROM employees
UNION ALL => 중복데이터 모두 나옴(SELECT 절의)
SELECT employee_id, job_id, 0
FROM job_history;
SELECT employee_id, job_id, salary
FROM employees
UNION ALL
SELECT employee_id, job_id, 0
FROM job_history
ORDER BY 3; => 제일 마지막에 기술
==============================================
SELECT employee_id
FROM employees
UNION => 107개 출력
SELECT employee_id
FROM job_history;
SELECT employee_id
FROM employees
UNION ALL => 117개 출력
SELECT employee_id
FROM job_history;
- 교집합(INTERSECT)
- 양 쪽 다 들어있는 것을 출력
SELECT employee_id, job_id
FROM employees
INTERSECT => 2개 출력(부서 이동 후 다시 돌아온 경우)
SELECT employee_id, job_id
FROM job_history;
- 차집합(MINUS)
- A MINUS B, B MINUS A 결과 다르므로 순서 주의
SELECT employee_id, job_id
FROM employees
MINUS => 부서 이동X인 사원들 정보 출력
SELECT employee_id, job_id
FROM job_history;
SELECT employee_id, job_id
FROM job_history
MINUS
SELECT employee_id, job_id
FROM
employees;
[문제80] job_id를 한 번이라도 바꾼 사원들의 정보를 출력하세요.
SELECT *
FROM employees
WHERE job_id를 한 번이라도 바꾼 사웜;
SELECT employee_id, job_id
FROM job_history
MINUS
SELECT employee_id, job_id
FROM employees;
SELECT *
FROM employees
WHERE employee_id IN (SELECT employee_id
FROM job_history);
SELECT *
FROM employees
WHERE employee_id IN (SELECT employee_id
FROM employees
intersect
SELECT employee_id
FROM job_history);
SELECT *
FROM employees e
WHERE EXISTS (SELECT 'x'
FROM job_history
WHERE employee_id = e.employee_id);
=> 교집합 만들 때 exists 연산자를 사용 선호
[문제81] job_id를 한 번도 바꾸지 않은 사원들의 정보를 출력하세요.
1) 집합 연산자 사용
SELECT *
FROM employees
WHERE employee_id IN (SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history);
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT employee_id
FROM employees
INTERSECT
SELECT employee_id
FROM job_history);
2) NOT EXISTS 사용
SELECT *
FROM employees e
WHERE NOT EXISTS (SELECT 'x'
FROM job_history
WHERE employee_id = e.employee_id);
[문제82] 부서가 소재하지 않은 국가의 리스트가 필요합니다. country_id, country_name 출력해주세요.
SELECT c.country_id, c.country_name
FROM countries c, locations l, departments d
WHERE d.location_id = l.location_id => 부서가 소재한 국가는
AND l.country_id = c.country_id; US, CA, UK, DE 네 국가
1) 집합 연산자 사용
SELECT l.country_id, c.country_name
FROM locations l, countries c
WHERE location_id NOT IN (SELECT location_id
FROM locations
INTERSECT
SELECT location_id
FROM departments)
AND l.country_id = c.country_id;
SELECT country_id, country_name
FROM countries
MINUS
SELECT c.country_id, c.country_name
FROM countries c, locations l, departments d
WHERE d.location_id = l.location_id
AND l.country_id = c.country_id; => 동일한 테이블 돌아감
2) NOT EXISTS 연산자 사용
SELECT l.country_id, c.country_name
FROM locations l, countries c
WHERE NOT EXISTS (SELECT 'x'
FROM departments
WHERE location_id = l.location_id)
AND l.country_id = c.country_id;
SELECT country_id, country_name
FROM countries c
WHERE NOT EXISTS(SELECT 'x'
FROM locations l, departments d
WHERE d.location_id = l.location_id
AND l.country_id = c.country_id);
