Database 기본6

------------------ 2021-03-09
 
-- 스칼라 서브쿼리 : 상관서브쿼리, RESULTSET이 무조건 단일행인 것
-- 컬럼 작성하는 곳에 사용가능. WHERE절에서 사용 가능
-- 모든 사원의 매니저 이름 조회
SELECT EMP_ID, EMP_NAME, MANAGER_ID,
       NVL((SELECT EMP_NAME FROM EMPLOYEE M WHERE E.MANAGER_ID=M.EMP_ID ), '없음') AS 매니저이름
FROM EMPLOYEE E;
 
-- 사원명, 부서코드, 부서별 평균임금 조회
SELECT EMP_NAME, DEPT_CODE,
       TO_CHAR(FLOOR((SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE)),'L999,999,999')
FROM EMPLOYEE E;
 
-- 자신의 부서의 평균급여보다 많이 받는 사원을 조회
SELECT EMP_NAME, DEPT_TITLE, SALARY
FROM EMPLOYEE E
JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE EE WHERE EE.DEPT_CODE=E.DEPT_CODE);
 
-- 인라인 뷰 : FROM절에 서브쿼리 사용한 것
-- 실제 물리적테이블에 있는 값이 아닌 가상의 테이블 값
-- INLINE VIEW : 1번만 사용할 수 이쓴 테이블로 쓰이는 서브 쿼리문
-- STORED VIEW : 테이블로 쓰이는 서브쿼리문을 저장하고 이름을 부여해서 재호출 가능 -> VIEW
 
-- 여사원의 사번, 사원명, 부서코드, 성별 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DECODE(SUBSTR(EMP_NO,8,1),'1','남',2,'여') AS 성별
FROM EMPLOYEE
WHERE DECODE(SUBSTR(EMP_NO,8,1),'1','남',2,'여')='여';
 
-- INLINE VIEW로 처리
SELECT *
FROM (SELECT EMP_ID, EMP_NAME, DEPT_CODE, DECODE(SUBSTR(EMP_NO,8,1),'1','남',2,'여') AS 성별
     FROM EMPLOYEE)
WHERE 성별='여';
 
-- TOP-N 분석 : 순위를 부여하는 것
-- 급여를 많이 받는 사원 1~3위 조회
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC; -- 이건 순위컷 불가
 
-- 순위컷
SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 3;
 
-- 급여 5~10위 조회
SELECT *
FROM (
    SELECT ROWNUM AS RNUM, E.*
    FROM (SELECT EMP_NAME, SALARY
        FROM EMPLOYEE
        ORDER BY SALARY DESC) E
    )
WHERE RNUM BETWEEN 5 AND 10;
-- ROWNUM으로 비교하면 1부터 시작함.
-- 위에건 게시판 구성할 때, PAGING 처리에 사용하는 SQL문이다.
 
-- D5부서 고연봉 3명 조회
SELECT *
FROM (
         SELECT ROWNUM AS RNUM, E.*
         FROM (
                  SELECT EMP_NAME, DEPT_CODE, DEPT_TITLE, SALARY, (SALARY + (SALARY * NVL(BONUS, 0))) * 12 AS 연봉
                  FROM EMPLOYEE
                           JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
                  WHERE DEPT_CODE='D5'
                  ORDER BY 4 DESC
              ) E
     )
WHERE RNUM BETWEEN 1 AND 3;
 
-- 함수 이용해서 순위 매기기
-- RANK() OVER : 각 ROW에 순서 부여
SELECT *
FROM (
      SELECT EMP_NAME, SALARY,
      RANK() OVER (ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE
    )
WHERE 순위 BETWEEN 1 AND 3;
 
-- DENSE_RANK() : 중복값이 있을 때 번호 뛰어넘지 않음
SELECT RANK() OVER (ORDER BY SALARY) AS 순위,
       EMP_NAME, SALARY,
       DENSE_RANK() OVER (ORDER BY SALARY) AS DENSE순위
FROM EMPLOYEE;
 
-- 계층형 쿼리 : 각 ROW별 연결할 수 있는 컬럼 필요
-- 댓글, 대댓글 사용할 때 이용하는 쿼리문이다.
SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE;
 
SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
START WITH EMP_ID=200
CONNECT BY PRIOR EMP_ID=MANAGER_ID;
 
SELECT LEVEL ||''||LPAD('',(LEVEL-1)*5,'' )||EMP_NAME|| NVL2(MANAGER_ID,'('||MANAGER_ID||')','') AS 조직도
FROM EMPLOYEE
--START WITH EMP_ID=200
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID=MANAGER_ID;
 
-- ORACLE 의 데이터형
-- VARCHAR2 : 가변형 문자열 타입으로, 대입된 데이터만큼 공간 확보
-- CHAR : 고정형 문자열 타입으로 길이만큼 공간을 확보
 
CREATE TABLE TBL_STR(
    A CHAR(6),
    B VARCHAR2(6)
);
 
SELECT *
FROM TBL_STR;
 
INSERT  INTO TBL_STR VALUES ('ABC','AAA');
 
SELECT LENGTHB(A), LENGTHB(B) FROM TBL_STR;
 
-- NUMBER : 모든 숫자형
-- NUMBER([PRECISION, SCALE])
-- PRECISON : 표현할 수 있는 전체 자리수
-- SCALE : 소수점 이하 자리수
CREATE TABLE TBL_NUM(
    A NUMBER,
    B NUMBER(5),
    C NUMBER(5,1),
    D NUMBER(5,-2)
);
SELECT*FROM TBL_NUM;
INSERT INTO TBL_NUM VALUES(123.1123,213.1123,123.3144,213.1231);
 
CREATE TABLE TBL_DATE(
    BIRTHDAY DATE,
    DAY TIMESTAMP
);
 
INSERT INTO TBL_DATE VALUES (SYSDATE, SYSTIMESTAMP);
SELECT*FROM TBL_DATE;
INSERT INTO TBL_DATE VALUES ('1920/11/23', '1999/11/12');
 
-- 기본 테이블 생성
-- CREATE TABLE 테이블명 (컬럼명 자료형, 컬럼명 자료형.............);
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20)
);
 
-- 테이블 컬럼에 COMMENTS 설정하기
-- COMMENT ON COLUMN 테이블.컬럼명 IS 코멘트내용
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비번';
 
-- 코멘트 포함 테이블 조회
SELECT * FROM USER_COL_COMMENTS
    WHERE TABLE_NAME='MEMBER';
 
DROP TABLE TBL_STR; -- 테이블 삭제
 
SELECT*FROM MEMBER;