[데이터베이스] #7 Sub Query

September 30, 2023

집계 함수

Aggregate Function

그룹에 대해 적용 가능하므로 Group function라고도 합니다.

함수	설명
COUNT	결과 행의 수
AVG	필드의 평균 값
MIN	필드의 최솟 값
MAX	필드의 최대 값
SUM	필드의 전체 합

GROUP BY

GROUP BY 절은 레코드들을 컬럼을 기준으로 그룹으로 나눕니다.

학과별 평균 성적

학과별(deptno)로 성적을 나누고 나눈 그룹내의 성적 평균을 구합니다.

SELECT deptno, AVG(grade)
FROM STUDENT
GROUP BY deptno;

GROUP BY를 사용할 때, SELECT에서는 GROUP BY에 사용된 컬럼 또는 집계 함수를 적용한 컬럼만 올 수 있습니다.

만약 이에 해당하지 않는 다른 컬럼도 사용하고 싶은 경우, 서브 쿼리를 사용할 수 있습니다. 이 부분은 뒷부분에 기술해놓았습니다.

또한, 집계 함수 값은 WHERE 절에 사용 불가합니다.

→ HAVING 사용하면 그룹 함수에 대한 조건을 제한할 수 있습니다.

HAVING

GROUP BY 에서 특정 조건을 만족하는 그룹만을 검색하고자 하는 경우, HAVING 사용합니다.

HAVING 절에는

WHERE은 단순 필드의 조건, HAVING은 그룹 함수에 대한 조건
학생수가 3 이상인 과의 학과 번호와 학점의 평균 출력

SELECT deptno, AVG(grade)
FROM STUDENT
GROUP BY deptno
HAVING COUNT(*) > 3;

Sub Query

서브 쿼리는 두 개이상의 query를 하나로 표현한 것입니다.

서브 쿼리내에서는 ORDER BY를 사용할 수 없음
SubQuery는 연산자 우측에 위치해야 하며, 괄호로 감싸주어야 함

단일 결과

학점이 가장 높은 학생의 이름, 학과 번호, 학점 구하기

SELECT sname, deptno, grade
FROM STUDENT
WHERE grade = (SELECT MAX(grade)
               FROM STUDENT);

IN

조건절에서 사용하며 다수의 비교값과 비교하여 비교값 중 하나라도 같은 값이 있다면 true

단과 대학이 'Engineering'인 학과들 소속인 학생의 이름, 학과 번호, 학점 구하기
단, 한 단과 대학은 여러 개의 과들로 이루어져 있음.

SELECT sname, deptno, grade
FROM STUDENT
WHERE deptno IN (SELECT deptno
                 FROM DEPARTMENT
                 WHERE college = 'Engineering');

ANY

다수의 비교값 중 한개라도 만족하면 true

> ANY : 최소값 보다 크면
>= ANY : 최소값보다 크거나 같으면
< ANY : 최대값보다 작으면
<= ANY : 최대값보다 작거나 같으면
= ANY : IN과 같은 효과
!= ANY : NOT IN과 같은 효과

> ANY

40번 학과에서 학점이 가장 낮은 학생보다 학점이 더 높은 학생들의 이름, 학점, 학과 번호를 학점의 내림 차순으로 출력

SELECT sname, grade, deptno
FROM STUDENT
WHERE grade > ANY (SELECT grade
                   FROM STUDENT
                   WHERE deptno = 40)
ORDER BY grade DESC;

ALL

전체 값을 비교하여 모두 만족해야만 true

> ALL : 최대값 보다 크면
>= ALL : 최대값보다 크거나 같으면
< ALL : 최소값보다 작으면
<= ALL : 최소값보다 작거나 같으면
= ALL : SUBSELECT의 결과가 1건이면 상관없지만 여러 건이면 오류가 발생
!= ALL : 위와 마찬가지로 SUBSELECT의 결과가 여러 건이면 오류가 발생

> ALL

