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 활용)
update attend
set at_term =
case
when at_num <= 11 then 1
when at_num >= 12 then 2
end;
3. at_mid:40 / at_final:40 / at_hw:10 / at_attend:10
1학기 점수만 채우기
update attend
set at_attend = 10
where at_num = 1;
update attend
set at_attend = 10
where at_num = 2;
update attend
set at_attend = 8
where at_num = 3;
update attend
set at_attend = 9
where at_num = 4;
update attend
set at_attend = 3
where at_num = 5;
update attend
set at_attend = 6
where at_num = 6;
update attend
set at_attend = 2
where at_num = 7;
update attend
set at_attend = 10
where at_num = 8;
update attend
set at_attend = 2
where at_num = 9;
update attend
set at_attend = 4
where at_num = 10;
update attend
set at_attend = 1
where at_num = 11;
중앙 set 자리에 4개를 바꿔가며 업데이트 했는데,
update attend set at_mid = , at_final = , at_hw = , at_attend = , where at_num =
이렇게 한번에 하는게 더 좋았을거같음.
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
else F
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;
5. at_repetition(재수강여부) 1학기만 채우기
at_score 'F' 또는 at_attend이 3이하이면 y
update attend
set at_repetition = (
case
when (at_score = 'F' or at_attend<= 3) then 'y'
else 'n'
end)
where at_term = 1;
----------------------------------------------------------------------------
- score별 인원수 집계(단, null은 제외)
select at_score , count(at_score) from attend
group by at_score
having at_score is not null
order by at_score;
- 재수강 인원 집계
select at_repetition ,count(at_repetition) as '재수강 인원' from attend
group by at_repetition
having at_repetition = 'y';
- 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 학점 학생들의 명단을 출력
select * from student s
inner join attend a on s.std_num = a.at_std_num
where a.at_score = 'A';
- 학번, 이름 ,학점만 표시
select s.std_num, s.std_name, a.at_score from attend a
join student s on s.std_num = a.at_std_num
where a.at_score = 'A';
ERD Cloud : ERD 다이어그램을 그리는 사이트
식별관계 : 둘다 같은 기본키
비식별관계 : 기본키를 따로두고 외래키로 연결
workbench 에서도 표시가능 단, 전부 만들고나서 가능 만들기전에는 불가능.
-----------------------------------------------------------
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 학점의 학생명단 (학번,이름,과목)
select s.std_num, s.std_name, c.co_name from student s
join attend a on s.std_num = a.at_std_num
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, (at_mid+at_final+at_attend+at_hw) as '성적' from student s
join attend a on s.std_num = a.at_std_num
join course c on a.at_co_code = c.co_code
where a.at_score = 'A';
4. 과목별 (co_name) 성적 (중간,기말,출석,과제) 합계
과목 중 기 출 과
프로그래밍일반 65 68 30 40
select c.co_name, sum(at_mid) 중간, sum(at_final) 기말, sum(at_attend) 출석, sum(at_hw) 과제 from attend a
join course c on a.at_co_code = c.co_code
group by c.co_name
order by c.co_name;
5. 학점별 중간,기말,출석,과제 평균
select at_score, round(avg(at_mid+at_final+at_attend+at_hw),2) as '성적평균' from attend
group by at_score
having at_score is not null
order by at_score;
select at_score, round(avg(at_mid),2) 중간, round(avg(at_final),2) 기말, round(avg(at_attend),2) 출석, round(avg(at_hw),2) 과제 from attend
group by at_score
having at_score is not null
order by at_score;
6. 강철수가 수강하고 있는 과목들의 이름, 교수명 출력
select c.co_name, c.co_professor from attend a
join student s on s.std_num = a.at_std_num
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 std_num like '%2020%';
2. 프로그래밍일반 과목을 듣는 학생 명단 출력
학번, 이름, 학과 출력
select s.std_num, s.std_name, s.std_major from student s
join attend a on s.std_num = a.at_std_num
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_name, c.co_professor from attend a
join student s on s.std_num = a.at_std_num
join course c on c.co_code = a.at_co_code
where c.co_professor = '홍길동';
4. 최수지, 강길동 학생이 흭득한 학점(co_point) 합계
=> 2학기는 미포함, 미 이수학점을 제외
select s.std_name, sum(c.co_point) from attend a
join student s on s.std_num = a.at_std_num
join course c on c.co_code = a.at_co_code
where at_score is not null and at_repetition ='n'
-- 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 std_name = '최수지';
select s.std_name, sum(c.co_point) from attend a
join student s on s.std_num = a.at_std_num
join course c on c.co_code = a.at_co_code
where at_score is not null and at_repetition ='n'
group by s.std_name
having std_name = '강길동';
5. 재수강자 점수(중간+기말+출석+과제) as 합계
학번, 이름, 학과, 과목명, 점수합계
select s.std_num , s.std_name, s.std_major, c.co_name, (a.at_mid+ a.at_final + a.at_attend + a.at_hw) as 합계 from attend a
join student s on s.std_num = a.at_std_num
join course c on c.co_code = a.at_co_code
where at_repetition ='y';
WITH ROLLUP : 그룹별로 합계를 한번에 구할때 사용
GROUP BY 그룹명 WITH ROLLUP
과목별 (co_name) 성적 (중간,기말,출석,과제) 합계
과목 중 기 출 과
프로그래밍일반 65 68 30 40
디자인기초 50 50 10 10
합계 00 00 00 00
select
case
when co_name is NULL then '합계'
else co_name
end as co_name,
sum(at_mid) 중간, sum(at_final) 기말, sum(at_attend) 출석, sum(at_hw) 과제 from attend a
join course c on a.at_co_code = c.co_code
group by c.co_name WITH ROLLUP
order by grouping(c.co_name);
select if(co_name is Null, '합계', c.co_name) as co_name, sum(at_mid) 중간, sum(at_final) 기말, sum(at_attend) 출석, sum(at_hw) 과제 from attend a
join course c on a.at_co_code = c.co_code
group by c.co_name WITH ROLLUP
order by grouping(c.co_name);
ifnull(값1, 값2) : 값1이 null이면 값2를 표시
select ifnull(c.co_name,'합계') as co_name, sum(at_mid) 중간, sum(at_final) 기말, sum(at_attend) 출석, sum(at_hw) 과제 from attend a
join course c on a.at_co_code = c.co_code
group by c.co_name WITH ROLLUP
order by grouping(c.co_name);
학점별 중간,기말,출석,과제 평균
select at_score, round(avg(at_mid),2) 중간, round(avg(at_final),2) 기말, round(avg(at_attend),2) 출석, round(avg(at_hw),2) 과제 from attend
group by at_score
having at_score is not null
order by at_score;
select ifnull(at_score,'평균') as 학점, round(avg(at_mid),2) 중간, round(avg(at_final),2) 기말, round(avg(at_attend),2) 출석, round(avg(at_hw),2) 과제 from attend
where at_term = 1
group by at_score WITH ROLLUP
order by grouping(at_score);
-- having a.at_score is not null
with rollup은 having 조건을 인식못함.
---------------------------------------------------------------------
rank() : 순위
rank() over(order by 기준칼럼 desc, asc) as 칼럼명
=> 값이 같다면 같은 순위를 가짐 다음순위는 건너 뜀
dense_rank() over(order by 기준칼럼 desc, asc) 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;
select at_std_num, at_final, dense_rank() over(order by at_final desc) as ranking from attend
where at_term = 1;
- 기말 성적을 기준으로 5등 미만까지만 장학금대상 명단
select at_std_num as 장학금대상, at_final, ranking from
( select at_std_num, at_final, rank() over(order by at_final desc) as ranking from attend
where at_term = 1 ) p
where p.ranking < 5;
