【SQL】18.test3练习题及答案（1）

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

select a.* from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score;

1

2、查询平均成绩大于60 分的同学的学号和平均成绩

select sno,avg(score) from sc
group by sno
having avg(score) >60;

2

3、查询所有同学的学号、姓名、选课数、总成绩

解法一

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

解法二

select a.*,s.sname from
(select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno

3

4、查询姓“刘”的老师的个数

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

4

5、查询没学过“谌燕”老师课的同学的学号、姓名

#学过谌燕老师课的同学
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a 
join teacher b on 
a.tno = b.tno
where tname='谌燕') c on 
b.cno = c.cno;
#没学过谌燕老师课的同学
select a.sno,a.sname from student a
where a.sno not in
(select distinct a.sno from sc a 
join course b on a.cno=b.cno
join teacher c on c.tno=b.tno
where tname='谌燕');

5

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join sc c on b.sno=c.sno
where b.cno='c001' and c.cno='c002' and a.sno=c.sno;

6

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名

select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a 
join teacher b on 
a.tno = b.tno
where tname='谌燕') c on 
b.cno = c.cno;

7

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

解法一

select sno,sname from student 
where sno in (select a.sno from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score);

8解法一

解法二

select * from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score

8解法二

9、查询所有课程成绩小于60 分的同学的学号、姓名

select a.sno,a.sname from student a 
join sc b on a.sno=b.sno
join course c on c.cno=b.cno
where b.score <60;

9

10、查询没有学全所有课的同学的学号、姓名

select a.sno,a.sname,count(b.cno) from student a 
left join sc b on a.sno=b.sno 
group by a.sno,a.sname 
having count(b.cno)<(select count(distinct cno) from course);

10

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

解法一

select distinct a.sno,a.sname from student a
join sc b on a.sno=b.sno
where b.cno in
(select cno from sc a
join student b on a.sno=b.sno
where a.sno='s001')
and a.sno<>'s001';

11解法一

解法二

select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'

11解法二

12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名
此题解法我认为与11题相同，但网上解法如下

  select * from sc
left join student st
on st.sno=sc.sno
where sc.sno<>'s001'
and sc.cno in
(select cno from sc
where sno='s001')

12

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩

#替换数据应使用
update sc set score =
#“谌燕”老师教的课的平均成绩
(select avg(score) from sc
join (course a,teacher c) on a.tno=c.tno and sc.cno=a.cno 
and c.tname='谌燕'
group by sc.cno )
#where cno in 
#'谌燕'老师教的课程
(select  cno from course a
join teacher b on a.tno=b.tno
where b.tname='谌燕');

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名

select sno from sc 
where cno in (select cno from sc where sno='s001')
#join sc b on a.cno= b.cno and a.sno='s001'
group by sno 
having count(*)=
(select count(*) from sc where sno='s001');

14

15、删除学习“谌燕”老师课的SC 表记录

#删除数据应使用delete from sc where sc.cno in 
(select  cno from course a
join teacher b on a.tno=b.tno
where b.tname='谌燕');

16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩

#插入数据应使用insert into sc(sno,cno,score)
#平均成绩
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
#没有上过编号“c002”课程的同学学号
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno,max(score),min(score) from sc
group by cno

17

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select cno,avg(score),
sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc
group by cno
order by avg(score) ,及格率 desc;

18

19、查询不同老师所教不同课程平均分从高到低显示

select a.tno,a.tname,b.cname,c.cno,avg(c.score) from teacher a
join (course b,sc c) on a.tno=b.tno and b.cno=c.cno
group by c.cno
order by avg(score) desc

19

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select sc.cno,c.cname,
sum(case  when score between 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case  when score between 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case  when score between 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case  when score <60 then 1 else 0 end) as "[<60]"
from sc, course c
where  sc.cno=c.cno
group by sc.cno ,c.cname;

20

<b> 未解答 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)</b>

select b.cno,a.sno,a.sname,b.score from student a
join sc b on a.sno=b.sno
group by b.cno
order by b.score desc;

22、查询每门课程被选修的学生数

select a.cno,b.cname,count(a.sno) from sc a,course b
where a.cno=b.cno
group by cno;

22

23、查询出只选修了一门课程的全部学生的学号和姓名

select a.sno,a.sname from student a 
join sc b on a.sno=b.sno
group by b.sno 
having count(b.cno) =1;

23

24、查询男生、女生人数

select ssex,count(sno) from student
group by ssex;

24

25、查询姓“张”的学生名单

select * from student
where sname like '张%';

25

26、查询同名同性学生名单，并统计同名人数

select sname,count(*)from student 
group by sname 
having count(*)>1;

26