[SQLD] 2과목 SQL 기본 및 활용 (PART2 SQL활용) 요약 정리

서브쿼리

• 서브쿼리는 하나의 쿼리 안에 존재하는 또 다른 쿼리
• 메인 쿼리의 컬럼이 포함된 서비쿼리를 연관 서브 쿼리, 메인 쿼리의 컬럼이 포함되지 않은 서브쿼리를 비연관 서브쿼리라고 함
• ORDER BY 절, INSERT 문의 VALUE 절 등에 사용 가능
• 다중 행 서브쿼리의 경우 '=' 조건과 함께 사용 불가
• 다중 컬럼 서브쿼리의 경우 IN 절과 함께 사용 가능

​

​

서브쿼리의 위치에 따라

• SELECT 절: 스칼라 서브쿼리(Scalar Subquery), 스칼라 서브쿼리는 주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용 가능. 컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 함
• FROM 절: 인라인 뷰(Inline View), 인라인 뷰는 FROM 절 등 테이블 명이 올 수 있는 위치에 사용 가능
• WHERE 절, HAVING 절: 중첩 서브쿼리(Nested Subquery), 중첩 서브쿼리는 메인 쿼리와의 관계에 따라 비연관 서브쿼리와 연관 서브쿼리로 나눌 수 있음

​

​

중첩 서브쿼리

• 비연관서브쿼리: 메인 쿼리와 관계를 맺고 있지 않음. 서브쿼리 내에 메인 쿼리의 컬럼이 존재하지 않음
• 연관서브쿼리: 메인 쿼리와 관계를 맺고 있음. 서브쿼리 내에 메인 쿼리의 컬럼이 존재
• 중첩쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있다.

단일 행 서브쿼리	서브쿼리가 1건 이하의 데이터를 반환 단일 행 비교 연산자와 함께 사용 =, <, >, <=, >=, <>
다중 행 서브쿼리	서브쿼리가 여러 건의 데이터를 반환 다중 행 비교 연산자와 함께 사용 IN, ALL, ANY, SOME, EXISTS
다중 컬럼 서브쿼리	서브쿼리가 여러 컬럼의 데이터를 반환

​

​

Q. 다음 SQL과 같은 결과를 출력하는 SQL?

[SQL]
SELECT A.FIRST_NAME,
       A.LAST_NAME,
       (SELECT B.DEPT_NAME
          FROM DEPT B
       WHERE B.DEPT_ID = A.DEPT_ID) AS DEPT_NAME
FROM EMP A;

EMP
| EMP_ID     | 
|------------|
| FIRST_NAME | 
| LAST_MANE  | 
| DEPT_ID(FK)|

DEPT
| DEPT_ID    | 
|------------|
| DEPT_NAME  |

[답]

SELECT A.FIRST_NAME,
       A.LAST_NAME,
       B.DEPT_NAME
   FROM EMP A
    LEFT OUTER JOIN DEPT B
      ON A.DEPT_ID = B.DEPT_ID;

<해설> 문제에 주어진 SQL은 EMP 테이블의 데이터가 모두 출력되면서 DEPT_NAME 데이터를 스칼라 서브쿼리를 이용하여 조회하는 쿼리. EMP 테이블이 모두 출력되기 때문에 EMP 테이블 기준의 OUTER JOIN으로 변환 가능

​

​

뷰(View): 특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트로, SQL에서 테이블처럼 사용할 수 있음. 뷰는 가상 테이블로, 실제 데이터를 저장하지 않고 해당 데이터를 조회해오는 SELECT 문만 가지고 있음.

• 보안성: 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 별도의 뷰를 생성하여 제공함으로써 보안을 유지
• 독립성: 테이블 스키마가 변경되었을 경우 어플리케이션은 변경하지 않고 관련 뷰만 수정
• 편리성: 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용

​

​

집합 연산자

• UNION ALL: 각 쿼리의 결과 집합의 합집합. 중복된 행도 그대로 출력
• UNION: 각 쿼리의 결과 집합의 합집합. 중복된 행은 한 줄로 출력
• INTERSECT: 각 쿼리의 결과 집합의 교집합. 중복된 행은 한 줄로 출력
• MINUS/EXCEPT: 앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합. 중복된 행은 한 줄로 출력

