数据库上机实验四答案

一：
嵌套查询：
(1):找出与李勇在同一个班级的学生信息。

select*
from Student
where Clno=--可以使用谓词in
(select Clno
from Student
where Sname='李勇')
select*
from Students1
where exists
(select*
from Students2
where s2.Clno=s1.Clno and
s2.Sname='李勇')
(2):找出所有与李勇有相同选修课的学生信息。

select*
from Student
where Sno in
(select Sno
from Grade
where o in
(select Cno
from Grade
where Sno=
(select Sno
from Student
where Sname='李勇')))and
Sname<>'李勇'
(3):找出年龄介于学生李勇和25岁之间的学生信息。

select*
from Student
where Sage<25 and Sage>
(select Sage
from Student
where Sname='李勇')
union
select*
from Student
where Sage<
(select Sage
from Student
where Sname='李勇')and Sage>25
(4):找出选修了课程操作系统的学生学号和姓名。

select Sno,Sname
from Student
where Sno in
(select Sno
from Grade
where Cno=
(select Cno
from Course
where Cname='操作系统'))
(5):找出所有没有选修1号课程的学生姓名。

select Sname
from Student
where notexists
(select*
from Grade
where Sno=Student.Sno and Cno='1')
(6):找出选修了全部课程的学生姓名。

select Sno,Sname
from Student
where notexists
(select*
from Course
where notexists
(select*
from Grade
where Sno=Student.Sno and Cno=o))
连接查询：
(1):找出与李勇在同一个班级的学生信息。

select s1.*
from Students1,Students2
where s1.Clno=s2.Clno and s2.Sname='李勇'
(2):找出所有与李勇有相同选修课的学生信息。

selectdistinct s2.*
from Students1,Students2,Gradeg1,Gradeg2
where s1.Sno=g1.Sno and s2.Sno=g2.Sno and o=o and s1.Sname='李勇'and s2.Sname<>'李勇'
(3):找出年龄介于学生李勇和25岁之间的学生信息
select s2.*
from Students1,Students2
where s1.Sage<s2.Sage and s2.Sage<25 and s1.Sname='李勇'
(4):找出选修了课程操作系统的学生学号和姓名。

select Student.Sno,Sname
from Student,Course,Grade
where Student.Sno=Grade.Sno and o=o and Cname='操作系统'
(5)：找出所有没有选修1号课程的学生姓名。

select Sname
from Student
except
selectdistinct Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='1'
(6):找出选修了全部课程的学生姓名。

select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='1' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='2' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='3' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='4' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='5' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='6' intersect
select Sname
from Student,Grade
where Student.Sno=Grade.Sno and Cno='7'
完成查询：
(1):查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列。

select Sno,Gmark
from Grade
where Cno='3'
orderby Gmark desc
(2):查询全体学生信息，要求查询结果按班级号升序，同一班级学生按年龄降序排列。

select*
from Student
orderby Clno,Sage desc
(3):求每个课程号相应的选课人数。

select Cno,COUNT(Sno)number
from Grade
groupby Cno
(4):查询选修了3门以上课程的学生学号。

select Sno
from Grade
groupby Sno
having COUNT(*)>3
二：
存在量词exists：如果内层查询结果非空，则外层的where子句返回真值，否则返回假值。

存在量词not exists：如果内层查询结果为空，则外层的where子句返回真值，否则返回假值。

三：
当表的结构比较小的时候，两种查询的效率差距并不是很大。

当表的结构比较大的时候，使用嵌套查询比较有效率，因为连接查询需要创建中间过程表，当涉及的表数目较多的时，会消耗空间资源，进而影响查询的效率。

但是，当嵌套次数比较多的时候，会降低查询效率，而用连接查询，仅仅只需一次查询，可以大大提高效率。