偶然搜索看到了大佬的训练题,我也跟着大佬思路过了一遍。
大佬链接:点这里
在这个链接里面有表以及数据,下面开始第一题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select stu.*,sc1.s_score as '语文',sc2.s_score as '数学'
from Student stu
left join Score sc1 on stu.s_id =sc1.s_id and sc1.c_id ='01'
left join Score sc2 on stu.s_id =sc2.s_id and sc2.c_id ='02'
where sc1.s_score >sc2.s_score ;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select stu.*,sc1.s_score as '语文',sc2.s_score as '数学'
from Student stu
left join Score sc1 on stu.s_id = sc1.s_id and sc1.c_id = '01'
left join Score sc2 on stu.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score < sc2.s_score ;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select stu.s_id,stu.s_name,round(avg(sc.s_score),2)
from Student stu
left join Score sc on stu.s_id = sc.s_id
group by stu.s_id having AVG(sc.s_score) >=60
这儿的round函数是控制小数的,比如说小数后两位,则写2。
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select stu.s_id,stu.s_name,round(avg(sc.s_score),2)
from Student stu
left join Score sc on stu.s_id = sc.s_id
group by stu.s_id having AVG(sc.s_score)<60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select stu.s_id ,stu.s_name ,count(sc.c_id) '课程总数', sum(sc.s_score) '总分'
from Student stu
left join Score sc on stu.s_id = sc.s_id
group by stu.s_id ;
6、查询"李"姓老师的数量
select count(*) as '李数量' from Teacher t where t_name like '李%';
7、查询学过"张三"老师授课的同学的信息
select stu.* from Student stu
left join Score sc on stu.s_id = sc.s_id
left join Course c on sc.c_id = c.c_id
left join Teacher t on c.t_id = t.t_id
where t.t_name = '张三';
就把所有表连在一起
8、查询没学过"张三"老师授课的同学的信息
select stu.* from Student stu where stu.s_id not in(
select sc.s_id from Score sc where sc.c_id in (
select c.c_id from Course c join Teacher t on c.t_id = t.t_id
where t.t_name ='张三' ));
三步:第一步,找到张三教授的课程号。第二步,找到选了这些课程号的学生编号。第三步,反选。
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select stu1.* from Student stu1
inner join Score sc1 on stu1.s_id = sc1.s_id
inner join Course c1 on sc1.c_id = c1.c_id and c1.c_id = '01'
where stu1.s_id in (
select stu2.s_id from Student stu2
inner join Score sc2 on stu2.s_id = sc2.s_id
inner join Course c2 on sc2.c_id = c2.c_id and c2.c_id = '02'
);
查询02课程的学号,通过where查看到两门课程都有学的学生的学号
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select stu1.* from Student stu1
inner join Score sc1 on stu1.s_id = sc1.s_id
inner join Course c1 on sc1.c_id = c1.c_id and c1.c_id = '01'
where stu1.s_id not in (
select stu2.s_id from Student stu2
inner join Score sc2 on stu2.s_id = sc2.s_id
inner join Course c2 on sc2.c_id = c2.c_id and c2.c_id = '02'
);
11、查询没有学全所有课程的同学的信息
select stu.* from Student stu
left join Score sc on stu.s_id = sc.s_id
group by stu.s_id
having count(sc.c_id)<(select count(c.c_id) from Course c);
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct stu.* from Student stu
join Score sc on stu.s_id = sc.s_id
where sc.c_id in (
select sc2.c_id from Student stu2 join Score sc2 on
stu2.s_id = sc2.s_id where sc2.s_id = '01');
首先查找到01同学所有所学的课程,而后在这里面查找课程号
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select distinct stu.* from Student stu
join Score sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(sc.c_id) = (
select group_concat(sc2.c_id) from Score sc2 group by sc2.s_id
having sc2.s_id='01');
这里使用到了group_concat函数,如下sql所示,我们希望按照s_id分组,并且分组后将c_id打印
select group_concat(c_id) from Score group by s_id;
如图所示:
这是一个比较有趣且实用的函数,是我个人首次认识。
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select stu.s_name,stu.s_id from Student stu
where stu.s_id not in (
select s_id from Score sc
join Course c on sc.c_id = c.c_id
join Teacher t on c.t_id = t.t_id
where t.t_name = '张三'
);
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select stu.s_id,stu.s_name,round(AVG(sc.s_score),2)
from Student stu
left join Score sc on stu.s_id = sc.s_id
where stu.s_id in(
select sc2.s_id from Score sc2
where sc2.s_score < 60
group by sc2.s_id having count(s_id)>=2
) group by stu.s_id
16、检索"01"课程分数小于60,按分数降序排列的学生信息
select stu.*,sc.s_score,sc.c_id from Student stu
join Score sc on stu.s_id = sc.s_id
where sc.c_id = '01' and sc.s_score<60
order by sc.s_score
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.s_id,stu.s_name,sc1.s_score as "语文",sc2.s_score as "数学",sc3.s_score as "英语",round(AVG(sc.s_score),2) as "平均成绩"
from Student stu
left join Score sc on stu.s_id = sc.s_id
left join Score sc1 on stu.s_id = sc1.s_id and sc1.c_id = '01'
left join Score sc2 on stu.s_id = sc2.s_id and sc2.c_id = '02'
left join Score sc3 on stu.s_id = sc3.s_id and sc3.c_id = '03'
group by stu.s_id
order by AVG(sc.s_score) desc