【SQL】16.test2练习题及答案

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select a.*, b.score, c.score
from student a, sc b,sc c
where a.sid = b.sid and a.sid = c.sid
and b.cid ='01' and c.cid = '02'
and b.score > c.score;

1

2.查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩

select a.sid,b.sname,avg(a.score) from sc a
join student b on a.sid=b.sid
group by sid
having avg(a.score) >= 60;

2

3.查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩(包含无成绩的)

select a.sid,a.sname,avg(b.score) from student a
left join sc b on a.sid=b.sid
group by sid
having avg(b.score) <=60;

3

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.sid,b.sname,count(a.cid),sum(a.score) from sc a
join student b on a.sid=b.sid
group by sid;

4

5.查询"李"姓老师的数量

select count(tname) from teacher
where tname like '李%';

5

6.统计课程01分最高的前三名

select a.sid,b.sname,a.score from sc a
join student b on a.sid=b.sid
where cid='01'
order by score desc
limit 3;

6

7.统计课程分最高的前三名,不分课程(分数的前三，人数可能大于三)

解法一

select a.sname, a.score, a.cid from 
(select Student.sname, sc.score, sc.cid from Student 
join SC on Student.Sid = SC.Sid) as a 
join (select distinct sc.score from SC
order by score  desc limit 3) as b
on a.score = b.score;

解法二

select a.sname,b.score,b.cid from student a
join sc b on a.sid=b.sid 
join (select distinct sc.score from SC
order by score  desc limit 3) as c
on b.score = c.score;

7

8.统计分数出现次数前三的分数

select score, count(*) from SC
group by score
order by count(*) desc limit 3; 

8

9.统计每个学生选课总数

select a.sid,b.sname,count(a.cid) from sc a
join student b on a.sid=b.sid
group by a.sid

9

10.每个老师教多少学生（一个教师给某个学生教两门课，计入两次）

select c.tid,c.tname,count(a.cid) from course a
join sc b on a.cid=b.cid
right join teacher c on a.tid=c.tid
group by tid
order by tid;

10