[MySQL] ERD 수강관리 프로그램 4. join을 이용한 검색

1. A받은 학생의 명단(학번, 이름) 

mysql> select a.at_std_num, std_name, a.at_score from attend a
    -> join student s
    -> on at_std_num = s.std_num
    -> where at_score = 'A';
+------------+----------+----------+
| at_std_num | std_name | at_score |
+------------+----------+----------+
| 2020160001 | 강철수   | A        |
| 2020123020 | 박철수   | A        |
+------------+----------+----------+
2 rows in set (0.00 sec)

2. 재수강자 명단(학번, 이름, 재수강여부 )

mysql> select a.at_std_num as "학번", std_name as "이름", a.at_repetition as '재수강여부' from attend a
    -> join student s
    -> on at_std_num = s.std_num
    -> where at_repetition = 'y';
+------------+--------+------------+
| 학번       | 이름   | 재수강여부 |
+------------+--------+------------+
| 2020123001 | 강나래 | y          |
+------------+--------+------------+
1 row in set (0.00 sec)

3. A학점 학생들의 과목 (학번, 이름, 과목, 성적)

mysql> select a.at_std_num as "학번", s.std_name as "이름", c.co_name as "과목", a.at_score as "성적"  from attend a
    -> join student s on at_std_num = s.std_num
    -> join course c on at_co_code = c.co_code
    -> where at_score = 'A';
+------------+--------+--------------+------+
| 학번       | 이름   | 과목         | 성적 |
+------------+--------+--------------+------+
| 2020123020 | 박철수 | 컴퓨터개론   | A    |
| 2020160001 | 강철수 | 대학수학기초 | A    |
+------------+--------+--------------+------+
2 rows in set (0.00 sec)

mysql> select at_std_num as "학번", std_name as "이름", co_name as "과목", at_score as "성적"  from attend
    -> join student on at_std_num = std_num
    -> join course on at_co_code = co_code
    -> where at_score = 'A';
+------------+--------+--------------+------+
| 학번       | 이름   | 과목         | 성적 |
+------------+--------+--------------+------+
| 2020123020 | 박철수 | 컴퓨터개론   | A    |
| 2020160001 | 강철수 | 대학수학기초 | A    |
+------------+--------+--------------+------+
2 rows in set (0.00 sec)