[SQL] 데이터 분석, 활용 _ 그룹화와 서브쿼리 연습문제

강지영·2023년 2월 23일
0

연습문제

🔫 다음은 CS 대학 1학년 학생들의 첫 학기 성적 관련 정보이다. 이를 실제 DB로 만들고, 관련 문제를 푸시오.

  • DB 구조도

  • DB 데이터


준비

  • 테이블 생성 및 레코드 등록
/* 테이블 생성 */
-- 학생
CREATE TABLE students (
  id       SERIAL      PRIMARY KEY, -- PK
  nickname VARCHAR(30)              -- 닉네임
);
-- 과목
CREATE TABLE courses (
  id    SERIAL      PRIMARY KEY, -- PK
  title VARCHAR(60)              -- 과목명
);
-- 등급 점수
CREATE TABLE grade_points (
  grade VARCHAR(15)   PRIMARY KEY, -- PK
  point DECIMAL(3, 2)              -- 학점
);
-- 수강성적
CREATE TABLE grades (
  id         SERIAL      PRIMARY KEY,                   -- PK
  student_id INTEGER     REFERENCES students(id),       -- FK
  course_id  INTEGER     REFERENCES courses(id),        -- FK
  exam_score INTEGER,                                   -- 시험점수
  grade      VARCHAR(15) REFERENCES grade_points(grade) -- 성적 등급
);
/* 레코드 등록 */
-- 학생
INSERT INTO
  students(nickname)
VALUES
  ('홍팍'),
  ('쿠마'),
  ('호크'),
  ('젤리'),
  ('알파고'),
  ('베이글'),
  ('라이언')
;
-- 과목
INSERT INTO
  courses(title)
VALUES
  ('프로그래밍'),
  ('대학 수학'),
  ('영작문'),
  ('글쓰기'),
  ('물리와 실험'),
  ('사고와 표현'),
  ('공학 윤리')
;
-- 등급 점수
INSERT INTO
  grade_points(grade, point)
VALUES
  ('A+', 4.5),
  ('A0', 4.0),
  ('B+', 3.5),
  ('B0', 3.0),
  ('C+', 2.5),
  ('C0', 2.0),
  ('D0', 1.5),
  ('F',  0.0)
;
-- 수강성적
INSERT INTO
  grades(student_id, course_id, exam_score, grade)
VALUES
  (1, 1, 90,  'A0'),
  (2, 1, 97,  'A+'),
  (3, 1, 100, 'A+'),
  (4, 1, 98,  'A+'),
  (5, 1, 64,  'D0'),
  (6, 1, 81,  'B+'),
  (7, 1, 79,  'B+'),
  (1, 2, 88,  'A0'),
  (2, 2, 99,  'A+'),
  (3, 2, 82,  'B+'),
  (4, 2, 68,  'C+'),
  (5, 2, 76,  'B0'),
  (6, 2, 63,  'C0'),
  (7, 2, 71,  'C+'),
  (1, 3, 76,  'B+'),
  (2, 3, 72,  'B+'),
  (3, 3, 79,  'B+'),
  (4, 3, 83,  'A0'),
  (5, 3, 91,  'A+'),
  (6, 3, 69,  'B0'),
  (7, 3, 84,  'A0'),
  (1, 4, 90,  'A0'),
  (2, 4, 82,  'B+'),
  (3, 4, 88,  'A0'),
  (4, 4, 99,  'A+'),
  (5, 4, 68,  'C+'),
  (6, 4, 94,  'A+'),
  (7, 4, 60,  'C0'),
  (1, 5, 66,  'B0'),
  (2, 5, 78,  'A0'),
  (3, 5, 73,  'B+'),
  (4, 5, 84,  'A+'),
  (5, 5, 76,  'A0'),
  (6, 5, 84,  'A+'),
  (7, 5, 71,  'B+'),
  (1, 6, 79,  'B+'),
  (2, 6, 89,  'A0'),
  (3, 6, 96,  'A+'),
  (4, 6, 82,  'B+'),
  (5, 6, 91,  'A0'),
  (6, 6, 100, 'A+'),
  (7, 6, 70,  'C+'),
  (1, 7, 99,  'A+'),
  (2, 7, 94,  'A0'),
  (3, 7, 60,  'C0'),
  (4, 7, 68,  'C+'),
  (5, 7, 75,  'B0'),
  (6, 7, 81,  'B0'),
  (7, 7, 89,  'B+')
