Advanced SQL - 조인, 서브쿼리 활용
※ JOIN시 참고 사항
첫 번째 : 테이블이 모델링 될때 대부분 1 : N의 관계가 됨
두 번째 : 두 테이블이 JOIN될 때 다른 테이블에 중복이 되는 값이 있는 경우가 있음
-> 회원 테이블에는 1명이 유일 하지만
-> 주문 정보 테이블에는 해당 회원이 여러번 나올 수 있음
세 번째 : 중복되는 값이 있는 테이블을 FROM절에 올려 두고 JOIN을 시도하면 해당 경우의 수 만큼의 행이 만들어 짐
-> M : N의 관계가 만들어져 M * N의 행이 만들어 짐
네 번째 : JOIN을 시도 할 때 조건절에 오는 조건은 무조건 두 테이블 관의 관계를 작성
다섯 번째 : JOIN문장이 실행 될 때 Subquery를 적절히 이용해서 테이블의 수를 먼저 GROUP BY를 시키면 성능을 올리고 오류를 줄여 줌
-> JOIN을 시도하는 행의 수는 필요에 따라 최대한 중복이 없는 행으로 만들어서 하는것이 성능면에서 높은 효율을 보임
-> 그냥 상황별로 잘 활용 하면 될듯... 문제는 경험인...
여섯 번째 : JOIN을 시도 할 때에는 두 테이블 간에 관계가 있어야 적절한 JOIN이 이루어 짐
-> 그렇지 않는다면 불필요한 JOIN문이 필요 하므로 성능 면에서 저하 됨
일곱 번째 : JOIN을 하고서 조건을 검사 하는지, 혹은 조건을 먼저 검사를 하고 JOIN을 할 것인지 생각 해야 함
1. dept, emp 테이블을 사용하여 sales 부서에 근무하고, 1981년 상반기(1월 ~ 6월)에 입사한 사원 정보를 검색 하시오. 이때 커미션을 합한 급여 (sal + comm)을 함께 검색하고 입사 일자 순으로 정렬 합니다.
해답 SQL문
SELECT
empno, ename, NVL(comm,0)+sal INCONE
FROM emp
WHERE
deptno IN (SELECT deptno FROM dept WHERE dname = 'SALES')
-- 만약, SALES라는 부서의 이름이 중복 되는 값이 있다면, '='보다는 'IN'을 사용 한다.
AND
hiredate BETWEEN
TO_DATE('19810101','YYYYMMDD')
AND
TO_DATE('19810701','YYYYMMDD')- 1/86400
-- 1981y/06h/30d 23h:59m:59s
ORDER BY hiredate;
-- 실행 결과 --
3 rows selected.
2. dept, emp 테이블을 사용하여 job이 'MANAGER'인 사원들의 부서 정보 및 사원 정보를 검색 하시오.
해답 SQL문
SELECT
D.deptno, D.dname,
E.ename, E.sal
FROM
dept D JOIN emp E
ON
D.deptno = E.deptno
AND
E.job = 'MANAGER';
-- 실행 결과 --
3 rows selected.
3. dept, emp 테이블을 사용하여, 소속 부서의 평균 급여보다 많은 급여를 받는 'MANAGER' 들의 부서 번호, 사원 번호, 사원 이름, 급여를 검색 하시오.
해답 SQL문
SELECT
D.deptno, D.dname,
E.empno, E.ename, E.sal
FROM
dept D
JOIN
emp E
ON
D.deptno = E.deptno
WHERE
E.job = 'MANAGER'
AND
E.sal > (SELECT AVG(sal) FROM emp WHERE deptno = E.deptno);
-- 평균 급여까지 확인하고 싶을 때는 Subquery를 JOIN에 이용한다.
SELECT
D.deptno, D.dname,
E.empno, E.ename, E.sal, A.AVG_SAL
FROM emp E
JOIN (SELECT deptno, AVG(sal) AVG_SAL FROM emp GROUP BY deptno) A
ON
E.deptno = A.deptno
AND
E.sal > A.AVG_SAL
AND
E.job = 'MANAGER'
JOIN dept D
ON
D.deptno = E.deptno;
-- 실행 결과 --
2 rows selected.
4. dept. emp 테이블을 사용하여 각 부서의 소속 사원 유무를 확인하는 검색 결과를 만드시오.
emp 컬럼은 소속 사원이 존재할 때 'YES', 아니면 'NO'를 검색합니다.
해답 SQL문
-- 검색을 하는 테이블은 emp도 하지만,
-- 정확하게 테이블 상에 올려서 보여 주는 내용은 dept테이블의 정보 뿐임
-- 따라서 FROM절에 올려 줄 테이블은 dept 뿐
SELECT
D.*,
NVL(( SELECT 'YES'
FROM DUAL
WHERE EXISTS ( SELECT *
FROM emp
WHERE deptno = D.deptno)),'NO') EMP
FROM dept D;
-- 실행 결과 --
4 rows selected.
5. countries, employees 테이블을 이용하여 'Canada'에서 근무 중인 사원 정보를 다음과 같이 검색하시오. 만약 추가적으로 필요한 테이블이 더 있다면 함께 사용합니다.
해결 SQL문
-- countries 테이블과 employees 테이블은 설계상 연결되는 부분이 없기 때문에
-- 불필요한 JOIN작업이 이루어 짐 (설계의 중요성)
-- 모델링이 잘못됨을 보여주는 문제
SELECT * FROM countries;
-- country_name = 'Canada'
SELECT * FROM departments;
-- location_id = E.location_id
SELECT * FROM employees;
-- first_name, last_name, salary, job_id
-- E.department_id = D.department_id
SELECT * FROM locations;
-- L.location_id = D.location_id
SELECT
E.first_name, E.last_name,
E.salary, E.job_id,
C.country_name
FROM employees E
JOIN departments D
ON
E.department_id = D.department_id
JOIN locations L
ON
D.location_id = L.location_id
JOIN countries C
ON
L.country_id = C.country_id
AND C.country_name = 'Canada';
-- 실행 결과 --
2 rows selected.
6. custmoers, orders, wishlist 테이블을 이용하여, wishlist(관심 상품)에 저장된 상품이있는 고객의 주문 합계(SUM(orders.order_total))를 검색 하시오.
참고, wishlist.deleted 컬럼이 'N'인 행이 현재 관심상품을 의미한다.
해답 SQL문
-- 참고 테이블
SELECT * FROM customers; -- 고객 정보
SELECT * FROM orders; -- 주문 내역
SELECT * FROM wishlist; -- 관심 상품
-- 검색된 내용은 customers와 orders에 저장된 내용만 있음
-- 따라서 FROM절에 올라올 테이블은 customers와 orders뿐이고,
-- wishlist테이블은 Subquery로서 사용된다.
SELECT
C.cust_id, C.cust_fname, C.cust_lname,
SUM(O.order_total)
FROM customers C
JOIN orders O
ON C.cust_id = O.cust_id
AND EXISTS (
SELECT cust_id
FROM wishlist
WHERE deleted = 'N'
AND cust_id = C.cust_id )
GROUP BY C.cust_id, C.cust_fname, C.cust_lname
ORDER BY 1;
-- 실행 결과 --
42 rows selected.
7. customers, orders, wishlist 테이블을 이용하여 고객별 주문 금액의 합계(SUM(order_total))와 관심상품 목록의 합계(SUM(unit_price*quantity))를 검색하시오.
참고, wishlist.deleted 컬럼이 'N'인 행이 현재 관심상품을 의미한다.
해답 SQL문
SELECT
C.cust_id, C.cust_fname, C.cust_lname,
O.ORD_TOT,
W.WISH_TOT
FROM customers C
JOIN (
SELECT
cust_id, SUM(order_total) ORD_TOT
FROM orders
GROUP BY cust_id ) O
-- JOIN을 시도하는 행의 개수를 중복이 없는 cust_id의 개수 만큼으로 줄임
ON
C.cust_id = O.cust_id
JOIN (
SELECT
cust_id, SUM(unit_price * quantity) WISH_TOT
FROM wishlist
WHERE deleted = 'N'
GROUP BY cust_id ) W
-- JOIN을 시도하는 행의 개수를 중복이 없는 cust_id의 개수 만큼으로 줄임
ON
C.cust_id = W.cust_id
ORDER BY 1;
-- 실행 결과 --
42 rows selected.
8. prods, sales 테이블을 이용하여 제품별 판매 수량(quantity_sold)의 합계를 검색하시오.
단, 판매되지 않은 제품이 존재한다면 해당 제품도 함께 표시
해답 SQL문
-- prods테이블에는 prod_id = 138이 존재 함
-- sales테이블에는 prod_id = 138이 없음
SELECT P.prod_id, P.prod_name, NVL(S.SOLD_SUM,0)
FROM prods P LEFT OUTER JOIN (
SELECT prod_id, SUM(quantity_sold) AS SOLD_SUM
FROM sales
GROUP BY prod_id) S
ON P.prod_id = S.prod_id;
-- 실행 결과 --
72 rows selected.
9. emp 테이블에서 1981년도에 입사한 사원들을 입사 월별로 인원수를 검색하시오.
단, 사원이 없는 월도 함께 출력
해답 SQL문
SELECT b.hire, NVL(a.cnt,0) CNT
FROM (
SELECT
TO_CHAR(hiredate,'YYYY/MM') hire, count(*) cnt
FROM emp
WHERE
hiredate
BETWEEN
TO_DATE('81/01/01','RR/MM/DD')
AND
TO_DATE('81/12/31','RR/MM/DD')
GROUP BY TO_CHAR(hiredate,'YYYY/MM')
) a,
(
SELECT
'1981/'||LPAD(LEVEL,2,0) hire
FROM dual
CONNECT BY LEVEL <= 12
) b -- 더미 데이터
WHERE
a.hire (+) = b.hire
ORDER BY 1 ;
-- 실행 결과 --
12 rows selected.
'Database - Oracle DB > 1주차(Database - Oracle DB)' 카테고리의 다른 글
| [SQL] Advanced SQL - 날짜 함수 활용 (0) | 2019.01.04 |
|---|---|
| [SQL] DB 스터디 - 06 (0) | 2019.01.03 |
| [SQL] DB 스터디 - 05 (0) | 2019.01.03 |
| [SQL] DB 스터디 - 04 (0) | 2019.01.02 |
| [SQL] Advanced SQL - 기본적인 SELECT 명령문 작성 (0) | 2019.01.02 |