SQLD 특강 4일차

SQLD 특강 4일차

• 함수

--- 날짜 함수
SELECT  SYSDATE ,
		EXTRACT (YEAR FROM SYSDATE),
		EXTRACT (MONTH FROM SYSDATE),
		TO_CHAR (SYSDATE, 'YYYY'),
		TO_CHAR (SYSDATE, 'HH24'),
		TO_NUMBER (TO_CHAR (SYSDATE, 'HH24'))		
FROM 	DUAL;

SELECT 	SYSDATE ,
		SYSDATE - 1 ,
		SYSDATE - (1/24),
		SYSDATE - TO_DATE ('16/01/01', 'YY/MM/DD')
FROM 	DUAL;

--- 데이터타입 변환
SELECT  '100' + '100'  FROM DUAL;

SELECT  TO_CHAR(ADD_MONTHS(SYSDATE , 1), 'YYYY/MM/DD') 
FROM 	DUAL;

-- 사원 테이블 입사일자에서 년, 월, 일을 각각 출력 (EXTRACT 이용)
SELECT 	FIRST_NAME, 
		EXTRACT(YEAR FROM HIRE_DATE) ,
		EXTRACT(MONTH FROM HIRE_DATE) ,
		EXTRACT(DAY FROM HIRE_DATE) 
FROM EMPLOYEES;

SELECT  * 
FROM EMPLOYEES e 
WHERE EXTRACT(YEAR FROM HIRE_DATE) > 2017 ;

--- CASE 문
-- 방법1
SELECT EMPLOYEE_ID,
       SALARY, 
       CASE WHEN SALARY>=10000 THEN '상'
            WHEN SALARY>=5000  AND SALARY<10000 THEN '중' -- SALARY>=5000  동일
            ELSE '하'
       END "월급3단계"
FROM EMPLOYEES;

-- 방법2
--CASE 컬럼 WHEN 값1  THEN 결과1
--         WHEN 값2  THEN 결과2
--END
SELECT JOB_ID FROM EMPLOYEES e ;

SELECT EMPLOYEE_ID,
		JOB_ID,
		CASE SUBSTR(JOB_ID,1,2) WHEN 'AC' THEN 'ACCOUNT' ELSE 'ELSE' END 
FROM	EMPLOYEES e ;

-- DECODE 
SELECT 	EMPLOYEE_ID, 
		FIRST_NAME,
		SALARY,
		DECODE(TRUNC(SALARY/10000), 2, 'A', 1, 'B', 0, 'C') SALARY_GRADE		
FROM EMPLOYEES;

-- NULL 함수
--- NVL
SELECT 	EMPLOYEE_ID ,
		COMMISSION_PCT,
		NVL(COMMISSION_PCT, 0)  -- DATATYPE이 같아야
FROM	EMPLOYEES e ;

-- NVL2
SELECT 	EMPLOYEE_ID ,
		COMMISSION_PCT,
		NVL2(COMMISSION_PCT, 'SAL*COM', 'SAL')  급여계산식
FROM	EMPLOYEES e ;

-- NULLIF
SELECT  NULLIF('SQL', 'SQL'),   -- 같으면 NULL, 같지 않으면 첫번째 인자
		NULLIF('SQL', 'SQL EXPERT')
FROM 	DUAL;

-- COALESCE 
SELECT 	COALESCE  (NULL, NULL, 3) 
FROM 	DUAL;

---

• GROUP BY

-- 전체 대상 
SELECT 	MAX(SALARY),
		MIN(SALARY),
		AVG(SALARY),
		COUNT(*)
FROM	EMPLOYEES;

-- JOB_ID 
SELECT 	JOB_ID,
		MAX(SALARY),
		MIN(SALARY),
		AVG(SALARY),
		COUNT(*)
FROM	EMPLOYEES
WHERE 	SALARY > 10000   -- (1)
GROUP BY  JOB_ID          -- (2)
HAVING 	COUNT(*) > 2      --(3) -- GROUP BY 집계함수에 대해 조건을 걸때
AND     AVG(SALARY) > 3000;