​

​

그룹 합수: GROUP BY 하여 나타낼 수 있는 데이터를 구하는 함수

• 집계 함수: COUNT, SUM, AVG, MAX, MIN 등
• 소계(총계) 함수: ROLLUP, CUBE, GROUPING SETS 등

​

​

ROLLUP: 소그룹 간의 소계 및 총계를 계산

ROLLUP (A)	A로 그룹핑 총합계
ROLLUP (A, B)	A, B로 그룹핑 A로 그룹핑 총합계
ROLLUP (A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A로 그룹핑 총합계

​

​

CUBE: 소그룹 간의 소계 및 총계를 다차원적으로 계산. GROUP BY가 일방향으로 그룹핑하여 소계를 구했다면, CUBE는 조합할 수 있는 모든 그룹에 대한 소계를 집계

CUBE (A)	A로 그룹핑 총합계
CUBE (A, B)	A, B로 그룹핑 A로 그룹핑 B로 그룹핑 총합계
CUBE (A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A, C로 그룹핑 B, C로 그룹핑 A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

​

​

GROUPING SETS: 특정 항목에 대한 소계를 계산. 인자값으로 ROLLUP이나 CUBE를 사용할 수 있음

GROUPING SETS (A, B)	A로 그룹핑 B로 그룹핑
GROUPING SETS (A, B, ( ))	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, ROLLUP(B))	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, ROLLUP(B, C))	A로 그룹핑 B, C로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, B, ROLLUP(C))	A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

​

ROLLUP은 인수의 순서에 따라 결과가 달라지며 CUBE와 GROUPING SETS는 인수의 순서가 바뀌어도 같은 결과를 출력

​

​

Q. 다음과 같은 결과 데이터를 얻기 위해 SQL 괄호 안에 들어갈 문장?

[SQL]
SELECT CASE ( )
      WHEN 1 THEN '전체' ELSE 주문 연월
   END 주문 연월,
  CASE ( )
      WHEN 1 THEN '전체' ELSE 주문상품
   END 주문상품,
  COUNT(*) AS 주문수량
 FROM 주문
GROUP BY ( )
ORDERY BY 주문 연월, 주문상품;

[결과]

주문 연월	주문상품	주문수량
202105	전체	50
202106	전체	32
202107	전체	43
전체	모니터	25
전체	마우스	30
전체	노트북	20
전체	키보드	50

[답]

[SQL]
SELECT CASE GROUPING(주문 연월)
      WHEN 1 THEN '전체' ELSE 주문 연월
   END 주문 연월,
  CASE GROUPING(주문상품)
      WHEN 1 THEN '전체' ELSE 주문상품
   END 주문상품,
  COUNT(*) AS 주문수량
 FROM 주문
GROUP BY GROUPING(주문 연월, 주문상품)
ORDERY BY 주문 연월, 주문상품;

<해설> GROUPING은 소계가 계산된 행에서 결과값 1을 출력하는 함수이고, GROUPING SETS는 인수들에 대한 개별 집계를 구하는 함수. 결과 데이터에 총합계 행이 출력되지 않았으므로 GROUPING SETS 함수의 인수에 ROLLUP은 적용되지 않았다고 볼 수 있음

​

​

윈도우 함수: OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나뉨

• 순위 함수: RANK, DENSE_RANK, ROW_NUMBER
• 집계 함수: SUM, MAX, MIN, AVG, COUNT
• 행 순서 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

​

​

순위 함수

• RANK: 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뜀
• ex) 1, 2, 2, 4, 5, 5, 7
• DEBSE_RANK: 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매김
• ex) 1, 2, 2, 3, 4, 4, 5
• ROW_NUMBER: 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여
• ex) 1, 2, 3, 4, 5, 6, 7

​

​

집계 함수

• SUM: 데이터의 합계를 구하는 함수. 인자값으로는 숫자형만 올 수 있음. Oracle의 경우 OVER 절내에 ORDER BY 절을 써서 데이터의 누적값을 구할 수 있음

​

​

Q. 다음 SQL 결과?

