数据库系统概论(王珊第五版)实验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<any(select sage from student where sdept='cs')
And sdept<>'cs'
9.使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；Select sname from student
Where sage<all(select sage from student where sdept='wm') And sdept<>'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。