MySQL JOIN(2)

https://koop.tistory.com/16

MySQL JOIN(1)

지난번 포스팅에 이어 계속 실습 진행.

 

--1. F학점의 학생명단 (학번, 이름, 학과) => student / F => attend
select s.std_num, s.std_name, s.std_major 
from student s
join attend a
on s.std_num = a.at_std_num
where a.at_score='F';
+------------+----------+--------------+
| std_num    | std_name | std_major    |
+------------+----------+--------------+
| 2019160123 | 전봉준   | 컴퓨터공학과 |
| 2019456001 | 강길동   | 디자인       |
| 2020123001 | 강나래   | 화학공학     |
+------------+----------+--------------+

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

select s.std_num, s.std_name, c.co_name
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 a.at_score='A';

+------------+----------+--------------+
| std_num    | std_name | co_name      |
+------------+----------+--------------+
| 2020160002 | 나영희   | 대학수학기초 |
| 2023160002 | 최수지   | 디자인기초   |
+------------+----------+--------------+

--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 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 a.at_score='A';

+------------+----------+--------------+------+
| std_num    | std_name | co_name      | 성적 |
+------------+----------+--------------+------+
| 2020160002 | 나영희   | 대학수학기초 |  100 |
| 2023160002 | 최수지   | 디자인기초   |   95 |
+------------+----------+--------------+------+

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

select c.co_name,
sum(a.at_mid) as '중간', 
sum(a.at_final) as '기말',
sum(a.at_attend) as '출석',
sum(a.at_hw) as '과제'
from attend a
join course c 
on a.at_co_code = c.co_code
group by c.co_code
order by c.co_name;

+----------------+------+------+------+------+
| co_name        | 중간 | 기말 | 출석 | 과제 |
+----------------+------+------+------+------+
| 기초전기       |   30 |   30 |    8 |    7 |
| 대학수학기초   |   70 |   78 |   18 |   19 |
| 디자인기초     |   39 |   39 |    9 |    8 |
| 색채이론       |   37 |   20 |    9 |    1 |
| 컴퓨터개론     |   46 |   43 |    9 |   14 |
| 프로그래밍일반 |   32 |   26 |    3 |    5 |
| 화학이론       |   54 |   50 |   13 |   13 |
+----------------+------+------+------+------+

--5. 학점별 중간, 기말, 출석, 과제 평균 	avg, group by
select a.at_score,
round(avg(a.at_mid),2) as '중간', 
round(avg(a.at_final),2) as '기말',
round(avg(a.at_attend),2) as '출석',
round(avg(a.at_hw),2) as '과제'
from attend a
group by a.at_score
having a.at_score is not null
order by a.at_score;
+----------+-------+-------+------+------+
| at_score | 중간  | 기말  | 출석 | 과제 |
+----------+-------+-------+------+------+
| A        | 39.50 | 39.50 | 9.50 | 9.00 |
| B        | 30.00 | 38.00 | 8.00 | 9.00 |
| C        | 30.00 | 30.00 | 8.00 | 7.00 |
| D        | 28.00 | 26.25 | 6.50 | 4.75 |
| F        | 19.00 | 11.33 | 2.67 | 4.67 |
+----------+-------+-------+------+------+

--6. 강철수가 수강하고 있는 과목들의 이름, 교수명 출력
--과목명 co_name / 교수명 co_professor => course
--강철수 수강 => attend a
--강철수 학번 => student s
select c.co_name, c.co_professor 
from course c
join attend a
on c.co_code = a.at_co_code
join student s
on s.std_num = a.at_std_num
where s.std_name = '강철수';

+--------------+--------------+
| co_name      | co_professor |
+--------------+--------------+
| 대학수학기초 | 홍길동       |
+--------------+--------------+

