오라클 DBMS 정리 05

-- 중복 순위가 발생(같은 번호가 발생)
-- 1, 2, 3, 4로 안나올 수 있고 1, 1, 3, 4 이렇게 나올 수 있다는 말
RANK() OVER()
 
-- 중복 순위가 발생하지 않음
ROW_NUMBER() OVER()
 
-- 아래 내용들은 RANK() OVER(), ROW_NUMBER OVER() 동일하게 사용 가능
-- 단순 순위 구하기 ORDER BY(필수 속성)
RANK() OVER( ORDER BY 순위를정할컬럼명 ASC[DESC] )
 
-- 그룹별 순위 구하기 PARTITION BY
RANK() OVER( PARTITION BY 그룹으로보여질컬럼명 ORDER BY 순위를정할컬럼명 ASC[DESC] ) 

-- 사원테이블에서 사원번호, 사원명, 연봉, 연봉순위
-- RANK() OVER()는 중복순위 발생할 수 있음
SELECT empno, ename, sal, RANK() OVER(ORDER BY sal DESC) rank
FROM emp;

-- 위 내용을 중복순위가 발생하지 않도록 처리하여 조회
SELECT empno, ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rank
FROM emp;

GROUP BY ROLLUP(그룹으로묶을컬럼명)
GROUP BY CUBE(그룹으로묶을컬럼명)

-- 사원테이블에서 부서별 연봉의 합을 조회하고 총계 출력
-- 그룹화한 컬럼이 한개면 총계=소계
-- ROLLUP은 소계가 마지막에 출력된다
SELECT   deptno, SUM(sal)
FROM     emp
GROUP BY ROLLUP(deptno);

-- CUBE는 소계가 먼저 출력된다
SELECT   deptno, SUM(sal)
FROM     emp
GROUP BY CUBE(deptno);

-- 여러컬럼이 그룹으로 묶여지면 ROLLUP은 그룹별 소계를 마지막에 출력
 
-- 사원테이블에서 부서별 직무의 연봉합 조회
SELECT deptno, job, COUNT(empno) members, SUM(sal)
FROM emp
GROUP BY deptno, job
ORDER BY deptno;
 
-- ROLLUP을 하면 ORDER BY하지 않아도 정렬돼 나옴
SELECT   deptno, job, COUNT(empno) members, SUM(sal)
FROM     emp
GROUP BY ROLLUP(deptno, job);
-- 1. deptno 그룹 집계
-- 2. deptno, job 그룹 집계
-- 3. 전체 그룹 집계

-- 총계를 먼저 얻고 그룹별로 소계를 먼저 얻고 싶다면? - CUBE!
-- 사원테이블에서 부서별, 직무의 연봉합, 직무별(그룹별)소계, 총계
SELECT deptno, job, COUNT(empno) members, SUM(sal)
FROM emp
GROUP BY CUBE(deptno, job);
 
-- CUBE가 롤업보다 더 많은 데이터를 얻을 수 있음(리소스도 그만큼 더 잡아 먹음)

(SELECT ... )의 형식으로 사용

-- 제약사항 보는 테이블(딕셔너리)
SELECT *
FROM user_constraints;

CREATE TABLE 테이블명 
AS (SELECT ... );

-- 사원테이블에서 사원번호, 사원명, 입사일, 연봉을 조회하여
-- 조회한 결과로 CP_EMP라는 테이블을 생성
CREATE TABLE cp_emp
AS (SELECT empno, ename, hiredate, sal FROM emp);
-- INLINE VIEW, 서브쿼리의 결과는 복수행 subquery

-- 구조만 복사 (레코드를 조회하지 않고 서브쿼리를 수행)
-- 항상 거짓인 조건을 걸면 레코드 없이 구조만 복사가능
-- 사원 테이블에서 사원번호, 사원명, 부서번호, 연봉을
-- 레코드가 조회되지 않도록 조회
CREATE TABLE cp_emp3
AS (SELECT empno, ename, deptno, job, sal
    FROM   cp_emp
    WHERE  1=0);
 
-- 조건 1=1은 항상 참, 1=0은 항상 거짓으로 사용가능

-- 단일행 Subquery의 결과는 무조건 하나여야 한다
INSERT INTO 테이블명(컬럼명, ...)
VALUES (값, (SELECT 한컬럼명 ...), 값, 값, ... );

