SQL sever试题

最新学习了几道有难度SQL sever的查询:

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

select s.sno,s.sname

from student s

inner join sc c on s.sno=c.sno

inner join course o on c.cno=o.cno

inner join teacher t on t.tno=o.tno

where t.tname='谌燕'

group by s.sno,s.sname

having count(c.sno)=(select count(*) from course o inner join teacher t on o.tno=t.tno where t.tname='谌燕')

因为要查询'谌燕'老师所教的所有课的;所以先连表查出'谌燕'老师过得学生,然后再用条数对比'谌燕'老师有多少门课程.

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

查询:

select c1.sno,c1.cno,s2.score

from sc c1 INNER JOIN (

select c.cno,avg(c.score)score 

from sc c

inner join course o on c.cno=o.cno

inner join teacher t on o.tno=t.tno

where t.tname='谌燕'

group by c.cno) s2

on s2.cno=c1.cno

用子查询出平均值,再根据课程id来找到对应的值。

效果:

修改:

update sc set sc.score=s2.score

from sc c1

INNER JOIN (

select c.cno,avg(c.score)score 

from sc c inner join course o on c.cno=o.cno

inner join teacher t on o.tno=t.tno

where t.tname='谌燕'

group by c.cno) s2

on s2.cno=c1.cno

基本和上面查询一样。

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

select c.sno,s.sname

from sc c

inner join student s on c.sno= s.sno

where c.cno in (

select c1.cno

from sc c1 inner join student s1 on c1.sno= s1.sno

where c1.sno='s001')

and c.sno!='s001'

group by c.sno,s.sname

having count(c.cno) >= (select count(*) from sc where sno='s001')

查询出学过“s001”号的同学任意一门课程的所有同学，再通过条数对比可没有等于或者比“s001”号的同学学的多课程的同学。