1. 과목별 중간, 기말, 출석, 과제 합계
mysql> select co_name as '과목', sum(at_mid+at_final+at_attend+at_hw) as '점수 합계'
-> from attend
-> join course on at_co_code = co_code
-> group by co_name
-> ;
+----------------+-----------+
| 과목 | 점수 합계 |
+----------------+-----------+
| 컴퓨터개론 | 135 |
| 대학수학기초 | 173 |
| 기초전기 | 125 |
| 프로그래밍일반 | 142 |
+----------------+-----------+
4 rows in set (0.00 sec)
mysql> select co_name as '과목', sum(at_mid) as '중간 합계', sum(at_final) as '기말 합계', sum(at_attend) as ' 출석 합계',sum(at_hw) as '과제 합계'
-> from attend
-> join course on at_co_code = co_code
-> group by co_name ;
+----------------+-----------+-----------+-----------+-----------+
| 과목 | 중간 합계 | 기말 합계 | 출석 합계 | 과제 합계 |
+----------------+-----------+-----------+-----------+-----------+
| 컴퓨터개론 | 65 | 52 | 9 | 9 |
| 대학수학기초 | 68 | 75 | 16 | 14 |
| 기초전기 | 37 | 68 | 13 | 7 |
| 프로그래밍일반 | 67 | 42 | 16 | 17 |
+----------------+-----------+-----------+-----------+-----------+
4 rows in set (0.00 sec)
2. 학점별 중간, 기말, 출석, 과제 합계
mysql> select co_point as '학점', sum(at_mid+at_final+at_attend+at_hw) as '점수 합계'
-> from attend
-> join course on at_co_code = co_code
-> group by co_point;
+------+-----------+
| 학점 | 점수 합계 |
+------+-----------+
| 2 | 135 |
| 3 | 440 |
+------+-----------+
2 rows in set (0.00 sec)
mysql> select at_score, sum(at_mid)as '중간', sum(at_final)as '기말', sum(at_attend)as '출석', sum(at_hw)as '과제'
-> from attend
-> where at_score is not null
-> group by at_score
-> order by at_score;
+----------+------+------+------+------+
| at_score | 중간 | 기말 | 출석 | 과제 |
+----------+------+------+------+------+
| A | 78 | 75 | 16 | 12 |
| B | 30 | 40 | 8 | 5 |
| C | 35 | 22 | 8 | 9 |
| D | 69 | 88 | 21 | 15 |
| F | 25 | 12 | 1 | 6 |
+----------+------+------+------+------+
5 rows in set (0.01 sec)
3. 강철수가 수강하고 있는 과목들의 교수명 출력
mysql> select co_professor as '교수명', co_name as '과목명'
-> from attend
-> join course on at_co_code = co_code
-> join student on at_std_num = std_num
-> where std_name = '강철수'
-> order by co_name;
+--------+----------------+
| 교수명 | 과목명 |
+--------+----------------+
| 홍길동 | 대학수학기초 |
| 임꺽정 | 프로그래밍일반 |
+--------+----------------+
2 rows in set (0.00 sec)