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

背景

MySQL经典练习题及答案，常用SQL语句练习50题

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

先查询出01同学学过的课程id，然后判断下学过这几个id课程的同学

select distinct stu.*
from student stu
         left join score sc
                   on stu.s_id = sc.s_id
where sc.c_id in (select c_id from score where s_id = '01');

查询和”01”号的同学学习的课程完全相同的其他同学的信息

• Step1. 查询出01同学学过的课程

select c_id from score where s_id = '01'

• Step2. 假设所有同学都学过这几个课程，做一个连接，然后判断是否真的学过对应的课程，这里应该用full join，但是MySQL没有这个，所以用union和left join与right join结合。

select t.*,sc.c_id cid2
from (select *
      from student
         , (select c_id from score where s_id = '01') t) t
         left join score sc
                   on t.s_id = sc.s_id
                       and t.c_id = sc.c_id
union
select t.*,sc.c_id cid2
from (select *
      from student
         , (select c_id from score where s_id = '01') t) t
         right join score sc
                   on t.s_id = sc.s_id
                       and t.c_id = sc.c_id

• 从上述结果中判断cid2中没有空值的学生即为结果
  tips:可以先将之前的表保存为一个临时表或者视图，我这里就直接写一起了。最后记得剔除01的学生，题目并不要求查询01.

select *
from student
where s_id not in 
  (select s_id
   from (select t.*, sc.c_id cid2
         from (select *
               from student
                  , (select c_id from score where s_id = '01') t) t
                  left join score sc
                            on t.s_id = sc.s_id
                                and t.c_id = sc.c_id
         union
         select t.*, sc.c_id cid2
         from (select *
               from student
                  , (select c_id from score where s_id = '01') t) t
                  right join score sc
                             on t.s_id = sc.s_id
                                 and t.c_id = sc.c_id) tt
   where cid2 is null
   group by s_id)
and s_id != '01'

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select stu.s_id,stu.s_name, ifnull(avg(score.s_score),0)
    from student stu left join score
on  stu.s_id = score.s_id
    group by stu.s_id
having sum(case when score.s_score>=60  then 0 else 1 end) >=2