개발새발, 써놓기

가상컬럼 Virtual Column ?

• 디스크에 저장되지 않는다.
• 가상 컬럼은 쿼리, DML, DDL 등을 사용할 수 있다.
• 가상 컬럼에는 INSERT, UPDATE 할 수 없다.
• 다른 테이블의 컬럼을 이용한 가상 컬럼은 생성이 불가하다.
• 가상 컬럼에는 SYSDATE 등 동적인 결과를 출력하는 함수는 사용할 수 없다.

다음 테이블을 생성한다.

-- test2 테이블

hak 문자(30) PRIMARY KEY

name 문자(30) NOT NULL

kor NUMBER(3) NOT NULL

eng NUMBER(3) NOT NULL

mat NUMBER(3) NOT NULL

tot NUMBER(3) 가상컬럼(kor+eng+mat)

ave NUMBER(4, 1) 가상컬럼(kor+eng+mat) / 3

reg_date DATE

CREATE TABLE test2 (
    hak VARCHAR2(30) PRIMARY KEY, -- 숫자로 들어가도 VARCHAR2로 줄 것! 유의
    name VARCHAR2(30) NOT NULL,
    kor NUMBER(3) NOT NULL,
    eng NUMBER(3) NOT NULL,
    mat NUMBER(3) NOT NULL,
    tot NUMBER(3) GENERATED ALWAYS AS (kor + eng + mat) VIRTUAL,
    ave NUMBER(4, 1) GENERATED ALWAYS AS ( (kor + eng + mat) / 3) VIRTUAL,
    -- 총 4자리인데 1은 소수점 한자리
    reg_date DATE
)

SELECT * FROM tab;

INSERT INTO test2(hak, name, kor, eng, mat, reg_date)
    VALUES('11', '호호호', 100, 90, 85, SYSDATE);
SELECT * FROM test2;

INSERT INTO test2 VALUES('22', '너너너', 100, 90, 85, SYSDATE);
    -- 에러. 테이블 뒤에 모든 컬럼이 들어가 있으므로 컬럼과 값의 개수가 일치하지 않는다.
INSERT INTO test2 VALUES('22', '너너너', 90, 90, 90, 270, 90, SYSDATE);
    -- 에러. 가상컬럼은 값을 추가할 수 없다.

다음 테이블을 작성한다.

-- test3 테이블

eno VARCHAR2(30) PRIMARY KEY

name VARCHAR2(30) NOT NULL

pay NUMBER(10) NOT NULL

-- tax는 pay가 3000000이상 일 때 0.03%,

   2000000이상 일 때 0.02%이고 1의 자리에서 절삭한다.

CREATE TABLE test3 (
    eno VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    pay NUMBER(10) NOT NULL,
    tax NUMBER(10) GENERATED ALWAYS AS ( 
    CASE 
        WHEN (pay >= 3000000) then TRUNC(pay * 0.03, -1)
        WHEN (pay >= 2000000) then TRUNC(pay * 0.02, -1)
        ELSE 0 
    END
    ) VIRTUAL
); -- 실무에서는 세금은 매년마다 바뀌기 때문에 이렇게 컬럼으로 추가하면 안된다.

INSERT INTO test3 (eno, name, pay) VALUES('111', '가가가', 3500000);
SELECT * FROM test3; -- 확인

INSERT INTO 테이블명 (컬럼, 컬럼) VALUES (값, 값); 은 한번에 하나의 테이블에 하나의 행만 추가가 가능하다.

서브쿼리를 이용하여 한 번에 여러 행을 추가할 수 있다.

테스트를 위해 새롭게 테이블을 만들어 보자.

--구조만 복사, 데이터는 복사되지 않음.
CREATE TABLE emp1 AS
    SELECT empNo, name, dept, pos FROM emp WHERE 1= 0;

DESC emp1;
SELECT * FROM emp1;

SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

WHERE 절에 1 = 0; 을 줌으로써 항상 false 이므로 값은 가져오지 않고 테이블의 구조만 복사한 것이다. 여기에 밑에 쿼리로 뽑은 개발부의 인원만 추가해 보자.

-- 서브쿼리를 이용하여 한번에 여러행을 추가
INSERT INTO emp1
    SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

COMMIT;
SELECT * FROM emp1;

SELECT를 이용해서 개발부만 뽑은 문장을 INSERT INTO 추가하고싶은테이블명 다음에 넣어서 간단하게 추가할 수 있다.

