이중에서 가장 많이 쓰이는 건 left join과 inner join이다.
일반적으로 join 이라고 하면 inner join을 말한다. 기본값.
--school db에서 활용
--1. at_year을 2024로 업데이트
update attend set at_year='2024';
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 2 | 2020160002 | 2020msc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 3 | 2023160002 | 2021deg001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 4 | 2023160002 | 2021deg002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 5 | 2019160123 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 6 | 2019456001 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 7 | 2020123001 | 2020ipc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 8 | 2020123020 | 2020ipc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 9 | 2020123020 | 2022che001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 10 | 2020123001 | 2022che001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 11 | 2019456001 | 2020ipc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 12 | 2019160123 | 2020ipc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | NULL | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
--2. at_term 학기 11명은(1~11) => 1 / 12~22 => 2로
--업데이트 (if문 / case when then 활용)
update attend set at_term =
if(at_num <12, 1, 2);
update attend set at_term =
case
when at_num>= 1 and at_num <=11 then 1
when at_num>=12 and at_num<=22 then 2
end;
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
--3. at_mid:40 / at_final:40 / at_hw:10 / at_attend:10
--1학기 점수만 채우기 (3명은 잘하게, 3명은 못하게, 나머지는 골고루) => 11번까지만 채우면 됨.
update attend set at_mid = 30, at_final=38, at_hw=9,at_attend=8 where at_num = 1;
update attend set at_mid = 40, at_final=40, at_hw=10,at_attend=10 where at_num = 2;
update attend set at_mid = 39, at_final=39, at_hw=8,at_attend=9 where at_num = 3;
update attend set at_mid = 37, at_final=20, at_hw=1,at_attend=9 where at_num = 4;
update attend set at_mid = 22, at_final=11, at_hw=2,at_attend=1 where at_num = 5;
update attend set at_mid = 10, at_final=15, at_hw=3,at_attend=2 where at_num = 6;
update attend set at_mid = 25, at_final=8, at_hw=9,at_attend=5 where at_num = 7;
update attend set at_mid = 21, at_final=35, at_hw=5,at_attend=4 where at_num = 8;
update attend set at_mid = 23, at_final=26, at_hw=6,at_attend=6 where at_num = 9;
update attend set at_mid = 31, at_final=24, at_hw=7,at_attend=7 where at_num = 10;
update attend set at_mid = 26, at_final=18, at_hw=7,at_attend=8 where at_num = 11;
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | NULL |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | NULL |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | NULL |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | NULL |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | n | NULL |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | n | NULL |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | n | NULL |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | NULL |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | NULL |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | NULL |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 26 | 18 | 8 | 7 | n | NULL |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
/*
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
*/
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;
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | B |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | A |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | A |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | D |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | n | F |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | n | F |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | n | F |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | D |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | D |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | D |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 26 | 18 | 8 | 7 | n | F |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
--5. at_repitition(재수강 여부) 1학기만 채우기
--at_score 'F' 또는 at_attend가 3이하면 y
update attend set at_repitition=
case
when at_score='F' or at_attend<=3 then 'y'
else 'n'
end
where at_term=1;
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | B |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | A |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | A |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | D |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | y | F |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | y | F |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | y | F |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | D |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | D |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | D |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 26 | 18 | 8 | 7 | y | F |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
-- score별 인원수 집계(단, null은 제외)
select at_score, count(*) from attend
group by at_score
having at_score is not null
order by at_score;
+----------+----------+
| at_score | count(*) |
+----------+----------+
| A | 2 |
| B | 1 |
| D | 4 |
| F | 4 |
+----------+----------+
-- 재수강 인원 집계
select at_repitition, count(at_repitition) from attend
group by at_repitition
having at_repitition = 'y';
+---------------+----------------------+
| at_repitition | count(at_repitition) |
+---------------+----------------------+
| y | 4 |
+---------------+----------------------+
--C학점이 하나도 없으므로 F한명을 C로 변경
update attend set at_mid=30, at_final=30 where at_num=11;
--학점 다시 업데이트
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;
--재수강 여부 다시 업데이트
update attend set at_repitition=
case
when at_score='F' or at_attend<=3 then 'y'
else 'n'
end
where at_term=1;
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | B |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | A |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | A |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | D |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | y | F |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | y | F |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | y | F |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | D |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | D |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | D |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 30 | 30 | 8 | 7 | n | C |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
--재수강 학생 명단
select s.std_name from
student s inner join attend a on s.std_num = a.at_std_num
where a.at_repitition = 'y';
+----------+
| std_name |
+----------+
| 전봉준 |
| 강길동 |
| 강나래 |
+----------+
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;
+------------+----------+--------------+----------+-----------+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+------------+----------------+--------------+----------+---------+----------------------+
| std_num | std_name | std_major | std_term | std_point | at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score | co_code | co_name | co_professor | co_point | co_time | co_timetable |
+------------+----------+--------------+----------+-----------+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+------------+----------------+--------------+----------+---------+----------------------+
| 2020123001 | 강나래 | 화학공학 | 1 | 56 | 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | y | F | 2020ipc001 | 컴퓨터개론 | 유관순 | 2 | 2 | 화1A,1B,2A,2B |
| 2020123020 | 박철수 | 화학공학 | 1 | 57 | 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | D | 2020ipc001 | 컴퓨터개론 | 유관순 | 2 | 2 | 화1A,1B,2A,2B |
| 2019456001 | 강길동 | 디자인 | 3 | 60 | 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 30 | 30 | 8 | 7 | n | C | 2020ipc002 | 기초전기 | 이순신 | 3 | 4 | 월1A,1B,2A목1A,1B,2A |
| 2019160123 | 전봉준 | 컴퓨터공학과 | 2 | 64 | 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020ipc002 | 기초전기 | 이순신 | 3 | 4 | 월1A,1B,2A목1A,1B,2A |
| 2020160001 | 강철수 | 컴퓨터공학 | 1 | 40 | 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | B | 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A |
| 2020160002 | 나영희 | 컴퓨터공학 | 1 | 60 | 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | A | 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A |
| 2019160123 | 전봉준 | 컴퓨터공학과 | 2 | 64 | 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A |
| 2019456001 | 강길동 | 디자인 | 3 | 60 | 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A |
| 2019160123 | 전봉준 | 컴퓨터공학과 | 2 | 64 | 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | y | F | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2019456001 | 강길동 | 디자인 | 3 | 60 | 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | y | F | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2022123001 | 강다운 | 화학공학 | 1 | 45 | 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2020160002 | 나영희 | 컴퓨터공학 | 1 | 60 | 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2023160001 | 이영철 | 컴퓨터공학 | 1 | 55 | 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2023160002 | 최수지 | 디자인 | 1 | 65 | 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A |
| 2023160002 | 최수지 | 디자인 | 1 | 65 | 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | A | 2021deg001 | 디자인기초 | 황희 | 2 | 3 | 목1A,1B,2A,2B |
| 2022123002 | 김수진 | 디자인 | 1 | 50 | 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2021deg001 | 디자인기초 | 황희 | 2 | 3 | 목1A,1B,2A,2B |
| 2023160002 | 최수지 | 디자인 | 1 | 65 | 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | D | 2021deg002 | 색채이론 | 신사임당 | 3 | 2 | 금1A,1B,2A,2B |
| 2022123002 | 김수진 | 디자인 | 1 | 50 | 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2021deg002 | 색채이론 | 신사임당 | 3 | 2 | 금1A,1B,2A,2B |
| 2020123020 | 박철수 | 화학공학 | 1 | 57 | 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | D | 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B |
| 2020123001 | 강나래 | 화학공학 | 1 | 56 | 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | D | 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B |
| 2023160002 | 최수지 | 디자인 | 1 | 65 | 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B |
| 2022123001 | 강다운 | 화학공학 | 1 | 45 | 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL | 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B |
+------------+----------+--------------+----------+-----------+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+------------+----------------+--------------+----------+---------+----------------------+
-- A 학점 학생들의 명단을 출력
학번, 이름, 학점
select std_num, std_name, at_score from attend a
join student s on s.std_num = a.at_std_num
where a.at_score='A';
+------------+----------+----------+
| std_num | std_name | at_score |
+------------+----------+----------+
| 2020160002 | 나영희 | A |
| 2023160002 | 최수지 | A |
+------------+----------+----------+'백엔드 > MySQL' 카테고리의 다른 글
| ROLL UP 및 RANK (2) | 2024.09.02 |
|---|---|
| MySQL JOIN(2) (0) | 2024.09.02 |
| 새로운 DB 생성 및 외래키 지정 (1) | 2024.08.30 |
| ERD 그리기 실습 (0) | 2024.08.29 |
| MySQL generated columns 및 transaction (3) | 2024.08.29 |