MySQL 기초(join, rollup, rank)- AWS 풀스택 과정 34일차

오늘은 join, rollup, rank에 대해서 배워보도록 하겠습니다

 

목차
1.	join
2.	rollup, rank
3.	느낀 점

 

1. join

◈ ERD Cloud

ERD Cloud

 

DB - day04

school db에서 활용
1.at_year을 2024로 업데이트
update attend
set at_year = 2024;

2.at_term 학기 11명은(1~11) => 1 / 12 ~ 22 => 2
업데이트(if문 / case when then 활용)

start transaction;

update attend
set at_term = case
when at_num between 1 and 11 then 1
when at_num between 12 and 22 then 2
else 0
end;

commit;

3. at_mid : 40 / at_final : 40 / at_hw : 10 / at_attend : 10
1학기 점수만 채우기

start transaction;

update attend set at_mid = 38, at_final = 26, at_hw = 8, at_attend = 9 where at_num = 1;
update attend set at_mid = 28, at_final = 25, at_hw = 10, at_attend = 10 where at_num = 2;
update attend set at_mid = 8, at_final = 16, at_hw = 2, at_attend = 3 where at_num = 3;
update attend set at_mid = 16, at_final = 36, at_hw = 7, at_attend = 10 where at_num = 4;
update attend set at_mid = 40, at_final = 17, at_hw = 10, at_attend = 6 where at_num = 5;
update attend set at_mid = 40, at_final = 40, at_hw = 10, at_attend = 10 where at_num = 6;
update attend set at_mid = 5, at_final = 10, at_hw = 1, at_attend = 2 where at_num = 7;
update attend set at_mid = 29, at_final = 32, at_hw = 9, at_attend = 10 where at_num = 8;
update attend set at_mid = 38, at_final = 37, at_hw = 9, at_attend = 10 where at_num = 9;
update attend set at_mid = 15, at_final = 31, at_hw = 8, at_attend = 8 where at_num = 10;
update attend set at_mid = 10, at_final = 3, at_hw = 2, at_attend = 4 where at_num = 11;

commit;

4. at_score(학점) 1학기만 채우기
at_mid + at_final + at_hw + at_attend >= 90 : A
at_mid + at_final + at_hw + at_attend >= 80 : B
at_mid + at_final + at_hw + at_attend >= 70 : C
at_mid + at_final + at_hw + at_attend >= 60 : D
나머지는 F

start transaction;

update attend
set at_score = case
when at_mid + at_final + at_hw + at_attend >= 90 then 'A'
when at_mid + at_final + at_hw + at_attend >= 80 then 'B'
when at_mid + at_final + at_hw + at_attend >= 70 then 'C'
when at_mid + at_final + at_hw + at_attend >= 60 then 'D'
else 'F'
end
where at_term = 1;

commit;

5. at_repetition(재수강 여부) 1학기만 채우기
at_score 'F' 또는 at_attend이 3이하면 y

start transaction;

update attend
set at_repetition = case
when at_score = 'F' or at_attend <= 3 then 'y'
else 'n'
end
where at_term = 1;

commit;

-----------------------------------
- score별 인원수 집계(단, null은 제외)
select at_score, count(at_score) 
from attend 
group by at_score 
having at_score = 'A' or 
at_score = 'B' or 
at_score = 'C' or 
at_score = 'D' or 
at_score = 'F' 
order by at_score asc;

-> not 이용
having at_score is not null

- 재수강 인원 집계
select at_repetition, count(at_repetition) from attend 
group by at_repetition
having at_repetition = 'y';

 

▷ 출력

출력(1)

 

출력 (2)

 

출력 (3)

 

출력 (4)

 

출력 (5)

 

출력 (6)

 

출력 (7)

 

출력 (8)

 

◈ join

sql joins

 

- join
inner join => join

- 재수강 학생 명단
select s.std_name from 
student s inner join attend a on s.std_num = a.at_std_num
where a.at_repetition = 'y';
---------------------------------------------
select * from
student s inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code;
-----------------------------------------------------------
- A학점 학생들의 명단을 출력 => student / F => attend
select s.std_num, s.std_name, a.at_score from student s
inner join attend a on s.std_num =a.at_std_num
where a.at_score = 'A';

--------------------------------------------------
1. F학점의 학생명단 (학번, 이름, 학과)
select s.std_num, s.std_name, s.std_major from student s
inner join attend a on s.std_num =a.at_std_num
where a.at_score = 'F';

2. A학점의 학생명단 (학번, 이름, 과목) 
학번, 이름 => student s
A => attend a
과목 => course c

select s.std_num, s.std_name, c.co_name from student s
inner join attend a on s.std_num =a.at_std_num
inner join course c on a.at_co_code = c.co_code
where a.at_score = 'A';

3. A학점 학생들의 (학번, 이름, 과목, 성적)
- 성적(mid+final+attend+hw) as '성적'

select s.std_num, s.std_name, c.co_name, a.at_mid + a.at_final + a.at_attend + a.at_hw as '성적' from student s
inner join attend a on s.std_num =a.at_std_num
inner join course c on a.at_co_code = c.co_code
where a.at_score = 'A';

4. 과목별(co_name) 성적(중간, 기말, 출석, 과제) 합계
과목 중간 기말 출석 과제
프로그래밍일반 65 68 30 40
디자인 기초 50 50 10 10
co_name => course c
성적 => attend a

