SELECT 2

정렬 (ORDER BY)과 집단 함수

 

정렬

• ORDER BY 절에서 속성을 사용하여 검색 결과를 오름차순(ASC)나 내림차순(DESC)로 정렬함
• ORDER BY절은 SELECT문에서 가장 마지막에 사용
• 디폴트 정렬 순서는 오름차순(ASC)
• NULL 값은 오름차순에서 가장 마지막에 나타나고, 내림차순에선 가장 앞에 나타남
• SELECT절에 명시한 속성들을 사용해서 정렬해야함
• 사용 예) ORDER BY 속성 [ASC | DESC]

 

# 2번 부서에 근무하는 사원들의 급여, 직급, 이름을 검색하고 급여 기준 오름차순 정렬
SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE WHERE DNO = 2
ORDER BY SALARY;

출력 결과

# 사원들의 부서번호, 사원번호, 이름을 검색하고 부서번호는 내림차순, 
# 사원번호는 부서번호에 대해 오름차순 정렬
SELECT DNO, EMPNO, EMPNAME FROM EMPLOYEE
ORDER BY DNO DESC, EMPNO ASC

출력 결과

집단 함수

• 데이터베이스에서 검색된 여러 튜플들의 집단(GROUP)에 적용되는 함수
• 각 집단 함수는 한 릴레이션에서 한 개의 속성에 적용되어 단일 값을 반환함
• SELECT절과 HAVING절에만 나타날 수 있음
• COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(속성)는 해당 속성에서 NULL값이 아닌 값들의 개수를 구함
• 집단 함수의 기능

집단 함수	기능
COUNT	튜플이나 값들의 개수
SUM	값들의 합
AVG	값들의 평균값
MAX	값들의 최대값
MIN	값들의 최소값

# 사원들의 평균 급여와 최대 급여를 검색
SELECT AVG(SALARY) AVGSAL, MAX(SALARY) MAXSAL
FROM EMPLOYEE;

출력 결과

# 사원들의 총 급여를 검색
SELECT SUM(SALARY) 총급여
FROM EMPLOYEE;

출력 결과

# 전체 부서 수를 검색
SELECT COUNT(*) AS '부서 수' FROM DEPARTMENT;

# 급여를 2500000이상 받는 사원 수 검색
SELECT COUNT(*) AS 사원수 FROM EMPLOYEE
WHERE SALARY >= 2500000;

 

그룹화(GROUP BY)

 

개요

• GROUP BY절에 명시된 속성의 값에 따라 테이블을 그룹으로 분할
• SELECT절의 속성 리스트에는 각 그룹마다 하나의 값을 갖는 속성, 집단 함수, 그룹화에 사용된 속성들만 나타낼 수 있음
• 잘못 사용 예) SELECT EMPNO, AVG(SALARY) FROM EMPLOYEE GROUP BY DNO;

# 사원들에 대해 부서별 부서번호와 평균 급여, 최대 급여를 검색
SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;

출력 결과

 

# 사원들에 대해 직급별 평균 급여를 검색
SELECT TITLE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY TITLE;

출력 결과

 

# 부서(DNO)별 사원수 검색
SELECT DNO AS 부서번호, COUNT(*) AS 사원수
FROM EMPLOYEE
GROUP BY DNO;

출력 결과

 

HAVING

• GROUP BY 절에 의해 생성된 그룹을 대상으로 조건을 적용
• GROYP BY 절에서 명시한 속성에 의해 그룹으로 분류한 다음, 그룹에 대해 HAVING 절의 검색 조건을 지정
• HAVING절은 GROUP BY절과 함께 사용
• HAVING절에 나타나는 속성은 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함

# 모든 사원들에 대해서 부서(DNO)별 평균 급여가 2500000원 이상인 부서에 대해서 부서번호, 
# 평균 급여, 최대 급여를 검색
SELECT DNO, AVG(SALARY) AVGSAL, MAX(SALARY) MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000;

출력 결과

 

# 모든 사원들에 대해 직급(TITLE)별 사원수가 2명 이상인 직급과 사원 수를 검색
SELECT TITLE, COUNT(*) 사원수
FROM EMPLOYEE
GROUP BY TITLE
HAVING COUNT(*) >= 2;

출력 결과

 

# 2명 이상 사원이 근무하는 부서별 평균 급여를 검색
SELECT DNO, COUNT(*) 사원수, AVG(SALARY) 평균급여 FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT(*) >= 2;

