개발새발, 써놓기

-- 휴지통 정보 확인
-- 삭제된 개체 확인
SELECT * FROM RECYCLEBIN;
-- 삭제된 테이블 복원
FLASHBACK TABLE 테이블명 TO BEFORE DROP;
FLASHBACK TABLE "BIN이름" TO BEFORE DROP;

FLASHBACK TABLE test TO BEFORE DROP;
-- 동일한 이름이 두개 이상이면 마지막 테이블 복원
SELECT * FROM tab;

FLASHBACK TABLE emp2 TO BEFORE DROP;
SELECT * FROM emp2;

FLASHBACK TABLE emp2 TO BEFORE DROP; -- 에러. 이미 같은 이름의 테이블이 존재함
-- 이름을 변경해서 복원
FLASHBACK TABLE emp2 TO BEFORE DROP RENAME TO emp22;

SELECT * FROM tab;

Oracle 에서 DROP TABLE 테이블명 으로 테이블을 삭제하고 SELECT * FROM tab; 으로 확인해보면 테이블명에 해당하는 테이블은 사라지고 bin~ 으로 시작하는 항목이 생긴다. 휴지통으로 들어간 것이다.

이렇게 휴지통으로 들어간 테이블을 보는 명령은  SELECT* FROM RECYCLEBIN; 으로 확인할 수 있다.

삭제된 테이블을 복원하려면 FLASHBACK TABLE 테이블명 TO BEFORE DROP; 혹은 FLASHBACK TABLE "BIN이름" TO BEFORE DROP;으로 복원 할 수 있다. 동일한 이름이 두 개 이상이면 마지막에 삭제한 테이블을 복원한다. 그렇기에 같은 이름이었던 것을 또 다시 복원하기 위해서는 FLASH TABLE 테이블명 TO BEFORE DROP RENAME TO 새로운테이블명; 으로 복원해야한다

-- 휴지통 비우기
-- 전체 비우기
PURGE RECYCLEBIN;
-- 특정 테이블 비우기
PURGE RECYCLEBIN 비울테이블명;

-- 모든 데이터 삭제. ROLLBACK 불가(자동 COMMIT)
TRUNCATE TABLE emp1; -- 모든 데이터 삭제(구조는 삭제 안됨). 속도 빠름.

BIN~으로 되어있는 것을 삭제하기 위해서는 휴지통 비우기를 통해 아예 없앨 수 있다.PRUGE RECYCLEBIN; 을 통해 비워주거나 특정 테이블을 없애고 싶으면 PURGE RECYCLEBIN 비울테이블명; 을 통해 없애주면 된다. 

테이블 안에 있는 모든 데이터를 삭제할 때 TRUNCATE TABLE 테이블명; 을 통해 안에있는 데이터들을 다 삭제할 수 있다. 이 명령어는 자동으로 COMMIT이 되기 때문에 ROLLBACK이 불가하고, 테이블 안에 있는 데이터를 삭제하는 것이기 때문에 구조는 남아 있게 된다.

SELECT 컬럼, 컬럼 FROM 테이블 WHERE 조건;
INSERT INTO 테이블명(컬럼명, 컬럼명) VALUES (값1, 값2);
UPDATE 테이블명 SET 컬럼명 = 값, 컬럼명 = 값 WHERE 조건;
DELETE FROM 테이블명 WHERE 조건;

데이터 타입 : VARCHAR2, NUMBER, DATE, CLOB

CREATE TABLE 테이블명
(
    컬럼명 자료형[(크기)],
    컬럼명 자료형[(크기)]
)

-- SUM() OVER() 함수
SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (ORDER BY empNo) 앞누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (ORDER BY dept) 부서별앞누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept) 부서총급여
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept ORDER BY empNo) 부서개인별누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept ORDER BY pos) 부서직위별
FROM emp;

-- name, dept, sal, 부서총급여에대한백분율
SELECT name, dept, sal,
    ROUND ( SAL / SUM ( sal ) OVER( PARTITION BY DEPT) * 100 ) 부서별비율
FROM emp;

-- 부서명 성별 인원수 부서성별백분율
SELECT dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
    COUNT(*) 인원수,
    SUM( COUNT(*) ) OVER( PARTITION BY dept) 부서인원
FROM emp
GROUP BY dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자');

SELECT dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
    COUNT(*) 인원수,
    ROUND( COUNT(*) / SUM( COUNT(*) ) OVER( PARTITION BY dept) * 100 ) ||'%'비율
FROM emp
GROUP BY dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자');

-- AVG() OVER() 함수
SELECT name, dept, sal, ROUND( AVG(sal) OVER()) 전체평균
FROM emp;

SELECT name, dept, sal, sal - ROUND( AVG(sal) OVER()) "전체평균과 차이"
FROM emp;

SELECT name, dept, sal, ROUND( AVG(sal) OVER( ORDER BY empNo)) 
FROM emp; -- 처음부터 나까지 평균 // 처음부터 출력행까지의 평균 계속 바뀜

SELECT name, dept, sal, ROUND( AVG(sal) OVER( ORDER BY dept)) 
FROM emp; -- 처음부터 현재 부서까지 평균

SELECT name, dept, sal, ROUND( AVG(sal) OVER(PARTITION BY dept)) 
FROM emp; -- 부서별 평균
-- MAX() OVER()와 MIN() OVER() 함수
-- name, dept, sal, 부서별최대급여
SELECT name, dept, sal, MAX( sal ) OVER( PARTITION BY dept ) 부서최대
FROM emp;

-- name, dept, sal, 최대급여와 차이
SELECT name, dept, sal, MAX( sal ) OVER() - sal 최대와차이
FROM emp;

-- name, dept, sal, 최소급여와 차이
SELECT name, dept, sal, sal - MIN( sal ) OVER() 최대와차이
FROM emp;

-- RATIO_TO_REPORT() OVER() 함수: 비율
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept;
-- 부서별 인원

SELECT dept, ROUND( COUNT(*) / (SELECT COUNT(*) FROM emp ) * 100) 비율
FROM EMP
GROUP BY dept;
-- 전체인원 대비 부서별 인원 비율

SELECT dept, ROUND( RATIO_TO_REPORT( COUNT(*) ) OVER() * 100) 비율
FROM EMP
GROUP BY dept;

-- LISTAGG() WITHIN GROUP() 함수 : 각 값을 결합
SELECT dept, name
FROM emp
ORDER BY dept;

SELECT dept, LISTAGG(name, ',') WITHIN GROUP ( ORDER BY empNO) 부서사원명
FROM EMP
GROUP BY dept;

-- LAG() OVER() 함수와 LEAD() OVER() 함수
SELECT name, sal,
    LAG( sal, 1, 0 ) OVER ( ORDER BY sal DESC) lag
FROM emp;
-- LAG( sal, 1, 0 )
-- sal : 출력컬럼 
-- 1 : offset. 1줄씩 밀림. 3을 주면 3줄씩 밀림
-- 0 : 밀린 자리에 들어올 값

SELECT name, sal,
    LEAD( sal, 1, 0 ) OVER ( ORDER BY sal DESC) lead -- 위로 한칸 올라감
FROM emp;

-- NTILE() OVER() 함수 : 그룹별 분리
-- 60개를 7그룹
-- (1~9 : 1그룹)
-- (10~18 : 2그룹)
-- (19~27 : 3그룹)
-- (28~36 : 4그룹) ...
SELECT name, sal, 
    NTILE( 7 ) OVER ( ORDER BY sal DESC )그룹
FROM emp;

-- 윈도우 절 
-- FIRST_VALUE() OVER()
SELECT name, dept, sal,
    FIRST_VALUE( sal) OVER (PARTITION BY dept ORDER BY sal DESC) 가장높은연봉
FROM emp;
-- 부서별로 내림차순 정렬시켰을 때 처음 값을 가져와라

-- 적은순에서 큰 수 : 기준이 현재행이 처음이며 마지막
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ( ORDER BY sal)
FROM emp;

-- SELECT * FROM emp의 마지막 값만 출력
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ()
FROM emp;

-- 가장 큰 값 출력 (ORDER BY sal로 인해)
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ()
FROM emp
ORDER BY sal;

-- 적은값에서 큰순
-- CURRENT ROW 현재가 시작과 끝점(기본)
SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   )
FROM emp
ORDER BY sal;

SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal  )
FROM emp
ORDER BY sal;

-- 가장 큰 값만
-- UNBOUNDED FOLLOWING : 마지막이 끝점 
SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   )
FROM emp
ORDER BY sal;

-- dept별 pos의 sal 소계, dept별 소계, 마지막에 총계 출력
-- dept + pos 소계
-- dept 소계
-- 전체
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY ROLLUP(dept, pos);

SELECT pos, dept, SUM(sal)
FROM EMP
GROUP BY ROLLUP(pos, dept);

SELECT city, dept, pos, SUM(sal)
FROM EMP
GROUP BY ROLLUP(city, dept, pos); -- 4레벨 출력

-- dept별 pos의 sal 소계, dept별 소계 출력하며 마지막에 총계는 출력하지 않는다.
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY dept, ROLLUP(pos);

-- 부서별 인원수와 마지막에 전체인원수
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept; -- 부서별 인원수

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept); -- 인수가 1개면 2레벨

-- CUBE 절 예
-- dept 별 pos의 sal소계, dept 별 소계, pos별 소계, 마지막에 총계 출력
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY CUBE( dept, pos)
ORDER BY dept, pos;

SELECT city, dept, pos, SUM( sal)
FROM EMP
GROUP BY CUBE(city, dept, pos)
ORDER BY city, dept, pos;

SELECT city, dept, pos, SUM( sal)
FROM EMP
GROUP BY city, CUBE(dept, pos)
ORDER BY city, dept, pos;

-- GROUPING 함수와 GROUP_ID 함수
SELECT dept, pos, GROUPING(dept), GROUPING(pos), COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos);

SELECT dept, pos, GROUPING(dept), GROUPING(pos), COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos)
HAVING GROUPING(pos) = 1;

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos)
HAVING GROUPING(pos) = 1;

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept);

SELECT dept, empNo, name, SUM(sal)
FROM EMP
GROUP BY ROLLUP (dept, (empNo, name));

SELECT dept, empNo, name, GROUP_ID(), SUM(sal)
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name));

SELECT dept, empNo, name, GROUP_ID(), 
    DECODE( GROUP_ID(), 0, NVL(name, '합계'), '평균') name,
    DECODE( GROUP_ID(), 0, SUM(sal), ROUND( AVG(sal))) sal
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;

SELECT dept, empNo,
    DECODE( GROUP_ID(), 0, NVL(name, '합계'), '평균') name,
    DECODE( GROUP_ID(), 0, SUM(sal), ROUND( AVG(sal))) sal
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;

-- 문제풀이
SELECT dept, pos, COUNT(*)
FROM emp
GROUP BY CUBE(dept, pos)
ORDER BY dept;

SELECT dept, COUNT(DECODE(pos, '부장', 1, 0)) 부장,
COUNT(DECODE(pos, '과장', 1, 0)) 과장
FROM emp
GROUP BY dept, CUBE(pos);

SELECT city, MAX ( COUNT (*)) 인원수
FROM emp
WHERE COUNT(DECODE( MOD ( SUBSTR( rrn, 8, 1) , 2), 0, '여자') )
GROUP BY city;

SELECT city, COUNT (*) 인원수
FROM emp
WHERE MOD ( SUBSTR( rrn, 8, 1) , 2) = 0
GROUP BY city
HAVING COUNT(*) = 
(SELECT MAX( COUNT(*)) FROM emp WHERE MOD(SUBSTR(rrn, 8, 1),2) = 0 
GROUP BY city);

SELECT dept, COUNT(DECODE(SUBSTR(pos, 1, 2), '부장', 1, 0)) 부장, 
FROM emp
GROUP BY dept;

SELECT dept, pos, COUNT(*) 인원수
FROM emp
GROUP BY pos, ROLLUP(dept)
ORDER BY dept;

SELECT dept, 
COUNT(DECODE(pos, '부장', 1) ) 부장, 
COUNT(DECODE(pos, '과장', 1) ) 과장, 
COUNT(DECODE(pos, '대리', 1) ) 대리, 
COUNT(DECODE(pos, '사원', 1) ) 사원
FROM emp
GROUP BY dept;

SELECT dept, 
COUNT(DECODE(pos, '부장', 1) ) 부장, 
COUNT(DECODE(pos, '과장', 1) ) 과장, 
COUNT(DECODE(pos, '대리', 1) ) 대리, 
COUNT(DECODE(pos, '사원', 1) ) 사원
FROM emp
GROUP BY ROLLUP(dept);
-- =========================================

CREATE TABLE test
(
    num NUMBER(10) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL, -- 최대 한글 10자
    birth DATE, -- DATE는 크기주지않음.
    city VARCHAR2(30)
);

-- 테이블 목록 확인
SELECT * FROM tab;
SELECT * FROM tabs; -- 자세히 나옴

-- 테이블 컬럼 확인
SELECT * FROM col WHERE tname = 'TEST'; -- ' '안에는 꼭 대문자
SELECT * FROM cols WHERE table_name = 'TEST'; -- 자세히 나옴
DESC test;

-- 테이블 내용 확인
SELECT * FROM emp;
SELECT * FROM test; -- 아직 구조만 만들었기 때문에 아무것도 출력되지 않음.

-- 존재하는 테이블을 이용하여 새로운 테이블 만들기 : 구조 및 데이터 복사
SELECT empNo, name, sal, bonus, sal+bonus FROM emp;
CREATE TABLE emp1 AS SELECT empNo, name, sal, bonus, sal+bonus FROM emp;
-- 에러. 컬럼명 규칙 위반. 컬럼의 이름에는 +가 들어갈 수 없다.
CREATE TABLE emp1 AS SELECT empNo, name, sal, bonus, sal+bonus pay FROM emp;
-- 테이블 구조 및 데이터도 복사. NOT NULL을 제외한 제약조건은 복사되지 않음.
SELECT * FROM tab;
DESC emp1;
SELECT * FROM col WHERE tname = 'EMP1';

SELECT * FROM emp1;

CREATE TABLE emp1 AS SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';
-- 에러. ORA-00955 : 기존의 객체가 이름을 사용하고 있습니다. 
-- emp1 이라는 객체를 이미 만들었으므로 다른 이름을 줘야한다.
CREATE TABLE emp2 AS SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

SELECT * FROM tab;
SELECT * FROM emp2;

SELECT empNo, name, TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 
    DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
FROM emp;

-- 컬럼명을 지정해서 구조 및 데이터 복사
CREATE TABLE emp3 (eno, name, birth, gender) AS 
    SELECT empNo, name, TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 
        DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
    FROM emp;

SELECT * FROM tab;
DESC emp3;

SELECT * FROM emp3;

-- 테이블의 구조만 복사 ( NOT NULL을 제외한 제약 조건은 복사 안됨)
SELECT * FROM emp WHERE 1 = 1;

SELECT * FROM emp WHERE 1 = 2;

CREATE TABLE emp4 AS
    SELECT * FROM emp WHERE 1= 2;

SELECT * FROM tab;
DESC emp4;
SELECT * FROM col WHERE tname = 'EMP4';

SELECT * FROM emp4;

-- 기존 테이블에 새로운 컬럼을 추가
-- 새로 추가되는 컬럼은 마지막에 추가 된다.

DESC test;

-- 컬럼 추가
ALTER TABLE test ADD ( 
    dept VARCHAR2(30),
    sal NUMBER(3) NOT NULL );

DESC test;

-- emp1 테이블에 다음의 컬럼을 추가
ALTER TABLE emp1 ADD(
    dept VARCHAR2(30) NOT NULL);   
-- 에러. 이미 데이터가 존재하는 경우 NOT NULL 속성의 컬럼을 추가 할 수 없다.
ALTER TABLE emp1 ADD(
    dept VARCHAR2(30) );
DESC emp1; 
SELECT * FROM emp1;

-- ALTER TABLE 테이블명 MODIFY (컬럼명 자료형(크기));
-- 테이블에 존재하는 컬럼의 타입, 폭 등을 수정한다.
-- 데이터가 존재하면 데이터 타입 변경은 불가능하고,
-- 폭은 데이터 길이보다 크거나 같아야 한다.
ALTER TABLE test MODIFY (
    sal NUMBER(8) );