서브 쿼리를 이용해서 한 번에 여러 행을 추가할 수 있었다.

그럼 한 번에 두 개 이상의 테이블에 여러 행을 추가하려면 어떻게 해야할까 ?

-- 연습할 테이블을 만들어보자
CREATE TABLE emp2 AS
    SELECT empNo, name, dept, pos FROM emp WHERE 1 = 0;
CREATE TABLE emp3 AS
    SELECT empNo, sal, bonus FROM emp WHERE 1 = 0;
-- 구조를 복사해서 테이블을 만들어 줌.

DESC emp2;
DESC emp3;
-- 테이블 구조 확인

-- INSERT ALL 을 이용해서 두 개 이상의 테이블에 여러 행에 추가
INSERT ALL
    INTO emp2 VALUES(empNo, name, dept, pos)
    -- INTO emp2(empNo, name, dept, pos) VALUES(empNo, name, dept, pos)
    INTO emp3 VALUES(empNo, sal, bonus)
    -- INTO emp3(empNo, sal, bonus) VALUES(empNo, name, bonus)
SELECT * FROM emp WHERE dept = '개발부';

-- 개발부의 사람들의 값을 테이블에 넣는다.

COMMIT;

SELECT * FROM emp2;
SELECT * FROM emp3;

INSERT ALL을 이용해서 다음의 데이터를 emp2, emp3에 추가해보자.

empNo '9999' name '너자바' dept '개발부' pos '과장' sal 2500000, bonus 100000

INSERT ALL
    INTO emp2  (empNo, name, dept, pos) VALUES ('9999', '너자바', '개발부', '과장')
    INTO emp3  (empNo, sal, bonus) VALUES ('9999', 2500000, 100000)
SELECT * FROM dual;

SELECT '9999', '너자바', '개발부', '과장', 2500000, 100000 FROM dual;  라고 생각하면 되는 건가! 하고 생각 중 . . .

다른 예. . .emp4 에는 남자들만 넣고 emp5 에는 여자들만 넣어보자.

CREATE TABLE emp4 AS
    SELECT empNo, rrn, name, dept, pos FROM emp WHERE 1 = 0;
CREATE TABLE emp5 AS
    SELECT empNo, rrn, name, dept, pos FROM emp WHERE 1 = 0;

INSERT ALL
    WHEN MOD(SUBSTR(rrn, 8, 1), 2) = 1 THEN
        INTO emp4(empNo, rrn, name, dept, pos) VALUES (empNo, rrn, name, dept, pos)
    ELSE
        INTO emp5(empNo, rrn, name, dept, pos) VALUES (empNo, rrn, name, dept, pos)
SELECT * FROM emp;

COMMIT;

SELECT * FROM emp4;
SELECT * FROM emp5;

WHEN ELSE 를 써서 남자만 emp4에 넣고, 여자만 emp5에 넣는다. 

데이터 조작언어 ( DATA MANIPULATION LANGUAGE, DML) 에 해당하는 INSERT에 대해 알아보자.

INSERT 후 COMMIT 또는 ROLLBACK으로 트랜잭션을 완료 해야 한다.

COMMIT 트랜잭션 완료 (테이블에 저장 완료)ROLLBACK 트랜잭션 취소 (테이블 저장 취소)

데이터 조작언어를 실행 후 DDL(Data Definition Language : CREATE, ALTER, DROP)명령을 실행하면 자동 COMMIT 된다.

예를 들어 INSERT 후 CREATE 명령을 실행하면 INSERT 후 CREATE 명령을 실행하면 INSERT는 COMMIT 된다. 

제약조건을 위반하거나 데이터 타입이 일치하지 않거나, 데이터 폭이 적으면 INSERT는 실행되지 않고 에러가 발생된다.

※ 자바 등 응용프로그램에서 INSERT 하면 기본적으로 자동 COMMIT 된다.

위험한 상황이 발생될 수 있으므로 VS Code를 사용한다면 자동으로 COMMIT 되지 않도록 설정을 해야한다.

SET AUTOCOMMIT OFF;

CREATE TABLE test1 (
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    birth DATE NOT NULL,
    memo VARCHAR2(1000)
);

