SQL select - 실습15

그룹 함수

- MAX(컬럼): 컬럼 값 중 최대 값을 반환한다.

- MIN(컬럼): 컬럼 값 중 최소 값을 반환한다.

- AVG(컬럼): 컬럼 값의 평균 값을 반환한다.

- COUNT(컬럼 | *): 컬럼 값이 NULL이 아닌 행의 수를 반환 한다.(*은 NULL 포함)

- SUM(컬럼): 컬럼의 합계를 반환한다.

- STDDEV(컬럼): 컬럼의 표준 편차를 반환한다.

- VARIANCE(컬럼): 컬럼의 분산을 반환한다.

※ 그룹 함수는 GROUP BY 절 없이 사용할 수 없다.

※ 그룹 함수에서 NULL은 무시되지만, 무결성의 문제를 유발함으로 NVL 함수를 사용하는 것이 좋다.

 

GROUP BY

SELECT ...
FROM  테이블 ...
WHERE 조건 ...
GROUP BY 컬럼
ORDER BY 정렬_대상 ... ;

- GROUP BY절에 컬럼에는 항상 SELECT절에 기술된 일반 컬럼이 모두 기술되어야 한다. 그렇지 않을 경우 카디널리티가 일치하지 않아 에러가 발생할 수 있다.

- 카디널리티

• 값의 개수를 의미한다.
• SELECT문의 각 컬럼은 반드시 카디널리티가 같아야 실행된다. 

 

실습

1. 각 학과별 학생 수를 검색한다.

SELECT major 학과, count(sno) 학생수
FROM student
GROUP BY major;

2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.

SELECT major 학과, AVG(avr*4.5/4.0)
FROM student
WHERE major IN('화학', '생물')
GROUP BY major;

3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다.

SELECT orders 직급, count(pno) 교수의수
FROM professor
WHERE TRUNC(sysdate)-TRUNC(hiredate)+1 >= 3654
GROUP BY orders;

4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색한다.

SELECT  sum(st_num) 과목명에_화학이_포함된_과목
FROM course
WHERE cname LIKE '%화학%';

5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다.

SELECT st.sno, sname, major, TO_CHAR(AVG(result), '00.99') 기말고사_성적
FROM student st, score sc
WHERE st.sno = sc.sno AND major = '화학'
GROUP BY st.sno, sname, major
ORDER BY AVG(result) DESC;

6. 학과별 기말고사 평균을 성적순으로 검색한다.

SELECT major, TO_CHAR(AVG(result), '00.99') 기말고사_평균
FROM student st, score sc
WHERE st.sno = sc.sno
GROUP BY major
ORDER BY AVG(result) DESC;

7. 30번 부서의 업무별 연봉의 평균을 검색한다.

   ‐ 단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.

SELECT job, TO_CHAR(AVG(sal*12+NVL(comm, 0)), '999,999.99') "30번부서_연봉_평균"
FROM emp
WHERE dno = 30
GROUP BY job;

8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다.

SELECT sno, sname, syear, TO_CHAR(max(avr), '0.99') 성적
FROM student
WHERE major = '물리'
AND (syear, avr) IN (SELECT syear, MAX(avr) FROM student
		WHERE major = '물리'
		GROUP BY syear)
GROUP BY sno, sname, major, syear
ORDER BY syear desc;

9. 학년별로 환산 평점의 평균값을 검색한다.

   ‐ 단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.

SELECT syear, TO_CHAR(AVG(avr*4.5/4.0), '0.00') 평점
FROM student
GROUP BY syear;

10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.

SELECT sno, sname, syear, major, TO_CHAR(AVG(avr), '0.00') 평점
FROM student
WHERE major = '화학' AND syear = 1
AND avr <= (SELECT AVG(avr) FROM  student 
	WHERE  major = '화학' AND syear = 1)
GROUP BY sno, sname, syear, major;

 

 

 

 

 

2024.01.24 - [SQL] - SQL select - 실습1

SQL select - 실습1

<테이블 정보>