[정보처리기사] SQL 응용 : 응용 SQL

 

1️⃣ 집계성 SQL

☆☆☆

 

---

(1) 다중 행 연산자

 

⓵ 다중 행 연산자의 개념

 

• 서브쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자

---

⓶ 다중 행 연산자의 종류

 

연산자	설명
IN	리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
ANY	서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참
ALL	값을 서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야만 참
EXISTS	메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

※ ANY와 SOME은 같은 개념임

 

■ IN 연산자

 

[DEPT] 테이블

DEPT_ID	DEPT_NAME	MGR_ID
10	개발팀	101
20	운영팀	106

 

[EMP] 테이블

EMP_ID	EMP_NAME	DEPT_ID
101	김철수	10
102	홍길동	10
103	장보고	10
104	이순신	10
105	유관순	10
106	박영희	20
107	허준	20
108	정약용	20
109	스티브	20

 

[쿼리]

SELECT EMP_ID, EMP_NAME, DEPT_ID FROM EMP WHERE EMP_ID IN (SELECT MGR_ID FROM DEPT);

• SELECT 절에서 EMP_ID, EMP_NAME, DEPT_ID 컬럼을 조회 • FROM 절에서 EMP 테이블을 검색 • WHERE 절에서 EMP_ID 컬럼에 대한 IN 조건을 다중 행 서브쿼리로 정의 • 서브쿼리에서 DEPT 테이블의 MGR_ID 값을 조회 • 결과적으로 부서별 관리자의 정보를 조회

 

[조회 결과]

EMP_ID	EMP_NAME	DEPT_ID
101	김철수	10
106	박영희	20

 

■ ANY 연산자

 

[EMP] 테이블

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[쿼리]

SELECT EMP_ID, EMP_NAME, SALARY, JOB_TITLE FROM EMP A WHERE SALARY > ANY (SELECT SALARY FROM EMP WHERE JOB_TITLE = '과장');

• SELECT 절에서 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 컬럼을 조회 • FROM 절에서 EMP 테이블을 검색 • WHERE 절에서 SALARY 컬럼에 대한 조건을 ANY 연산자를 활용한 다중 행 서브쿼리로 정의 • 서브쿼리에서 JOB_TITLE 컬럼이 과장인 직원의 SALARY 값을 조회 • 결과적으로 직책(JOB_TITLE)이 과장인 직원들보다 많은 급여(SALARY)를 받는 직원들을 조회

 

[조회 결과]

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
106	박영희	5000000	팀장
101	김철수	4500000	팀장
102	홍길동	4000000	과장
107	허준	4000000	과장

■ ALL 연산자

 

[EMP] 테이블

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[쿼리]

SELECT EMP_ID, EMP_NAME, SALARY, JOB_TITLE FROM EMP A WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE JOB_TITLE = '과장');

• SELECT 절에서 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 컬럼을 조회 • FROM 절에서 EMP 테이블을 검색 • WHERE 절에서 SALARY 컬럼에 대한 조건을 ALL 연산자를 활용한 다중 행 서브쿼리로 정의 • 서브쿼리에서 JOB_TITLE 컬럼이 과장인 직원의 SALARY 값을 조회 • 결과적으로 직책(JOB_TITLE)이 과장인 직원들보다 많은 급여(SALARY)를 받는 직원들을 조회

 

[조회 결과]

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
101	김철수	4500000	팀장
106	박영희	5000000	팀장

 

■ EXISTS 연산자

 

[EMP] 테이블

A.EMP_ID	A.EMP_NAME	A.SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[쿼리]

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY FROM EMP A WHERE EXISTS (SELECT 1 FROM EMP B WHERE A.SALARY = B.SALARY AND B.EMP_NAME = '홍길동');

• SELECT 절에서 EMP_ID, EMP_NAME, SALARY 컬럼을 조회 • FROM 절에서 EMP 테이블을 검색 • WHERE 절에서 EXISTS 연산자를 활용한 다중 행 서브쿼리로 정의 • 서브쿼리에서 메인쿼리와의 관계를 SALARY 컬럼의 등치 조건으로정의 • 결과적으로 홍길동과 동일한 급여(SALARY)를 받는 직원들을 조회

 

[조회 결과]

A.EMP_ID	A.EMP_NAME	A.SALARY
102	홍길동	4000000
107	허준	4000000

 

---

 

(2) 집계 함수(Aggregate Function)

 

1. 집계 함수의 개념

 

• 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

---

2. 집계 함수 구문

 

SELECT 컬럼1, 컬럼2, ..., 집계함수
  FROM 테이블명
  [WHERE 조건]
  GROUP BY 컬럼1, 컬럼2, ...
  [HAVING 조건식(집계함수 포함)];

• WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시에 HAVING을 사용하는 것
• GROUP BY 구문 뒤에는 테이블을 구분하는 컬럼을 기재하여 그룹화함
• HAVING 구문은 그룹화도니 집합에 대한 조건 지정 시 사용하고, 상수나 집약 함수, 집약 키를 사용할 수 있음

 

⓵ GROUP BY 구문

 

• SQL에서는 WHERE 구문을 활용하여 조건별 대상 행을 선택함
• 복수 행 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있음

< GROUP BY 절의 특성 >

• NULL 값을 가지는 행은 제외한 후 산출함 • SELECT에서 사용하는 것과 같은 ALIAS 사용이 불가함 • WHERE 구문 안에 포함되지 않음 • WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 함

• 실제 구체적 데이터 분석값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며, 이 부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해 줌

---

⓶ HAVING 구문

 

• WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용됨
• 일반적으로 GROUP BY 뒤에 기재하며, GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용함

---

3. 집계 함수의 종류

 

• 집계 특성상 숫자 유형의 계산에 사용되는 것이 대다수이나, MAX/MIN 또는 COUNT와 같이 문자열 유형의 최대/최소나 건수 계산 등에도 사용함

종류	내용
COUNT	복수 행의 줄 수를 반환하는 함수
SUM	복수 행의 해당 컬럼 간의 합계를 계산하는 함수
AVG	복수 행의 해당 컬럼 간의 평균을 계산하는 함수
MAX	복수 행의 해당 컬럼 중 최대값을 계산하는 함수
MIN	복수 행의 해당 컬럼 중 최소값을 계산하는 함수
STDDEV	복수 행의 해당 컬럼 간의 표준편차를 계산하는 함수
VARIANCE	복수 행의 해당 컬럼 간의 분산을 계산하는 함수

※ 집계 함수를 계산할 때 NULL이 포함된 경우 없는 데이터로 판단하면 됨