[Web_DBMS] 09

● CASE문

CASE 컬럼명

WHEN 값

THEN 컬럼명 또는 값

END

 

 

 

● SUB QUERY

1. FROM 절 : IN LINE VIEW

2. SELECT 절 : SCALAR

3. WHERE 절 : SUB QUERY

 

 

 

● SQL 실행 순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

 

 

● ROWNUM

- 결과 행 앞에 1부터 1씩 증가하는 시퀀스를 붙여준다.

 

 

 

※ FROM절에서 AS 사용

- AS를 사용해서는 안되고 공백으로만 구분해야 한다.

 

 

---

 

● 옵티마이저(OPTIMIZER)

- SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로(최저 비용)를 생성해주는 DBMS 내부의 핵심 엔진

- 사용자가 쿼리문(SQL)으로 결과를 요청하면, 이를 생성하는 데 필요한 처리 경로는

   DBMS에 내장된 옵티마이저가 자동으로 생성한다.

- 옵티마이저가 생성한 SQL 처리 경로를 실행 계획(Execution Plan)이라고 한다.

 

1. COST

- 예상 수행 시간

- 퀴리를 수행하는 데 소요되는 일량 또는 시간

 

2. CARDINALITY

- 실행 결과의 건수

 

 

 

● 옵티마이저의 SQL 최적화 과정

1. 사용자가 작성한 쿼리 수행을 위해 실행될 만한 실행 계획을 찾는다.

2. 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계 정보를 이용해서

    각 실행 계획의 예상 비용을 산정한다.

3. 각 실행 계획을 비교해서 최저 비용을 갖는 하나를 선택하여 실행한다.

 

 

 

● 옵티마이저 종류(Rule Based Optimizer, Cost Based Optimizer)

1. 규칙 기반 옵티마이저(RBO), 휴리스틱(Heuristic) 옵티마이저

- 미리 정해진 규칙에 따라 실행

- Oracle 10g 버전부터는 RBO에 대한 지원 중단

 

2. 비용 기반 옵티마이저(CBO)

- 비용이 가장 낮은 실행 계획을 선택

 

 

---

​

실습(CASE문)

1. PLAYER 테이블에서 전체 평균 키와 포지션별 평균 키 구하기

- 포지션 검색

SELECT "POSITION", AVG(HEIGHT) FROM PLAYER
GROUP BY "POSITION";

 

 

- 검색한 포지션별로 작성

SELECT 
	AVG(CASE "POSITION" WHEN 'GK' THEN HEIGHT END) GK,	
	AVG(CASE "POSITION" WHEN 'DF' THEN HEIGHT END) DF,	
	AVG(CASE "POSITION" WHEN 'FW' THEN HEIGHT END) FW,	
	AVG(CASE "POSITION" WHEN 'MF' THEN HEIGHT END) MF,
	AVG(HEIGHT) "전체 평균 키" 
FROM PLAYER;

 

 

 

 

실습(SUB QUERY(1))

1. PLAYER 테이블에서 전체 평균 키와 포지션별 평균 키 구하기

SELECT "POSITION", AVG(HEIGHT), (SELECT AVG(HEIGHT) FROM PLAYER) "전체 평균 키"
FROM PLAYER
WHERE "POSITION" IS NOT NULL
GROUP BY "POSITION";

 

 

 

2. PLAYER 테이블에서 TEAM_ID가 'K01'인 선수 중에서 POSITION이 'GK'인 선수

- SUB QUERY 사용

SELECT * FROM PLAYER
(
	SELECT * FROM PLAYER WHERE TEAM_ID = 'K01'
)
WHERE "POSITION" = 'GK';

 

 

※ SUB QUERY 미사용

SELECT * FROM PLAYER
WHERE TEAM_ID = 'K01' AND "POSITION" = 'GK';

 

 

 

3. 정남일 선수가 소속된 팀의 선수들 조회

SELECT * FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일');

 

 

 

4. PLAYER 테이블에서 평균 키보다 작은 선수 겁색

- 검색

SELECT * FROM PLAYER
WHERE HEIGHT < (SELECT AVG(HEIGHT) FROM PLAYER);

 

 

- 평균 키 179.xx에서 179 포함 확인

SELECT MAX(HEIGHT) FROM
(
	SELECT * FROM PLAYER
	WHERE HEIGHT < (SELECT AVG(HEIGHT) FROM PLAYER)
);

 

 

 

5. 경기장 중 경기 일정이 20120501 ~ 20120502 사이에 있는 경기장 전체 정보 조회(제시된 일정 포함)

SELECT * FROM STADIUM
WHERE STADIUM_ID IN
(
	SELECT STADIUM_ID FROM SCHEDULE
	WHERE SCHE_DATE BETWEEN '20120501' AND '20120502'
);

 

 

 

 

실습(SUB QUERY(2))

※ TCL 버튼 클릭해서 None으로 변경한 후 진행한다.

 

1. PLAYER 테이블에서 NICKNAME이 NULL인 선수들을 정태민 선수의 닉네임으로 바꾸기

UPDATE PLAYER
SET NICKNAME = (SELECT NICKNAME FROM PLAYER WHERE PLAYER_NAME = '정태민')
WHERE NICKNAME IS NULL;

 

 

※ 실행 확인

SELECT * FROM PLAYER
WHERE NICKNAME = (SELECT NICKNAME FROM PLAYER WHERE PLAYER_NAME = '정태민');

 

 

 

2. EMPLOYEES 테이블에서 평균 급여보다 낮은 사원들의 급여를 20% 인상

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.2
WHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES);

 

 

※ 확인

SELECT SALARY FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES);

 

 

 

3. PLAYER 테이블에서 평균 키보다 큰 선수들을 삭제

DELETE FROM PLAYER
WHERE HEIGHT > (SELECT AVG(HEIGHT) FROM PLAYER);

 

 

 

 

실습(ROWNUM)

1. EMPLOYEES 테이블의 데이터에 ROWNUM 붙이기

SELECT ROWNUM, EMPLOYEES.* FROM EMPLOYEES;

 

 

 

※ FROM절에서 AS 사용

SELECT ROWNUM, E.* FROM EMPLOYEES E;

 

 

 

2. EMP 테이블에서 SAL을 내림차순으로 정렬한 후 ROWNUM을 붙여서 조회한다.

SELECT ROWNUM, "RESULT".* FROM 
(
	SELECT * FROM EMP ORDER BY SAL DESC
) "RESULT";

 

 

 

3. 연봉 1위부터 5위까지 조회한다.

SELECT ROWNUM, "RESULT".* FROM
(
	SELECT * FROM EMP ORDER BY SAL DESC
) "RESULT"
WHERE ROWNUM BETWEEN 1 AND 5;