INSERT INTO test1 VALUES(1, '홍길동', '2000-10-10', '테스트입니다'); 
-- VSCODE 에서는 에러. 년-월-일이 미국식으로 되어있기 때문에 TO_DATE를 써줘야한다.
-- sqldeveloper 에서는 가능. 

INSERT INTO test1 VALUES(1, '홍길동', TO_DATE('2000-10-10', 'YYYY-MM-DD'), '테스트입니다');

SELECT * FROM test1; 
-- 제대로 INSERT가 되었는지 확인

INSERT INTO test1 VALUES(2, '김자바', TO_DATE('2000-11-11', 'YYYY-MM-DD'), '테스트2');
COMMIT; -- 트랜잭션 완료

INSERT INTO test1 VALUES(3, '이이이', TO_DATE('2000-11-11', 'YYYY-MM-DD') );
-- 에러. 컬럼의 개수와 값의 개수가 다름

-- 컬럼명을 명시해서 데이터 추가
INSERT INTO test1 (num, name, birth) VALUES(3, '이이이', TO_DATE('2000-11-11', 'YYYY-MM-DD') );
-- memo는 제약조건이 없기 때문에 NULL이어도 추가가 가능하다. 
-- 하지만 테이블명 뒤에 컬럼명을 명시하지 않으면
-- 가지고 있는 모든 컬럼에 값을 넣는 다는 의미이므로 컬럼의 개수와 값의 개수를 맞춰주어야 한다.

INSERT INTO test1 (num, name, birth, memo) 
    VALUES(1, '나나나', TO_DATE('2000-11-11', 'YYYY-MM-DD'), '테스트' );
-- 에러 ORA-00001 제약조건 위반(기본키 제약 조건 위반)
-- num 은 PRIMARY KEY이므로 중복값을 가질 수 없다.

INSERT INTO test1 (num, name) VALUES(4, '나나나');
-- 에러 ORA-01400: cannot insert NULL into ("SKY"."TEST1"."BIRTH")
-- birth는 NOT NULL로 null을 추가할 수 없다.
INSERT INTO test1 (num, name, birth) VALUES(4, '나나나', '05/05/80' );
-- 에러 : ORA-01843: not a valid month 날짜 형식 오류

INSERT INTO test1 (num, name, birth) VALUES(4, '나나나', TO_DATE('05/05/80', 'MM/DD/RR') );
SELECT * FROM test1; -- INSERT가 잘 되었는지 확인

INSERT INTO test1 (num, name, birth,memo) 
    VALUES(5, '다다다', TO_DATE('90/05/05', 'RR/MM/DD/'), '');
    -- ''는 NULL 임. 문자열 길이가 0임.
    
INSERT INTO test1 (num, name, birth,memo) 
    VALUES(6, '로로로', TO_DATE('90/05/05', 'RR/MM/DD/'), NULL);

INSERT INTO test1 (num, name, birth,memo) 
    VALUES(7, '가나다라마바사아자차파', TO_DATE('90/05/05', 'RR/MM/DD/'), NULL);
    -- 에러 : ORA-12899 입력 문자열길이가 폭보다 크다. 
    -- 한글은 1글자에 3byte를 차지하므로 VARCHAR2에서 30을 줬으므로 10글자만 가능하다.

ROLLBACK; -- 트랜잭션 취소(테이블에 저장되지 않음)

SELECT * FROM test1;
-- 따라서 확인해보면 위에서 COMMIT을 했었던 홍길동과 김자바만 들어가 있는 것을 확인 할 수 있다.

-- test1 테이블에 다음의 컬럼을 추가한다.
-- 컬럼명 : reg_date 타입 : TIMESTAMP
ALTER TABLE test1 ADD ( reg_date TIMESTAMP);

-- test1 테이블에 다음의 데이터를 추가한다.
-- num : 3, name : 너너너, birth : 950101, reg_date : 2021 08 10 10 10 10 777
INSERT INTO test1 ( num, name, birth, reg_date)
    VALUES( 3, '너너너', TO_DATE('950101', 'RRMMDD'),
        TO_TIMESTAMP('20210810101010777', 'YYYYMMDDHH24MISSFF3'));

COMMIT;

SELECT * FROM test1;

-- 휴지통 정보 확인
-- 삭제된 개체 확인
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위임을 알 수 있다. (위의 사진은 맞는지 확인을 위한 사진) 

-- 문자열 결합 시에는 ||를 사용한다.
-- 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;