MySql教程

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题(第三天)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!