8.2.1: Complex queries example

영어로 공부하니까 문제를 이해를 못하겠네

 

level 1

A university wants to know the names of instructors who taught sections that are not full, meaning that the capacity is greater than the number of students enrolled. 

What tables contain data relevant to the university's question?

▼View solution

Instructor, SectionInstructor, Section, Enrollment

 

Instructor: Instructor 테이블에는 강사의 이름과 성이 포함되어 있으며, 이는 결과 테이블에 필요하다.

SectionInstructor: SectionInstructor의 각 행은 SectionID와 InstructorID 외래 키를 사용하여 각 강사를 섹션에 할당한다. 질문에서는 특정 섹션을 가르친 강사를 원하므로, SectionInstructor 테이블을 참조해야 한다.

Section: 질문에서 섹션의 수용 인원을 언급하고 있으며, 이는 Section 테이블에서 확인할 수 있다.

Course: 질문에 코스가 언급되지 않았으므로 Course 테이블은 필요하지 않다.

Enrollment: Enrollment의 각 행은 SectionID와 StudentID 외래 키를 사용하여 각 학생을 섹션에 할당한다. 질문에서는 각 섹션에 등록된 학생 수를 요구하므로, Enrollment 테이블을 참조해야 한다.

Student: 질문에서 학생을 언급하고는 있지만, Student 테이블에는 학생의 ID, 이름, 이메일 주소, 생년월일만 기록되어 있다. 따라서 Student 테이블은 필요하지 않다.

level 2

A university wants to know all instructors who have taught the student Ani Cox.

The query below answers the university's question. Fill in the blanks.

SELECT DISTINCT I.FirstName, I.LastName
FROM ____(A)____
INNER JOIN SectionInstructor SI ON I.InstructorID = ____(B)____
INNER JOIN Section SE ON SI.SectionID = SE.SectionID
INNER JOIN Enrollment E ON SE.SectionID = E.SectionID
____(C)____ Student ST ON E.StudentID = ST.StudentID
WHERE ST.FirstName = 'Ani' AND ST.LastName = 'Cox';

▼View solution

(A) Instructor I (B) SI.InstructorID (C) INNER JOIN

 

대학교의 질문에 답하기 위해 Instructor, SectionInstructor, Section, Enrollment, Student 테이블을 조인해야 한다.
(A): Instructor 테이블이 질문에 필요하므로 FROM 절에 Instructor I가 나타난다.
(B): SectionInstructor 테이블의 SI.InstructorID 열이 Instructor 테이블의 I.InstructorID와 비교된다.
(C): INNER JOIN은 두 테이블의 데이터를 결합한다.

level 3

A university wants to know the course names of the sections taught by instructors with rank Associate Professor whose salaries are less than the average salary of instructors with rank Assistant Professor.

The query below answers the university's question. Fill in the blanks.

SELECT C.Name
FROM Course C
INNER JOIN Section S ON C.CourseID = S.CourseID
INNER JOIN SectionInstructor SI ON S.SectionID = SI.SectionID
INNER JOIN Instructor I ON SI.InstructorID = I.InstructorID
WHERE I.AcademicRank = 'Associate Professor' AND I.Salary <
    (SELECT ____(A)____
    FROM Instructor
    WHERE ____(B)____ = '____(C)____');

▼View solution

(A) AVG(Salary) (B) AcademicRank (C) Assistant Professor

 

서브쿼리는 조교수(Assistant Professor) 직급을 가진 강사의 평균 급여를 반환한다.

(A): SELECT AVG(Salary)는 Instructor 테이블의 모든 급여의 평균 급여를 찾는다.

(B), (C): WHERE AcademicRank = 'Assistant Professor'는 조교수 직급을 가진 강사만을 지정한다.

level 4

A university wants to know the names of students who have enrolled in at most 3 sections in a single term, where all courses taken have fewer credits than the course with the most credits.

The query below answers the university's question. Fill in the blanks.

SELECT ST.FirstName, ST.LastName, ____(A)____
FROM Student ST
INNER JOIN Enrollment E ON ST.StudentID = E.StudentID
INNER JOIN Section SE ON E.SectionID = SE.SectionID
INNER JOIN Course C ON SE.CourseID = C.CourseID
WHERE C.Credits < 
    (SELECT MAX(Credits)
    FROM Course)
GROUP BY ____(B)____, ST.LastName, SE.Term
____(C)____ COUNT(*) <= 3;

▼View solution

(A) COUNT(*) (B) ST.FirstName (C) HAVING

 

(A): COUNT(*)는 각 그룹에 존재하는 행의 개수를 센다.
(B): GROUP BY ST.FirstName, ST.LastName, SE.Term은 ST.FirstName, ST.LastName, SE.Term 열을 기준으로 그룹을 형성한다.
(C): HAVING COUNT(*) <= 3은 행 개수가 3 이하인 그룹만 선택한다.