SQL：检索没有选修某课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列

一、库中有4个表

1、s表中有s#（学号）、sn（学生姓名）、age（学生年龄）、dept（所在系）

2、c表中有c#（课程号）、cn（课程名）

3、sc表中有s#（学号）、c#（课程号）、gr（成绩）

4、t表中有t#（教师号）、tn（教师姓名）、sal（工资）、comm（职务津贴）、c#（课程号）

二、题目解答

作业题目1：用SQL的DDL语言创建s表，s#为主码，sn不能为空

create table s
( s# char(10) constraint s_prim primary key,
  sn nvarchar(10) not null,
  age int,
  dept nvarchar(20)
)

作业题目2：创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号、和任课老师号组成

create view dept_s(s#,sn,c#,t#)
as select s.s#,sn,sc.c#,t#
from s,sc,t
where s.s#=sc.s# and sc.c#=t.c#

作业题目3：检索计算机系年龄在20岁以上的学生学号

select s#
from s
where dept='计算机' and age>20

作业题目4：检索姓王的教师所讲课程的课程号及课程名称

select c.c#,cn
from c,t
where c.c#=t.c# and tn='王*'

作业题目5:检索张三同学所学课程的成绩，列出sn，c#和gr

select s.sn,c#,gr
from s,sc
where sn='张三' and s.s#=sc.s#

作业题目6:检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩

select sn,sc.c#,gr
from s,sc,t
where t.c#=sc.c# and sc.s#=s.s# and (sal+comm)>1000

作业题目7:检索没有选修c1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列

select sn,avg(gr) as '平均分'
from sc,s
where ( sc.s# in(select s#
from sc
where 'c1' not in(select c# from sc where s.s#=sc.s#)
group by s# having count(c#)=2 
 )  and s.s#=sc.s# )
group by s.sn
order by round(avg(gr),2) desc

作业题目8：检索选修和张三同学所选课程中任意一门相同的学生姓名、课程名

select distinct s.sn,cn
from s,sc,c
where s.s# in
(
select s# from sc
where c# in
(
select  c# from sc,s 
where sn = '张三'
) and sn!='张三' and s.s#=sc.s# and c.c#=sc.c#
)

作业题目9：s1同学选修了c3，将此信息插入sc表中

insert into sc(s#,c#)
values('s1','c3')

作业题目10：删除s表中没有选修任何课程的学生记录

delete
from s
where s# != all(select s# from sc)