-- 컬럼명은 생략가능(컬럼이 입력할 테이블의 컬럼 순서와 동일하다면)
-- VALUES를 안씀!
INSERT INTO 테이블명(컬럼명, ...)
(SELECT 컬럼명, ...)

UPDATE 테이블
SET    컬럼명=값, 컬럼명=(SELECT ...)
WHERE  컬럼명=(SELECT ...);

UPDATE 테이블명
SET    컬럼명=값, 컬럼명=(단수행 Subqeury만 가능)
WHERE  컬럼명 IN (복수행 Subquery도 가능);

DELETE FROM 테이블
WHERE 컬럼명=(SELECT ...);
--    컬럼명 IN (SELECT.. 복수행 Subquery도 가능)

-- SELECT에 들어가는 서브쿼리는 Scalar Subquery
SELECT 컬럼명, (SELECT ... ), ...
FROM   테이블명
WHERE  컬럼명=(SELECT ...)
-- WHERE절에 들어가는 서브쿼리는 단수행 Subquery

WHERE 컬럼명 IN (다중행)
-- IN은 다중행 서브쿼리 결과 중 DISTINCT로 중복값들을 배제시킴
 
WHERE EXITS (다중행)
-- 컬럼명 안씀!!
 
WHERE 컬럼명 관계연산자 ANY(다중행)
 
-- WHERE 컬럼명 < ANY(SELECT ...)
-- SELECT결과가 10, 20이 나왔다면
-- 10 OR 20 보다 작은 값만 나옴, 즉 20보다 작은 값이 나옴
 
WHERE 컬럼명 관계연산자 ALL(다중행)
 
-- WHERE 컬럼명 < ALL(SELECT ...)
-- SELECT결과가 10, 20이 나왔다면
-- 10 AND 20 보다 작은 값만 나옴, 즉 10보다 작은값만 나옴
-- ANY, ALL은 서브쿼리 모든 결과값을 가지고 비교를 함

-- Scalar Subquery, SELECT 조회하는 컬럼에 서브쿼리를 쓰는 것
-- emp테이블에서 사원번호, 사원명, 부서번호, 부서명 조회
-- 부서명은 dept테이블에 존재
-- 같은 이름의 컬럼이 존재하면 어떤 테이블의 존재하는 컬럼인지 구분하여 사용해야 함 (테이블명.컬럼명)
SELECT empno, ename, deptno, (SELECT dname
                              FROM   dept
                              WHERE  dept.deptno=emp.deptno)
FROM   emp;
 
-- 사원테이블에서 사원번호가 '7698'인 사원의 부서번호와
-- 같은 부서를 cp_emp3테이블에서 조회
-- 조회 컬럼은 사원번호, 사원명, 부서번호, 직무를 조회
SELECT empno, ename, deptno, job
FROM   cp_emp3
WHERE  deptno=(SELECT deptno
               FROM   emp
               WHERE  empno=7698);
 
 
 
SELECT empno, ename, deptno, job
FROM    cp_emp3
WHERE  deptno=(SELECT deptno
               FROM emp
               WHERE empno IN (7698, 7566));
                -- Error!

-- 컬럼값의 다중행 서브쿼리를 쓸 때는 IN, ANY, ALL, EXISTS
 
-- 사원테이블에서 사원번호가 '7698', '7782'인 사원의 부서번호와
-- 같은 부서를 cp_emp3테이블에서 조회
-- 조회 컬럼은 사원번호, 사원명, 부서번호, 직무를 조회
SELECT   empno, ename, deptno, job
FROM     cp_emp3
WHERE    deptno IN (SELECT deptno
                    FROM   emp
                    WHERE  empno IN (7698, 7566));
 
-- 외부값 입력받기 
-- 쿼리문에 &변수명 - 입력창이 동작 : 입력창에 입력한 값이 쿼리대입
-- &변수명이 쿼리문에 그대로 대입되므로 문자열을 입력할 경우 ''로 묶어 사용해야 함
SELECT *
FROM   emp
WHERE  deptno=&deptno;
 
-- KING을 입력하면 Error, ename=KING 이렇게 들어가므로 문자열은 ''로 묶어 조회
SELECT *
FROM   emp
WHERE  ename=&ename;
 
