MySQL Lesson 8: SQL面试50-Part2

1.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 

SELECT * FROM Student WHERE 
  S_id IN (SELECT S_id FROM Score WHERE C_id='01')
  AND
  S_id NOT IN (SELECT S_id FROM Score WHERE C_id='02')   

2.查询没有学全所有课程的同学的信息

思路：1）子查询找出没有学全所有课程（count(C_id)<3）的同学

           2）利用S_id找出同学的所有信息

SELECT * FROM Student WHERE S_id IN  
   (SELECT S_id FROM Total GROUP BY S_id HAVING count(C_id)<3)  

也可不用子查询：

SELECT S_id,S_name,S_age,S_sex  
FROM Total
GROUP BY S_id 
HAVING count(C_id)<3  

3.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

思路：1）先找出 “01” 同学学过的 C_id

           2）再找出学过任一门的 S_id

           3）再根据 S_id 用子查询在 Student 找学生信息

SELECT * FROM Student WHERE S_id IN
(S