Mysql必练50题（第三天）

第十一题
查询没有学全所有课程的同学的信息

select s.* from student s left join score sc on s.s_id = sc.s_id
group by s_id having count(*)<3;

第十二题
查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct s.* from student s inner join score sc
on s.s_id = sc.s_id where c_id in
(select c_id from score where s_id =1)

第十三题
查询和"01"号的同学学习的课程完全相同的其他同学的信息

select * from student where s_id in (
select s_id from score s inner join 
(select c_id from score where s_id = 1) t1
on s.c_id = t1.c_id
where s_id != 1 
group by s_id having count(*) = (
select count(*) from score where s_id = 1)
);

第十四题
查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name from student  where s_id not in (select sc.s_id from score sc 
inner join course c 
on sc.c_id = c.c_id
inner join teacher t 
on c.t_id = t.t_id where t.t_name = "张三");

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

select s.s_id,s.s_name,round(avg(sc1.s_score),2) as avg_score
from student s inner join (select * from score sc where s_score<60) sc1
on s.s_id = sc1.s_id group by s.s_id having count(*)>=2;