数据库 实验四 数据库的复杂查询（二）

实验内容

（一）嵌套查询

1．查询比“王敏”年纪大的男学生信息。

子查询SQL语句：

SELECT *

FROM Student

WHERE Sage > (SELECT Sage FROM Student WHERE Sname = '王敏') AND Ssex = '男';

2．查询选修了“信息系统”的学生的学号和姓名。

子查询SQL语句：

SELECT Sno, Sname

FROM Student

WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = (SELECT Cno FROM Course WHERE Cname = '信息系统'));

3．查询与“刘晨”在同一个系的学生学号、姓名、性别。。

子查询SQL语句：

SELECT Sno, Sname, Ssex

FROM Student

WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '刘晨');

4．查询其他系中比计算机科学系任一学生年龄大的学生的学号、姓名。

带有ANY或ALL谓词的子查询语句：

SELECT Sno, Sname

FROM Student

WHERE Sage > ANY (SELECT Sage FROM Student WHERE Sdept = 'CS')

AND Sdept <> 'CS';

用聚合函数实现：

SELECT Sno, Sname

FROM Student

WHERE Sage > (SELECT MAX(Sage) FROM Student WHERE Sdept = 'CS')

AND Sdept <> 'CS';

5．找出每个学生超过他自己选修课程平均成绩的课程号。

用相关子查询实现：

SELECT Sno, Cno

FROM sc x

WHERE Grade >= (SELECT AVG(Grade)

                FROM sc y

                WHERE y.Sno = x.Sno);

用基于派生表的查询实现：

SELECT Sno, Cno

FROM sc, (SELECT Sno, Avg(Grade) FROM sc GROUP BY Sno)

AS Avg_sc(avg_sno, avg_grade)

WHERE sc.Sno=Avg_sc.avg_sno AND sc.Grade>=Avg_sc.avg_grade;