ADVANCED SQL

 NVL FUCTION : null 값을 포함하는 컬럼을 지정된 값으로 변경하는데 사용,  NVL함수 사용시 변경되는 값의 데이터 타입 일치

NVL (형식1, 형식2)

DECODE FUNCTION : 3항 연산자와 유사하며 값을 비교하여 해당 값을 돌려준다.

DECODE(형식, 비교값,1 결과치1, 비교값2, 결과치, ..기본치)

 

SELECT name, salary, DECODE(TRUNC(SALARY/1000),0,'E',1,'D',2,'C',3,'B','A') --decode

CASE문

SELECT id, name,
CASE WHEN 1000 THEN 'low'
    WHEN 5000 THEN 'High'
ELSE 'Medium'
END
FROM s_emp;

PIVOT함수 : 데이터 다차원 분석

SELECT *FROM(SELECT dept_id, title FROM s_emp)--pivot
PIVOT(COunt(*)FOR title IN('사원','과장','부장','이사','사장')
)ORDER BY dept_id;

SELECT dept_id,
COUNT(DECODE(title, '사원',0,null)) "사원",--책에는 null이 생략되어잇다 null일때 카운트하면 세지지않이에 null을 카운트하라는 의미
COUNT(DECODE(title, '과장',0,null)) "과장",
count(decode(title, '부장',0,null)) "부장",
count(decode(title, '이사',0,null)) "이사",
count(decode(title, '사장',0,null)) "사장"
FROM s_emp
Group by dept_id
order by dept_id;
 

 

Rollup함수 : 그룹별 합계

SELECT dept_id, title, count(*) From s_Emp
WHERE dept_id IN (106,112,113)
GROUP BY ROLLUP(dept_id, title)
ORDER BY dept_id;

CUBE함수 : 그룹별 합계 & 소계

SELECT dept_id title, count(*) From s_emp
WHERE dept_id IN (106, 112,113)
GROUP BY CUBE(dept_id, title)
ORDER BY dept_id;

RANK함수 :  그룹별 순위

 

CORRELATED SUBQUERY : subquery가 mainquery의 값을 쓰는 것이 상관 query이다.

SELECT name, salary, dept_id
FROM s_emp outer
WHERE salary< (SELECT AVG(salary)
FROM s_emp
WHERE dept_id= outer.dept_id);  --outer.dept_id가 118, 110 ....으로 바뀌면서 작동

 

Multi Row Comparsion Operator

• IN : LIST에 있는 값과 같은지를 비교
• NOT IN : LIST에있는 값과 같지 않은 지를 비교
• ANY : SUBQUERY에 의해 RETURN되는 모든 값을 비교
• EXISTS :  SUBQUERY에 만족되는 값이 발견되면 TRUE가 되고 INNER QUERY에서의 SEARCH종료
• NOT EXISTS : SUBQUERY에서 만족되는 값이 발견되지 않으면 TRUE가 종료되고 INNER QUERY에서의 SEARCH종료

SELECT id, name, title, dept_id--exists
FRom s_emp e
WHERE EXISTS(SELECT id FROm s_emp
WHERE manager_id = e.id);
 

 

PSEUDO COLUMN (rowid, rownum)

• ROWID : 각 테이블에 있는 각 행의 무리적인 주소를 고유하게 지정, 데이터 접근에 가장 빠른 방법
• ROWNUM :  QUERY의 결과로 반환되는 각 행들에 대하여 TABLE에서 선택된 순서로 ROWNUM이 지정, 계속 변동

SELECT distinct manager_id from s_emp;

SELECT rowid, rownum , id ,name ,title, salary from s_emp;--rowid, rownum rownum의 값은 매번 달라지게된다.
 

예제)

--Q12. 직원(s_emp)테이블에서 이름을 사전순으로 정렬하여 5개의 데이터만 나타내시오 (PSEUDO COLUMN)
SELECT rownum, id, name, title, salary from s_emp
where rownum < 6
order by name
;

ROWID

오브젝트 번호(32비트 6자리)

상대파일 번호(10bit 3자리)

블록 번호(22bit 6자리)

데이터 번호(16bit 3자리)

※데이터 번호는 저장된 순서를 의미

 

INDEX 효율적 사용 : index를 잘못 사용하면 속도가 느려지기에 index가 존재하여도 사용하지 않는 경우는 다음과 같다. INDEXED 칼럼이 비교되지 전에 변형이 일어나거나, 부정(<>,not)으로 조건을 기술하거나 null로 비교하는 경우(null은 index에 저장되지 않아), OPTIMIZER의 취사선택

SELECT 'x', id, name from s_region;--행의 갯수만큼 x 출력된다 
 

예제)

--Q13. 직원 중 연봉 상위 5명 출력하시오.--주말과제 rownum
 SELECT rownum, name, salary, title 
 from (SELECT name, salary, title from s_emp order by salary desc)
 where rownum < 8;

TRIGGER : 이벤트 발생시 다른 이벤트 발생하는 것을 말한다, 락으로 인해 문제가 발생하여 사용하지 않는다.