01. ORACLE BASIC QUERY (NULL, ALIAS, CONCATENATION, WHERE, PLAN, INDEX_VIEW, ORDER BY)

1. Temp 테이블 생성
CREATE TABLE TEMP (
 EMP_ID      NUMBER NOT NULL PRIMARY KEY,
 EMP_NAME    VARCHAR2(10) NOT NULL,
 BIRTH_DATE  DATE,
 DEPT_CODE   VARCHAR2(06) NOT NULL,
 EMP_TYPE    VARCHAR2(04),
 USE_YN      VARCHAR2(01) NOT NULL,
 TEL         VARCHAR2(15),
 HOBBY       VARCHAR2(30),
 SALARY      NUMBER,
 LEV         VARCHAR2(04)
);

2. TDEPT 테이블 생성
CREATE TABLE TDEPT (
 DEPT_CODE   VARCHAR2(06) NOT NULL PRIMARY KEY,
 DEPT_NAME   VARCHAR2(20) NOT NULL,
 PARENT_DEPT VARCHAR2(06) NOT NULL,
 USE_YN      VARCHAR2(01) NOT NULL,
 AREA        VARCHAR2(10),
 BOSS_ID     NUMBER
);

3. 두 테이블에 테이블 생성
INSERT INTO TEMP VALUES (19970101,'김길동',TO_DATE('19740125','YYYYMMDD'),'AA0001','정규','Y','','등산',100000000,'부장');
INSERT INTO TEMP VALUES (19960101,'홍길동',TO_DATE('19730322','YYYYMMDD'),'AB0001','정규','Y','','낚시',72000000,'과장');
INSERT INTO TEMP VALUES (19970201,'박문수',TO_DATE('19750415','YYYYMMDD'),'AC0001','정규','Y','','바둑',50000000,'과장');
INSERT INTO TEMP VALUES (19930331,'정도령',TO_DATE('19760525','YYYYMMDD'),'BA0001','정규','Y','','노래',70000000,'차장');
INSERT INTO TEMP VALUES (19950303,'이순신',TO_DATE('19730615','YYYYMMDD'),'BB0001','정규','Y','','',56000000,'대리');
INSERT INTO TEMP VALUES (19966102,'지문덕',TO_DATE('19720705','YYYYMMDD'),'BC0001','정규','Y','','',45000000,'과장');
INSERT INTO TEMP VALUES (19930402,'강감찬',TO_DATE('19720815','YYYYMMDD'),'CA0001','정규','Y','','',64000000,'차장');
INSERT INTO TEMP VALUES (19960303,'설까치',TO_DATE('19710925','YYYYMMDD'),'CB0001','정규','Y','','',35000000,'사원');
INSERT INTO TEMP VALUES (19970112,'연흥부',TO_DATE('19761105','YYYYMMDD'),'CC0001','정규','Y','','',45000000,'대리');
INSERT INTO TEMP VALUES (19960212,'배뱅이',TO_DATE('19721215','YYYYMMDD'),'CD0001','정규','Y','','',39000000,'과장');
--
INSERT INTO TDEPT VALUES ('AA0001','경영지원','AA0001','Y','서울',19940101);
INSERT INTO TDEPT VALUES ('AB0001','재무','AA0001','Y','서울',19960101);
INSERT INTO TDEPT VALUES ('AC0001','총무','AA0001','Y','서울',19970201);
INSERT INTO TDEPT VALUES ('BA0001','기술지원','BA0001','Y','인천',19930301);
INSERT INTO TDEPT VALUES ('BB0001','H/W지원','BA0001','Y','인천',19950303);
INSERT INTO TDEPT VALUES ('BC0001','S/W지원','BA0001','Y','인천',19966102);
INSERT INTO TDEPT VALUES ('CA0001','영업','CA0001','Y','본사',19930402);
INSERT INTO TDEPT VALUES ('CB0001','영업기획','CA0001','Y','본사',19950103);
INSERT INTO TDEPT VALUES ('CC0001','영업1','CA0001','Y','본사',19970112);
INSERT INTO TDEPT VALUES ('CD0001','영업2','CA0001','Y','본사',19960212);
--
COMMIT;