--- 연도별 입사자가 얼마나 입사했는지 확인
--- 연도별 GROUP BY 수행, HIRE_DATR에서 YEAR 추출해서 GROUP BY에 사용

SELECT  TO_CHAR(HIRE_DATE, 'YYYY') 년도,
		COUNT(*)
FROM	EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
HAVING  COUNT(*) < 10;

-- CASE WHEN 통계표 만들기, 피봇팅
-- 전체 대상 
SELECT
  	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2015 THEN 1 ELSE 0 END) AS "2015입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2016 THEN 1 ELSE 0 END) AS "2016입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2017 THEN 1 ELSE 0 END) AS "2017입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2018 THEN 1 ELSE 0 END) AS "2018입사자",
	COUNT(*) CNT
FROM EMPLOYEES e ;


-- JOB ID별 연도별 입사자 수
SELECT
	JOB_ID,
	DEPARTMENT_ID , 
  	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2015 THEN 1 ELSE 0 END) AS "2015입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2016 THEN 1 ELSE 0 END) AS "2016입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2017 THEN 1 ELSE 0 END) AS "2017입사자",
	SUM(CASE WHEN TO_CHAR(HIRE_DATE, 'YYYY') = 2018 THEN 1 ELSE 0 END) AS "2018입사자",
	COUNT(*) CNT
FROM EMPLOYEES 
GROUP BY JOB_ID, DEPARTMENT_ID 
ORDER BY JOB_ID, DEPARTMENT_ID ;

---

• ORDER BY

SELECT 	EMPLOYEE_ID,
		FIRST_NAME
FROM	EMPLOYEES
ORDER   BY FIRST_NAME DESC;  -- ASC, DESC 

-- 컬럼 순서를 줄 수 있다
SELECT 	EMPLOYEE_ID,
		FIRST_NAME
FROM	EMPLOYEES
ORDER   BY  2  DESC;  -- ASC, DESC 

-- 컬럼 순서를 줄 수 있다
SELECT 	EMPLOYEE_ID,
		FIRST_NAME
FROM	EMPLOYEES
ORDER   BY  2,1, LAST_NAME  DESC;  -- ASC, DESC 

--- NULL ORDER BY
--- 오라클은 NULL을 가장 큰 값으로 간주
SELECT  EMPLOYEE_ID,
		COMMISSION_PCT
FROM 	EMPLOYEES e 
ORDER BY 2;

--- ROWNUM 으로 상위 5개 출력 - 아래처럼은 불가능

SELECT 	EMPLOYEE_ID,
		FIRST_NAME
FROM	EMPLOYEES
WHERE 	ROWNUM < 6
ORDER BY FIRST_NAME;

--- ORDER BY 상위 몇개 출력하는 방법 - subquery

SELECT EMPLOYEE_ID,  
		FIRST_NAME
FROM
(
	SELECT 	EMPLOYEE_ID,
			FIRST_NAME
	FROM	EMPLOYEES
	ORDER BY FIRST_NAME
)
WHERE ROWNUM < 6;

-- 부서별로 급여합계를 구하는데, 그 급여합계가 100000 이상이면서
-- 부서번호, 인원수, 합계 출력하는데, 급여합계가 큰 순서부터 출력
SELECT 	DEPARTMENT_ID AS 부서번호 ,
       	COUNT(*) AS 인원수 ,
       	SUM(SALARY) AS 기본급여합계
FROM 	EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING 	SUM(SALARY) >= 100000
ORDER BY 3 DESC;

---

• JOIN

SELECT * FROM EMPLOYEES e 
WHERE DEPARTMENT_ID  IS NULL;

-- 커미션을 받는 사원의 이름, 직업 (JOB_ID 또는 JOB_TITLE), 부서번호,부서명을 출력

SELECT 	E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE , D.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM 	EMPLOYEES E, DEPARTMENTS D , JOBS J
WHERE 	E.DEPARTMENT_ID = D.DEPARTMENT_ID  - 조인
AND 	E.JOB_ID = J.JOB_ID  -- 추가     
AND 	COMMISSION_PCT IS NOT NULL;

-- 3개  -> 조인조건 최소 몇 개? JOIN할 테이블 갯수  - 1