DESC test;

ALTER TABLE emp1 MODIFY (
    name VARCHAR2(8) );
DESC test; -- 에러 한글3글자 9바이트
-- 에러. 데이터가 존재하면 폭은 데이터 길이보다 크거나 같아야 한다.

-- ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새로운컬럼명
-- 존재하는 컬럼이름 변경하기
DESC test;

ALTER TABLE test RENAME COLUMN num TO empNo;
DESC test;

-- ALTER TABLE 테이블명 DROP COLUMN 컬럼명
-- 존재하는 컬럼 제거
DESC test;

ALTER TABLE test DROP COLUMN dept;
DESC test;

SELECT * FROM emp1;
ALTER TABLE emp1 DROP COLUMN name;
SELECT * FROM emp1;
-- 데이터가 존재하면 데이터도 삭제된다.

-- ALTER TABLE ~ SET UNUSED
-- 컬럼은 삭제하지 않지만 논리적으로 접근하지 못하도록 제한. 즉, 논리적으로 삭제
SELECT * FROM emp3;

ALTER TABLE emp3 SET UNUSED( name );
DESC emp3; -- name 컬럼은 보이지 않음
SELECT * FROM emp3; -- name 컬럼은 보이지 않음
-- UNUSED된 컬럼 개수 확인
SELECT * FROM USER_UNUSED_COL_TABS;
-- UNUSED된 컬럼 제거
ALTER TABLE emp3 DROP UNUSED COLUMNS;

SELECT * FROM USER_UNUSED_COL_TABS;

COUNT() OVER() 함수

-- COUNT() OVER() 함수
SELECT name, dept, SAL,
    COUNT(*) OVER( ORDER BY dept) cnt
FROM emp;
-- 부서별 인원수를 누적하여 앞 부서 인원수와 누적해서 출력
-- 동일 부서는 동일 인원

동일부서에는 동일한 인원의 합계가 출력된다. 개발부에는 14명이 존재하고 기획부에는 (21-14) 7명이 존재함.

SELECT name, dept, SAL,
    COUNT(*) OVER( ORDER BY sal) cnt
FROM emp;
-- sal 오름차순으로 정렬하여 같은 sal이면 출력되는 COUNT 값은 같은 인원수를 가짐.

SELECT name, dept, SAL,
    COUNT(*) OVER( ORDER BY sal DESC) cnt
FROM emp;
-- sal 내림차순으로 정렬하여 같은 sal이면 출력되는 COUNT 값은 같은 인원수를 가짐.

COUNT는 누적되는 것이고 같은 sal을 가지고 있으면 COUNT(같은 sal의 수를 출력하되 계속 누적됨) 위에 보는 것 처럼 11이 두명에게서 보인다.

SELECT name, dept, SAL,
    COUNT(*) OVER() cnt
FROM emp; -- OVER에 아무것도 기술하지 않으면 전체

SELECT name, dept, SAL,
    COUNT(*) OVER( PARTITION BY dept) cnt
FROM emp; -- 그룹의 인원수를 구하고 이전 그룹은 누적하지 않음


SELECT name, dept, SAL,
    COUNT(*) OVER( PARTITION BY dept) cnt1,
    COUNT(*) OVER( PARTITION BY dept ORDER BY pos) cnt2
FROM emp;

SELECT name, dept, SAL,
    COUNT(*) OVER( PARTITION BY dept) cnt1,
    COUNT(*) OVER( PARTITION BY dept ORDER BY empNo) cnt2
FROM emp;

COUNT() OVER( PARTITION BY ~) 그룹별로 해당하는 값만 출력하고 누적되지 않는다.

COUNT() OVER( PARTITION BY ~ ORDER BY) 같은 부서를 가지고 있으면 CNT1에 같은 값을 출력한다. CNT2에는 직위별로 정렬하여 세고 그 값은 동일 부서안에서만 누적된다.

개발부서의 인원수를 CNT1에서는 출력하고 CNT2에서는 empNo를 부서별로 누적해서 출력해준다.

SELECT name, dept, SAL,
    COUNT(*) OVER( ORDER BY empNo) cnt
FROM emp
WHERE dept = '개발부';
-- 개발부서만 뽑아서 empNo 오름차순으로 누적하며 출력

SELECT name, dept, SAL,
    COUNT(*) OVER( ORDER BY sal) cnt
FROM emp
WHERE dept = '개발부';
-- 개발부서만 뽑아서 sal 오름차순으로 누적하며 출력

RANK() OVER() 함수

-- RANK() OVER()
SELECT empNo, name, sal, RANK() OVER(ORDER BY sal DESC) 순위 FROM emp;
-- empNo, name, sal 을 출력하고, sal 내림차순으로 순위를 매겨서 출력해라.
SELECT empNo, name, sal, RANK() OVER(ORDER BY sal) 순위 FROM emp;
-- empNo, name, sal 을 출력하고, sal 오름차순으로 순위를 매겨서 출력해라.

SELECT empNo, name, sal, bonus, RANK() OVER(ORDER BY sal, bonus DESC) 순위 FROM emp;
-- emp 테이블에서 empNo, name, sal, bonus를 출력하고
-- sal 내림차순으로 순위를 부여하고, sal이 동일하면 bonus 내림차순 순위를 매겨으로 정렬하고 출력.

-- 그룹별 순위
SELECT empNo, name, dept, pos, sal, 
    RANK() OVER( ORDER BY sal DESC ) 전체순위,
    RANK() OVER( PARTITION BY dept ORDER BY sal DESC ) 부서순위,
    RANK() OVER( PARTITION BY dept, pos ORDER BY sal DESC ) 부서직위순위
FROM emp;

-- 급여(sal)를 가장 많이 받는 순으로 1~10등까지 출력
SELECT empNo, name, dept, pos, SAL
FROM EMP
WHERE RANK() OVER( ORDER BY sal DESC) <= 10;
-- 에러. 분석함수는 WHERE절에 사용 불가

SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
FROM EMP;

SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
FROM EMP
WHERE ROWNUM <= 10; -- 이상한 결과. 10등이 2명이상이어도 한명만 출력
-- WHERE로 먼저 10명을 짜르기 때문에 순위가 아예 이상함.
-- ORDER BY가 있는 경우 ROWNUM을 사용하면 안됨.

SELECT * FROM (
    SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
    FROM EMP
) WHERE 순위 <= 10;
-- 먼저 RAN() OVER()로 정렬한 후에 서브 쿼리를 이용해서 10명의 순위를 짜른다.

-- 급여 상위 10%
-- 연봉을 내림차순으로 정렬하기 때문에 10% 보다 작은 순위들이 급여 상위 10%
SELECT * FROM (
    SELECT empNo, name, dept, pos, sal,
    RANK() OVER( ORDER BY sal DESC) 순위
    FROM emp
) WHERE 순위 < (SELECT COUNT(*) FROM emp) * 0.1;

-- 급여 하위 10%
-- 연봉을 오름차순으로 정렬하기 때문에 10% 보다 작은 순위들이 급여 하위 10%
SELECT empNo, name, dept, pos, sal FROM (
    SELECT empNo, name, dept, pos, sal,
    RANK() OVER( ORDER BY sal ) 순위
    FROM emp
) WHERE 순위 < (SELECT COUNT(*) FROM emp) * 0.1;

-- dept별 급여(sal+bonus)가 가장 높은 name, dept, pos, sal, bonus 출력
SELECT name, dept, pos, sal, bonus FROM (
    SELECT name, dept, pos, sal, bonus, 
    RANK() OVER( PARTITION BY dept ORDER BY sal+bonus DESC) 순위
    FROM EMP
    -- 부서별로 연봉+보너스의 값으로 순위를 매김
) WHERE 순위 = 1;
-- 부서별 연봉+보너스 순위가 1인 사람들만 출력

-- dept별 여자인원수가 가장 많은 부서 및 인원수 출력
-- 잘라서 가져올 수 있으면 자르는 것이 1순위
SELECT dept, COUNT(*) 인원수
FROM emp
WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0
GROUP BY dept;
-- WHERE 절로 emp 테이블에서 여자들만 빼와서 부서별로 여자들의 인원수를 출력함.

SELECT dept, COUNT(*) 인원수,
    RANK() OVER( ORDER BY COUNT(*) DESC )순위
