数据库系统概论(王珊第五版)实验6答案

实验6答案

1.查询名字中第2个字为‘向’的学生姓名和学号及选修的课程号、课程名；select a.sname,a.sno,o,ame from student a,course b,sc c

Where a.sname like'_向%' and a.sno=c.sno and o=o

2.列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

Select a.sno,sname,sdept,o,grade from student a,course b,sc c Where ame in('数学','大学英语')and a.sno=c.sno and o=o

3.查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

Select * from student

Where sage<>(select sage from student where sname='张力')

4.按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

Select a.sno,sname,sdept,sum(ccredit) as 已修学分

from student a,course b,sc c

Where grade>60 and a.sno=c.sno and o=o

Group by a.sno,sname,sdept

5.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；Select a.sno,a.sname,o from student a,sc c

Where Cno in(select Cno from sc where sno in(select sno from student where sname='张力'))

and a.sno=c.sno

6.查询只被一名学生选修的课程的课程号、课程名；

Select cno,cname from course

Where cno in(select cno from sc group by cno having COUNT(*)=1)

7.使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

Select sno,sname from student

Where sno in(select sno from sc where Cno=(select Cno from course where cname='数据结构'))

8.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

Select sname,sage,sdept from student

Where sage

And sdept<>'cs'

9.使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；Select sname from student

Where sage'wm'

10.分别使用连接查询和嵌套查询，列出与‘张力’在一个院系的学生的信息；Select b.*from student a,student b

where a.sname='张力' and a.sdept=b.sdept

或

SELECT * FROM Student

WHERE Sdept IN(SELECT Sdept FROM Student WHERE sname='张力')

11.使用集合查询列出CS系的学生以及性别为女的学生学号及姓名；

SELECT sno,sname from student

Where sdept='cs'

union

select sno,sname from student

where ssex='女'

12.使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；SELECT *

FROM Student

WHERE Sdept='CS'

EXCEPT

SELECT *

FROM Student

WHERE Sage<=19

SELECT *

FROM Student

WHERE Sdept='CS'

INTERSECT

SELECT *

FROM Student

WHERE Sage<=19