---

SQL 활용

---

• 표쥰 조인

-- Natural Join
SELECT 	E.FIRST_NAME, 
		D.DEPARTMENT_NAME,
		E.SALARY,
		DEPARTMENT_ID    -- ALIAS를 주면 안됨
FROM 	EMPLOYEES E NATURAL JOIN DEPARTMENTS D     
WHERE 	E.SALARY BETWEEN 3000 AND 5000
ORDER BY E.FIRST_NAME;

-- USING 
SELECT 	E.FIRST_NAME, 
		D.DEPARTMENT_NAME,
		E.SALARY,
		DEPARTMENT_ID    -- ALIAS를 주면 안됨
FROM 	EMPLOYEES E JOIN DEPARTMENTS D  USING (DEPARTMENT_ID)
WHERE 	E.SALARY BETWEEN 3000 AND 5000
ORDER BY E.FIRST_NAME;

-- ON 절 이용
SELECT  FIRST_NAME, E.JOB_ID, JOB_TITLE   -- ALIAS 줘야함 
FROM	EMPLOYEES e  JOIN	JOBS j   
ON		E.JOB_ID = J.JOB_ID
WHERE 	UPPER(J.JOB_TITLE) LIKE '%STOCK%';

---- OUTER JOIN
-- 실습 시작 전 OUTER JOIN 테스트를 위해 데이터 생성 
ALTER TABLE employees DROP CONSTRAINT EMP_JOB_FK;
INSERT INTO JOBS VALUES ('RR_MGR', 'Research', 0 ,0);
INSERT INTO JOBS VALUES ('RR_ASST', 'Test', 0 ,0);
INSERT INTO employees VALUES
( 999
, 'TEST'
, 'Marlow'
, '99999'
, '1.650.555.0131'
, TO_DATE('16-02-2015', 'dd-MM-yyyy')
, '0000' -- 존재하지 않는 JOB_ID
, 2500
, NULL
, 121
, 50
);

-- JOBS 테이블에 찾는 문구열로 시작하는 JOB_ID 는 EMP에는 없는 경우 - NULL 출력

-- FULL OUTER JOIN
SELECT 	E.FIRST_NAME,   -- NULL 출력
		E.JOB_ID,
		J.JOB_ID,
		J.JOB_TITLE
FROM JOBS j FULL OUTER JOIN EMPLOYEES e
ON J.JOB_ID = E.JOB_ID 
ORDER BY E.FIRST_NAME ;

--- 카테시안 프러덕트
SELECT FIRST_NAME,
	DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D
ORDER BY FIRST_NAME ;

---

• 집합연산자

SELECT 	EMPLOYEE_ID ,
		FIRST_NAME ,
		SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 13000 AND 17000
UNION ALL
SELECT 	EMPLOYEE_ID ,
		FIRST_NAME ,
		SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 17000 AND 25000;

---

• 계층형 질의 및 셀프 조인 self JOIN

-- 사원의 게층 구조 (순방향)
-- 관리자 ID 가 NULL 값 부터 시작 
SELECT 	LEVEL ,
		EMPLOYEE_ID ,
	    MANAGER_ID ,
		CONNECT_BY_ISLEAF ISLEAF
FROM 	EMPLOYEES E
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID;

--- 순방향, 부서명 출력
SELECT 	LEVEL ,
		LPAD(' ', 4*(LEVEL-1)) || EMPLOYEE_ID || ' ( ' || FIRST_NAME || ' ) ' AS "직원",
		D.DEPARTMENT_NAME,
		CONNECT_BY_ISLEAF
FROM 	EMPLOYEES E, DEPARTMENTS D
WHERE	E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID ;


--- 순방향, 부서명 출력
SELECT 	LEVEL ,
		LPAD(' ', 4*(LEVEL-1)) || EMPLOYEE_ID || ' ( ' || FIRST_NAME || ' ) ' AS "직원",
		D.DEPARTMENT_NAME,
		CONNECT_BY_ISLEAF
FROM 	EMPLOYEES E, DEPARTMENTS D
WHERE	E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID ;

