SQLD - Week 5 - 실전문제 Part2 - 2. SQL 활용

<SQL 자격검정 실전문제> (일명 노랭이) 개정판 1회독 + 오답노트
- 출제 비중이 높은 Part 2 부터 시작
오답노트하며 중요한 개념 정리

51번

서브쿼리 종류

52번

UNION ALL
GROUPING SETS
ROLLUP
CUBE

SELECT ENAME,
       SUM(SAL)
FROM EMP
GROUP BY ENAME
UNION ALL
SELECT NULL,
       SUM(SAL)
FROM EMP
ORDER BY 1 ASC

	ENAME	SUM(SAL)
0	ADAMS	1100
1	ALLEN	1600
2	BLAKE	2850
3	CLARK	2450
4	FORD	3000
5	JAMES	950
6	JONES	2975
7	KING	5000
8	MARTIN	1250
9	MILLER	1300
10	SCOTT	3000
11	SMITH	800
12	TURNER	1500
13	WARD	1250
14	None	29025

SELECT ENAME,
       SUM(SAL)
FROM EMP
GROUP BY GROUPING
SETS (ENAME)
ORDER BY 1 ASC

	ENAME	SUM(SAL)
0	ADAMS	1100
1	ALLEN	1600
2	BLAKE	2850
3	CLARK	2450
4	FORD	3000
5	JAMES	950
6	JONES	2975
7	KING	5000
8	MARTIN	1250
9	MILLER	1300
10	SCOTT	3000
11	SMITH	800
12	TURNER	1500
13	WARD	1250

SELECT ENAME,
       SUM(SAL)
FROM EMP
GROUP BY ROLLUP (ENAME)
ORDER BY 1 ASC

	ENAME	SUM(SAL)
0	ADAMS	1100
1	ALLEN	1600
2	BLAKE	2850
3	CLARK	2450
4	FORD	3000
5	JAMES	950
6	JONES	2975
7	KING	5000
8	MARTIN	1250
9	MILLER	1300
10	SCOTT	3000
11	SMITH	800
12	TURNER	1500
13	WARD	1250
14	None	29025

SELECT ENAME,
       SUM(SAL)
FROM EMP
GROUP BY CUBE (ENAME)
ORDER BY 1 ASC

	ENAME	SUM(SAL)
0	ADAMS	1100
1	ALLEN	1600
2	BLAKE	2850
3	CLARK	2450
4	FORD	3000
5	JAMES	950
6	JONES	2975
7	KING	5000
8	MARTIN	1250
9	MILLER	1300
10	SCOTT	3000
11	SMITH	800
12	TURNER	1500
13	WARD	1250
14	None	29025

GROUPING SETS에서 전체 그룹 연산 결과 출력하려면 () 꼭 포함해야함

SELECT ENAME,
       SUM(SAL)
FROM EMP
GROUP BY GROUPING
SETS (ENAME, ())
ORDER BY 1 ASC

	ENAME	SUM(SAL)
0	ADAMS	1100
1	ALLEN	1600
2	BLAKE	2850
3	CLARK	2450
4	FORD	3000
5	JAMES	950
6	JONES	2975
7	KING	5000
8	MARTIN	1250
9	MILLER	1300
10	SCOTT	3000
11	SMITH	800
12	TURNER	1500
13	WARD	1250
14	None	29025

GROUPING SETS는 지정한 컬럼에 대한 그룹연산 결과만 출력
반면, ROLEUP, CUBE는 전체 그룹 연산까지 출력함!

55번

FK는 여러 개일수 있다는 점!
- 단순히 PK와 FK가 같은 데이터를 찾으면 FK가 여러개인 경우 해당 데이터들이 모두 출력됨
쿼리문 꼼꼼히 끝까지 해석하기!

56번

UNION은 무조건 중복데이터는 1개만 두고 나머지 삭제함!

57번

SELECT b.grade,
       a.job,
       sum(a.sal) AS sum_sal,
       count(*) AS cnt