40번 학과에서 학점이 가장 높은 학생보다 학점이 더 높은 학생들의 이름, 학점, 학과 번호를 학점의 내림 차순으로 출력

SELECT sname, grade, deptno
FROM STUDENT
WHERE grade > ALL (SELECT grade
                   FROM STUDENT
                   WHERE deptno = 40)
ORDER BY grade DESC;

가져온 레코드중에는 40번 학과 학생이 존재하지 않습니다.

EXISTS

EXISTS 연산자는 subquery의 결과 값이 한 개 이상 존재할 경우, true를 반환
지도 학생이 있는 교수의 이름, 학과 번호, 전공

SELECT pname, deptno, major
FROM PROFESSOR p
WHERE EXISTS (SELECT sid
              FROM STUDENT
              WHERE advisor = p.pid)
ORDER BY deptno;

서브 쿼리 예제

학생수가 가장 많은 학과 번호

-- Oracle
SELECT deptno
FROM STUDENT
GROUP BY deptno
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                   FROM STUDENT
                   GROUP BY deptno);

-- MySQL
SELECT deptno
FROM STUDENT
GROUP BY deptno
HAVING COUNT(*) = (SELECT MAX(student_count)
                   FROM (SELECT COUNT(*) AS student_count
                         FROM STUDENT
                         GROUP BY deptno) AS counts);

MySQL의 경우, SELECT 문을 한 번 더해야지 최대값을 가져올 수 있습니다.

학생수가 가장 많은 학과의 이름, 예산 출력

SELECT dname, budget
FROM DEPARTMENT
WHERE deptno = (SELECT deptno
                FROM STUDENT
                GROUP BY deptno
                HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                                   FROM STUDENT
                                   GROUP BY deptno));

위의 1번에서 찾은 학과 번호인 deptno를 WHERE 절에 사용하면 되겠습니다.

평균 학점이 가장 높은 학과의 번호와 평균 학점

SELECT deptno, AVG(grade)
FROM STUDENT
GROUP BY deptno
HAVING AVG(grade) = (SELECT MAX(AVG(grade))
                     FROM STUDENT
                     GROUP BY deptno)

학점이 가장 높은 학생이 재학중인 학과 이름

SELECT dname
FROM DEPARTMENT
WHERE deptno IN (SELECT deptno
                 FROM STUDENT
                 WHERE grade = (SELECT MAX(grade) FROM STUDENT));

먼저 가장 높은 학점을 학생 테이블에서 찾고, 찾은 최고 학점을 가진 학생의 학과 번호를 찾습니다.

찾은 학과 번호로 학과 이름을 학과 테이블에서 찾으면 됩니다.

잘못된 서브 쿼리 예시

학과 별로 학점이 가장 높은 학생의 이름, 학점 그리고 학과 번호를 찾기

-- 잘못된 예시
SELECT sname, grade, deptno
FROM STUDENT
WHERE grade IN (SELECT MAX(grade)
                FROM STUDENT
                GROUP BY deptno);

위의 쿼리는 정상적으로 동작하는 것 같습니다. 하지만 성적으로만 비교하기 때문에 문제가 있습니다.

예를 들어, 다른 학과에서는 최고점이 3.4일 때, 그 학과가 아닌 학생의 학점이 3.4인 경우에도 최고점으로 잘못 함께 포함되는 문제가 있습니다.

이를 올바르게 고치면 다음과 같습니다.

-- 올바른 예시, Oracle, MySQL 둘다 가능
SELECT sname, grade, deptno
FROM STUDENT
WHERE (deptno, grade) IN ( -- deptno인 학과 번호도 함께 비교
    SELECT deptno, MAX(grade)
    FROM STUDENT
    GROUP BY deptno);

학과 번호와 성적을 같이 비교하기 때문에 원하는 값인 해당 학과의 최고 성적들만 나옵니다.

이렇게 WHERE절에는 여러 개의 컬럼을 동시에 비교할 수 있습니다.

대신, Subquery의 SELECT 절에서도 동일한 수의 컬럼이 필요합니다.