CHAPTER 02 - 응용 SQL, CHAPTER 03 - SQL 활용 및 최적화

2024년도 수제비 실기책(6판) 내용 정리

---

CHAPTER 2 - 응용 SQL  / 1. 집계성 SQL

1) 다중 행 연산자

• 개념 : 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자
• 다중 행 비교 연산자는 단일 행 비교 연산자(<, >, =, <>)와 결합하여 사용할 수 있음

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

 

[1] IN 연산자

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컬럼을 조회 FORM 절에서 EMP 테이블을 검색 WHERE 절에서 EMP_ID 컬럼에 대한 IN 조건을 다중행 서브쿼리로 정의 서브 쿼리에서 DEPT 테이블의 MGR_ID 값을 조회 결과적으로 부서별 관리자의 정보를 조회

 

[2] ANY 연산자

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

SELECT 절에서 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 컬럼을 조회 FROM 절에서 EMP 테이블을 검색 WHERE 절에서 SALARY컬럼에 대한 조건을 ANY 연산자를 활용한 Multi Row Subquery 로 정의 서브쿼리에서 JOB_TITLE 컬럼이 과장인 직원의 SALARY의 값을 조회  -> 서브쿼리의 값이 SALARY 와 비교했을 때 하나라도 맞으면 반환 직책이 과장이고 급여가 4000000 보다 많은 급여를 받는 직원들을 조회

 

[3] ALL 연산자

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

SELECT, FROM절 위와 동일 WHERE 절에서 SALARY컬럼에 대한 조건을 ALL 연산자를 활용한 Multi Row Subquery 로 정의 서브쿼리에서 JOB_TITLE 컬림이 과장인 직원의 SALARY의 값을 조회  -> 서브쿼리의 값이 SALARY와 비교 했을 때 모두 맞아야 반환 직책이 과장인 직원보다 많은 급여를 받는 직원들을 조회

 

[4] EXISTS 연산자

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

SELECT, FROM절 위와 동일, EMP를 A로 명명 WHERE 절에서 SALARY컬럼에 대한 조건을 EXISTS 연산자를 활용한 Multi Row Subquery 로 정의 서브쿼리에서 메인 쿼리와의 관계를 SALARY 컬럼의 등치조건으로 정의  -> 서브쿼리의 조건을 만족하면 1을 반환, EXISTS에서 많이 사용  -> 서브쿼리가 하나이상의 행을 반환하면 EXISTS조건이 TRUE가 됨 홍길동과 동일한 급여를 받는 직원들을 조회

 

2) 집계 함수

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

(1) 집계 함수 구문

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

 

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

[1]  GROUP BY 구문

 

• SQL에서는 WHERE 구문을 활용하여 조건별 대상 ROW를 선택함
• 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있을 때 GROUP BY를 사용하며 아래와 같은 특성을 가짐

NULL 값을 가지는 ROW는 제외한 후 산출

SELECT에서 사용하는 것과 같은 ALIAS 사용이 불가함

WHERE 구문 안에 포함되지 않음

WHERE 구문은 GROUP BY 보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 함

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

[2] HAVING 구문 - GROUP BY 및 집계 함수에 대한 WHERE 구문이라고 할 수 있음

 

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

(2) 집계 함수 종류

 

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

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

 

[1] 집계 함수 활용 예시

예시를 위한 학생 테이블

쿼리	설명
SELECT COUNT(*) FROM 학생 WHERE 국어 >= 80;	국어 점수가 80점 이상인 학생들(국어 >= 80)의 숫자를 알려줌(2명)
SELECT SUM(국어), AVG(영어) FROM 학생;	국어 점수의 합(300점), 영어 점수의 평균 값(85점)을 알려줌
SELECT MAX(국어), MIN(국어) FROM 학생;	국어 점수 최고점(100점)과 최저점(50점)을 알려줌
SELECT STDDEV(국어),                VARIANCE(국어) FROM 학생;	국어 점수의 표준편차(18.03), 분산(325)을 알려줌

---

CHAPTER 3 - SQL 활용 및 최적화 / 1. 절차형 SQL

1) 절차형 SQL (Procesural SQL) 개념

• 일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

2) 절차형 SQL 종류

종류	설명
프로시저(Procedure)	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
사용자 정의 함수 (User-Defined Function)	일련의 SQL처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거 (Trigger)	데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

---

CHAPTER 3 - SQL 활용 및 최적화 / 2. SQL 최적화

1) 튜닝(쿼리 성능 개선)의 개념

• 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
• SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선이 가능함

2) 옵티마이저(Optimizer)

• SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심엔진
• 옵티마이저가 생성한 SQL처리경로를 실행계획(Execution Plan)이라고 부름
• 옵티마이저의 유형으로 RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer)가 있음

항목	규칙기반 옵티마이저(RBO)	비용기반 옵티마이저(CBO)
개념	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저	통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저
핵심	규칙(우선순위) 기반	비용(수행 시간) 기반
평가기준	인덱스 구조, 연산자, 조건절 형태 등	레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값의 분포, 인덱스 높이, 클러스터링 팩터 등
장점	사용자가 원하는 처리경로로 유도하기가 쉬움	옵티마이저의 이해도가 낮아도 성능보장 가능(기본 설정)