2023.10.17 + outter join, subquery

-- OUTER JOIN 교집합 (INNERJOING) + INNER JOIN에서 제외된 데이터를 함께 출력
-- 형식 : SELECT 컬럼명 
-- FROM 테이블1 INNER JOIN 테이블2 ON 조인컬럼
-- LEFT OUTER JOIN / RIGHT OUTER JOIN ON 조인컬럼 
-- 오라클 형식 :  E.EMP_ID = V.EMP_ID (+)
-- 최근형식 : FROM 테이블 1 LEFT OUTER JOIN / RIGHT OUTER JOIN 테이블2 ON 조인컬럼 
-- OUTER JOIN 사용시 반드시 누락되는 데이터가 없도록 확인



-- 모든 부서의 정보와 소속 본부명을 함께 조회
SELECT d.dept_id ,d.dept_name, u.unit_name 
FROM department d left outer JOIN unit u ON d.unit_id =u.unit_id;




/*오라클방식
SELECT d.dept_id ,d.dept_name, u.unit_name 
FROM department d , unit u 
where d.unit_id(+) =u.unit_id; 
 * */



-- 2017년부터 2018년도까지 입사한 사원들의 사원명, 입사일, 연봉 , 부서명,퇴사일 조회해주세요
-- + 본부명도 추가해서 출력하시오
-- 단 퇴사한 사원들도 모두 조회
-- 소속 본부를 모두 조회
SELECT e.emp_name ,e.hire_date ,e.salary ,d.dept_name, e.retire_date , d.dept_id , u.unit_name 
FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id 
LEFT OUTER JOIN unit u ON u.unit_id = d.unit_id
WHERE LEFT(e.hire_date,4) BETWEEN '2017' AND '2018';





-- 서브쿼리 : 메인쿼리에 서브쿼리를 추가하여 실행하는 형식
-- SELECT 컬럼리스트 FROM 테이블명 WHERE 조건절
-- 		  (스칼라 서브쿼리)  (인라인뷰)    (서브쿼리)
-- 스칼라서브쿼리는 성능문제로 잘 사용안함(오라클에서는 더 이상 지원x) , sql 튜닝할때 우선순위로 수정


-- 홍길동 사원이 속한 부서의 이름을 조회

SELECT dept_name 
FROM department d
WHERE d.dept_id = (SELECT e.dept_id  FROM employee e WHERE e.emp_name ='홍길동');


-- 홍길동사원이 사용한 휴가의 내역을 조회

SELECT *
FROM vacation v  
WHERE v.emp_id = (SELECT e.emp_id  FROM employee e WHERE emp_name = '홍길동');


-- 제 3본부에 속해있는 부서들을 조회
SELECT * FROM department d 
WHERE d.unit_id = (SELECT u.unit_id  FROM unit u WHERE u.unit_name = '제3본부')



-- 제 3본부에 속해 있는 모든 사원들을 조회
-- =의 뜻은 동일하게 데이터가 하나만 나오는 경우를 뜻함(단일행 서브쿼리),
-- 다중행 서브쿼리 : 서브쿼리를 실행한 결과가 2행이상 출력되는 경우
-- 그래서 이처럼 복수의 결과가 출력을 할때는 or연산자인 in을 사용하여 출력을 해야한다(다중행 서브쿼리)
SELECT *
FROM employee e 
WHERE e.dept_id in (SELECT d.dept_id  FROM department d WHERE d.unit_id = 
(SELECT u.unit_id  FROM unit u WHERE u.unit_name= '제3본부'));



-- 가장 먼저 입사한 사원의 정보를 출력
SELECT * FROM employee e ORDER BY hire_date LIMIT 1; -- 내답변

SELECT * FROM employee e WHERE hire_date = (SELECT min(e.hire_date) FROM employee e );



-- 휴가를 간 적 있는 정보시스템 부서의 사원들을 출력
SELECT * FROM employee e
WHERE e.emp_id in (SELECT v.emp_id  FROM vacation v WHERE v.duration IS NOT null )
AND e.dept_id  in (SELECT d.dept_id  FROM department d WHERE d.dept_name = '정보시스템')


-- 휴가를 간 적이 없는 정보시스템 부서의 사원들을 출력
SELECT * FROM employee e
WHERE   e.dept_id  = (SELECT d.dept_id  FROM department d WHERE d.dept_name = '정보시스템')
AND e.emp_id  NOT in (SELECT v.emp_id  FROM vacation v)


-- 사원별 휴가사용 일수를 그룹핑하여, 사원아이디,사원명,입사일,연봉,휴가사용일수 조회해주세요
SELECT e.emp_id ,e.emp_name ,e.hire_date ,e.salary , sum(v.duration) '휴가사용일수' 
FROM employee e INNER JOIN vacation v ON e.emp_id = v.emp_id GROUP BY e.emp_id;



SELECT e.emp_id ,e.emp_name ,e.hire_date ,e.salary
 FROM employee e INNER JOIN (SELECT v.emp_id ,sum(v.duration) FROM vacation v GROUP BY emp_id) AS v
ON e.emp_id = v.emp_id;






SELECT count(*) FROM (SELECT e.emp_id ,e.emp_name ,e.hire_date ,e.salary , sum(v.duration) '휴가사용일수' 
FROM employee e INNER JOIN vacation v ON e.emp_id = v.emp_id GROUP BY e.emp_id) AS ev



-- 휴가 사용일수와 사원정보, 단 모든사원의 정보조회
-- 현시점휴가사용일수가없는사원은 0으로 조회
SELECT * FROM (SELECT e.emp_id ,e.emp_name ,e.hire_date ,e.salary , ifnull(sum(v.duration),0)  '휴가사용일수' 
FROM employee e LEFT outer JOIN vacation v ON e.emp_id = v.emp_id GROUP BY e.emp_id) AS ev

 





SELECT * FROM vacation v 

SELECT * FROM employee e 




-- my shop 

USE myshop2019;


SELECT count(*) FROM (SELECT c.category_id FROM category c 
INNER JOIN sub_category sc ON c.category_id = sc.category_id 
INNER JOIN product p on p.sub_category_id = sc.sub_category_id) AS a 

SELECT * FROM category c 
INNER JOIN sub_category sc ON c.category_id = sc.category_id 
INNER JOIN product p on p.sub_category_id = sc.sub_category_id  


-- 카테고리별 상품명을 조회

SELECT  c.category_id , c.category_name ,sc.sub_category_id  ,p.product_name  FROM category c 
INNER JOIN sub_category sc ON c.category_id = sc.category_id 
INNER JOIN product p on p.sub_category_id = sc.sub_category_id




-- 2018년도 기준 상품별 주문건수 조회 - 주문수량, 상품명, 총주문건수

SELECT ROW_NUMBER ()OVER(ORDER BY LEFT (oh.order_date,4)) 순번,LEFT (oh.order_date,4), p.product_name,sum(order_qty) AS 주문수량 ,count(order_qty) AS 총주문건수 
FROM order_header oh INNER JOIN order_detail od ON oh.order_id = od.order_id 
INNER JOIN product p ON p.product_id = od.product_id   
WHERE LEFT (oh.order_date,4) = '2018' GROUP BY p.product_name, LEFT (oh.order_date,4)


-- 행번호 생성 함수
SELECT ROW_NUMBER() OVER (ORDER BY c.customer_id DESC) num, c.customer_name  FROM customer c