[SQL]
SELECT EMPLOYEE_ID,
       NAME,
       SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY) AS SUM1,
       SUM(SALARY) OVER (ORDER BY SALARY
                         RANGE UNBOUNDED PRECEDING) AS SUM2
FROM EMPLOYEE;

[결과]

EMPLOYEE_ID	NAME	SALARY	SUM1	SUM2
107	Diana	4200	​	​
105	David	4800	13800	13800
106	Valli	4800	​	​
103	Alexander	10000	33800	33800
104	Bruce	10000	​	​

<해설> SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주면 RANGE UNBOUNDED PRECEDING 구문이 없어도 누적합이 집계됨. RANGE 옵션은 동일한 데이터가 있을 경우 모두 합한 값을 출력

​

• MAX: 데이터의 최댓값을 구하는 함수

​

Q. 다음 SQL 결과?

[SQL]
SELECT DEPARTMENT_ID,
       NAME,
       SALARY
FROM (
   SELECT DEPARTMENT_ID,
          NAME,
          SALARY,
          MAX(SALARY) OVER (PARTITIONING BY DEPARTMENT_ID)
          AS MAX_SAL
  FROM EMPLOYEES)
WHERE SALARY = MAX_SAL;

[EMPLOYEES 테이블]

DEPARTMENT_ID	NAME	SALARY
10	Jennifer	4400
10	Pat	6000
10	Alexander	3100
10	Guy	2600
20	Michael	13000
20	Kevin	6000
20	Tayler	9600
20	Kimberely	3100
30	Den	11000
30	Shelli	2900
30	Sigal	2800

[답]

DEPARTMENT_ID	NAME	SALARY
10	Pat	6000
20	Michael	13000
30	Den	11000

<해설> MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID)는 DEPARTMENT_ID 별로 SALARY의 최댓값을 구하는 함수. 쿼리가 Inline View로 한번 쌓인 다음 바깥쪽 쿼리에서 WHERE SALARY = MAX_SAL 조건을 주었으므로 DEPARTMENT_ID 별로 최대 SALARY를 받는 사람들만 최종적으로 출력됨

​

​

• MINI: 데이터의 최솟값을 구하는 함수

[SQL]
SELECT DEPARTMENT_ID,
       NAME,
       SALARY,
       MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS MIN_SAL
FROM EMPLOYEES;

 

[답]

DEPARTMENT_ID	NAME	SALARY
10	Jennifer	2600
10	Pat	2600
10	Alexander	2600
10	Guy	2600
20	Michael	3100
20	Kevin	3100
20	Tayler	3100
20	Kimberely	3100
30	Den	2800
30	Shelli	2800
30	Sigal	2800

<해설> MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID)는 DEPARTMENT_ID 별로 SALARY의 최솟값을 구하는 함수. OVER 절에 ORDER BY 구문이 없으므로 같은 DEPARTMENT_ID를 가진 Row는 모두 같은 MIN_SALARY 값을 가짐

​

• AVG: 데이터의 평균값을 구하는 함수

​

윈도우 함수 사용 옵션

: WINDOWING 절을 이용하여 집계하려는 데이터의 범위를 지정할 수 있음

범위	의미
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLOWING	아래쪽 끝 행
CURRENT ROW	현재 행
n PRECEDING	현재 행에서 위로 n만큼 이동
n FOLLOWING	현재 행에서 아래로 n만큼 이동

기준	의미
ROWS	행 자체가 기준이 됨
RANGE	행이 가지고 있는 데이터 값이 기준이 됨

• COUNT: 데이터의 건수를 구하는 함수

​

Q. 다음 SQL 결과?

[SQL]
SELECT DEPARTMENT_ID,
       NAME,
       SALARY,
       COUNT(*) OVER (PARTITION BY DEPARTMENT_ID
                        ORDER BY SALARY DESC
                       RANGE BETWEEN 2000 PRECENDING
                         AND CURRENT ROW) AS HIGHER_SAL
FROM EMPLOYEES;

[EMPLOYEES 테이블]

DEPARTMENT_ID	NAME	SALARY	HIGHER_SAL
10	Pat	6000	​
10	Jeniffer	4400	​
10	Alexander	3100	2
10	Guy	2600	​
20	Michael	13000	​
20	Tayler	9600	​
20	Kevin	6000	​
20	Oliver	6000	2
20	Amit	4200	​
20	Kimberely	3100	​

