已知有如下4张表：
学生表：student(学号,学生姓名,出生年月,性别)
成绩表：score(学号,课程号,成绩)
课程表：course(课程号,课程名称,教师号)
教师表：teacher(教师号,教师姓名)

根据以上信息按照下面要求写出对应的SQL语句。
ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。下图是我画的这4张表的关系图，可以看出它们之间是通过哪些外键关联起来的。

1. 查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重要）

-- 1. 查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重要）
select * from score where c_id ='01'  -- 找出课程01成绩的所有学生
select * from score where c_id ='02'  -- 找出课程02成绩的所有学生
-- 构建出两个课程的查询，使用子查询的方式
-- --------------------------------------------------------------
select a.* from 
(select * from score where c_id ='01')a  -- 找出课程01成绩的所有学生
inner join
(select * from score where c_id ='02')b  -- 找出课程02成绩的所有学生
on  a.s_id = b.s_id
where a.s_score>b.s_score

运行结果：

2.查询平均成绩大于60分的学生学号

SELECT s_id,avg(s_score) as avg_score FROM score
GROUP BY s_id having avg_score>60
-- group by 后面跟having，不能跟where，如果where和分组一起的话where要在分组后面，不然会报错。

3.查询所有学生的学号、姓名、选课数、总成绩

-- 03题 | 查询所有学生的学号、姓名、选课数、总成绩
-- 做统计count、sum，一般需要使用group by，select字段一般放分组的字段，即groupby分组的字段
-- 使用的表 student\score   
select a.s_id,a.s_name,count(b.c_id), 
-- sum(case when b.s_score is null then 0 else b.s_score end)
sum(ifnull(b.s_score,0)) -- ifnull写法
from student as a 
left join score as b  -- 使用左连接
on a.s_id=b.s_id            
group by a.s_id,a.s_name

4.查询姓猴的老师的个数

-- 04 | 查询姓猴的老师的个数
select count(t_id)
from  teacher
where t_nme like '猴%' -- 姓猴的老师
-- where t_nme like '%' 主要考察like的用法 ；如果题意是求有多少个姓那就用distinct

5.(重要)查询没学过张三老师课的学生的学号和姓名

基本思路 查询出学过张三课的数据，然后使not in排除掉

-- 05 | 查询没学过张三老师课的学生的学号和姓名（重要）
-- 基本思路 查询出学过张三课的数据，然后使not in排除掉
select s_id,s_name from student 
where s_id not in 
( 
select s_id from score as s -- 成绩表
inner join course as c 
on s.c_id=c.c_id
inner join teacher as t     
on c.t_id=t.t_id
where t.t_name='张三' ) -- 到这一步就已经把学过张三的课的数据拿到了

6.(重要)查询学过张三老师课的所有学生的学号和姓名

select  st.s_id,st.s_name,c.*,t.t_name
from
student as st 
inner join score as s on s.s_id=st.s_id
inner join course as c on s.c_id = c.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name='张三'
ORder by s_id

思路，使用层层表关联获得结果图上的表结构，实际场景中还是需要使用临时表的方式。

7.(重要)查询学过编号为01和02的课程的学生名字、学号

-- 查询学过编号为01的课程的学生名字、学号（重点）

-- select * from score  where c_id='01'or c_id='02';
-- 先分别求出两个学号的学生，再求交集即可
select s_id,s_name from student
where s_id in (
SELECT a.s_id from (
select s_id from score where c_id='01')a
INNER JOIN(
select s_id from score where c_id='02')b
on a.s_id=b.s_id
)

8.查询课程编号为02的总成绩总成绩

  SELECT c_id,sum(s_score),avg(s_score),max(s_score) from score  
GROUP BY c_id having c_id='02'

9.查询所有课程成绩小于60分的学生学号和姓名

-- 查询所有课程成绩小于60分的学生学号和姓名
-- 1. 得出同学课程小于60分的课程数
-- 2. 统计同学总共学了几门课

select a.s_id,t.*  from 
(select s_id,COUNT(c_id)cnt from score where s_score<60
GROUP BY s_id)a
INNER    join
(select s_id,count(c_id)cnt from score GROUP BY s_id)b
on a.s_id=b.s_id
INNER JOIN student t on t.s_id=b.s_id
where a.cnt = b.cnt

10.(重点)查询没学全所有课的学生的学号、姓名

-- 10.(重点)查询没学全所有课的学生的学号、姓名
-- 思路count课程id，小于所有课程总数的学生


SELECT st.s_id,st.s_name

from student st

LEFT join score sc on st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(DISTINCT sc.c_id)<(select count(DISTINCT c_id)from course)

11.(重点)查询至少有一门课与学号01的学生所学课程相同的学生的学号和姓名

select * from student where s_id in (
SELECT DISTINCT s_id from score
where c_id in (
SELECT c_id from score 
where s_id='01')
and s_id!='01'
)

select * from student a  INNER join  (
SELECT DISTINCT s_id from score
where c_id in (
SELECT c_id from score 
where s_id='01')
and s_id!='01'
)b on a.s_id=b.s_id

方法一是用的in方法，方法二用的inner join方法，数据量大的时候使用inner join更为高效方便。

12.(重点)查询和01号同学所学课程完全相同的其他同学的学号

SELECT c_id from score where s_id='01' -- 先查出01同学学了什么课

SELECT DISTINCT s_id from score where c_id not in ( -- not in 选出不在01同学学过的课的同学
SELECT c_id from score where s_id='01'  
)

SELECT s_id from score 
where s_id !='01'
GROUP BY s_id having  COUNT(DISTINCT c_id)=(SELECT count(DISTINCT c_id) from score where 
s_id='01') -- 查询出学过课程数量和01同学一样的同学

-- 最后整合
SELECT
    * 
FROM
    student 
WHERE
    s_id IN (
    SELECT
        s_id 
    FROM
        score 
    WHERE
        s_id != '01' 
    GROUP BY
        s_id 
    HAVING
        COUNT( DISTINCT c_id ) = ( SELECT count( DISTINCT c_id ) FROM score WHERE s_id = '01' ) 
    ) -- 查询出学过课程数和01同学一样的同学

    AND s_id NOT IN ( SELECT DISTINCT s_id FROM score WHERE c_id NOT IN ( -- not in 选出不在01同学学过的课的同学
            SELECT c_id FROM score WHERE s_id = '01' -- 先查出01同学学了什么课
        ) )

思路

1. 首先查询出01同学学过什么课
   1. SELECT c_id FROM score WHERE s_id = ‘01’
2. 筛选出所学课和01同学不一样的同学，使用not in排除
3. 然后再判断其他同学选课数目是否和01同学一样

   SELECT s_id from score 
   where s_id !='01'
   GROUP BY s_id having  COUNT(DISTINCT c_id)=(SELECT count(DISTINCT c_id) from score where 
   s_id='01')
   

   

   15.(重点)查询两门及以上不及格课程的同学的学号姓名及其平均成绩

   SELECT
    s_id 
   FROM
    score 
   WHERE
    s_score < 60 
   GROUP BY
    s_id HAVING COUNT(DISTINCT c_id)>=2
   

   SELECT 
   a.s_id,a.s_name
   from student a INNER JOIN score b
   on a.s_id=b.s_id
   

   SELECT
    a.s_id,
    a.s_name,
    AVG( s_score ) 
   FROM
    student a
    INNER JOIN score b ON a.s_id = b.s_id 
   WHERE
    a.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT( DISTINCT c_id ) >= 2 ) 
   GROUP BY
    s_id,
    s_name