FROM emp
WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0
GROUP BY dept;
-- 위에서 여자들의 인원수 내림차순으로 순위를 매겨 출력한다.

SELECT dept, 인원수 FROM ( 
    SELECT dept, COUNT(*) 인원수,
        RANK() OVER( ORDER BY COUNT(*) DESC )순위
    FROM emp 
    WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0 
    GROUP BY dept
) WHERE 순위 = 1;
-- 위의 쿼리에서 순위가 1인 부서와 그 부서의 여자인원수를 출력한다.

RANK() OVER() 함수와

DENSE_RANK() OVER() 함수, ROW_NUMBER() OVER() 함수의 차이점

SELECT name, sal, RANK() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT name, sal, DENSE_RANK() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT name, sal, ROW_NUMBER() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT RANK( 3000000 ) WITHIN GROUP ( ORDER BY sal DESC ) 순위
FROM emp;

RANK() OVER() 함수는 동순위를 같은 순위를 주고 그 인원수 만큼 더한 후에 다음 순위를 준다. 위의 사진을 보면 제일 왼쪽의 숫자 10, 11은 같은 sal을 갖고 있기 때문에 순위가 10위로 동일하다. 그 다음 순위인 11위는 없고 12위가 있다.

DENSE_RANK() OVER() 함수는 동순위를 같은 순위를 주고, 동순위와 관계없이 다음순위를 준다. 위의 예시 참고.

ROW_NUMBER() OVER() 함수는 같은 sal을 가지고 있는 사람이더라도 순위를 각각 매겨 동순위가 존재하지 않는다. ( 같은 sal을 가지고 있을 때 누가 먼저 순위를 갖느냐는 무작위)

RANK() WITHIN GROUP () 

RANK( 여기 값) 이 WITHIN GROUP (순위를 매기는 기준이 되는 그룹) 에서 몇 번째 순위이냐를 출력해주는 것으로 16위임을 알 수 있다. (위의 사진은 맞는지 확인을 위한 사진) 

ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_CURRENCY = '￦';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

SELECT * FROM emp;

-- TO_NUMBER 문자열을 숫자로 변경
SELECT '23,123' + 10 FROM dual; -- 에러
SELECT REPLACE('23,123', ',') + 10 FROM dual; 
-- 이것도 되지만
SELECT TO_NUMBER('23,123','99,999') + 10 FROM dual;
-- 이것을 통해서도 가능

-- TO_DATE 문자열을 날짜로 변경
SELECT TO_DATE('900101') FROM dual;
-- 가능할 수도 있고 오류가 발생할 수도 있다.
SELECT TO_DATE('900101', 'RRMMDD') FROM dual;
-- RR은 조심해야함. 문제가 발생될 수 있다.

-- TO_TIMESTAMP 
-- 문자열을 TIMESTAMP로 변환
-- DATE는 YYYY-MM-DD HH24:MI:SS 까지만 표현 가능
-- 밀리초는 TIMESTAMP 사용
-- SYSDATE - > DATE 형, SYSTIMESTAMP -> TIMESTAMP 형
SELECT SYSDATE, SYSTIMESTAMP FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
        TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF4') 
FROM dual;
-- .FF4 밀리세컨드를 소수점 4자리까지 표현
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
        TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') 
FROM dual;

SELECT TO_TIMESTAMP('2021-08-06 12:24:35.200', 'YYYY-MM-DD HH24:MI:SS.FF3')
FROM dual;

-- NULL 
-- NVL (expr1, expr2) expr1이 NULL이면 expr2로 치환
-- NVL2 (expr1, expr2, expr3) expr1이 NULL이 아니면 expr2, NULL이면 expr3
SELECT name, NVL2(tel, tel, '전화없음')tel FROM emp;
SELECT name, NVL2(tel, '있다', '없다')tel FROM emp;

-- NULLIF(expr1, expr2) : expr1과 expr2가 같으면 NULL 다르면 expr1 반환
SELECT NULLIF(1, 1), NULLIF(1, 2) FROM dual;

-- COALESCE(expr [, expr ] ...) null 이 아닌 처음 값
SELECT COALESCE(null, 1, 2) FROM dual;

-- COUNT : 자료의 개수
SELECT COUNT(*) FROM emp; -- 모든 행의 수(전체 개수)
SELECT COUNT( empNO ) FROM emp; -- NULL은 COUNT하지 않음
-- *나 PRIMARY KEY, NOT NULL 컬럼을 이용하여 COUNT하면 전체 행수가 출력

SELECT COUNT( tel ) FROM emp; -- NULL은 COUNT하지 않음.

SELECT name, COUNT( empNO ) FROM emp; -- 에러
-- GROUP BY를 사용하지 않는 경우 일반 컬럼과 집계함수는 함께 사용 불가

-- 서울 사람 인원 수
SELECT COUNT( empNO ) FROM emp WHERE city = '서울';

-- 조건 만족하는 행수가 하나도 없으면 COUNT는 0 출력
SELECT COUNT ( empNO ) FROM emp WHERE 1 = 2;

SELECT COUNT(dept) FROM emp;
SELECT DISTINCT dept FROM emp;
SELECT COUNT ( DISTINCT dept ) FROM emp;

-- 전체인원수, 남자인원수, 여자인원수
SELECT COUNT(empNo) 전체인원, 
    COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) 여자인원,
    COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 남자인원
FROM emp;

SELECT COUNT(empNo) 전체인원, 
    COUNT ( NULLIF(MOD(SUBSTR(rrn, 8, 1), 2), 1) ) 여자인원,
    COUNT ( NULLIF(MOD(SUBSTR(rrn, 8, 1), 2), 0) ) 남자인원
FROM emp;

SELECT COUNT(empNo) 전체인원 FROM emp;

-- 서울 남자 인원수
SELECT COUNT(empNo) 서울남자
FROM EMP
WHERE city = '서울' AND MOD(SUBSTR(rrn, 8, 1), 2) = 1;

SELECT COUNT( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 서울남자
FROM EMP
WHERE city = '서울'; -- 효율이 안좋음
-- WHERE 로 짤라버리고 연산하는 것이 더 효율적이다.


SELECT '전체' 구분, COUNT(*) 인원 FROM emp
    UNION ALL
SELECT '남자' 구분, COUNT(*) 인원 FROM EMP WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
    UNION ALL
SELECT '여자' 구분, COUNT(*) 인원 FROM emp WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
ORDER BY 인원 DESC;

-- MAX 최대값
-- MIN 최소값
-- AVG 평균
-- SUM 합
SELECT SUM(sal), AVG(sal), MAX(sal), MIN(sal)
FROM emp;

SELECT sal FROM emp WHERE 1 = 2; -- 한줄도 출력 안됨

SELECT SUM(sal) FROM emp WHERE 1 = 2; 
-- NULL (한줄), 조건 만족하는 것이 없으면 한 줄로 NULL 출력
SELECT AVG(sal) FROM emp WHERE 1 = 2; 
-- NULL (한줄), 조건이 만족하는 것이 없으면 한 줄로 NULL 출력
SELECT NVL(AVG(sal), 0) FROM emp WHERE 1 = 2; -- 0

SELECT SUM(sal) 전체급여합,
    SUM ( DECODE ( MOD( SUBSTR(rrn, 8, 1), 2), 1, sal) ) 남자급여합,
    SUM ( DECODE ( MOD( SUBSTR(rrn, 8, 1), 2), 0, sal) ) 여자급여합
FROM emp;

SELECT '전체' 구분, SUM(sal) 합계 FROM EMP
    UNION ALL
SELECT '남자' 구분, SUM(sal) 합계 FROM emp WHERE MOD( SUBSTR( rrn, 8, 1), 2) = 1
    UNION ALL
SELECT '여자' 구분, SUM(sal) 합계 FROM emp WHERE MOD ( SUBSTR(rrn, 8, 1), 2) = 0;

SELECT COUNT(*) 전체,
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 1, 1)) "1월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 2, 1)) "2월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 3, 1)) "3월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 4, 1)) "4월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 5, 1)) "5월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 6, 1)) "6월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 7, 1)) "7월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 8, 1)) "8월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 9, 1)) "9월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 10, 1)) "10월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 11, 1)) "11월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 12, 1)) "12월"
FROM emp;

