MySQL Lesson 7: SQL面试50-Part1

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT t1.S_id,t1.Score,t2.Score FROM     
 (SELECT S_id,S_name,S_age,S_sex,C_id,C_name,Score FROM Total WHERE C_id= '01') t1  
JOIN 
 (SELECT S_id,S_name,S_age,S_sex,C_id,C_name,Score FROM Total WHERE C_id= '02') t2 
ON t1.S_id=t2.S_id  
WHERE t1.Score>t2.Score  

注：1)只需要学生id，“01”和“02”两门课程的成绩

        2)C_id='01'，这里必须是'01'，不能是01

2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT t1.S_id,Score1,Score2  FROM  
 (SELECT S_id,Score as Score1 FROM Total WHERE C_id='01') t1    
JOIN
 (SELECT S_id,Score as Score2 FROM Total WHERE C_id='02') t2 
ON t1.S_id=t2.S_id 
WHERE Score1<Score2

3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 

SELECT S_id,S_name,avg(Score) 
FROM Total
GROUP BY S_id 
HAVING avg(Score)>60  

这里考到一个统计函数：avg（）求平均

4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT Student.S_id,S_name,avg(Score) as avg
FROM Student LEFT JOIN Score ON Student.S_id = Score.S_id 
GROUP BY Student.S_id 
HAVING avg<60 

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT S_id,S_name,count(C_id),sum(Score)
FROM Total
GROUP BY S_id  

思路：1）选课总数用count(C_id)表示

           2）所有课程的总成绩用sum(Score)表示

           3）最后用GROUP BY 分组

6.查询"李"姓老师的数量

SELECT count(*) 
FROM Teacher 
WHERE T_name LIKE '李%' 

知识点：LIKE '李%' 可以匹配到所有姓李的老师，包括名字只有一个“李”字的人；

              如果要选出姓李并且名字只有一个字的老师，可以用 LIKE '李_'

7.查询学过"张三"老师授课的同学的信息

SELECT S_id,S_name,S_age,S_sex  
FROM Total
WHERE T_name ='张三' 

8.查询没学过"张三"老师授课的同学的信息

SELECT * FROM Student WHERE S_id NOT IN 
 (SELECT DISTINCT S_id FROM Total WHERE T_name='张三')     

知识点：col NOT IN () 表示列不在集合中 

错误写法：

SELECT S_id,S_name,S_age,S_sex
FROM Total
WHERE T_name not in ('张三')      

错误原因：

      有同学选了不止一门课，于是一个同学对应着多行。这么写只是把选了张三的课的行删除，选了别的老师的行依旧还在，于是该同学的信息仍会输出，但是该同学是选择了张三老师的，与题意不符。

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

自己的思路：1）先找出学过编号为“01”的学生

                      2）再找出学过编号为“02”的学生

                      3）用 t1 INNER JOIN t2 ON t1.S_id = t2.S_id 连接，只保留 S_id 相等的 row ，即                                既学过“01”又学过“02”

SELECT t1.S_id,t1.S_name,t1.S_age,t1.S_sex FROM         
 (SELECT * FROM Total WHERE C_id='01') t1
  INNER JOIN 
 (SELECT * FROM Total WHERE C_id='02') t2
  ON t1.S_id = t2.S_id

思路二：1）先找出既学过 “01” 也学过 “02” 的学生的 id

               2）根据 id 找出学生的信息

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

整个 sql 语句就是一个 SELECT xx FROM xx WHERE col IN (子查询1) AND col IN(子查询2) 的形式 .