关系代数及SQL语言的习题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
针对书上的学生选课数据库S_T，用关系代数和SQL语言完成以下查询：
1、查询“CS”系所有学生的学号和姓名。
2、Slelect sno,sname from student where sdept =’CS’
3、查询所有姓“刘”的学生的信息。
4、Select * from student where sname like ‘刘%’
5、查询年龄在18至20岁之间的学生信息。
6、Select * from student where sage between 18 and 20
7、查询不在“CS”系也不在“MA”系的学生的所有信息。
8、Select * from student where sdept not in (‘CS’,’MA’)
9、查询“CS”系所有学生的平均年龄。
10、Select avg(sage) from student where sdept like ‘CS’
11、查询课程名是以“系统”结尾的课程信息。
12、Select * from course where cname like ‘%系统’
13、查询先行课为“6”号课程的课程信息。
14、Select * from course where cpno=6
15、查询间接先行课为“5”号课程的课程号及课程名。
16、Select , from c c1,c c2 where = and =5
17、Select cno ,cname from course where cpno in (select cno from course
where cpno=5)
18、查询没有先行课的课程名。
19、Select cname from course where cpno is null
20、查询选修了“1”号课程的学生选课信息。
21、Select * from sc where cno=1
22、查询成绩为90分以上的学生姓名和课程名。
23、Select sname ,cname from s,c,sc where = and = and grade>=90
24、查询被选修了的课程号及课程名。
25、Select cno ,cname from course where cno in (Select distinct(cno )
from sc)
26、Select cno ,cname from course where exists (select * from sc where
=
27、查询没有选修课程的学生学号及姓名。
28、Select sno,sname from s where sno not in (select distinct(sno) from
sc)
29、Select sno ,sname from s where not exists(select * from sc where
=
30、查询没有选修“1”号课程的学生姓名。
31、Select sname from s where sno not in (select distinct(sno) from sc
where cno=1)
32、Select sname from s where not exists (select * from sc where = and
=1)
33、查询既选修了“数据结构”又选修了“操作系统”的学生姓名。
34、Select sname from ,sc where = and = and cname=’数据结构’
35、Select sname from s where sno in (select sno from sc where
cno=(slect cno from c where cname=’数据结构’) and sno in (select sno from sc where cno=(select cno from c where cname=’操作系
统’)))
36、Select sname from s where sno in (select sno from sc sc1,sc sc2 where
= and =(slect cno from c where cname=’数据结构’) and =(select cno from c where cname=’操作系统’))
37、查询既选修了“2”号又选修了“4”号课程的学生学号。
38、Select sno from sc where cno=2 and sno in(select sno from sc where
cno=4)
39、Select sno from sc sc1,sc sc2 where = and =2 and =4
40、查询选修了“2”号或“4”号课程的学生学号。
41、Select sno from sc where cno=2 or cno=4
42、查询至少选修了“95002”学生所选课程的学生学号。
43、Select from sc scx where not exists (select * from sc scy where
=’95002’ and not exists (select * from sc scz where = and =)
44、查询至少选修了一门其间接先行课为“7”号课程的学生姓名。
45、Select sname from s where sno in(Select sno from sc where cno in
(Select from c c1,c c1 where = and =7))
46、查询选修了所有课程的学生姓名。
47、Select sname from s where not exists (select * from c where not