국비 TIL(Today I Learned) 20220715 오라클 테이블 만들기, 데이터 삭제, OT문제

테이블 만들고, 데이터 추가

MANAGER 부분 시퀀스도 추가함

 

MANAGER 테이블

 

DIVS 테이블

 

 

EDU_DIVS 테이블

 

EDU 테이블

 

 

 

 

-- MANAGER 테이블에 데이터 추가
SELECT*
FROM MANAGER
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '김소미', '영업1팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '김찬진', '영업1팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '안정훈', '영업1팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '오영수', '영업2팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '윤대현', '영업2팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '최소라', '영업2팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '유가을', '영업3팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '김덕훈', '영업3팀')                                                                                             
;

INSERT INTO MANAGER (EMP_NO, NAME, DEPT)
VALUES(MANAGER_SEQ.NEXTVAL, '선하라', '영업3팀')                                                                                             
;

COMMIT;


-- DIVS 테이블에 데이터 추가
SELECT *
FROM DIVS
;

INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('A01', '역량강화')
;

INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('A02', '리더쉽')
;
INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('B01', 'IT')
;
INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('B02', '프로젝트')
;
INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('B03', '모바일')
;
INSERT INTO DIVS(DIV_CODE, DIV_NAME)
VALUES ('C01', '영업/서비스')
;

COMMIT;