<해설> PARTITION BY DEPARTMENT_ID OREDER BY SALARY DESC RANGE 2000 PRECENDING AND CURRENT ROW 구문을 해석해보면 구문별로 급여가 가장 높은 사원부터 정렬한 다음, 같은 부서의 맨 위쪽 끝 행부터 현재 행까지 비교하여 본인의 급여보다 2000 이하로 차이가 나는 건수를 카운트하는 것.

DEPARTMENT_ID가 10인 파티션에서 3100보다 2000 이하로 적은 급여는 4400과 3100이므로 2건,

DEPARTMENT_ID가 20인 파티션에서 6000보다 2000 이하로 적은 급여는 6000 2건이므로 2건

​

​

행 순서 함수

• FIRST_VALUE: 파티션 별 가장 선두에 위치한 데이터를 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음
• LAST_VALUE: 파티션 별 가장 끝에 위치한 데이터를 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음
• LAG: 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음
• LEAD: 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음

​

​

비율 함수

• RATIO_TO_REPORT: 파티션 별 합계에서 차지하는 비율을 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음
• PERCENT_RANK: 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수. SQL Server(MSSQL)에서는 지원하지 않음
• CUME_DIST: 해당 파티션에서 누적 백분율을 구하는 함수. 결과값은 0보다 크고 1보다 작거나 같은 값을 가짐. SQL Server(MSSQL)에서는 지원하지 않음
• NTLILE: 주어진 ㅜ만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수

​

​

Q. 다음 SQL 결과?

SELECT NAME,
       JOB_ID,
       SALARY,
       NTILE(2) OVER(PARTITION BY JOB_ID
                     ORDER BY SALARY DESC) AS RESULT
FROM EMPLOYEES;

[결과]

NAME	JOB_ID	SALARY	RESULT
NeeNA	AD_VP	17000	​
Lex	AD_VP	17000	2
Bruce	IT_PROG	14000	​
Alexander	IT_PROG	10000	​
Valli	IT_PROG	5800	1
David	IT_PROG	4800	​
Diana	IT_PROG	4200	​

<해설> NTILE 함수는 주어진 수만큼 행들을 N등분한 후 현재 행에 해당하는 등급을 구하는 함수. 문제에서는 SALARY 데이터가 2개의 그룹으로 분리된 후 동등하게 등급이 부여됨. 동일한 데이터가 2개 있는 경우 각각 1그룹과 2그룹으로 할당되고 똑같은 행 수로 할당되지 않는 경우 1그룹부터 다시 할당이 시작되므로 IT_PROG 그룹의 경우 1그룹이 3개 2그룹이 2개로 나뉨.

​

​

Top-N 쿼리: N은 N위까지 출력하겠다는 의미

• ROWNUM: 실제로 존재하지 않는 가짜 컬럼. 액셀의 자동 순번 매기기가 +1이 되는 것처럼 ROWNUM도 행이 반환될 때마다 순번이 1씩 증가. ROWNUM은 항상 < 조건이나 <= 조건으로 사용. ROWNUM으로 순서를 지정할 때에는 ORDER BY 절 바깥에서 해야 함
• 윈도 함수의 순위 함수

​

​

셀프 조인(Self Join): 나 자신과의 조인. FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막기 위해 ALIAS를 반드시 표기해야 함

​

​

Q. EMPLOYEES 테이블로 다음과 같은 결과 데이터를 얻기 위한 SQL?

[EMPLOYEES 테이블]

EMPLOYEES_ID	NAME	MANAGER_ID
201	Michael	100
100	Steven	​
200	Jennifer	101
115	Alexander	114
...	...	...

[결과]

MANAGER_ID	MANAGER_NAME	EMPLOYEE_ID	NAME
100	Steven	124	Kevin
100	Steven	201	Michael
101	Neena	200	Jennifer
114	Den	115	Alexander
114	Den	118	Guy
145	John	155	Oliver
147	Alberto	167	Amit
148	Gerald	170	Tayler
149	Eleni	178	Kimberely
210	Michael	202	Pat

[답]