출력 결과

 

집합 연산

 

개요

• 집합 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 함
• UNION(합집합), EXCEPT(차집합), INTERSECT(교집합)
• UNION ALL(합집합), EXCEPT ALL(차집합), INTERSECT ALL(교집합)

 

합집합

# 김창섭이 속한 부서이거나 개발 부서의 부서번호를 검색
(SELECT DNO FROM EMPLOYEE
WHERE EMPNAME = '김창섭')
UNION
(SELECT DEPTNO FROM DEPARTMENT
WHERE DEPTNAME = '개발');

출력 결과

교집합

# 김창섭 또는 최종철이 속한 부서이면서 기획부서의 부서번호를 검색
(SELECT DNO FROM EMPLOYEE
WHERE EMPNAME = '김창섭' OR EMPNAME = '최종철')
INTERSECT
(SELECT DEPTNO FROM DEPARTMENT
WHERE DEPTNAME = '기획');

출력 결과

조인 연산

 

개요

조인은 여러 테이블의 연관된 튜플들을 하나의 테이블로 결합

SELECT ...
FROM R, S
WHERE R.A <비교연산자> S.B;

• FROM절에 3개의 테이블이 명시되면, 먼저 두 테이블을 조인하고 조인된 결과와 나머지 테이블과 조인함
• 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성됨
• 조인 질의가 수행되는 개념적 과정

1. 조인 조건을 만족하는 튜플을 찾고
2. 이 튜플들로 부터 SELECT절에 명시된 속성들만 프로젝트하고
3. 필요하다면 중복을 배제하는 순서로 진행

 

SELECT ... FROM 테이블명 JOIN 테이블명 ON (조인 조건식)
SELECT EMPNO, EMPNAME, DEPTNAME
FROM EMPLOYEE JOIN DEPARTMENT ON
(EMPLOYEE.DNO = DEPARTMENT.DEPTNO);

SELECT ... FROM R JOIN S USING (조인속성)
SELECT EMPNO, EMPNAME, DEPTNAME
FROM EMPLOYEE JOIN DEPARTMENT USING(DNO);

 

조인에서 속성 이름 중복 해결 방법

조인 조건이 명확해지도록 조인 조건의 속성 이름 앞에 테이블 이름이나 튜플 변수를 사용

SELECT *
FROM STUDENT, ENROL
WHERE STUDENT.SNO = ENROL.SNO

SELECT절에서 속성 이름 중복 시 테이블 이름 사용

 

조인 질의

# 모든 사원의 이름과 이 사원이 속한 부서 이름을 검색
SELECT EMPNAME, DEPTNAME
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.DNO = D.DEPTNO;

출력 결과

출력 과정

 

# 기획 부서의 사원 이름과 급여를 검색
SELECT EMPNAME, SALARY
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.DNO = D.DEPTNO
AND D.DEPTNAME = '기획';

출력 결과

 

조인과 ORDER BY 결합

# 모든 사원에 대해 소속 부서 이름, 사원의 이름, 직급, 급여를 검색
# 단, 부서 이름에 대해 오름차순, 부서 이름이 같은 경우 SALARY에 대해 내림차순 정렬
SELECT DEPTNAME, EMPNAME, TITLE, SALARY
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.DNO = D.DEPTNO
ORDER BY DEPTNAME, SALARY DESC;

출력 결과

 

데이터 베이스 예시

# 이홍근 학생이 신청한 수강 과목 수를 검색
SELECT COUNT(*) AS 수강과목수
FROM STUDENT AS S, ENROL AS E
WHERE S.SNO = E.SNO
AND S.NAME = '이홍근';

SELECT S.NAME, E.CREDIT
FROM STUDENT AS S, ENROL AS E, COURSE AS C
WHERE (S.SNO = E.SNO) AND (E.CNO = C.CNO)
AND (C.CNAME = '데이터베이스');

 

자체조인(SELF JOIN)

• 한 릴레이션에 속하는 튜플을 동일한 릴레이션에 속하는 튜플들고 ㅏ조인하는 것
• 실제로는 한 릴레이션에 접근하지만 FROM 절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 별칭을 두개로 지정해야함

SELECT E.EMPNAME, M.EMPNAME
FROM EMPLOYEE AS E, EMPLOYEE AS M
WHERE E.MANAGER = M.EMPNO;

출력 과정

출력 결과