关系代数及SQL语言的习题
针对书上的学生选课数据库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
exists (select * from sc where = and =)
48、查询“李勇”同学所选课程的平均成绩。
49、Select avg(grade) from sc where sno =(Select sno from s where sname
=’李勇’)
50、查询“操作系统”这门课的最高分及最低分。
51、Select max(grade),min(grade) from sc where cno=(select cno from
course where cname =’操作系统’)
52、查询“数据结构”这门课的选课人数。
53、Select count(sno) from sc where cno =(select cno from course where
cname=’数据结构’)
54、查询“CS”系的所有学生的学号、姓名、课程名及成绩。
55、Select sno,sname,cname,grade from s,sc,c where = and = and
sdept=’CS’
56、查询“CS”系选修课程的成绩在90分以上的所有女生的姓名、课程名和
成绩。
57、Select sname,cname,grade from s,sc,c where = and = and sdept=’CS’
and grade >=90 and ssex like ‘女’