-- 사원 테이블에서 입력되는 부서번호가 존재한다면
-- 사원번호, 사원명, 입사일, 부서번호를 조회
-- EXISTS는 서브쿼리가 존재하면 메인쿼리 수행
SELECT  empno, ename, hiredate, deptno
FROM    emp
WHERE   EXISTS (SELECT deptno
                FROM   emp
                WHERE  deptno=&no);
 
-- ALL : AND 
-- 가장 sal인 1300보다 작은 결과를 조회함
SELECT empno, ename, sal, deptno
FROM   emp
WHERE  sal < ALL (SELECT sal
                  FROM emp
                  WHERE deptno=10);
 
-- ANY : OR
SELECT empno, ename, sal, deptno
FROM   emp
WHERE  sal < ANY(SELECT sal
                 FROM   emp
                 WHERE  deptno = 10);

SELECT ROWNUM, 컬럼명, ...

-- 사원테이블에서 번호, 사원번호, 사원명, 입사일을 조회
-- 단, 번호는 조회결과에 1번부터 순차적으로 나와야 함
SELECT ROWNUM, empno, ename, hiredate
FROM   emp;

-- ORDER BY와 같이 사용되면 번호가 섞임(먼저 수행하기 때문)
SELECT   ROWNUM, empno, ename
FROM     emp
ORDER BY ename;

-- ROWNUM을 WHER절에서 사용하면 1번부터는 조회가 되나
-- 1번이 아닌 번호는 조회되지 않는다
-- 사원테이블에서 사원번호, 사원명, 입사일, 연봉을 조회
-- 단, 출력은 조회 순서  1~5까지만 출력
SELECT ROWNUM, empno, ename, hiredate, sal
FROM   emp
WHERE  ROWNUM BETWEEN 1 AND 5;
 
-- 1번이 아닌 번호로 시작을 하면 조회가 안됨
SELECT ROWNUM, empno, ename, hiredate, sal
FROM   emp
WHERE  ROWNUM BETWEEN 2 AND 5;

SELECT 컬럼명, ...
FROM   (SELECT ...);

SELECT empno, ename, sal, hiredate
FROM (SELECT empno, ename, sal, hiredate
      FROM   emp);
 
-- INLINE VIEW에 comm 컬럼이 없기 때문에 에러 발생
SELECT empno, ename, sal, hiredate, comm
FROM (SELECT empno, ename, sal, hiredate
      FROM   emp);
 
-- INLINE VIEW에 alias로 컬럼명을 바꿨기 때문에 에러 발생
SELECT empno, ename, sal, hiredate
FROM (SELECT empno, ename, sal, hiredate hi
FROM   emp);

-- ROWNUM을 실제 컬럼처럼 사용하고 싶으면 alias를 사용 
-- 사원테이블에서 가장 마지막에 입사한 이전 사원(1987-04-19)부터 5명 조회
-- 조회 컬럼은 사원번호, 사원명, 입사일을 조회
SELECT ROWNUM, rn, empno, ename
FROM   (SELECT   ROWNUM rn, empno, ename, hiredate
        FROM     (SELECT     empno, ename, hiredate
                  FROM       emp
                  ORDER BY   hiredate DESC))
WHERE  rn BETWEEN 2 AND 6;
 
-- 이게 게시판 리스트 뽑아내는 방법
-- 가장 먼저 입사한 녀석을 맨위로 올리고
-- 새로운 재조회로 ROWNUM으로 입사일순으로 순서를 셀 수 있게됨
-- 하지만 ROWNUM을 사용할 수 없기 때문에 alias를 붙여 사용토록함, 그러나 WHERE절에서 바로 못 씀
-- 때문에 또 재조회를 해서 alias를 WHERE절 조건으로 만들어서 사용, 2~6 조회할 수 있음

-- 두 가지 방법으로 제약사항을 줄 수 있다.
-- 컬럼단위 제약사항 (Column Level Constraint) - CONSTRAINT 제약사항명 생략 가능(오라클이 제약사항명을 자동생성 부여)
-- 테이블 단위 제약사항 (Table Level Constraint)
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 제약사항명 제약사항종류,
    ...
    CONSTRAINT 제약사항명 제약사항종류(적용컬럼),
    ...
);
-- Column Level Constraint로 제약사항 생성 시 제약사항명을 생략하면 오라클이 자동생성부여