SELECT A.EMPLOYEE_ID AS MANAGER_ID,
       A.NAME AS MANAGER_NAME,
       B.EMPLOYEE_ID,
       B.NAME
   FROM EMPLOYEES A,
        EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.MANAGER_ID //"A" 테이블의 "EMPLOYEE_ID"와 "B" 테이블의 "MANAGER_ID" 열을 비교하여 "A"의 직원이 "B"의 관리자인 경우에 해당하는 행만 선택
ORDER BY MANAGER_ID;

<해설> 위 결과를 도출하기 위해서는 EMPLOYEE 테이블을 셀프조인해야 한다. 계층 구조를 이루기 위해 조인 조건은 A.EMPLOYEE_ID = B.MANAGER_ID가 되어야 하며 이런 방식으로 조인을 할 경우 A쪽에 MANAGER 정보가 나타나기 때문에 SELECT 절에 A쪽 컬럼이 MANAGER 정보로 출력되어야 함

​

​

계층 쿼리

• LEVEL: 현재의 DEPT를 반환. 루드 노트는 1이 됨
• SYS_CONNECT_BY_PATH (컬럼, 구분자): 루드 노드부터 현재 노드까지의 경로 출력
• START WITH: 경로가 시작되는 루트 노드를 생성해주는 절
• CONNECT BY: 루트로부터 자식 노드를 생성해주는 절. 조건에 만족하는 데이터가 없을 때까지 노드 생성. 순방향 전개인지 역방향 전개인지 결정
• PRIOR: 바로 앞에 있는 부모 노드의 값을 반환
• CONNECT_BY_ROOT 컬럼: 루트 노드의 주어진 컬럼 값을 반환
• CONNECT_BY_ISLEAF: 가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환
• 계층 쿼리에서 ORDER BY 절을 사용하면 계층과 상관없이 전체 정렬이 되기 때문에 계층 구조를 유지할 수 없음. ORDER SIBLINGS BY 절을 사용하면 같은 레벨들끼리 정렬을 수행하기 때문에 계층 구조를 유지할 수 있음

​

​

Q. 다음 SQL의 결과?

[SQL]
SELECT EMPLOYEE_ID,
       NAME,
       MANAGER_ID
  FROM EMPLOYEES
START WITH MANGER_ID IS NULL
CONNECT BY PRIOIR EMPLOYEE_ID = MANAGER_ID // 이전 행의 "MANAGER_ID"가 현재 행의 "EMPLOYEE_ID"와 일치하는 경우에만 다음 레벨의 직원을 검색
ORDER SIBLINGS BY EMPLOYEE_ID DESC;

[EMPLOYEES 테이블]

EMPLOYEES_ID	NAME	MANAGER_ID
100	Steven	NULL
101	Neena	100
102	Lex	100
108	Nancy	101
109	Daniel	108
110	John	108
111	Ismael	108
112	Jose Manual	108
113	Luis	108

[결과]

EMPLOYEES_ID	NAME	MANAGER_ID
100	Steven	NULL
101	Neena	100
102	Lex	100
108	Nancy	101
113	Luis	108
112	Jose Manual	108
111	Ismael	108
110	John	108
109	Daniel	108

<해설> 문제에서 주어진 쿼리는 가장 상위 매니저부터 순방향으로 전개되는 계층 쿼리로 같은 레벨일 경우 EMPLOYEE_ID로 내림차순하여 정렬함. 계층을 이루는 순서는 다음과 같다.

​

START WITH MANAGER_ID IS NULL

EMPLOYEES_ID	NAME	MANAGER_ID
100	Steven	NULL

CONNECT BY PRIOIR EMPLOYEE_ID = MANAGER_ID

EMPLOYEES_ID	NAME	MANAGER_ID
100	Steven	NULL
101	Neena	100
108	Lex	101
109	Nancy	108
110	Luis	108
111	Jose Manual	108
112	Ismael	108
113	John	108
102	Daniel	100

ORDER SIBLINGS BY EMPLOYEE_ID DESC

EMPLOYEES_ID	NAME	MANAGER_ID
100	Steven	NULL
101	Neena	100
102	Lex	100
108	Nancy	101
113	Luis	108
112	Jose Manual	108
111	Ismael	108
110	John	108
109	Daniel	108

​