select c.co_name, sum(a.at_mid), sum(a.at_final), sum(a.at_attend), sum(a.at_hw) from attend a
inner join course c on a.at_co_code = c.co_code
group by c.co_code;

5. 학점별 중간, 기말, 출석, 과제 평균
select c.co_point, avg(a.at_mid), avg(a.at_final), avg(a.at_attend), avg(a.at_hw) from attend a
inner join course c on a.at_co_code = c.co_code
group by c.co_point;

6. 강철수가 수강하고 있는 과목들의 이름, 교수명 출력
과목명 co_name / 교수명 co_professor => course c
강철수 수강 => attend a
강철수 학번 => student s

select s.std_name, c.co_name, c.co_professor from student s
inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code
where s.std_name = '강철수';

-----------------------------------------------
1. 학번이 2020으로 시작하는 학생들의 학번, 이름, 학과 출력

select std_num, std_name,std_major from student 
where substr(std_num,1,4) = 2020;

(= where std_num like '2020%';)

2. 프로그래밍 일반 과목을 듣는 학생 명단 출력
학번, 이름, 학과 출력

select s.std_num, s.std_name, s.std_major from student s
inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code
where c.co_name = '프로그래밍일반';

3. 홍길동 교수가 강의하는 과목을 듣는 학생 명단 출력
학번, 이름, 학과, 수강과목, 담당교수

select s.std_num, s.std_name, s.std_major, c.co_professor from student s
inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code
where c.co_professor = '홍길동';


4. 최수지 학생이 획득한 학점(co_point) 합계
=> 2학기는 미포함, 미 이수학점을 제외

select sum(c.co_point) from student s
inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code
where s.std_name ='최수지' and a.at_repetition = 'n' and a.at_score is not null;

( = 
-- where at_term = 1 and at_repetition = 'n'
-- where at_term <> 2 and at_repetition = 'y'
-- where not at_term = 2 and not at_repetition ='y'
group by s.std_name
having s.std_name = '최수지';)

5. 재수강자 점수(중간 + 기말 + 출석 + 과제) as 합계
학번, 이름, 학과, 과목명, 점수합계

select s.std_num, s.std_name, s.std_major, (a.at_mid + a.at_final + a.at_attend + a.at_hw) as '합계' from student s
inner join attend a on s.std_num = a.at_std_num
inner join course c on a.at_co_code = c.co_code
where a.at_repetition = 'y';

 

▷ 출력

출석 (1)

 

출석 (2)

 

출석 (3)

 

출석 (4)

 

출석 (5)

 

출석 (6)

 

출석 (7)

 

출석(8)

 

출석 (9)

 

출석 (10)

2. rollup, rank

-----------------------------------------------------------
WITH ROLLUP : 그룹별로 합계를 한 번에 구할 때 사용
group by 그룹명 WITH ROLLUP

- 과목별(co_name) 성적(중간, 기말, 출석, 과제) 합계
과목 중간 기말 출석 과제
프로그래밍일반 65 68 30 40
디자인 기초 50 50 10 10
합계 00 00 00 00
co_name => course c
성적 => attend a

select if(c.co_name is null, '합계',c.co_name) as 'co_name', sum(a.at_mid), sum(a.at_final), sum(a.at_attend), sum(a.at_hw) from attend a
inner join course c on a.at_co_code = c.co_code
group by c.co_name WITH ROLLUP
order by GROUPING(c.co_name) ASC;

ifnull(값1, 값2) : 값1이 null이면 값2를 표시
-- if(c.co_name is null, '합계',c.co_name)
-- ifnull(c.co_name,'합계')

- 학점별 중간, 기말, 출석, 과제 평균
select ifnull(a.at_score,'평균'), avg(a.at_mid), avg(a.at_final), avg(a.at_attend), avg(a.at_hw) from attend a
where a.at_score is not null
group by a.at_score with rollup
order by grouping(a.at_score);

-- having a.at_score is not null
with rollup은 having 조건을 인식하지 못함

------------------------------------------------------
rank() : 순위
rank() over(order by 기준 칼럼 desc) as 칼럼명
=> 값이 같다면 같은 순위를 가짐 다음 순위는 건너 뜀

dense_rank() over(order by 기준 칼럼 desc) as 칼럼명
=> 값이 같다면 같은 순위를 가짐 다음 순위를 이어 감

at_final을 기준으로 순위를 작성
학번 at_final ranking
select at_std_num, at_final,
rank() over(order by at_final desc) as ranking
from attend
where at_term = 1;

- 기말 성적을 기준으로 5등 미만까지만 장학금대상 명단
select * from
(select at_std_num as '장학금명단', at_final,
rank() over(order by at_final desc) as ranking
from attend
where at_term = 1) as r
where r.ranking < 5;

※ grouping

: roll up 출력값을 맨 아래에서 출력함

 

※ rank (시험에 잘 나옴)

 

▷ 출력

출석 (1)

 

출석 (2)

 

출석 (3)

 

출석 (4)

 

3. 느낀 점

MySQL의 join의 개념, 서브쿼리의 활용이 너무 어려워서 어지러워졌다. 복습을 꼭 다시해보면서 내용 정리를 해야할 것 같다