1. 기본적인 SELECT
1.1. TEMP 테이블의 연봉을 이용하여 홀수달에는 연봉의 1/18을 지급하고, 짝수 달에는 2/18의 연봉을 지급하는경우 홀수와 짝수달 간의 금액을 셀렉트하라.
SELECT  EMP_NAME,
        SALARY/18,
        SALARY * 2 / 18
FROM TEMP;

1.2. 월 급여에 홀수달은 100000 의 교통비가 지급되고, 짝수달에는 200000의  교통비가 지급되는 경우
SELECT  EMP_NAME,
        SALARY / 18 + 100000,
        SALARY * 2 / 18 + 200000
FROM TEMP;    

2. NULL의 이용
-- 1. NULL의 경우 칼럼에 아무런 값이 없다는 것을 의미한다.
-- 2. 널의 속성은 다음과 같다.
-- 2.1 널과 수치형 자료를 수치 연산(+, - *, /)을 하면 언제나 NULL이다.
-- 2.2 문자형 자료는 주로 조건절에서 비교할때 주의해야한다.
-- 2.3 널이 퐇마될 우려가 있는 컬럼을 이요할때는 NVL을 이용하여 값을 치환하도록 해준다.
-- 2.4 숫자형 컬럼이나 변수에 NULL이 들어갈 경우 0, 이나 1의 값으로 디폴트 세팅하는 것이 좋다.
-- 2.5 문자형 컬럼에 NULL이 들어갈경우 공백이나 다른 특정 문자 값으로 치환해야한다.
-- 2.6 조건절에서 비교 연산자를 사용할때도 NULL을 비교할때는 일반값과 다르다.
--   예 ) WHERE A = '1', WHERE A <> '1'이 될 수 있다.
--        WHERE A IS NULL, WHERE A IS NOT NULL이 되어야 한다.
--   즉, WHERE A = NULL이나 WHERE A <> NULL이어서는 안된다.

2.1 TEMP 테이블에서 HOBBY(취미)가 NULL이 아닌 사람들의 성명은
SELECT EMP_NAME
FROM TEMP
WHERE HOBBY IS NOT NULL;

2.2 <>이용하면 결과는?
SELECT EMP_NAME
FROM TEMP
WHERE HOBBY <> NULL;
-- 아무런 값도 나오지 않는다.

2.3 TEMP 테이블에서 HOBBY가 NULL인 사람은 모두 HOBBY를 없음 이라고 값을 치환햐여 나타내는 법
SELECT  EMP_NAME,
        NVL(HOBBY, '없음')
FROM TEMP;       

2.4 TEMP의 자료중 HOBBY의 값이 NULL인 사원을 '등산'으로 치환했을때 HOBBY 가 '등산'인사람의 성멸을 가져오시오
SELECT EMP_NAME
FROM TEMP
WHERE NVL(HOBBY, '등산') = '등산';

3. ALIAS의 이용하기
-- ALIAS는 테이블이나 필드명을 대시할 별명을 지정해주어, 테이블 이름이나 필드 이름을 간결하게 하고, 구분 가능하게 만들어 준다.
-- SELF 조인의 경우 반드시 ALIAS를 지정하여야, 필드를 사용하는데 혼란이 생기지 않는다.

3.1 기본 예제
SELECT  EMP_ID      SABUN,
        EMP_NAME AS NAME
FROM TEMP;

3.2 열의 정의가 애매하다는 오류 발생
SELECT  EMP_ID
        DEPT_CODE,
        DEPT_NAME
FROM TEMP, TDEPT
WHERE TDEPT.DEPT_CODE = TEMP.DEPT_CODE;

-- 이경우 해법 DEPT_CODE가 중복되어 혼돈이 되므로 TEMP.DEPT_CODE로 변경해 준다.
SELECT  EMP_ID
        TEMP.DEPT_CODE,
        DEPT_NAME
FROM TEMP, TDEPT
WHERE TDEPT.DEPT_CODE = TEMP.DEPT_CODE;

3.3 테이블 이름을 간단하게 만들자.
SELECT  A.EMP_ID,
        A.DEPT_CODE,
        B.DEPT_NAME