SELECT MAX(sal) FROM emp;
SELECT name, sal FROM emp WHERE sal = MAX(sal); 
-- 에러. WHERE 절에는 그룹함수 사용 불가

SELECT name, SAL
FROM EMP
WHERE sal = (SELECT MAX(sal) FROM emp) ;

SELECT name, SAL
FROM EMP
WHERE sal < (SELECT AVG(sal) FROM emp) ; -- 평균보다 적게 받는 사람

-- GROUP BY 절 사용 예
SELECT SUM(sal) FROM emp; -- 전체 총합 한 컬럼
SELECT '영업부' 부서, SUM(sal) 합계 FROM emp WHERE dept = '영업부'
    UNION ALL
SELECT '개발부' 부서, SUM(sal) 합계 FROM emp WHERE dept = '개발부';

SELECT SUM(sal)
FROM EMP
GROUP BY dept;

SELECT dept, SUM(sal)
FROM EMP
GROUP BY dept; -- GROUP BY 에 있는 컬럼은 그룹함수와 같이 사용 가능

-- dept의 pos별 급여 총합
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY dept, pos
ORDER BY dept, pos;

-- 부서별 인원수(dept, 인원수)
SELECT dept, COUNT(dept)
FROM EMP
GROUP BY dept;

-- 부서별 여자 인원수(dept, 인원수)
SELECT DISTINCT dept FROM emp;

SELECT dept, COUNT(dept) 여자인원
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT;
-- 문제 : 여자 인원수가 없는 부서는 출력되지 않음. (인사부)
-- 실행 순서 FROM 절 -> WHERE 절-> GROUP BY 절 -> HAVING 절-> SELECT 절 -> ORDER BY 절

SELECT dept, COUNT ( DECODE ( MOD ( SUBSTR ( rrn, 8, 1), 2), 0, 1)) 여자
FROM EMP
GROUP BY dept; -- 여자 인원수가 없는 부서도  출력


-- 개발부 인원수
SELECT dept, COUNT(dept)
FROM EMP
WHERE dept = '개발부'
GROUP BY dept;

SELECT COUNT(dept)
FROM EMP
WHERE dept = '개발부';


-- 부서별 전체, 남자, 여자 인원수
-- dept 전체 남자 여자
SELECT dept, COUNT(dept) "부서별 전체인원",
COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) 여자인원,
COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 남자인원
FROM EMP
GROUP BY dept;

-- 부서별 남자와 여자 비율(부서 인원수 대비)
SELECT dept, 
ROUND ( COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) / COUNT(*) * 100 ) 여자비율,
ROUND ( COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) / COUNT(*) * 100 ) 남자비율
FROM EMP
GROUP BY dept;

-- 부서별 남자와 여자 급여 총합, 평균
SELECT dept, DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자') 성별,
    SUM(sal) 총급여, TRUNC(AVG(sal)) "평균 급여"
FROM emp
GROUP BY dept, DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
ORDER BY dept, 성별;

-- HAVING 절 사용 예 : GROUP BY 결과에 대한 조건, 그룹함수 사용 가능
-- 부서별 인원수
SELECT dept, COUNT(*)
FROM emp
GROUP BY dept;

-- 부서별 인원수가 7명 이상인 부서만 출력
SELECT dept, COUNT(*)
FROM emp
GROUP BY dept
HAVING COUNT(*) >= 7;

-- 부서별 여자인원수(dept 인원수)
SELECT dept, COUNT(dept) 여자인원수
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT;

-- 부서별 여자인원수가 5명인 부서와 인원수 출력
SELECT dept, COUNT(dept) 여자인원수
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT
HAVING COUNT(dept) >= 5;

-- SUBQUERY
-- SELECT, INSERT, UPDATE, DELETE 문 등에서 사용되는 SELECT문
-- SELECT 문에서는 SELECT절, FROM 절, WHERE 절 등에서 사용 가능
-- WHERE 절에서 사용하는 경우 하나의 컬러만 가능하다.
-- WHERE 절에서 사용하는 경우 >, >=, <, <=, =, != 등과 비교할 경우 한 행만 가능하다.
-- WHERE 절에서 사용하는 경우 IN, ANY 등과 비교할 경우 여러행도 가능하다.

SELECT name, SAL
FROM EMP
WHERE sal = (SELECT MAX(sal), MIN(sal) FROM emp); -- 에러
-- WHERE 절의 서브쿼리에 컬럼이 두개 있음.

SELECT name, SAL
FROM EMP
WHERE sal > (SELECT sal FROM emp WHERE city='서울'); -- 에러
-- WHERE 절의 서브쿼리에서 > 으로 비교하는데 여러행이므로

SELECT name, SAL, city
FROM EMP
WHERE sal IN (SELECT sal FROM emp WHERE city='서울'); -- 가능

-- name, sal 평균보다 많이 받는 사람
SELECT name, SAL
FROM EMP
WHERE sal > (SELECT AVG(sal) FROM emp) ; -- 에러

-- 서울 사람이 아닌 사람 중 서울 평균보다 많이 받는 사람
SELECT name, SAL, city
FROM EMP
WHERE city !='서울' AND sal > (SELECT AVG(sal) FROM emp WHERE city = '서울');

-- name, sal, 평균급여와 차이
SELECT name, sal, sal - AVG(sal) FROM emp; -- 에러

SELECT name, SAL, sal - (SELECT AVG(sal) FROM emp ) 차이
FROM emp;

-- sal+bonus 가 가장 많은 사람
SELECT name, sal, bonus, sal+bonus pay
FROM EMP
WHERE sal+bonus = ( SELECT MAX(sal+bonus) FROM emp );

-- 부서별 인원수가 가장 많은 부서명과 인원수
-- 부서별 인원수
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept;

SELECT MAX( COUNT(*) )
FROM EMP
GROUP BY dept;

SELECT dept, COUNT(*) 인원수
FROM EMP
GROUP BY dept
HAVING COUNT(*) = 
( SELECT MAX( COUNT(*) ) FROM emp GROUP BY dept);

-- 입사년도 (hireDate)별 인원수가 가장 많은 년도 및 인원수
-- 입사년도별 인원수
SELECT TO_CHAR(hireDate, 'YYYY') 년도, COUNT(*)
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY') ;

-- 가장 많은 년도 및 인원수
SELECT TO_CHAR(hireDate, 'YYYY') 년도, COUNT(*)
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY')
HAVING COUNT(*) = ( SELECT MAX ( COUNT(*) ) FROM emp GROUP BY TO_CHAR(hireDate, 'YYYY'));

-- 생일이 동일한 사람이 2명 이상인 경우의 name, birth 출력.
-- 단 출력은 생년월일 중 월일오름차순

SELECT name, TO_DATE( SUBSTR( rrn, 1, 6), 'RRMMDD') birth
FROM emp
ORDER BY TO_CHAR(birth, 'MMDD');

SELECT SUBSTR( rrn, 3, 4), COUNT(*)
FROM EMP
GROUP BY SUBSTR( rrn, 3, 4)
HAVING COUNT( SUBSTR ( rrn, 3, 4) ) >= 2;

SELECT name, TO_DATE( SUBSTR( rrn, 1, 6), 'RRMMDD') birth
FROM emp
WHERE SUBSTR(rrn, 3, 4) IN 
(
    SELECT SUBSTR( rrn, 3, 4)
    FROM EMP
    GROUP BY SUBSTR( rrn, 3, 4)
    HAVING COUNT(*) >= 2
)
ORDER BY TO_CHAR(birth, 'MMDD');

-- NULL 값이 없는 상태. 문자열의 길이가 0이면 오라클은 NULL
-- IS 는 NULL인지를 확인하는 유일한 방법
-- 컬럼 IS [ NOT ] NULL

