SQL作业参考答案

1、设有以下三个关系：

学生关系：student（sno，sname，dep，age，sex）

选课关系：sc（sno，cno，grade）

课程关系：course（cno，cname，score）

1）建数据库，在数据库中建立以上三个关系。

要求：学生关系中：sno为主键，sname不允许为空，age默认值18，sex取值范围’男’、’女’、默认为’男’。

课程关系中：cno为主键，cname不为空，在cname上建立唯一索引。

选课关系中：sno、cno组合为主键，grade允许为空，sno为外键（参照学生关系的sno，级联删除、级联修改），cno为外键（参照课程关系的cno，限制删除、限制修改）。

2）试用SQL的查询语句表示下列查询。

(1)检索学分score等于4的所有课程的课程号和课程名。

select cno,cname

from course

where score=4;

(2)检索年龄在20和23岁之间的学生的学号与姓名。

select sno,sname

from student

where age between 20 and 23 ;

(3)检索Wang同学不学的课程的课程号。

select cno

from course

where cno not in

(select cno

from sc,student

where sc.sno =student.sno

and student.sname='Wang');

(4)检索所有姓李的学生情况。

select *

from student

where sname like '李%';

(5)检索所有学生情况及其选课情况。（可以用f1键查看左外联接）

select student.*,sc.*

from student left outer join sc

on student.sno = sc.sno;

(6)检索所有选修了课程的学生情况和选课情况。

select student.*,sc.*

from student , sc

where student.sno = sc.sno;

(7)检索至少选修两门课程的学生学号和姓名。只能用嵌套查询！

select sno,sname

from student

where sno in

(select sno

from sc

group by sno

having count(*)>=2);

注意：如果用如下的联接查询实现，会出现错误提示

列'student.sname' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUP BY 子句中。

select student.sno,sname

from student,sc

where student.sno= sc.sno

group by student.sno

having count(*)>=2;

(8)将所有成绩为空值的学生成绩置为0。

update sc

set grade=0

where grade is null;

注意：判断是否为null 时不能用=null

(9)删除课程名为“DB”的课程和所有成绩的元组。

delete

from sc

where cno =

(select cno

from course

where cname='DB');

delete

from course

where cname='DB';

(10)平均成绩大于60分的课程的课程号和平均成绩存入关系G1（C#，A VG_G）中。

表G1不存在：

select cno as c#,avg(grade) as A VG_G

into G1

from sc

group by cno

having avg(grade)>60;

表G1存在：

insert into G1

select cno,avg(grade)

from sc

group by cno

having avg(grade)>60;

(11)把课程名为“OS”的课程的学生成绩提高5%。

update sc

set grade=grade*(1+0.05)

where cno=

(select cno

from course

where cname='OS');

(12)统计有学生选修的课程门数。

select count(distinct cno) as 已选修课程数

from sc;

(13)查询选修了课程名为C2课程的学生的平均年龄。

select avg(sage) as 平均年龄

from student ,sc,course

where student.sno=sc.sno

and https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o

and cname='C2';

或：

select avg(sage) as 平均年龄

from student

where sno in (select sno

from sc,course

where https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o

and cname='C2');

或：

select avg(sage) as 平均年龄

from student

where sno in (select sno

from sc

where cno in (select cno from course where cname='C2') );

14）查询选修人数超过15的课程的课程名称（如果为课程号）和学生人数。查询结果按人数降序排列，若人数相同，按课程名称（如果为课程号）升序排列。

如果是课程号就是下面的答案

select cno ,count(distinct sno) as 选修人数

from sc

group by cno

having count(distinct sno)>=15

order by 2 desc,1 asc;

但是如果是课程名，就会出错，需要修改：

select https://www.360docs.net/doc/bf11261002.html,ame ,count(sno) as 选修人数

from course,sc

where https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o

group by https://www.360docs.net/doc/bf11261002.html,ame

having count(sno)>=15

order by 2 desc,1 asc;

原题解2：

select cno, count(sno) as num

into G2

from sc

group by cno

having count(sno)>15

select cname,num

from course,G2

where https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o

order by 2 desc,1 asc

原题解3：

select cname,num

from course,(select cno, num=count(sno) from sc group by cno having count(sno)>15) a where https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o

order by 2 desc,1 asc

15）检索成绩为空值的学生姓名和课程名。

select sname, cname

from student ,sc,course

where student.sno=sc.sno and https://www.360docs.net/doc/bf11261002.html,o=https://www.360docs.net/doc/bf11261002.html,o and grade is null;

16）关系student中插入一个学生元组（02201101，“李平”，“CS”），AGE、SEX分别以默认值插入。

insert

into student

values('02201101','李平','CS',default,default);

17）为“CS”系创建一个视图：CS_S（sno，sname，cname，grade）。

create view cs_s

as

select student.sno,sname,cname,grade

from student,sc,course

where student.sno=sc.sno

and sc.sno=https://www.360docs.net/doc/bf11261002.html,o

and student.dep=’CS’

18）查询课程号为“1001”课程考试成绩排名为5-7名的学生的学号和成绩。

select top 3 sno, grade

from sc

where (cno = '1001') and (sno not in

(select top 4 sno

from sc

where cno = '1001'

order by grade desc))

order by grade desc

注：在SQL Server 2000中，支持UNION运算，但不支持EXCEPT、INTERSECT运算；

在SQL Server 2005中，开始支持EXCEPT、INTERSECT运算；

另一个比较符合思考习惯的答案：

select sno,grade

from sc

where cno = '1001' and sno not in

(select top 4 sno

from sc

where cno = '1001'

order by grade desc)and sno in

(select top 7 sno

from sc

where cno = '1001'

order by grade desc);

请思考使用EXCEPT如何实现？