[데이터베이스] 4. MySQL 중첩질의문

중첩 질의문

SELECT문의 FROM, WHRER 절에 부속 질의문이 있는 경우를 중첩 질의문이라 한다. 예를들어 평균학점 이상을 받은 학생들의 이름을 구하는 질의는 아래와 같다.

SELECT S.name

FROM student S

WHERE S.gpa > (SELECT avg(S2.gpa) FROM student S2);

 

집합 비교 연산자(IN, NOT IN, ALL, ANY)

• 원소 IN 집합: 집합에 원소가 있을 경우 참이다.
• 원소 NOT IN 집합: 집합에 원소가 없을 경우 참이다.
• 원소* < ALL  집합: 집합의 모든 원소가 원소*보다 클 경우 참이다.
• 원소* < ANY 집합: 집합의 원소 중에 원소*보다 큰게 하나라도 있을 경우 참이다.

하나 이상의 통계 과목을 수강한학생의 이름을 구하라.
SELECT S.name
FROM student S, course_taken CT
WHERE CT.cid IN ('Sss111', 'ss311', 'ss312', 'ss321') AND CT.sid=S.id;

 

집합 비교 연산자를 사용한 중첩 질의문 예시

전산 전공이 아닌 교수들이 가르치는 과목의 이름을 구하시오

SELECT name

FROM course

WHERE instructor NOT IN (SELECT pid FROM instructor WHERE dept='cs');

학번 980397 학생이 수강했던 각 모든 과목의 학점보다 평점이 더 높은 학생의 이름을 구하시오. 

SELECT name

FROM student

WHERE gpa > ALL (SELECT grade FROM course_taken WHERE sid='950564');

 

 

EXISTS

• EXISTS 집합: 집합에 원소가 하나라도 존재할 경우 참이다.
• NOT EXISTS 집합: 집합에 원소가 없을 경우 참이다.

선수과목이 있는 과목의 이름을 구하시오

SELECT C.name

FROM course C

WHERE EXISTS (SELECT * FROM course C1 WHERE C.prerequisite=C1.id);

모든 전산학과목을 수강한 학생들의 학번과 이름을 구하시오(학생이 수강하지 않은 전산학 과목이 없을 경우 학생이름 반환)

SELECT S.id, S.name
FROM student S
WHERE NOT EXISTS(SELECT C.id FROM course C
				WHERE C.id LIKE 'cs%' 
                	and NOT EXISTS(SELECT * FROM course_taken CT
                					WHERE C.id=CT.cid and CT.sid=s.id));
                                    
-- 다른 SQL
SELECT S.id, S.name
FROM Students S
WHERE NOT EXISTS(
	(SELECT C.id FROM Course C WHERE C.id LIKE 'CS%')
    EXCEPT
    (SELECT CT.cid FROM Course_Taken CT WHERE CT.sid=S.id));

모든 mod_cook 유형의 책을 출판한 출팔사의 이름을 구하시오

SELECT P.pub_name

FROM publishers P

WHERE NOT EXISTS (SELECT * FROM titles T 
					WHERE T.type='mod_cook' 
                    	AND NOT EXISTS(SELECT * FROM titles T2
                        				WHERE T.title_id=T2.title_id and P.pub_id=T2.pub_id));

 

UNIQUE 집합: 집합에 중복된 원소가 없을때 참이다.

재수강 이력이 없는 학생의 학번을 구하라.

SELECT S.id

FROM student S

WHERE UNIQUE (SELECT CT.cid FROM course_taken CT WHERE CT.sid=S.id);

 

FROM 절의 중첩 질의문

학생들의 수강내역으로부터 학생 별 수강과목 수, 수강 과목의 학점 평균을 구하시오.

SELECT S.name, T.NO_course, T.avg_grade

FROM student S, (SELECT sid, COUNT(*) AS NO_course, AVG(grade) AS avg_grade
					FROM course_taken GROUP BY sid) T
                    
WHERE S.id=T.sid

 

SELECT 절의 충접 질의문

학생들의 수강내역으로부터 학생 별 수강과목 수, 수강 과목의 학점 평균을 구하시오.

SELECT S.name, T.NO_course, SELECT avg(CT.grade) FROM course_taken CT WHERE CT.sid=S.id) AS avg_grade

FROM student S, (SELECT sid, COUNT(*) AS NO_course, AVG(grade) AS avg_grade
					FROM course_taken GROUP BY sid) T
                    
WHERE S.id=T.sid