SQL面试50题-12

SQL面试50题
12.--查询和"01"号同学所学课程完全相同的其他同学的学号(重点)
1.‘01’号同学所学课程
SELECT c_id FROM Score WHERE s_id = '01'
2.'01'同学所学课程数
SELECT s_id, COUNT(c_id) FROM Score GROUP BY s_id HAVING s_id = '01'
3.没学过‘01’号同学所学课程的其他同学
SELECT s_id FROM Score WHERE c_id NOT IN (SELECT c_id FROM Score WHERE s_id = '01')
4.至少学过一门‘01’号同学所学课程的其他同学
SELECT s_id FROM Score WHERE c_id IN (SELECT c_id FROM Score WHERE s_id = '01')
本题满足：1️⃣学过01同学所学的课程2️⃣不存在01同学没有学过的课程3️⃣课程数和01同学相同
SELECT s_name,s_id FROM Student WHERE s_id IN
(SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM Score GROUP BY s_id HAVING s_id = '01'))
AND s_id IN
(SELECT s_id FROM Score WHERE c_id IN
(SELECT c_id FROM Score WHERE s_id = '01') )
AND s_id NOT IN
(SELECT s_id FROM Score WHERE c_id NOT IN
(SELECT c_id FROM Score WHERE s_id = '01'))
AND s_id <> '01';