FROM emp a,
     salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal
GROUP BY GROUPING sets(grade, (job, grade))

	GRADE	JOB	SUM_SAL	CNT
0	1	CLERK	2850	3
1	2	SALESMAN	2500	2
2	2	CLERK	1300	1
3	3	SALESMAN	3100	2
4	4	MANAGER	8275	3
5	4	ANALYST	6000	2
6	5	PRESIDENT	5000	1
7	1	None	2850	3
8	2	None	3800	3
9	3	None	3100	2
10	4	None	14275	5
11	5	None	5000	1

SELECT b.grade,
       a.job,
       sum(a.sal) AS sum_sal,
       count(*) AS cnt
FROM emp a,
     salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal
GROUP BY ROLLUP (grade,
                 job)

	GRADE	JOB	SUM_SAL	CNT
0	1.0	CLERK	2850	3
1	2.0	SALESMAN	2500	2
2	2.0	CLERK	1300	1
3	3.0	SALESMAN	3100	2
4	4.0	MANAGER	8275	3
5	4.0	ANALYST	6000	2
6	5.0	PRESIDENT	5000	1
7	1.0	None	2850	3
8	2.0	None	3800	3
9	3.0	None	3100	2
10	4.0	None	14275	5
11	5.0	None	5000	1
12	NaN	None	29025	14

SELECT b.grade,
       a.job,
       sum(a.sal) AS sum_sal,
       count(*) AS cnt
FROM emp a,
     salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal
GROUP BY grade,
         ROLLUP (job)

	GRADE	JOB	SUM_SAL	CNT
0	1	CLERK	2850	3
1	2	SALESMAN	2500	2
2	2	CLERK	1300	1
3	3	SALESMAN	3100	2
4	4	MANAGER	8275	3
5	4	ANALYST	6000	2
6	5	PRESIDENT	5000	1
7	1	None	2850	3
8	2	None	3800	3
9	3	None	3100	2
10	4	None	14275	5
11	5	None	5000	1

SELECT b.grade,
       a.job,
       sum(a.sal) AS sum_sal,
       count(*) AS cnt
FROM emp a,
     salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal
GROUP BY grade,
         CUBE (job)

	GRADE	JOB	SUM_SAL	CNT
0	1	None	2850	3
1	1	CLERK	2850	3
2	2	None	3800	3
3	2	CLERK	1300	1
4	2	SALESMAN	2500	2
5	3	None	3100	2
6	3	SALESMAN	3100	2
7	4	None	14275	5
8	4	ANALYST	6000	2
9	4	MANAGER	8275	3
10	5	None	5000	1
11	5	PRESIDENT	5000	1

ROLLUP 에 전달한 컬럼이 2개면 해당 컬럼들의 총합이 출력됨

58번

WINDOW FUNCTION 윈도우함수

SELECT 윈도우함수(대상) OVER([PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC|DESC]
[ROWS|RANGE BETWEEN A AND B]);

OVER 절을 활용해서 그룹함수를 윈도우함수로 활용
PARTITION BY : 연산결과만 출력하는 / 그룹연산 수행할 GROUP BY 컬럼

RATIO_TO_REPORT()

비율을 계산

60번

계층형 질의

하나의 테이블 내 각 행끼리의 관계를 가질때, 연결고리로 행-행 사이의 계층을 표현

PRIOR 위치에 따라 연결 데이터가 달라짐

  SELECT
  FROM 테이블 명
  START WITH 시작조건
  CONNECT BY PRIOR 연결조건;

START WITH : 데이터 출력할 시작 지정하는 조건
- 해당 데이터가 있는 행이 LEVEL 1
CONNECT BY PRIOR : 시작점을 기준으로 하위 레벨을 찾아가는 조건
- 행을 이어나갈 조건
ORDER SIBLINGS BY 컬럼 : 같은 LEVEL 일 경우 정렬 수행

DESC : 내림차순
- 큰 → 작은