[정보처리기사/실기] SQL - SELECT (2)

1. WINDOW 함수 

GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계한다 

POW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환
RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영
DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여 

PARTITION BY : WINDOW 함수의 적용 범위가 될 속성을 지정함 
ORDER BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용 
HAVING 절 : GROUP BY 와 함께 사용되며, 그룹에 대한 조건을 지정함 

2. 그룹 함수

GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용
COUNT() : 그룹별 튜플 수를 구함
SUM(): 그룹별 합계 
AVG() : 그룹별 평균 

SELECT 상여내역, 상여금
	ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

SELECT 상여내역, 상여금
RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금 순위
FROM 상여금;

# 부서별 상여금의 평균 

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2

HAVING 은 GROUP BY 와 함께 사용되며, 그룹에 대한 조건을 지정함 

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금 
GROUP BY ROLLUP(부서, 상여내역);

함수 : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수. 속성의 개수가 n개이면 n+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계됨 

SELECT 부서 AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금 
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

ROLLUP(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수 
속성의 개수가 n개이면 n+1 레벨까지, 하위레벨에서 상위 레벨 순으로 데이터가 집계됨 

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역);

CUBE(속성명, 속성명 ...) : ROLLUP 과 유사한 형태지만 CUBE 는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함
속성의 개수가 n개 이면 2^n 레벨까지, 상위레벨에서 하위레벨 순으로 데이터가 집계됨 

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서, 상여내역);

3. 집합 연산자를 이용한 통합 질의 

UNION : 두 SELECT 문의 조회 결과를 통합하여 모두 출력함. 중복된 행은 한 번만 출력함 
UNION ALL : 두 SELECT 문의 조회 결과를 통합하여 모두 출력함. 중복된 행도 그대로 출력함 
INTERSECT : 두 SELECT 문의 조회 결과 중 공통된 행만 출력. 
EXCEPT : 첫번째 SELECT 문의 조회 결과에서 두번째 SELECT 문의 조회 결과를 제외한 행을 출력함 

<사원> 테이블과 <직원> 테이블을 통합

SELECT *
FROM 사원
UNION
SELECT *
FROM 직원;

 

<문제풀이> p.50 ~ 
#1

SELECT 학과, COUNT(*) AS 학과별튜플수
FROM 학생 
GROUP BY 학과;

#2 

SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 
FROM 성적 
GROUP BY 과목이름 
HAVING AVG(점수) >= 90;

#3

SELECT 이름, 전공, 신청과목 
FROM 학생정보, 신청정보 
WHERE 학생정보.학번 = 신청정보.학번 
AND 신청정보.신청과목 = 'Java'
GROUP BY 이름, 전공, 신청과목 -- 이름, 전공, 신청과목을 기준으로 그룹을 지정한다 
HAVING 전공 = '컴퓨터공학'

#4

SELECT 결제여부, COUNT(*) AS 학생수 
FROM 결제
GROUP BY 결제여부;

# 5

SELECT SUM(psale) 
FROM Sale
WHERE pid IN (SELECT id
	   FROM Product
       WHERE name LIKE 'USB%')

#6

SELECT 소속도시, AVG(매출액)
FROM 지점정보 WHERE 매출액 > 1000
GROUP BY 소속도시
HAVING COUNT(*) >= 3;

# 7 

SELECT 장학내역, 장학금, 
	ROW_NUMBER() OVER (PARTITION BY 장학내역 ORDER BY 장학금 DESC) AS NUM
FROM 장학금;

# 8 

SELECT 학과, 장학내역, SUM(장학금) AS 장학금 합계 
FROM 장학금
GROUP BY ROLLUP(학과, 장학내역)