-- EDU_DIVS 테이블에 데이터 추가
SELECT *
FROM EDU_DIVS
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('MS01', 'B03', '경영전략 수립과정', 4, 600000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('IS01', 'B03', '아이디어 서바이벌', 4, 600000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('SD02', 'B01', '전략적 의사결정 과정', 3, 500000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('RD01', 'B01', '성과관리 리더십', 3, 500000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('EX01', 'B01', '엑셀 실무활용', 4, 600000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('EX02', 'B01', '엑셀 매크로 활용', 4, 600000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('PP02', 'B01', '프리젠테이션 스킬 업', 4, 600000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('MB01', 'B03', '안드로이드 개발자를 위한 Java', 5, 800000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('MB02', 'B03', '스마트폰 APP 기획 및 UX 전문가', 5, 800000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('CS01', 'C01', '감성 고객 응대', 2, 400000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('CS02', 'C01', '영업전략 수립과정', 2, 400000)
;
INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('CS03', 'C01', '고객불만 응대', 2, 400000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('PM01', 'C01', 'PM 양성', 5, 300000)
;

INSERT INTO EDU_DIVS(EDU_CODE, DIV_CODE , EDU_NAME, TERM, PRICE)
VALUES ('PM02', 'C01', 'MS Project 핵심', 5, 400000);

COMMIT;


-- EDU 테이블에 데이터 추가
SELECT *
FROM EDU
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('MS01', '2018-01-06', '2018-01-09', 7, 15)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('IS01', '2018-01-11', '2018-01-14', 2, 6)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('SD02', '2018-01-04', '2018-01-06', 9, 18)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('RD01', '2018-01-06', '2018-01-08', 8, 9)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('EX01', '2018-01-18', '2018-01-21', 9, 7)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('EX02', '2018-01-25', '2018-01-28', 5, 20)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('PP02', '2018-01-27', '2018-01-30', 6, 22)
;


INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('CS01', '2018-01-11', '2018-01-12', 2, 19)
;


INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('CS02', '2018-01-25', '2018-01-26', 1, 21)
;


INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('PM01',   '2018-01-18',   '2018-01-22',   6,   25)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('MB01',    '2018-01-03',   '2018-01-07',   6,   27)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('MB02',   '2018-01-03'   ,'2018-01-07',   1,   23)
;
INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('MS01',   '2018-02-08',   '2018-02-11',   6,   11)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('IS01',   '2018-02-08',   '2018-02-11',   1,   20)
;
INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('SD02',   '2018-02-22',   '2018-02-24',   3,   17)
;

INSERT INTO EDU(EDU_CODE, ST_DT, ED_DT, EMP_NO, EDU_PERSON)
VALUES ('RD01',   '2018-02-01',   '2018-02-03',   9,   24)
;

COMMIT;

 

 

 

OT문제 어제(14일) 팀별로 낸 거 풀이

-- 교통팀(우리) 
-- 주문이 취소된 물건이 가장 많이나온 웹사이트 고객의 풀네임과 글자수와
-- 글자수가 같은 사람들을 직원들중에서 출력하시오
-- 출력필드: 사원번호, 사원풀네임, 직업

SELECT E.EMPLOYEE_ID, E.FIRST_NAME || E.LAST_NAME, E.JOB_TITLE
FROM EMPLOYEES E INNER JOIN (
SELECT LENGTH(CT.FIRST_NAME || CT.LAST_NAME) AS LL
FROM CONTACTS CT INNER JOIN (
SELECT O.CUSTOMER_ID,
RANK() OVER(ORDER BY SUM(OI.QUANTITY) DESC) AS RNK
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
WHERE O.STATUS = 'Canceled'
GROUP BY O.CUSTOMER_ID) O
ON CT.CUSTOMER_ID = O.CUSTOMER_ID
AND O.RNK = 1) L
ON LENGTH(E.FIRST_NAME || E.LAST_NAME) = L.LL;


-- 산업경제팀
-- 'Crucial' 제품을 주문한 고객(회사)의 연락 담당자 이름(사람)과 연락처와 주문 수량을 구하시오.
-- 주문 수량으로 내림차순
-- 취소한 주문 제외
SELECT CT.FIRST_NAME || ' ' || CT.LAST_NAME AS NAME,
CT.PHONE, SUM(OI.QUANTITY) AS QUANTITY
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
INNER JOIN PRODUCTS P
ON OI.PRODUCT_ID = P.PRODUCT_ID
AND P.PRODUCT_NAME = 'Crucial'
INNER JOIN CONTACTS CT
ON O.CUSTOMER_ID = CT.CUSTOMER_ID
WHERE O.STATUS != 'Canceled'
GROUP BY O.CUSTOMER_ID, CT.FIRST_NAME || ' ' || CT.LAST_NAME, CT.PHONE
ORDER BY QUANTITY DESC;


-- 환경팀
-- 년도 별 판매 실적(횟수)을 구하고 그에 따른 1순위 직원을 찾아라
-- 배송 완료된 것만 판매 실적에 포함
SELECT S.YY, E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN (
SELECT TO_CHAR(ORDER_DATE, 'YYYY') AS YY, SALESMAN_ID,
COUNT(*) AS CNT,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM ORDERS
WHERE STATUS = 'Shipped'
AND SALESMAN_ID IS NOT NULL
GROUP BY TO_CHAR(ORDER_DATE, 'YYYY'), SALESMAN_ID) S
ON E.EMPLOYEE_ID = S.SALESMAN_ID
AND RNK = 1;


-- 보건팀
-- 창고중에서 'Samsung'제품이 가장 많은 창고를 구하고 그 창고는 어느 나라에 있는 창고 인지 구하시오.
-- 출력 : WAREHOUSE_ID(창고번호), WAREHOUSE_NAME, COUNTRY, QSUM(제품수량합계)
SELECT W.WAREHOUSE_ID, W.WAREHOUSE_NAME, C.COUNTRY_NAME AS COUNTRY,
W.QSUM
FROM ( SELECT W.WAREHOUSE_ID, W.WAREHOUSE_NAME, W.LOCATION_ID,
SUM(I.QUANTITY) AS QSUM,
RANK() OVER(ORDER BY SUM(I.QUANTITY) DESC) AS RNK
FROM WAREHOUSES W INNER JOIN INVENTORIES I
ON W.WAREHOUSE_ID = I.WAREHOUSE_ID
INNER JOIN PRODUCTS P
ON I.PRODUCT_ID = P.PRODUCT_ID
AND P.PRODUCT_NAME LIKE '%Samsung%'
GROUP BY W.WAREHOUSE_ID, W.WAREHOUSE_NAME, W.LOCATION_ID) W
INNER JOIN LOCATIONS L
ON W.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C
ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE W.RNK = 1;
​

-- 교육팀
-- Gabrielle(FIRST_NAME)의 주문 상태와 같은
-- 담당자 이름(CONTACTS), 고객사이름(CUSTOMERS), 주문번호, 주문별 상품의 총 개수를 출력하세요. (Gabrielle 제외)
SELECT CT.FIRST_NAME, C.NAME, O.ORDER_ID, SUM(OI.QUANTITY) AS QUANTITY
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
INNER JOIN CUSTOMERS C
ON O.CUSTOMER_ID = C.CUSTOMER_ID
INNER JOIN CONTACTS CT
ON C.CUSTOMER_ID = CT.CUSTOMER_ID
INNER JOIN (SELECT O.STATUS, O.CUSTOMER_ID
FROM ORDERS O INNER JOIN CONTACTS CT
ON O.CUSTOMER_ID = CT.CUSTOMER_ID
AND CT.FIRST_NAME = 'Gabrielle') G
ON O.STATUS = G.STATUS
AND O.CUSTOMER_ID != G.CUSTOMER_ID
GROUP BY CT.FIRST_NAME, C.NAME, O.ORDER_ID;