3단원 연습문제

-- a) 컴퓨터 과학과의 3학점짜리 과목의 제목을 찾아라
SELECT title 
FROM course 
WHERE dept_name = 'Comp. Sci.' AND credits = 3;

-- b) Einstein 교수가 가르치는 과목을 듣는 모든 학생의 아이디를 찾아라
SELECT DISTINCT id 
FROM takes
WHERE (course_id, sec_id, semester, year) IN 
(
    SELECT course_id, sec_id, semester, year 
    FROM teaches INNER JOIN instructor 
        ON teaches.id = instructor.id
    WHERE instructor.name = 'Einstein'
);

-- c) 교수의 가장 높은 급여를 구하라
SELECT MAX(salary) 
FROM instructor

-- d) 가장 높은 급여를 받는 모든 교수를 찾아라
SELECT id, name
FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor)

-- e) 2017년 가을에 개설된 각 분반에 걸친 최대 등록자 수를 구하라
SELECT course_id, sec_id, (
    SELECT COUNT(id)
    FROM takes
    WHERE takes.year = section.year
        AND takes.semester = section.semester
        AND takes.course_id = section.course_id 
        AND takes.sec_id = section.sec_id
) AS enrollment 
FROM section 
WHERE semester = 'Fall' AND year = 2017

-- f) 2017년 가을의 모든 분반에 걸친 최대 등록자 수를 구하라
WITH enrollment_in_fall_2017(course_id, sec_id, enrollment) AS (
    SELECT course_id, sec_id, COUNT(id)
    FROM takes
    WHERE semester = 'Fall' AND year = 2017
    GROUP BY course_id, sec_id
) 
SELECT CASE 
        WHEN MAX(enrollment) IS NOT NULL THEN MAX(enrollment)
        ELSE 0
       END
FROM enrollment_in_fall_2017;

-- g) 2017년 가을의 최대 등록자 수를 찾는 분반을 구하라.
WITH enrollment_in_fall_2017(course_id, sec_id, enrollment) AS (
    SELECT course_id, sec_id, COUNT(id) 
    FROM takes
    WHERE semester = 'Fall' AND year = 2017
    GROUP BY course_id, sec_id
) 
SELECT course_id, sec_id
FROM enrollment_in_fall_2017
WHERE enrollment = (SELECT MAX(enrollment) FROM enrollment_in_fall_2017);

-- a번
SELECT SUM(c.credits * g.points)
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
WHERE t.ID = '12345'

-- b번
SELECT SUM(c.credits * g.points) / SUM(credits) AS GPA
FFROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
WHERE t.ID = '12345'

-- c번
SELECT t.ID, SUM(c.credits * g.points) / SUM(credits) AS GPA
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
GROUP BY t.ID

-- d번
/*
위 쿼리들은 모두 grade_points와 takes 간의 grade 값에 대한 동등성 검사를 포함하고 있다. 
따라서, takes 관계에서 grade가 null인 튜플의 경우 해당 학생의 해당 과목은 결과 계산에서 제외된다. 
결과적으로, 해당 과목의 학점(credits)도 제외되므로, 일부 grade 값이 null이더라도 쿼리는 올바른 
결과를 반환한다.
*/

-- a번
UPDATE instructor
SET salary = salary * 1.10
WHERE dept_name = 'Comp. Sci.'

-- b번
DELETE FROM course
WHERE course_id NOT IN (SELECT course_id FROM section)

-- c번
INSERT INTO instructor
SELECT ID, name, dept_name, 10000
FROM student
WHERE tot_cred > 100

-- a번
SELECT COUNT(DISTINCT person.driver_id)
FROM accident, participated, person, owns
WHERE accident.report_number = participated.report_number
      AND owns.driver_id = person.driver_id 
      AND owns.license_plate = participated.license_plate
      AND year = 2017

-- b번
DELECT FROM car
WHERE year = 2010 AND license_plate IN 
    (SELECT license_plate FROM owns WHERE driver_id = '12345')

-- a. marks 릴레이션을 기반으로 각 학생들의 학점을 출력해보자.
SELECT ID
	CASE
		WHEN score < 40 then 'A'
		WHEN score < 60 then 'C'
		WHEN score < 80 then 'B'
		ELSE 'A'
	END 
FROM marks

-- b. 각 학점에 해당하는 학생 수를 구해라
WITH grades(ID,grade) AS (
    SELECT ID, 
        CASE
            WHEN score < 40 THEN 'F'
            WHEN score < 60 THEN 'C'
            WHEN score < 80 THEN 'B'
            ELSE 'A' 
        END
    FROM marks
) 
SELECT grade, COUNT(ID)
FROM grades
GROUP BY grade

SELECT dept_name
FROM department
WHERE LOWER(dept_name) LIKE '%sci%'