그룹화와 서브쿼리 연습문제 풀이를 해보았다.
이번에는 대학교 학점 테이블을 만들어서 사용,
해당 ERD는 아래처럼 생겨먹었다
a) 홍팍이의 프로그래밍 성적은 80점을 받아 A0(4.0)이다.
b) 라이언의 프로그래밍 성적은 79점을 받아 B+(3.5)이다.
c) 젤리의 대학 수학 성적은 68점을 받아 C+(2.5)이다.
d) 알파고의 영작문 성적은 91점을 받아 A+(5.0)이다.
처음에는 하나하나씩 문제풀이를 했다.
a와 b는 같은 프로그래밍이라서 한번에 풀이
select
students.nickname
, courses.title
, grades.exam_score
, grades.grade
, grade_points.point
from grades
inner join courses
on grades.course_id = courses.id
inner join students
on students.id = grades.student_id
inner join grade_points
on grades.grade = grade_points.grade
where
courses.id = 1
and
students.id in (1,7) ;
젤리와 알파고는 각각
select
students.nickname
, courses.title
, grades.exam_score
, grades.grade
, grade_points.point
from grades
inner join courses
on grades.course_id = courses.id
inner join students
on students.id = grades.student_id
inner join grade_points
on grades.grade = grade_points.grade
where
courses.id = 2
and
students.id in (4) ;
-- d) 알파고의 영작문 성적은 91점을 받아 A+(5.0)이다. TRUE
select
students.nickname
, courses.title
, grades.exam_score
, grades.grade
, grade_points.point
from grades
inner join courses
on grades.course_id = courses.id
inner join students
on students.id = grades.student_id
inner join grade_points
on grades.grade = grade_points.grade
where
courses.id = 3
and
students.id in (5) ;
문제풀이를 보니 where절에 IN을 사용해서 한번에 확인 가능함을 알음
select
students.nickname,
courses.title,
grades.exam_score,
grade_points.grade,
grade_points.point
from students
join grades on grades.student_id = students.id
join courses on courses.id = grades.course_id
join grade_points on grade_points.grade = grades.grade
where
(nickname, title, exam_score, grades.grade, point) in (
('홍팍', '프로그래밍', '80', 'A0', 4.0) ,
('라이언', '프로그래밍', '79', 'B+', 3.5) ,
('젤리', '대학 수학', '68', 'C+', 2.5) ,
('알파고', '영작문', '91', 'A+', 5.0)
);
문제 2. 과목별 평균 점수를 조회 하시오.
select
courses.title as 과목명
, round(avg(grades.exam_score), 2) as 평균시험점수
from grades
inner join courses
on grades.course_id = courses.id
group by courses.title ;
문제 3. 학생별 평균 학점(등급점수)을 수강성적으로 부터 구하고, 해당 점수 3.5미만인 학생을 조회하시오.
select
students.nickname as 닉네임
, round(avg(grade_points.point), 2) as 평균학점
from grades
inner join students
on grades.student_id = students.id
inner join grade_points
on grade_points.grade = grades.grade
group by students.nickname
having
round(avg(grade_points.point), 2) < 3.5 ;
문제 4. 모든 강의별 평균학점을 구하고, 평균학점 보다 높은 과목 top3를 조회하시오.
select
courses.title as 강의명
, round(avg(grade_points.point), 2) as 평균학점
from
grades
inner join courses
on grades.course_id = courses.id
inner join grade_points
on grades.grade = grade_points.grade
group by courses.title
having
round(avg(grade_points.point), 2) >= (
select
avg(grade_points.point)
from grades
inner join grade_points
on grades.grade = grade_points.grade)
order by round(avg(grade_points.point), 2) desc
limit 3 ;
다시 서브쿼리를 이용해서 내일 풀어볼 예정.
원하는 데이터는 알맞게 추출되긴 했다.
댓글
댓글 쓰기