1. 그룹 함수(Group Function)의 핵심 이해
단일행 함수(예: UPPER, ROUND 등)는 각 행마다 하나의 결과값을 내지만, 그룹 함수는 여러 행을 뭉쳐서 단 하나의 결과값만 출력합니다.
- 카디널리티(Cardinality) 불일치 오류 주의!
- "사원 번호(eno), 사원명(ename)과 함께 전체 급여의 최대값(MAX(sal))을 출력해라." ➡️ 출력 불가능 (에러 발생)
- 사원 번호와 사원명은 여러 개(N개)인데, 급여 최대값은 단 한 개(1개)이기 때문에 행의 개수(카디널리티)가 맞지 않아 같이 출력할 수 없습니다.
2. 자주 쓰는 그룹 함수 5가지
그룹 함수는 기본적으로 NULL 값을 없는 존재로 무시하고 계산합니다.
- MAX(컬럼) : 최댓값
- MIN(컬럼) : 최솟값
- AVG(컬럼) : 평균값
- SUM(컬럼) : 합계
- COUNT(컬럼 | *) : 행의 개수 구하기
- COUNT(*): NULL을 포함한 전체 행 개수
- COUNT(컬럼): 해당 컬럼에서 NULL이 아닌 행의 개수만 카운트
⚠️ NULL 처리 주의 (예시) 네 명의 직원 중 세 명은 보너스 10만 원을 받았고, 한 명은 보너스가 NULL(미지급)입니다.* `AVG(보너스)`를 그냥 쓰면 `30만 원 / 3명 = 10만 원`이 됩니다. * 원래 전체 인원(4명) 기준으로 평균을 내야 한다면, `NVL(보너스, 0)`을 사용해 `30만 원 / 4명 = 7.5만 원`으로 변환 후 계산해야 정확합니다.
3. 돈과 숫자를 다루는 실무 팁
- TO_CHAR 적극 활용: 돈이나 소수점 평균을 출력할 때는 가독성을 위해 반드시 TO_CHAR(값, '형식')을 사용하여 예쁘게 포맷팅해 줍니다.
- 진짜 숫자 vs 문자의 구분: 주민등록번호, 사번, 학번처럼 연산(더하기/빼기)할 일이 없는 숫자형 데이터는 애초에 문자형(VARCHAR2)으로 설계하는 것이 맞습니다.
4. GROUP BY 절의 절대 규칙
특정 그룹별(예: 부서별, 학과별)로 묶어서 그룹 함수를 쓰고 싶을 때 사용합니다.
- ★암기 필수 규칙★
- SELECT 절에 그룹 함수가 아닌 일반 컬럼이 들어가 있다면, 그 일반 컬럼은 무조건 GROUP BY 절에 그대로 적혀 있어야 합니다.
- 반대로, GROUP BY 절에 있는 컬럼이 SELECT 절에 안 나오는 것은 문법상 괜찮습니다.
Code
-- 올바른 예시 (deptno가 SELECT와 GROUP BY에 모두 존재)
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
5. 실습 문제 모범 답안 & 한 줄 해설
Q1. 학과별 학생 수 구하기
Code
SELECT major, COUNT(*)
FROM student
GROUP BY major;
- 해설: major(학과)별로 그룹을 묶고, 각 그룹의 학생 수(COUNT(*))를 셉니다.
Q2. 화학과와 생물학과 학생들의 4.5 만점 기준 평점 평균 구하기 (소수점 둘째 자리까지)
Code
SELECT major, TO_CHAR(AVG(avr*4.5/4.0),'0.00')
FROM student
WHERE major IN ('화학','생물')
GROUP BY major;
- 해설: WHERE 절로 화학/생물 학과만 필터링한 뒤, 4.0 만점 평점(avr)을 4.5 만점으로 환산하여 평균을 내고 TO_CHAR로 소수점 둘째 자리까지 예쁘게 출력합니다.
Q3. 임용된 지 10년(120개월) 이상 된 교수의 직급별 인원수 구하기
Code
SELECT orders, COUNT(*)
FROM professor
WHERE months_between(sysdate, hiredate) >= 120
GROUP BY orders;
- 해설: 오늘 날짜(sysdate)와 임용일(hiredate) 사이의 개월 수가 120개월 이상인 교수들을 필터링한 뒤, 직급(orders)별로 묶어 인원수를 셉니다.
Q4. 과목명에 '화학'이 들어가는 과목들의 학점 총합 구하기
Code
SELECT SUM(st_num)
FROM course
WHERE cname like '%화학%';
- 해설: 과목명(cname)에 '화학'이 포함된 행을 찾아 학점(st_num)의 총합을 구합니다. (그룹화할 일반 컬럼이 없으므로 GROUP BY는 생략합니다.)
Q5. 화학과 학생들의 학생별 성적 평균을 구하여 높은 순으로 정렬하기
Code
SELECT major, s.sno, sname, syear, to_char(avg(result),'999')
FROM student s, score r
WHERE s.sno=r.sno
AND major = '화학'
GROUP BY major, s.sno, sname, syear
ORDER BY 5 DESC; -- 5번째 컬럼(평균 성적) 기준 내림차순 정렬
- 해설: 학생 테이블과 성적 테이블을 조인(s.sno=r.sno)한 뒤, 화학과 학생들의 개인 정보들을 모두 GROUP BY에 적어주고 성적 평균을 구합니다. ORDER BY 5 DESC로 성적이 높은 사람부터 정렬합니다.
Q6. 학과별 평균 성적을 구해 높은 순으로 정렬하기
Code
SELECT major 학과, to_char(avg(result),'999') 성적
FROM student s, score r
WHERE s.sno=r.sno
GROUP BY major
ORDER BY 2 DESC; -- 2번째 컬럼(성적) 기준 내림차순 정렬
- 해설: 학과(major)별로 그룹을 묶어 성적 평균을 구하고, 성적이 높은 학과부터 내림차순 정렬합니다.
Q7. 30번 부서의 업무별 연봉 평균 구하기 (보너스 포함)
Code
SELECT job, TO_CHAR(AVG(sal*12+NVL(comm,0)),'999,999.99')
FROM emp
WHERE dno='30'
GROUP BY job;
- 해설: 30번 부서원들의 업무(job)별로 그룹을 묶습니다. 이때 보너스(comm)가 NULL인 경우를 대비해 NVL(comm, 0) 처리를 해준 뒤 연봉 평균을 구합니다.
Q8. 물리과 학생 중 학년별로 최고 평점을 가진 학생의 정보 출력하기 (서브쿼리 활용)
Code
SELECT major, syear, sno, sname, avr
FROM student
WHERE (syear, avr) IN ( SELECT syear, MAX(avr)
FROM student
WHERE major='물리'
GROUP BY syear)
AND major = '물리'
ORDER BY major, syear;
코드 접기 8줄
- 해설: 서브쿼리를 이용해 '물리과 학년별 최고 평점' 리스트를 먼저 뽑아낸 뒤, 메인 쿼리에서 이 조건과 일치하는 학생의 전체 정보를 가져옵니다. 다중 행 비교(IN)를 사용한 고난도 쿼리입니다.
Q9. 학년별 평점 평균 구하기 (4.5 만점 기준)
Code
SELECT syear, TO_CHAR(AVG(avr*4.5/4.0),'0.00')
FROM student
GROUP BY syear;
- 해설: 학년(syear)별로 그룹을 묶어 환산 평점 평균을 소수점 둘째 자리까지 구합니다.
Q10. 화학과 1학년 학생 중, 화학과 1학년 평균 평점 이하인 학생 검색하기
Code
SELECT *
FROM student
WHERE major='화학' AND syear=1
AND avr <= (SELECT AVG(avr) FROM student
WHERE major='화학' AND syear=1);
- 서브쿼리로 '화학과 1학년의 평균 평점'을 구한 뒤, 메인 쿼리에서 이 평균값 이하(<=)인 화학과 1학년 학생들만 걸러내어 출력합니다.