이번주 부터 학원 내에서 DB 스터디를 시작하게되었고, 어쩌다 보니 내가 이끌어서 하게되었는데, 맡은 만큼 좋은 방향으로 이끌도록 노력하자
목표는 푼 문제를 풀이형식으로 글을 쓰고 블로그에 올려서 정리하자
테이블은 오라클 샘플 데이터다.
문제1
오더 STATUS가 'Canceled' 상태인 사원들이 가장 많이 모시고 있는 매니저를 출력하시오
출력: 사원번호,이름(퍼스트+라스트),이메일,JOB_TITLE
SELECT E.EMPLOYEE_ID,E.FIRST_NAME || E.LAST_NAME AS FULL_NAME,E.EMAiL,E.job_title
FROM (SELECT E.MANAGER_ID,COUNT(*),
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN ORDERS O
ON EMPLOYEE_ID = SALESMAN_ID
AND O.STATUS IN 'Canceled'
GROUP BY E.MANAGER_ID ) M RIGHT OUTER JOIN EMPLOYEES E
ON M.MANAGER_ID = E.EMPLOYEE_ID
WHERE M.RNK = 1
;문제에서 오더 STATUS 'Cancled' 상태인 사원들 확인 하기 위해 EMPLOYEES 테이블과 ORDERS 테이블을 이너조인해 뷰를 만들고, 제일 핵심은 오더 바이를 MANAGER_ID로 해서 카운트를 한다. 그다음 매니저 정보를 위해서 다시한번 EMPLOYEES 테이블 붙여서 마지막 출력을 뽑는다.
문제2
주문이 취소된 물건이 가장 많이나온 웹사이트 고객의 풀네임과 글자수와
글자수가 같은 사람들을 직원들중에서 출력하시오
출력필드: 사원번호, 사원풀네임, 직업
SELECT E.EMPLOYEE_ID,(E.FIRST_NAME || E.LAST_NAME) AS RESULT,E.JOB_TITLE
FROM EMPLOYEES E INNER JOIN (SELECT LENGTH(C.FIRST_NAME || C.LAST_NAME) AS NL
FROM CONTACTS C INNER JOIN (SELECT C.NAME,COUNT(*),C.CUSTOMER_ID,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM ORDERS O INNER JOIN CUSTOMERS C
ON O.CUSTOMER_ID = C.CUSTOMER_ID
AND O.STATUS IN 'Canceled'
GROUP BY C.NAME,C.CUSTOMER_ID) W
ON C.CUSTOMER_ID = W.CUSTOMER_ID
WHERE W.RNK = 1) W2
ON 1=1
WHERE W2.NL = LENGTH(E.FIRST_NAME || E.LAST_NAME)
;처음에 취소된 물건이 가장 많이 나온 웹사이트를 뽑기위해 ORDERS테이블과 CUSTOMERS 테이블 조인 시키고, 그다음 GROUP BY를 NAME,CUSTOMER_ID로 해서 카운트를 해서 결과를 가장 많이 나온 웹사이트를 얻는다. 그 다음 웹사이트의 고객의 풀네임를 얻어야 하기 때문에 CONTACTS 테이블 조인하고 SELECT에서 고객의 풀네임를 '||' 활용해서 만들고 LENGTH로 글자수를 얻는다.
마지막으로 고객의 글자수와 같은 사원정보가 필요하므로 EMPLOYEES 테이블을 붙이고 WHERE에서 글자수를 조건으로 넣어 SELECT에서 출력에 필요한 컬럼들은 뽑아서 정답을 만든다.
문제3
제품ID가 20번대이면서 수량이 200개이상인 제품중 마진(판매가격-표준원가)이 가장 큰 제품과 가장 작은 제품의 차이를 구하고
그 차이보다 큰 판매가격을 가진 제품들중 가장 많은 카테고리의 카테고리 이름을 구하시오.
SELECT *
FROM (SELECT PC.CATEGORY_NAME,RANK()OVER(ORDER BY COUNT(*) DESC) AS RNK2
FROM PRODUCT_CATEGORIES PC INNER JOIN (SELECT p2.CATEGORY_ID, CASE WHEN P2.LIST_PRICE > R2.CHA
THEN P2.CATEGORY_ID
ELSE NULL
END AS BIG
FROM PRODUCTS P2 INNER JOIN (SELECT MAX(R.MARGIN)-MIN(R.MARGIN) AS CHA
FROM (SELECT P.PRODUCT_ID,I.QUANTITY,(LIST_PRICE - STANDARD_COST) AS MARGIN
FROM PRODUCTS P INNER JOIN INVENTORIES I
ON P.PRODUCT_ID = I.PRODUCT_ID
AND P.PRODUCT_ID >= 200
AND I.QUANTITY >= 200) R) R2
ON 1=1) R3
ON PC.CATEGORY_ID = R3.CATEGORY_ID
AND R3.BIG IS NOT NULL
GROUP BY PC.CATEGORY_NAME ) R4
WHERE R4.RNK2=1
;- 제품ID가 200번대 이면서 수량200개이상 마진을 얻기위해 PRODUCTS 테이블과 INVENTORIES 테이블을 붙여서 ON 으로 PRODUCT_ID 끼리 묶고
그 다음 AND로 조건을 넣어서 뷰를 만든다. SELECT에서는 마진을 얻기위해서 판매가격(LIST_PRICE)-표준원가(STANDARD_COST) 빼서 마진을 얻는다. - 위에서 얻은 마진 결과를 토대로 MAX함수와 MIN함수로 비교값을 필요한 차이를 얻는다. 별칭 CHA
- CHA를 가지고 비교하기위해서 제품정보가 담겨 있는 테이블 PRODUCTS 조인하고 SELECT에서 CASE문을 활용해 CHA보다 판매가격이 큰 제품들의 정보는 남기고 작은 제품들은 NULL처리를 시킨다.
4.정답이 카테고리를 출력해야 하기 때문에 PRODUCT_CATEGORIES 테이블과 위에뷰를 조인시키고 AND 조건으로 'IN NOT NULL' 을 넣어 판매가격이 큰 제품들은 남기고, GROUP BY를 CATEGORY_NAME 넣고 SELECT에서 집계함수 COUNT를 활용해 가장많은 카테고리를 뽑는다.
문제4
주문 기록중에 배송이 완료된 주문 중에서 주문한 물품들의 총 합 지불 금액이 가장 큰 주문(실구매가=소매가)의
물품들의 총 금액이 원래 원가로 샀을때와 비교해서 얼마만큼의 금액차이가 있는지 구하시오
출력필드: 물품 번호, 물품 이름, 개당 마진, 원가로 샀을때 가격, 실제로 회사가 지불한 소매가, 마진
문제가 조금 복잡해서 세분화 해서 풀이하자
배송완료 지불금액 실구매가 구함,원가로 샀을때 가격, 실제로 회사가 지불한 소매가
SELECT OI.ORDER_ID,SUM(OI.QUANTITY*OI.UNIT_PRICE) as REP,SUM(OI.QUANTITY * PR.STANDARD_COST) AS SC_SUM,
RANK() OVER(ORDER BY SUM(OI.QUANTITY*OI.UNIT_PRICE) DESC) AS RNK
FROM ORDER_ITEMS OI INNER JOIN ORDERS O
ON OI.ORDER_ID = O.ORDER_ID
AND O.STATUS IN 'Shipped'
INNER JOIN PRODUCTS PR
ON OI.PRODUCT_ID = PR.PRODUCT_ID
GROUP BY OI.ORDER_ID- 주문기록 'Shipped' 이면서 주문번호인 ORDER_ID를 위해서 ORDER_ITEMS 테이블과 ORDER 테이블을 붙이고 마지막으로 원가로 샀을 때 가격을 위해서 PRODUCTS 테이블 붙힌다. GROUP BY 에서는 주문 기록의 총 지불금액이 필요하기 때문에 주문번호끼리 그룹화 필요하기 때문에 ORDER_ID를 쓴다.
실구매 1등 오더번호 물품들
SELECT *
FROM ORDER_ITEMS OIS INNER JOIN (SELECT OI.ORDER_ID,SUM(OI.QUANTITY*OI.UNIT_PRICE) as REP,SUM(OI.QUANTITY * PR.STANDARD_COST) AS SC_SUM,
RANK() OVER(ORDER BY SUM(OI.QUANTITY*OI.UNIT_PRICE) DESC) AS RNK
FROM ORDER_ITEMS OI INNER JOIN ORDERS O
ON OI.ORDER_ID = O.ORDER_ID
AND O.STATUS IN 'Shipped'
INNER JOIN PRODUCTS PR
ON OI.PRODUCT_ID = PR.PRODUCT_ID
GROUP BY OI.ORDER_ID) RP
ON OIS.ORDER_ID = RP.ORDER_ID
AND RP.RNK = 1
;- 지불금액 1등인 ORDER_ID의 물품들을 뽑아야 하기에 ORDER_ITEMS를 조인 시켜서 테이블을 가공한다.
출력필드: 물품 번호, 물품 이름, 개당 마진, 원가로 샀을때 가격, 실제로 회사가 지불한 소매가, 마진
SELECT P.PRODUCT_ID,P.PRODUCT_NAME,(RP2.UNIT_PRICE - P.STANDARD_COST) AS MARGIN, (P.STANDARD_COST * RP2.QUANTITY) AS PRIME,RP2.REP, (RP2.REP -RP2.SC_SUM) AS RESULT_MARGIN
FROM PRODUCTS P INNER JOIN (SELECT *
FROM ORDER_ITEMS OIS INNER JOIN (SELECT OI.ORDER_ID,SUM(OI.QUANTITY*OI.UNIT_PRICE) as REP,SUM(OI.QUANTITY * PR.STANDARD_COST) AS SC_SUM,
RANK() OVER(ORDER BY SUM(OI.QUANTITY*OI.UNIT_PRICE) DESC) AS RNK
FROM ORDER_ITEMS OI INNER JOIN ORDERS O
ON OI.ORDER_ID = O.ORDER_ID
AND O.STATUS IN 'Shipped'
INNER JOIN PRODUCTS PR
ON OI.PRODUCT_ID = PR.PRODUCT_ID
GROUP BY OI.ORDER_ID) RP
ON OIS.ORDER_ID = RP.ORDER_ID
AND RP.RNK = 1) RP2
ON P.PRODUCT_ID = RP2.PRODUCT_ID
;3.출력필드에 물품번호, 물품이름,마진이 있어 PRODUCTS 테이블 붙이고 SELECT 에서 개당마진(UNIT_PRICE - STANDARD_COST) 활용해 구했고,
인복씨 와 답은 다르지만 원가로 샀을 때 가격은 이가격이 맞다.
나머지 컬럼은 이해가 가고 다음주에 만나서 이야기를해봐야겠다.
