Mysql必练50题（第三天）

本文主要是介绍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;

这篇关于Mysql必练50题（第三天）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！