;

문제

📑 다음 중 올바른 것을 모두 고르시오. [ b , c ]
a) 홍팍이의 프로그래밍 성적은 80점을 받아 A0(4.0)이다.
b) 라이언의 프로그래밍 성적은 79점을 받아 B+(3.5)이다.
c) 젤리의 대학 수학 성적은 68점을 받아 C+(2.5)이다.
d) 알파고의 영작문 성적은 91점을 받아 A+(5.0)이다.

SELECT
	students.nickname,
	courses.title,
	grades.exam_score,
	grade_points.grade,
	grade_points.point
FROM
	students			-- 1 : 학생
JOIN grades ON			-- 2 : 수강 성적
	grades.student_id = students.id
JOIN courses ON			-- 3 : 과목
	courses.id = grades.course_id
JOIN grade_points ON	-- 4 : 등급 점수
	grade_points.grade = grades.grade
WHERE
	(nickname, title, exam_score, grade_points.grade,point) IN (
	('홍팍','프로그래밍', 80, 'A0', 4.0), -- a
	('라이언','프로그래밍', 79, 'B+', 3.5), -- b
	('젤리','대학 수학', 68, 'C+', 2.5), -- c
	('알파고','영작문', 91, 'A+', 5.0) -- d
	)
;

📑 과목별 평균 시험점수를 다음과 같이 조회하시오.

-- 내가 구현한 것
SELECT
	courses.title AS "과목명",
	ROUND(AVG(grades.exam_score),2) AS "평균 시험 점수"
FROM
	grades
JOIN courses ON
	courses.id = grades.course_id
GROUP BY
	"과목명"
;

-- 강의에서 구현한 것
SELECT
	courses.title AS "과목명",
	avg_exam_score.round AS "평균 시험 점수"
FROM
	courses
JOIN (
	SELECT
		course_id,
		ROUND(AVG(grades.exam_score),2)
	FROM
		grades
	GROUP BY
		course_id 
	)	AS avg_exam_score ON
	avg_exam_score.course_id = courses.id 
;

📑 학생별 평균 학점(등급점수)을 수강성적으로부터 구하고, 해당 점수가 3.5 미만인 학생을 다음과 같이 조회하시오.

📌 조인할 레코드 수가 적을 수록 좋음
레코드 수가 많으면 처리가 느려짐

SELECT 
	nickname AS "닉네임",
	avg_grade_point.round AS "평균 학점"
FROM
	students
JOIN
	(
	SELECT 
		student_id,
		ROUND(AVG(point),2)
	FROM
		grades
	JOIN grade_points ON
		grade_points.grade = grades.grade
	GROUP BY
		student_id
	HAVING 
		AVG(point) < 3.5
	) AS avg_grade_point ON 
	avg_grade_point.student_id = students.id
;

📑 모든 강의별 평균 학점(등급점수)을 구하고, 이보다 후하게 준 “꿀 강의” TOP3를 조회하시오.

--모든 강의별 평균 학점(등급점수)을 구하고, 이보다 후하게 준 “꿀 강의” TOP3를 조회
SELECT
	title	AS "강의명",
	avg_grade_point.round AS "평균 학점"
FROM
	courses
JOIN
	(
	SELECT
		course_id,
		ROUND(AVG(point),2)
	FROM
		grades
	JOIN grade_points ON
		grade_points.grade = grades.grade
	GROUP BY
		course_id
	HAVING 
		AVG(point) > (
		-- 모든 강의의 평균 학점
		SELECT
			AVG(point)
		FROM
			grades
		JOIN grade_points ON
			grade_points.grade = grades.grade
	)
	ORDER BY
		AVG(point)DESC
	LIMIT
		3
	) AS avg_grade_point ON
	avg_grade_point.course_id = courses.id
;
profile
Hello World!

0개의 댓글