FROM TEMP A, TDEPT B
WHERE A.DEPT_CODE = B.DEPT_CODE;

4. CONCATENATION
-- CONCATENATION은 함수의 일종이다.
-- 2개 이상의 문자열을 연결하여 하나의 문자열을 만들어 줄때 사용한다.
-- CONCAT함수나, ||를 이용하여 처리한다.
4.1 CONCATENATION 기본
SELECT EMP_NAME || '(' || LEV || ')'  성명
FROM TEMP;

4.2 작은 따옴표
-- 작은 따옴표의 경우 ''''로 4개의 홑 따옴표를 붙여주어야 하나의 따옴표가 생성된다.
SELECT EMP_NAME || '''' || LEV || '''' 성명
FROM TEMP;

4.3 USER_OBJECT를 이용한 내가 현재 소유하고 있는 모든 오브젝트 내용을 볼 수 있다.
-- 유용한 기능으로 기억해두자.
SELECT  OBJECT_NAME,
        OBJECT_TYPE
FROM USER_OBJECTS;       

4.4. USER_OBJECT를 이용하여 고객이 가진 테이블을 드롭하는 문장을 만들어서 테스트 해 볼 수 있다.
-- 다음 쿼리를 실행해서 나온 결과중 원하는 부분을 복사해서 쿼리를 날리면 원하는 테이블이나 객체가 삭제된다.
SELECT 'DROP' || OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
FROM USER_OBJECTS;

4.5 성명과 생년월일을 묶어서 하나의 문자열로 보여주는 문장을 만들어 보라.
SELECT '''' || EMP_NAME || BIRTH_DATE || '''' NAME_BIRTH
FROM TEMP;

5. WHERE 절
5.1 WHERE 절의 기본
SELECT EMP_NAME
FROM TEMP
WHERE HOBBY = '등산';

5.2 다음 두 결과를 비교해 보자.
5.2.1 WHERE를 이용하지 않은경우
SELECT EMP_ID, EMP_NAME
FROM TEMP;

5.2.2 WHERE를 이용한 경우
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_ID > 0;

-- 위 두 결과는 모두 10건의 결과가 출력된다. 그러나 둘은 서로 다른 출력 순서를 가지고 있다.
-- 두 번째 쿼리는 EMP_ID 순으로 소트되어 출력됨을 확인해 볼 수 있다.

-- OPTIMIZER와 실행계획
-- DML문을 수행할경우 (SELECT, DELETE, UPDATE, INSERT)에는 옵티마이져가 수행된다.
-- 옵티마이저는 DML을 수행할 최적의 비용을 계산하여 최소의 비용으로 쿼리를 수행하도록 경로를 찾아준다.

-- PLAIN을 확인
-- EXPLAIN PLAN SET STATEMENT_ID = '임의지정' FOR
-- 해당 쿼리 문장...
-- 이렇게 해당 쿼리를 수행하게 되면 옵티마이저는 플랜 테이블에 (PLAN_TABLE)결과를 남긴다.
-- 이때 STATEMENT_ID에 해당하는 값으로 조회를 해 보면 해당 경로의 값이 나온다.

5.3 실행계획 실행해보기
EXPLAIN PLAN SET STATEMENT_ID = '임의지정' FOR
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_ID > 0;

5.4 실행계획 검사하기
SELECT lpad(operation, length(operation) + 2 * (level-1)) ||
      decode(id, 0, 'Cost Estimate : ' || decode(position, '0', 'N/A', position), null) ||
      '' || options || decode(object_name, null, null, ':') ||
      rpad(object_owner, length(object_name) + 1, ',') || object_name ||
      decode(object_type, 'UNIQUE', ' (U) ', 'NIN_UNIQUE',
      '(NU)', NULL) || decode(object_instance, null, null,
      '('||object_instance||')') PLAN
FROM PLAN_TABLE
START WITH ID=0 AND STATEMENT_ID='임의지정'
CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID = '임의지정';

-- 인덱스를 조회 할 수 있는 DATA DICTIONARY
-- 인덱스가 어떤 테이블에 대해 생성되어 있고, 어떤 칼럼으로 구성되어 있는지에 대한 정보를 보고자 할 경우
-- DICTIONARY VIEW에서 정보를 찾을 수 있다.
-- DICTIONARY VIEW는 오라클의 SYS USER가 소유하고 있는 데이터베이스와 관련된 정보를 제공해주는
-- READ ONLY VIEW 이다.

5.5 USER_INDEXES 테이블을 이용하여 사용자가 어떠한 인덱스를 소유하는지 검사하기
SELECT INDEX_NAME
FROM  USER_INDEXES
WHERE TABLE_NAME = 'TEMP';

5.6 USER_IND_COLUMNS를 이용하여 인덱스가 어떠한 칼럼으로 구성되어 있는지 검사하기
SELECT INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TEMP';
         
6. ORDER BY
-- 레코드를 정렬할 경우 ORDER BY를 사용한다.
6.1 ORDER BY 기본
SELECT EMP_ID, EMP_NAME
FROM TEMP
ORDER BY EMP_ID;

6.2 ORDER BY 역순 정렬
SELECT EMP_ID, EMP_NAME
FROM TEMP
ORDER BY EMP_ID DESC;

6.3 ORDER BY 대신 컬럼명을 이용하여 소팅하기
-- 1은 첫번째 칼럼이며 LEV를 나타낸다. 2는 2번째 칼럼인 EMP_ID를 의미한다. 이것을 DESC 하는 것이다.
SELECT LEV, EMP_ID, EMP_NAME
FROM TEMP
ORDER BY 1, 2 DESC;

-- DB 블록 구조
-- DB BLOCK
-- BLOCK은 오라클의 데이터베이스의 물리적인 저장 단위로서 입출력시의 최소 단위이다.
-- BLOCK의 구조는 위에서 부터 차례로 다음과 같은 구획으로 나뉘어 진다.
1. HEADER
-- 블록의 주소와 같은 BLOCK에 대한 일반적인 정보를 포함한다.
2. TABLE DIRECTORY
-- 블록 안에 존재하는 ROW를 소유하는 테이블에 대한 정보를 포함하고 있다.
3. ROW DIRECTORY
-- 각 ROW의 주소등 블록 안에 존재하는 ROW들에 대한 실제 정보를 포함하고 있다.
4. FREE SPACE
-- 널 컬럼의 값이 NOT NULL로 UPDATE될 때 추가적인 공간을 요구하게 된다.
-- 이때처럼 ROW의 변경이나 새로운 ROW의 삽입 시 필요로 하게 되는 추가적인 공간을 위해 확보된 영역이다.
5. ROW DATA
-- 테이블이나 인덱스의 데이터를 물리적으로 저장하는 영역

-- PCTUSED와 PCTFREE
1. PCTFREE
-- 이미 블록에 쓰여진 ROW의 UPDATE나 INSERT를 위하여 예약되는 공간이다.
-- 예를 들어
--   PCTFREE 20
-- 과 같이 값을 잡아주면 블록의 공간 중 20%를 이미 블록에 쓰여진 ROW의 UPDATE나 INSERT를 위하여 사용되지 않고 남게 하겠다는 뜻

2. PCTUSED
-- PCTFREE에서 지정한 영역만큼만 FREE SPACE가 남게 되면 오라클은 더이상 해당 블록에 새로운 ROW를 삽입하지 않는다.
-- 사용 공간이 ROW의 삭제 등으로 인하여 PCTUSED에서 지정한 값 아래로 떨어지게 되면 그때 다시 해당 BLOCK에 새로운 ROW가 삽입될 수 있다.

6.4 TDEPT 테이블을 이용해서 AREA 순서대로 부서정보를 읽어오는 SQL을 작성하시오.
SELECT *
FROM TDEPT
ORDER BY AREA;

7. 연산자
-- 연산자의 경우 where 조건절에 자료의 검색시 제약조건을 주기 위해 사용된다.
7.1 LIKE 검색
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM TEMP
WHERE DEPT_CODE LIKE 'A%';

7.2 부서코드중 A가 들어가는 ROW검색
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM TEMP
WHERE DEPT_CODE LIKE '%A%';

7.3 총 6자리 부서코드 중 2번째 자리에 A가 들어가는 ROW를 검색한다.
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM TEMP
WHERE DEPT_CODE LIKE '_A____';

7.4 BETWEEN 검색
-- 사번이 1997로 시작하는 사원의 사번과 성명을 검색할 BETWEEN 연산자 사용
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_ID BETWEEN 19970001 AND 19979999;

7.5 IN 연산자의 사용
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_NAME IN ('홍길동', '김길동');

변환대상 연산자	사용예	변환후
LIKE(%기호 미포함)	EMP_NAME LIKE '홍길동'	EMP_NAME = '홍길동'
ANY	EMP_NAME = ANY('홍길동', '김길동')	EMP_NAME = '홍길동' OR EMP_NAME = '김길동'
ANY(SUBQUERY)	A.SALARY > ANY (SELECT SALARY FROM TEMP WHERE LEV = '사원')	EXISTS(SELECT SALARY FROM TEMP WHERE LEV = '사원' AND A.SALARY > SALARY)
ALL	EMP_NAME > ALL('김길동', '배벵이')	EMP_NAME > '김길동' AND EMP_NAME > '배벵이'
ALL(SUBQUERY)	A.SALARY > ALL (SELECT SALARY FROM TEMP WHERE LEV = '사원')	NOT EXISTS(SELECT SALARY FROM TEMP WHERE LEV = '사원' AND A.SALARY <= SALARY)
BETWEEN	EMP_ID BETWEEN 19970001 AND 19979999	EMP_ID >= 19970001 AND EMP_ID <= 19979999
NOT	NOT SALARY < 5000000	SALARY >= 5000000
IN	EMP_NAME IN ('홍길동', '김길동')	EMP_NAME = '홍길동' OR EMP_NAME = '김길동'

8. GROUP BY 와 HAVING
-- GROUP BY 는 특정 컬럼이나 값을 기준으로 ROW를 묶어서 자료를 다류려고 할 때 사용
-- 그룹함수 ( MAX, MIN, AVG, SUM, COUNT 등)를 적용할 때가 이에 해당한다.

8.1 TEMP의 자료를 이용하여 LEV별로 최고 액 연봉이 얼마인지 검사
-- 결과를 보면 LEV값이 가나다 순으로 정렬 되어 잇다. 즉, ORDER BY를 이용하면 내부적으로 SORT가 된다.
SELECT LEV,
      MAX(SALARY) MAX_SAL
FROM TEMP
GROUP BY LEV;

8.2 전체 ROW에 대해 최고 연봉이 얼마인지 알고자 하는경우
-- GROUP BY 문장이 기술되지 않아도 되는 경우는, 그룹 함수만 사용할 경우 사용된다.
SELECT MAX(SALARY)
FROM TEMP;

8.3 TDEPT의 자료중 부서장 중 사번이 가장 빠른 사람이 근무하는 부서를 알고자 하는경우
-- 이렇게 값을 하나로 묶어 놓고 이용하면 결과가 상당히 빨리 나온다.
SELECT MIN(BOSS_ID || ' ' || DEPT_CODE)
FROM TDEPT;

8.4 GROUP BY 와 ORDER BY를 함께 사용한 경우
SELECT AREA, MIN(BOSS_ID)
FROM TDEPT
GROUP BY AREA
ORDER BY 2;

9. HAVING
-- 검색할 테이블에서 검색하고자 하는 ROW를 조건절을 사용하여 제한한다. 그러나 WHERE의 경우는 일반적인 쿼리문에서,
-- HAVING는 GROUP BY 절을 이용한 쿼리문에서 이용할 수 있도록 되어 있다.

9.1 직급별로 연봉 평균을 구한 상태에서 평균 연봉이 5천만원 이상인 경우의 직급과 평균 연봉을 읽어온다.
SELECT LEV, AVG(SALARY)
FROM TEMP
GROUP BY LEV
HAVING AVG(SALARY) > 50000000;

9.2 TEMP의 자료를 이용하여 직급별로 사번이 제일 늦은 사람을 구하고 글 결과 내에서 사번이 1997로 시작하는 결과만 보여준다.
SELECT LEV, MAX(EMP_ID)
FROM TEMP
GROUP BY LEV
HAVING MAX(EMP_ID) LIKE '1997%';