Oracle SQL Query

Oracle SQL Query - Join

오라클에서 지원하는 다양한 SQL 쿼리문에 대해 알아보겠습니다.

먼저 JOIN 입니다. JOIN은 둘 이상의 테이블을 합쳐서 하나의 큰 테이블로 만드는 방법입니다.

JOIN은 테이블의 Foreign Key를 이용하여 참조하며,

정규화된 테이블로부터 결합된 형태의 정보를 추출할 때 사용합니다.

방법1은 여러 DBMS의 SQL 문법을 맞추기 위해 나중에 생긴 편리한 방법입니다.

따라서 방법1을 추천하지만 출력결과는 같기 때문에 상관없습니다.

1. JOIN (방법1)

// NATURAL JOIN
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp JOIN dept USING (deptno);
SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;

// OUTER JOIN
SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno = dept.deptno);

2. JOIN (방법2)

SELECT * FROM EMP E1, EMP E2 WHERE E1.EMPNO = E2.MGR;
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO (+)= DEPT.DEPTNO;

---

Oracle SQL Query - Group, Aggregation

Group과 Aggregation은 그룹의 결과 값을 찾고자 할 때 사용됩니다.

이를 통해 여러행으로부터 하나의 결과 값을 반환할 수 있습니다.

종류 : AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE

1. Aggregation

SELECT AVG(sal) FROM emp;
SELECT COUNT(empno) FROM emp;

 Aggregation은 그룹의 결과 값을 찾고자 할 때 사용됩니다.

 이를 통해 여러 행으로부터 하나의 결과 값을 반환할 수 있습니다.

2. GROUP BY

// 부서별 평균 연봉을 출력
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

 GROUP BY는 어떤 조건에 의해 그룹으로 묶고자 할 때 사용됩니다.

 * 주의 : 단일 SQL문은 FROM - WHERE - GROUP BY - HAVING - ORDER BY - SELECT 순서로 실행

3. HAVING

// 평균 월급이 2000 이상인 부서
SELECT deptno, AVG(sal) FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno;

 HAVING은 Aggregation 결과에 대해 다시 조건을 검사할 때 사용됩니다.

 HAVING절에는 반드시 GROUP BY 에 참여한 컬럼이나 Aggregate 함수만 사용 가능합니다.

---

Oracle SQL Query - Subquery

Subquery는 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 형태입니다.

어떤 질의문이 5줄 짜리라면 상황에 따라 Subquery를 이용해서 간결하게 줄일 수 있습니다.

1. Single-Row Subquery

// 'SCOTT' 보다 월급이 많은 사람의 이름
SELECT ename FROM emp
WHERE sal > ( SELECT sal FROM emp WHERE ename = 'SCOTT' );

// 평균 월급보다 많이 받는 사람의 이름
SELECT ename, sal FROM emp
WHERE sal < ( SELECT AVG(sal) FROM emp );

 Single-Row Subquery란 Subquery의 결과가 하나의 ROW인 경우를 말합니다.

 이 경우에는  =, >, <, <>  과 같은 Operator를 사용하여 조건을 처리해야 합니다.

2. Multi-Row Subquery

// ROLE 생성, 관리
CREATE ROLE reviewer;
GRANT select any table TO reviewer;
GRANT create session, resource TO reviewer;

// 특정 ROLE을 사용자에게 GRANT / REVOKE
GRANT reviewer TO user3;

 Multi-Row Subquery란 Subquery의 결과가 둘 이상의 ROW인 경우를 말합니다.

 이 경우에는 ANY, ALL, IN, EXIST 와 같은 연산을 사용하여 조건을 처리해야 합니다.

3. TOP-K Query

// 81년도에 입사한 사람 중 월급이 가장 많은 3명
SELECT rownum, ename, sal
FROM ( SELECT * FROM emp
 WHERE hiredate like '81%'
 ORDER BY sal DESC)
WHERE rownum < 4;

 TOP-K Query는 오라클에서 제공하는 질의문입니다.

 ROWNUM 이라는 변수를 질의의 결과에 가상으로 부여하며,

 조건을 만족하는 상위 K개의 결과를 얻기 위해 사용합니다.