1. 서브쿼리
SELECT *
FROM EMP
WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE DEPTNO=10);--서브쿼리를 이용한 테이블 (인라인 뷰)
SELECT *
FROM (SELECT ROWNUM NUM, ENAME FROM EMP) A
WHERE NUM<5;2. IN
SELECT ENAME, DNAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND EMP.EMPNO IN (SELECT EMPNO FROM EMP WHERE SAL>2000);
3. ALL
--EMP의 DEPTNO가 20보다도 작고, 30보다도 작은것
SELECT * FROM EMP
WHERE DEPTNO <= ALL(20,30);
4. 스칼라 서브쿼리
--SELECT AVG(SAL) FROM EMP가 스칼라쿼리. 스칼라쿼리는 반드시 한행과 한열만 반환, 아닐시 오류발생
SELECT ENAME AS "이름", SAL AS "급여", (SELECT AVG(SAL) FROM EMP) AS "평균급여" FROM EMP
WHERE EMPNO=1000;
5. GROUP BY
GROUP BY의 칼럼에 대한 SUBTOTAL을 만들어 준다.
(1) ROLLUP 한개
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO),
SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO);|
DECODE |
SUM(SAL) |
|
10 |
3750 |
|
20 |
10875 |
|
30 |
14400 |
|
전체합계 |
29025 |
(2) ROLLUP 여러개
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);|
DEPTNO |
JOB |
SUM(SAL) |
|
10 |
CLERK |
1300 |
|
10 |
MANAGER |
2450 |
|
10 |
3750 |
|
|
20 |
CLERK |
1900 |
|
20 |
ANALYST |
6000 |
|
20 |
MANAGER |
2975 |
|
30 |
10875 |
(3) GROUPING (SELECT문에서 설정)
:요소별 합계, 해당 컬럼의 합을 나타낼 때 1, 그렇지 않을 때 0
SELECT DNAME, GROUPING(DNAME),
JOB, GROUPING(JOB),
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);|
DNAME |
GROUPING(DNAME) |
JOB |
GROUPING(JOB) |
Total Empl |
Total Sal |
|
SALES |
0 |
CLERK |
0 |
1 |
950 |
|
SALES |
0 |
MANAGER |
0 |
1 |
2850 |
|
SALES |
0 |
SALESMAN |
0 |
4 |
5600 |
|
SALES |
0 |
|
1 |
6 |
9400 |
|
RESEARCH |
0 |
CLERK |
0 |
2 |
1900 |
|
RESEARCH |
0 |
ANALYST |
0 |
2 |
6000 |
|
RESEARCH |
0 |
MANAGER |
0 |
1 |
2975 |
|
RESEARCH |
0 |
|
1 |
5 |
10875 |
|
ACCOUNTING |
0 |
CLERK |
0 |
1 |
1300 |
|
ACCOUNTING |
0 |
MANAGER |
0 |
1 |
2450 |
|
ACCOUNTING |
0 |
PRESIDENT |
0 |
1 |
5000 |
|
ACCOUNTING |
0 |
|
1 |
3 |
8750 |
|
|
1 |
|
1 |
14 |
29025 |
(4) GROUPING SET
각각 따로 합계를 계산
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);|
DEPTNO |
JOB |
SUM(SAL) |
|
- |
CLERK |
4150 |
|
- |
SALESMAN |
5600 |
|
- |
PRESIDENT |
5000 |
|
- |
MANAGER |
8275 |
|
- |
ANALYST |
6000 |
|
30 |
- |
1440 |
|
20 |
- |
10875 |
|
10 |
- |
3750 |
(5) CUBE
: 결합가능한 모든 집계를 계산
*만약 여기서 where로 조건이 있으면 조건에 맞는 행의 조합만 출력하고 나머지는 출력하지 않는다.
조합하지 않는 독립 속성의 합은 조건문과 상관없이 group by 되지만, 조합한 결합속성은 조건문의 영향을 받는다 (sqld p217 21번문제)
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);|
DEPTNO |
JOB |
SUM(SAL) |
|
- |
- |
29025 |
|
- |
CLERK |
4150 |
|
- |
ANALYST |
6000 |
|
- |
MANAGER |
8275 |
|
- |
SALESMAN |
5600 |
|
- |
PRESIDENT |
5000 |
|
10 |
- |
3750 |
|
10 |
CLERK |
1300 |
|
10 |
MANAGER |
2450 |
|
20 |
- |
10875 |
|
20 |
CLERK |
1900 |
|
20 |
ANALYST |
6000 |
|
20 |
MANAGER |
2975 |
|
30 |
- |
14400 |
|
30 |
CLERK |
950 |
|
30 |
MANAGER |
2850 |
|
30 |
SALESMAN |
5600 |
|
30 |
PRESIDENT |
5000 |