-- 
SELECT 	LEVEL ,
		LPAD(' ', 4*(LEVEL-1)) || EMPLOYEE_ID || ' ( ' || FIRST_NAME || ' ) ' AS "직원",
		D.DEPARTMENT_NAME,
		CONNECT_BY_ISLEAF
FROM 	EMPLOYEES E, DEPARTMENTS D
WHERE	E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.FIRST_NAME = 'Nancy'
CONNECT BY PRIOR E.MANAGER_ID = E.EMPLOYEE_ID;

--
SELECT 	LEVEL ,
		LPAD(' ', 4*(LEVEL-1)) || EMPLOYEE_ID || ' ( ' || FIRST_NAME || ' ) ' AS "직원",
		D.DEPARTMENT_NAME,
		CONNECT_BY_ISLEAF,
		CONNECT_BY_ROOT E.EMPLOYEE_ID AS CEO ,
		SYS_CONNECT_BY_PATH (EMPLOYEE_ID, '/'),
		SYS_CONNECT_BY_PATH (EMPLOYEE_ID || ' ( '  || FIRST_NAME || ' ) ', '-') AS "조직인원경로"
FROM 	EMPLOYEES E, DEPARTMENTS D
WHERE	E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID
ORDER SIBLINGS BY FIRST_NAME;
-- 
-- SELF JOIN 

SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID 
FROM EMPLOYEES e ;
-- MANAGER의 이름

-- A: 사원,  B : 관리자 
SELECT  A.EMPLOYEE_ID, A.FIRST_NAME, 
        A.MANAGER_ID 관리자ID, 
		B.FIRST_NAME 관리자명
FROM	EMPLOYEES A , EMPLOYEES B
WHERE 	A.MANAGER_ID = B.EMPLOYEE_ID 
ORDER   BY 1;

-- A: 사원,  B : 관리자 
SELECT  A.EMPLOYEE_ID, A.FIRST_NAME, 
        A.MANAGER_ID 관리자ID, 
		B.FIRST_NAME 관리자명
FROM	EMPLOYEES A LEFT OUTER JOIN  EMPLOYEES B 
ON  	A.MANAGER_ID = B.EMPLOYEE_ID 
ORDER   BY 1;

---

• 서브쿼리

-- 전체 사원의 평균 급여보다 더 많이 받는 리스트를 출력
SELECT  AVG(SALARY) FROM EMPLOYEES;
-- 평균급여 : 6495.850467289719626168224299065420560748
-- 51명
SELECT * 
FROM 	EMPLOYEES
WHERE 	SALARY  > 6495.850467289719626168224299065420560748;


SELECT	EMPLOYEE_ID,
		FIRST_NAME,
		LAST_NAME,
		SALARY,
		E.JOB_ID,
		D.DEPARTMENT_NAME 
FROM 	EMPLOYEES E, DEPARTMENTS D
WHERE 	SALARY  > (SELECT  AVG(SALARY) FROM EMPLOYEES)
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID  ;
--AND E.DEPARTMENT_ID = D.DEPARTMENT_ID (+) ;

--------
-- SELECT 절에도 서브쿼리 있을 수 있음
-- JOB_TITLE, 부서명, 관리자명..
SELECT  EMPLOYEE_ID,
		FIRST_NAME, 
		(SELECT J.JOB_TITLE FROM JOBS J 
        WHERE J.JOB_ID = E.JOB_ID ) AS JOB_TITLE,
		-- 부서명을 가져와보세요.
		(SELECT D.DEPARTMENT_NAME 
        FROM DEPARTMENTS d 
        WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID ) 
        AS DEPT_TITLE
FROM	EMPLOYEES E;
---------------------------------------------------------------------------
- VIEW 생성
```sql
CREATE OR REPLACE VIEW V_EMP_DEPT60("emp_no","name","sal")
AS
	SELECT 	EMPLOYEE_ID,LAST_NAME,SALARY
	FROM 	EMPLOYEES
	WHERE 	DEPARTMENT_ID = 60
	WITH READ ONLY
;

SELECT  * FROM V_EMP_DEPT60;

DROP VIEW V_EMP_DEPT60;