-- CASE  END : 조건에 따른 결과 반환
-- DECO`DE 함수 : 검색값과 비교하여 같으면 해당 결과 반환. 하지만 CASE ~ END보다 느리다.
-- DISTINCT : 선택 행 중에서 중복적인 행은 한번만 출력

-- 집합
-- UNION
-- UNION ALL

-- ROWNUM : 쿼리 결과로 나오는 각각의 행들에 대한 순서 값. 
-- 1부터 시작 작거나 같다로만 사용 가능

-- LENGTH(char) : 문자열의 길이 반환(문자 개수)
SELECT LENGTH('대한민국') FROM dual; 
-- 문자의 개수 반환 : 4
SELECT LENGTHB('대한민국')FROM dual; 
-- 문자열의 바이트수 반환. 한글은 3byte (UTF-8)

-- REPLACE(char , search_string [, replacement_string]) : 특정 문자열을 다른 문자열로 치환
SELECT REPLACE('seoul korea', 'seoul', 'busan') FROM dual;
-- seoul을 busan으로 치환하라는 의미
SELECT REPLACE('12345123458525', '5') FROM dual;
-- 인자를 두 개만 주면 두 번째에 해당하는 문자를 문자열에서 제거 (5 제거)
SELECT name, REPLACE(dept, '부', '팀') dept FROM emp;
-- 부를 팀으로 변경. 중간에 부가 있어도 변경 : '__부' 말고 '부_부' 뭐 이런 것에서 부가 다 팀으로 바뀜
SELECT name, SUBSTR(dept, 1, 2) || '팀' dept FROM emp;
-- 부서의 이름이 4자 이상이면 문제 발생.
SELECT name, SUBSTR(dept, 1, LENGTH(dept)-1) || '팀' dept FROM emp;
-- 가장 마지막에 있는 글자를 팀으로 바꿈 : 제일 문제발생의 여지가 없다

-- CONCAT(char1, char2) : 문자열 결합
SELECT CONCAT('대한', '민국') FROM dual;
SELECT '대한'||'민국' FROM dual;

-- LPAD(expr1, n [, expr2]) 남는 왼쪽 공간에 특정 문자로 채움
-- RPAD(expr1, n [, expr2]) 남는 오른쪽 공간에 특정 문자로 채움
SELECT LPAD('korea', 12, '*') FROM dual;
SELECT RPAD('korea', 12, '*') FROM dual;
SELECT LPAD('korea', 3, '*') FROM dual;
SELECT LPAD('korea', 0, '*') FROM dual;
-- 0은 NULL
SELECT LPAD('대한', 6, '*') FROM dual;
-- 한글은 2칸으로 처리. **대한 으로 출력.

-- emp테이블 : name, rrn(성별 다음부터는 *로 출력)
SELECT name, RPAD( SUBSTR(rrn, 1, 8 ), 14, '*')
FROM emp;
-- emp테이블 : name, tel(전화번호 마지막 3자리는 *로 출력)
SELECT  name, RPAD( SUBSTR(tel, 1, length(tel) -3), length(tel), '*')
FROM emp;
-- emp 테이블 : name, sal, 그래프 ( sal 10만원당 *하나씩 출력)
SELECT name, sal, RPAD ( '*', TRUNC(sal/100000), '*') 그래프 
FROM emp;
-- scott 계정 emp 테이블
    -- deptno 가 10인 사원의 이름(ename)과 이름(ename)을 총 9자리로 출력하되 
    -- 오른쪽 빈자리는 해당 자릿수로 출력
SELECT * FROM emp;

SELECT RPAD( ename, 9, (SUBSTR('123456789', LENGTH(ename)+1) ) )
FROM emp
WHERE deptno = 10;

-- LTRIM(char [, set])
-- RTRIM(char [, set])
-- TRIM([[LEADING | TRAILING | BOTH] trim_character FROM] trim_score) 
-- 공백 또는 특정 문자열 제거
SELECT ':' || LTRIM('    우리  나라      ') || ':' FROM dual;
SELECT ':' || RTRIM('    우리  나라      ') || ':' FROM dual;
SELECT ':' || TRIM('    우리   나라      ') || ':' FROM dual;

SELECT LTRIM('AABBBBCDAC', 'BA') FROM dual;

SELECT RTRIM('대한우리나라대한', '대한') FROM dual;
SELECT TRIM('A' FROM 'AABBACCCAA') FROM dual;

SELECT name, RTRIM(dept, '부') || '팀' dept FROM emp;

-- TRANSTALE(expr, from_string, to_string) : 치환
SELECT TRANSLATE('ababbcca', 'c', 'd') FROM dual;

SELECT TRANSLATE('2KAB35CC', 
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', -- (1)
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') FROM dual; -- (2)
-- (1) 에 해당되는 문자를 (2)로 치환한다.
-- 즉 0도 9, 1도 9, 2도 9 ...
-- A 도 X, B도 X, C도 X ...

SELECT TRANSLATE('2KAB35CC', 
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'0123456789') FROM dual;
-- 영문자는 제거

-- 날짜 + 숫자 : 숫자 만큼의 일 수를 날짜에 더함.
-- 날짜 - 숫자 : 숫자 만큼의 일수를 날짜에서 뺌
-- 날짜 + 숫자/24 : 숫자 만큼의 시간을 날짜에 더함
-- 날짜1 - 날짜2 : 날자 1에서 날짜 2를 빼면 두 날짜 사이의 일수가 나온다.

SELECT SYSDATE, CURRENT_DATE FROM dual;
SELECT SYSDATE - 1 FROM dual;
SELECT TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SELECT SYSDATE - 1/24 FROM dual;
SELECT TO_CHAR(SYSDATE - 1/24, 'YYYY-MM-DD HH24:MI:SS') 
FROM dual; -- 1시간 빼기

SELECT SYSDATE - 1/24/60 FROM dual;
SELECT TO_CHAR(SYSDATE - 1/24/60, 'YYYY-MM-DD HH24:MI:SS') 
FROM dual; -- 1분 빼기

-- 문자열을 날짜로 변환
SELECT TO_DATE('2000-10-10', 'YYYY-MM-DD') FROM dual;
-- 오늘까지 살아온 날 수
SELECT TRUNC(SYSDATE - TO_DATE('1996-09-19', 'YYYY-MM-DD') ) FROM dual;
-- 홍길동이 2021년 7월 10일에 여자친구를 만났다. 100일 후는 ?
SELECT TO_DATE('2021-07-10', 'YYYY-MM-DD') + 100 FROM dual;
-- 2021-12-25일까지의 디데이
SELECT TRUNC(TO_DATE('2021-12-25', 'YYYY-MM-DD') - SYSDATE) FROM dual;

-- emp 테이블에서 입사한지 100일이 되지 않은 사원 출력 : name, hireDate
SELECT name, hireDate,  TRUNC(SYSDATE - hireDate) 근무일수
FROM emp
WHERE (SYSDATE - hireDate) < 100;

-- 1년 후 오늘
SELECT SYSDATE + (INTERVAL '1' YEAR) FROM dual;
-- 1년 전 오늘
SELECT SYSDATE - (INTERVAL '1' YEAR) FROM dual;

-- 1달 전
SELECT SYSDATE + (INTERVAL '1' MONTH) FROM dual;
-- 1달 후
SELECT SYSDATE - (INTERVAL '1' MONTH) FROM dual;

-- 1일 전
SELECT SYSDATE + (INTERVAL '1' DAY) FROM dual;
-- 1일 후
SELECT SYSDATE - (INTERVAL '1' DAY) FROM dual;

-- 1시간 전
SELECT SYSDATE + (INTERVAL '1' HOUR) FROM dual;
-- 1시간 후
SELECT SYSDATE - (INTERVAL '1' HOUR) FROM dual;

-- 1분 후
SELECT SYSDATE + (INTERVAL '1' MINUTE) FROM dual;
-- 1초 후
SELECT SYSDATE - (INTERVAL '1' SECOND) FROM dual;

-- 2시간 30분 전
SELECT SYSDATE - ( INTERVAL '02:30' HOUR TO MINUTE) FROM dual;

SELECT TO_CHAR( SYSDATE - ( INTERVAL '02:30' HOUR TO MINUTE), 'YYYY-MM-DD HH24:MI:SS') 
FROM dual;

-- 근속년수가 1년 미만인 사람 출력 : name ,hireDate
SELECT name, hireDate
FROM emp
WHERE (hireDate + (INTERVAL '1' YEAR)) > SYSDATE ;

-- 날짜 함수 종류
-- SYSDATE : 현재 시스템 날짜 및 시간(YYYY-MM-DD HH24:MI:SS). ms는 출력 안됨
-- CURRENT_DATE : 현재 시스템 날짜를 그레고리력 값으로 반환
-- SYSTIMESTAMP : ms 까지 출력

SELECT SYSDATE, CURRENT_DATE FROM dual;
SELECT SYSTIMESTAMP FROM dual;

-- 근무일수
SELECT name, TRUNC(SYSDATE - hireDate) 근무일수 FROM emp;
SELECT name, (SYSDATE - hireDate) 근무일수 FROM emp;

-- EXTRACT 지정된 날짜 시간 필드 값을 추출
SELECT EXTRACT( YEAR FROM SYSDATE ) FROM dual;
SELECT EXTRACT( MONTH FROM SYSDATE ) FROM dual;
SELECT EXTRACT( DAY FROM SYSDATE ) FROM dual;

-- name, hireDate, 입사년도
SELECT name, hireDate, EXTRACT( YEAR FROM hireDate) 입사년도
FROM emp;

-- name, hireDate : 2010년 이후에 입사한 사람
SELECT name, hireDate, EXTRACT( YEAR FROM hireDate) 입사년도
FROM emp
WHERE EXTRACT( YEAR FROM hireDate) > = 2010;

-- MONTHS_BETWEEN(date1, date2) : 날짜 사이의 월 수
-- 문자열을 날짜로 변환 
SELECT TO_DATE('2000-10-10', 'YYYY-MM-DD') FROM dual; -- 되도록 이렇게 쓰는 것을 권장
SELECT TO_DATE('2000-10-10') FROM dual; -- 가능할 수도 있고 불가능할 수도 있음.

SELECT MONTHS_BETWEEN(
TO_DATE('2021-08-05', 'YYYY-MM-DD'), TO_DATE('2021-07-02', 'YYYY-MM-DD') ) 
FROM dual;

-- name, hireDate, 근무년수
SELECT name, hireDate,
TRUNC(MONTHS_BETWEEN(SYSDATE, hireDate) / 12 ) 근무년수
FROM emp;

-- 날짜 포맷
SELECT TO_DATE('80/05/05', 'YY-MM-DD') FROM dual; -- YY는 현재 시스템 날짜 기준
SELECT TO_CHAR(TO_DATE('80/05/05', 'YY-MM-DD'), 'YYYY-MM-DD') FROM dual;

SELECT TO_DATE('80/05/05', 'RR-MM-DD') FROM dual;  
SELECT TO_CHAR(TO_DATE('80/05/05', 'RR-MM-DD'), 'YYYY-MM-DD') FROM dual; 

SELECT TO_DATE('48/05/05', 'RR-MM-DD') FROM dual;  
SELECT TO_CHAR(TO_DATE('48/05/05', 'RR-MM-DD'), 'YYYY-MM-DD') FROM dual; 
-- 현재 세기가 0~49사이에 있으므로

-- name, rrn, 성별, 생년월일, 나이
SELECT name, rrn, DECODE( MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별 
FROM emp;

SELECT name, rrn, DECODE( MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
TO_CHAR( TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 'YYYY-MM-DD') 생년월일 FROM emp; -- 년도의 RR은 문제를 발생할 수 있다.

SELECT name, rrn, DECODE( MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
TO_CHAR( TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 'YYYY-MM-DD') 생년월일,
TRUNC( MONTHS_BETWEEN ( SYSDATE, TO_DATE( SUBSTR(rrn, 1, 6), 'RRMMDD') ) / 12 ) 나이
FROM emp; -- 년도의 RR은 문제를 발생할 수 있다.
-- 생년월일, 나이 등을 계산할 때 RR은 문제를 발생할 수 있다.
-- 2021년도에 48년을 RR로 표현하면 2048년이 되기 때문이다.

WITH tb AS(
    SELECT empNo, name, rrn,
        DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') gender,
        TO_DATE( -- 날짜자료형으로 바꾼다.
            CASE
                WHEN SUBSTR(rrn, 8, 1) IN (1, 2, 5, 6) THEN '19' -- 년도
                WHEN SUBSTR(rrn, 8, 1) IN (3, 4, 7, 8) THEN '20'
                ELSE '18'
            END || SUBSTR(rrn, 1, 6), 'YYYYMMDD'
        ) birth
    FROM emp
)
SELECT empNo, name, rrn, gender, TO_CHAR(birth, 'YYYY-MM-DD') birth,
    TRUNC(MONTHS_BETWEEN(SYSDATE, birth) / 12 ) age
FROM tb;

-- ADD_MONTHS(date, integer) : 날짜에 개월을 더한다.
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1) 다음달 FROM dual;
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -1) 이전달 FROM dual;

SELECT ADD_MONTHS( TO_DATE('20210330', 'YYYYMMDD'), 6),
    ADD_MONTHS( TO_DATE('20210331', 'YYYYMMDD'), 6)
FROM dual;

-- 최근 6개월 이내 입사한 사람 : name, hireDate
SELECT name, hireDate
FROM emp
WHERE ADD_MONTHS(hireDate, 6) > SYSDATE; -- 6개월까지 포함하고 싶으면 = 을 붙임

-- LAST_DAY(date) : 월의 마지막 일자 (28, 29, 30, 31)
SELECT SYSDATE, LAST_DAY( SYSDATE ) FROM dual;

-- ROUND(date [, fmt]) : 지정된 단위로 반올림
-- YEAR : 7월 1일 기준 / MONTH : 16일 기준
SELECT ROUND( TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'YEAR') FROM dual;
-- 2008-01-01
SELECT ROUND( TO_DATE('2007-06-10', 'YYYY-MM-DD'), 'YEAR') FROM dual;
-- 2007-01-01

SELECT ROUND( TO_DATE('2007-07-20', 'YYYY-MM-DD'), 'MONTH') FROM dual;
-- 2007-08-01
SELECT ROUND( TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'MONTH') FROM dual;
-- 2007-07-01

-- TRUNC(date [, fmt]) : 날짜를 내림
SELECT TRUNC( TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'YEAR') FROM dual;
SELECT TRUNC( TO_DATE('2007-08-10', 'YYYY-MM-DD'), 'YEAR') FROM dual;

SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM dual;

-- 홍길동의 생년월일은 1995-10-15일 입니다. 생일까지 몇일 남아 있나요 ? 
SELECT name, TO_CHAR( TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 'YYYYMMDD' ) 생년월일 
FROM emp
WHERE name = '홍길동';

SELECT 
    TRUNC( TO_DATE(EXTRACT ( YEAR FROM SYSDATE) || SUBSTR('1995-10-15', 5), 'YYYY-MM-DD')
    - TRUNC( SYSDATE) ) 남은일자
FROM dual;

-- emp : name, rrn, birth 생일까지 남은 일자
WITH tb AS (
    SELECT name, rrn,
        TO_DATE ( SUBSTR(rrn, 1, 6), 'RRMMDD' ) birth,
        TO_DATE (EXTRACT( YEAR FROM SYSDATE) || SUBSTR(rrn, 3, 4), 'YYYYMMDD') sdate
    FROM emp -- 올해생일로 맞춰줌
)
SELECT name, rrn, birth,
    CASE 
        WHEN TRUNC ( SYSDATE ) <= sdate THEN TRUNC( sdate - TRUNC( SYSDATE ) )
        -- sdate가 크다는 것 - 아직 생일이 지나지 않음 따라서 오늘날짜를 빼줌
        ELSE TRUNC( ( sdate + (INTERVAL '1' YEAR) ) - TRUNC( SYSDATE) ) 
        -- 올해에 이미 생일이 지났으면, sdate에 1년을 더 더해주고 오늘날짜를 뺌
    END 남은일자
FROM tb;

-- NEXT_DAT(date, char) : 이름(char)으로 지정된 첫번째 요일 반환
-- char은 숫자로 지정가능(1:일, 2:월,...7)
SELECT SYSDATE, NEXT_DAY(SYSDATE, '토요일') FROM dual;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM dual;

SELECT SYSDATE, NEXT_DAY(SYSDATE, 7) FROM dual;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 5) FROM dual;

-- 오늘, 이번주 일요일, 이번주 토요일 출력
SELECT SYSDATE, NEXT_DAY( SYSDATE-7 , 1), NEXT_DAY( SYSDATE-1, 7)  FROM dual;
SELECT SYSDATE, NEXT_DAY( SYSDATE , 1)-7, NEXT_DAY( SYSDATE-1, 7)  FROM dual;
-- 오늘 이후의 다가오는 토요일이 나오기 때문에, 토요일일 경우는 -1을 하지않으면 다음주 토요일이 나옴.

SELECT TO_DATE('20210808', 'YYYYMMDD'), -- 형식을 안넣으면 다른 환경에서는 (MAC, LINUX) 안됨.
    NEXT_DAY(TO_DATE('20210808', 'YYYYMMDD') , 1)-7,
    NEXT_DAY(TO_DATE('20210808', 'YYYYMMDD')-1, 7)
FROM dual;

-- 단일행 변환 함수
-- 암시적 형 변환 - 오라클 서버에 의해 자동 형 변환
SELECT 30 + '30' FROM dual; -- 60. 자동으로 문자열이 숫자로 변환
SELECT 30 || '30' FROM dual; -- 3030. 자동으로 숫자가 문자열로 변환
SELECT 20 || '대' FROM dual; -- 자동으로 숫자가 문자열로 변환

SELECT 30 + '3,300' FROM dual; -- 에러. , 가 있어서 숫자로 바꾸지 못함.

SELECT SYSDATE - ' 2021-08-01' FROM dual; --에러

SELECT * FROM NLS_SESSION_PARAMETERS;

-- 날짜 출력 형식 변경 (디폴트 : RR/MM/DD)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- 변환 함수
-- TO_CHAR(n [, fmt[, 'nlsparam'] ] ) 숫자를 문자로 변환
SELECT TO_CHAR(12345, '999,999') FROM dual;
SELECT TO_CHAR(12345, '9,999') FROM dual 
-- ##### 자리수가 부족하면 #으로 표시
SELECT TO_CHAR(12345, '0,999,999') FROM dual; -- 0, 012,345
-- 남는 자리수는 0으로 채움
SELECT TO_CHAR(12.67, '99') FROM dual; -- 13 반올림
SELECT TO_CHAR(12.34, '99') FROM dual; -- 12
SELECT TO_CHAR(12.67, '99.9') FROM dual; -- 12.7
SELECT TO_CHAR(0.03, '99.9') FROM dual; -- .0
SELECT TO_CHAR(36, '99.9') FROM dual;  -- 36.0
SELECT TO_CHAR(36, '99.0') FROM dual;  -- 36.0
SELECT TO_CHAR(0, '99') FROM dual;  -- 0

SELECT TO_CHAR(1234, '9999MI') FROM dual;  -- 1234
SELECT TO_CHAR(-1234, '9999MI') FROM dual; -- 1234-

SELECT TO_CHAR(1234, '9999PR') FROM dual; -- 1234
SELECT TO_CHAR(-1234, '9999PR') FROM dual; -- <1234>

SELECT TO_CHAR(1234.345, '9.999EEEE') FROM dual; -- 1.234E+03
SELECT TO_CHAR(300, '9999V9999') FROM dual;  -- 3000000

SELECT TO_CHAR(1234, 'L9,999,999') FROM dual; 
SELECT TO_CHAR(1234, '9,999,999')||'원' FROM dual; --1,234원

SELECT name, sal + bonus pay FROM emp;

SELECT name, TO_CHAR(sal + bonus, 'L99,999') pay FROM emp;
SELECT name, TO_CHAR(sal + bonus, 'L99,999,999') pay FROM emp;

-- TO_CHAR : 날짜를 문자열로 변환
SELECT TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 지금날짜 FROM dual;

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM dual; -- DAY : 요일 출력ㄴㄴ
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD D') FROM dual; -- D : 요일을 숫자로 (1~7)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY년 MM월 DD일') FROM dual; -- 에러
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일"') FROM dual;

-- 2000년도에 입사한 사람
SELECT name, hireDate 
FROM emp 
WHERE TO_CHAR(hireDate, 'YYYY') = 2000;

SELECT name, 
        TO_CHAR(hireDate, 'YYYY"년"MM"월"DD"일" DAY') 입사일 
FROM emp; 

-- 월을 영어로
SELECT SYSDATE, TO_CHAR( SYSDATE, 'MON DD DAY'),
    TO_CHAR( SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE = korean') ko,
    TO_CHAR( SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE = american') en
FROM dual;

-- DY : 요일을 간단히
SELECT SYSDATE,
    TO_CHAR( SYSDATE, 'Dy MONTH DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') a1,
    TO_CHAR( SYSDATE, 'dy month dd, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') a2,
    TO_CHAR( SYSDATE, 'DY MONTH DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') a3
FROM dual;    

-- 주
    -- w : 월기준(1~5까지 나옴), 1일~7일 : 1주, 8일~14일 2주
    -- ww : 년기준, 1월 1일~ 1월 7일 : 1주, ...(1~ 53주)
    -- iw : 년기준. 1~52 또는 53주. 주는 월요일이 시작. 한해의 1주는 1월 4일을 포함.
SELECT TRUNC( SYSDATE) 오늘,
    TO_CHAR( SYSDATE, 'd') "요일(수치)",
    TO_CHAR( TRUNC( SYSDATE, 'd'), 'YYYYMMDD') "일요일",
    -- TRUNC( SYSDATE, 'd') 이거는 무조건 일요일이 나옴. 주를 기준으로 내림 ( 그 주의 일요일)
    TO_CHAR( SYSDATE, 'w') "월기준주차",
    TO_CHAR( SYSDATE, 'ww') "년기준주차1",
    TO_CHAR( SYSDATE, 'iw') "년기준주차2"
FROM dual;

-- NULL 관련 함수
-- 비교 : 컬럼 IS [NOT] NULL
-- 길이가 0인 문자열도 NULL
SELECT 10+NULL FROM dual; -- NULL

-- NVL(expr1, expr2) : expr1이 NULL이면 expr2를 반환하고 그렇지 않으면 expr1반환
SELECT name, NVL(tel, '전화없음')
FROM emp;

CREATE TABLE userEx (
    empNo  VARCHAR2(10) PRIMARY KEY,
    name    VARCHAR2(30) NOT NULL,
    sal        NUMBER(10)   NOT  NULL,
    bonus   NUMBER(10)
);
INSERT INTO userEx(empNo, name, sal, bonus) VALUES ('1001', '오라클', 2200000, 300000);
INSERT INTO userEx(empNo, name, sal, bonus) VALUES ('1002', '스프링', 2300000, 200000);
INSERT INTO userEx(empNo, name, sal, bonus) VALUES ('1003', '이자바', 2300000, NULL);
INSERT INTO userEx(empNo, name, sal, bonus) VALUES ('1004', '서블릿', 1900000, 200000);
INSERT INTO userEx(empNo, name, sal, bonus) VALUES ('1005', '스파크', 1700000, NULL);
COMMIT;

SELECT * FROM cols;
SELECT * FROM TAB;
SELECT * FROM userEX;

SELECT name, sal, bonus, sal+bonus pay FROM userEx; -- 문제 발생
SELECT name, sal, bonus, sal+NVL(bonus, 0) pay FROM userEx;

-- 문자열 결합 시에는 ||를 사용한다.
-- DUAL 테이블은 오라클이 제공하는 테이블. 한줄 한컬럼
SELECT '대한민국' || '서울'  FROM dual;
SELECT name, sal  FROM  emp;
SELECT name || '님', sal  FROM  emp;

-- USER_TABLES(TABS) 딕셔너리 
-- 사용 예 : 사용자가 소유한 테이블명과 테이블의 테이블스페이스 조회
SELECT * FROM USER_TABLES;
SELECT * FROM TABS;

-- TAB뷰(VIEW) 
-- 사용 예 : 현재 사용자가 소유한 테이블 목록 조회
SELECT * FROM TAB;

-- ALL_TABLES
-- 관리자에서 일반 사용자의 테이블 정보를 확인할 때 사용

-- USER_TAB_COLUMNS(COLS) 딕셔너리
-- 사용 예 : EMP 테이블에 존재하는 모든 컬럼 명, 데이터 타입, 데이터 길이 조회
SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM COLS;

-- COL 뷰(VIEW)
-- 사용 예 : EMP 테이블에 존재하는 모든 컬럼 명, 데이터 타입, 데이터 길이 조회
SELECT * FROM COL;

-- DESCRIBE(DESC)
-- 테이블에 대한 컬럼 정보
DESC emp;