--1. 학번이 2020으로 시작하는 학생들의
--학번, 이름, 학과 출력
select std_num, std_name, std_major from student where std_num like '2020%';
+------------+----------+------------+
| std_num    | std_name | std_major  |
+------------+----------+------------+
| 2020123001 | 강나래   | 화학공학   |
| 2020123020 | 박철수   | 화학공학   |
| 2020160001 | 강철수   | 컴퓨터공학 |
| 2020160002 | 나영희   | 컴퓨터공학 |
+------------+----------+------------+

--2. 프로그래밍일반 과목을 듣는 학생 명단 출력
--학번, 이름, 학과 출력 (컬럼명이 내가 join하는 테이블 내에서 유일하게 다 구분이 된다면, as를 안써도 괜찮음.
--하지만 실무에서는 거의 겹치기 때문에 쓰는 연습을..)
select std_num, std_name, std_major
from student
join attend
on std_num = at_std_num
join course
on at_co_code = co_code
where co_name = '프로그래밍일반';

+------------+----------+--------------+
| std_num    | std_name | std_major    |
+------------+----------+--------------+
| 2019160123 | 전봉준   | 컴퓨터공학과 |
| 2019456001 | 강길동   | 디자인       |
| 2022123001 | 강다운   | 화학공학     |
| 2020160002 | 나영희   | 컴퓨터공학   |
| 2023160001 | 이영철   | 컴퓨터공학   |
| 2023160002 | 최수지   | 디자인       |
+------------+----------+--------------+

--3. 홍길동 교수가 강의하는 과목을 듣는 학생명단 출력
--학번, 이름, 학과, 수강과목, 담당교수
--(as를 썼다가 안쓰는건 상관 없음, 하지만 반드시 인지가 되어야 함.)
select s.std_num, s.std_name, s.std_major, c.co_name, c.co_professor
from attend a
join student s
on std_num = at_std_num
join course c
on a.at_co_code = c.co_code
where c.co_professor = '홍길동';

+------------+----------+--------------+--------------+--------------+
| std_num    | std_name | std_major    | co_name      | co_professor |
+------------+----------+--------------+--------------+--------------+
| 2020160001 | 강철수   | 컴퓨터공학   | 대학수학기초 | 홍길동       |
| 2020160002 | 나영희   | 컴퓨터공학   | 대학수학기초 | 홍길동       |
| 2019160123 | 전봉준   | 컴퓨터공학과 | 대학수학기초 | 홍길동       |
| 2019456001 | 강길동   | 디자인       | 대학수학기초 | 홍길동       |
+------------+----------+--------------+--------------+--------------+

--4. 최수지 학생이 획득한 학점(co_point) 합계 (강길동도 해보기)
--  => 2학기는 미포함, 미 이수학점을 제외
--  => 수강을 하고 있는 1학기의 과목 중 미이수학점을 제외, 들은 과목의 co_point의 합계
select s.std_name, sum(c.co_point) as '학점합계'
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 at_term = 1 and at_repitition = 'n'
-- where at_term = 1 and at_repitition = 'n'
-- where at_term <>2 and at_repitition <> 'y'
-- where not at_term = 2 and not at_repitition = 'y'
group by s.std_name
having s.std_name = '최수지';
--group by having 대신 where에 추가해도 됨. 

+----------+----------+
| std_name | 학점합계 |
+----------+----------+
| 최수지   |        5 |
+----------+----------+

--5. 재수강자 점수(중간+기말+출석+과제) as 합계
--학번, 이름, 학과, 과목명, 점수합계
select std_num, std_name, std_major, co_name, 
(at_mid+at_final+at_attend+at_hw) as '합계'
from attend 
join student 
on std_num = at_std_num
join course
on at_co_code = co_code
where at_repitition = 'y';

+------------+----------+--------------+----------------+------+
| std_num    | std_name | std_major    | co_name        | 합계 |
+------------+----------+--------------+----------------+------+
| 2019160123 | 전봉준   | 컴퓨터공학과 | 프로그래밍일반 |   36 |
| 2019456001 | 강길동   | 디자인       | 프로그래밍일반 |   30 |
| 2020123001 | 강나래   | 화학공학     | 컴퓨터개론     |   47 |
+------------+----------+--------------+----------------+------+