[SQL]GROUP BY(열 2개, 열 3개, null값 처리와 having 활용)

Configuration

database에 haksa_db 를 추가하고, dump.sql 에 해당하는 파일을 import한 후 실습 진행

dump.sql 다운로드
(우클릭 후 다른 이름으로 링크 저장)

show databases;
create database haksa_db;
use haksa_db;
## dump.sql import 후
show tables;

1. 2개 열에 대한 그룹화

student table 확인

select * from student;

1. student 테이블에 있는 학생의 학년(grade)별 그룹과 인원을 출력하고 학년(grade)을 기준으로 정렬하라

select grade as 학년, count(stu_no) as 인원 from student group by grade order by grade asc;

2. 각 입학년도별 총 학생 수를 출력하라

select left(stu_no,4) as 입학년도, count(stu_no) as 인원 from student group by  left(stu_no,4) order by left(stu_no,4);

fee table 확인

3. fee 테이블로부터 등록년도(fee_year)에 대하여 등록횟수를 출력하라.

select fee_year as 등록년도, count(fee_year) as 등록횟수 from fee
group by fee_year
order by fee_year asc;

4. fee 테이블에서 학번을 기준으로 학번, 등록횟수, 각 학생이 받은 장학금의 전체 합을 출력하라

select stu_no as 학번,
count(stu_no) as 등록횟수,
sum(jang_total) as 장학금총합
from fee
group by stu_no
order by stu_no asc;

5. 박정인 학생에 대하여 학번, 등록횟수를 출력하라 (fee, student 테이블 사용, 서브쿼리 사용)

select stu_no as 학번, count(stu_no) as 등록횟수 from fee
where stu_no in (select stu_no from student where stu_name = ('박정인'));

3개 이상의 열에 대한 그룹화

1. student 테이블로부터 학년(grade)별, 주야(juya)별 인원수를 출력하라. 단 출력 순서는 학년별 오름차순, 주야 오름차순이다.
   

2. student 테이블로부터 학년(grade), 반(class), 주야(juya)구분이 서로 다른 모든 조합을 인원수로 출력하라. 단 출력 순서는 학년별 오름차순이다.

#해당 모든 칼럼을 group by 에 넣는다!
select grade, class, juya from student group by grade, class, juya order by grade asc;

3. fee 테이블로부터 각 학생별로 대학 재학시 납입금(fee_pay) 총액과 등록금(fee_total) 최대값, 가장 적게 받은 장학금(jang_total), 등록 횟수를 출력하라.

select stu_no, sum(fee_pay), max(fee_total), min(jang_total), count(stu_no) from fee group by stu_no order by stu_no;

4. 등록한 학생에 대하여 학번, 이름, 납입금(fee_pay) 총액을 출력하라. (이름 정보는 student 테이블에 존재한다)

## 각 table별로 멀티select, 그리고 where 문 활용
select student.stu_no, stu_name, sum(fee.fee_pay) from student, fee where student.stu_no = fee.stu_no group by student.stu_no, stu_name;

3. Null 값의 그룹화 : isnull()

• NULL 값을 가지고 있는 열을 group by하면 NULL 값은 하나의 그룹으로 구성된다.

1. fee 테이블로부터 서로 다른 장학코드(jang_code)를 그룹화하고 인원수를 출력하라. 단 jang_code의 값이 null인 경우 null로 표시하라

select ifnull(jang_code,null), count(jang_code) from fee group by jang_code;

4. Having!

• GROUP BY 통해 그룹화된 그룹에 대한 조건문 설정(마치 where 처럼)

2. fee 테이블로부터 세 번 이상 등록한 학생의 학번과 등록 횟수를 출력하라

select stu_no, count(stu_no) from fee group by stu_no having count(stu_no)>3;

3. fee 테이블로부터 2006년에 등록한 학생의 학번과 등록 횟수를 출력하라.

select stu_no, fee_year, count(stu_no) from fee group by stu_no,fee_year having fee_year = '2006';

group by에 두 칼럼을 추가해야 오류가 안나네. 왤까?

4. fee 테이블로부터 납입금(fee_pay)의 총액이 5,000,000원 이상인 각 학생에 대하여 출력하라

select stu_no, sum(fee_pay) from fee group by stu_no having sum(fee_pay) > 5000000;