实验4 SQL的复杂查询功能

实验4 SQL的复杂查询功能

实验目的

学会使用MS SQL SERVER 2000的查询分析器完成SQL的复杂查询功能，主要包括连接查询、嵌套查询、集合查询及多表查询。

知识点拨：

1. 2. 3. 4. 5.

复杂查询 连接查询： 嵌套查询 集合查询 多表查询

实验内容

1. 2. 3. 4.

连接查询： 嵌套查询 集合查询 多表查询

实验步骤

1. 选择数据库JX；

注意：以下查询请尽量多使用不同的SQL语句实现！ 2. 查询选修了‘3’号课程且成绩在70分以上的学生信息。 SELECT Student.*,Cno,Grade FROM Student,SC

WHERE Student.Sno=SC.Sno AND Cno=3 AND Grade>70 3. 查询教‘3’号课程的任课教师的信息。 SELECT DISTINCT Teacher.* FROM Teacher,SC

WHERE Teacher.Tno=SC.Tno AND Cno=3

4.查询选修了‘3’课程的学生姓名，成绩及任课教师姓名。 SELECT Sname,Grade,Tname FROM Student,SC,Teacher

WHERE Student.Sno=SC.Sno AND SC.Tno=Teacher.Tno AND Cno=3

4. 查询开设的课程中选课人数不足20人的课程信息。 SELECT * FROM Course WHERE Cno IN

(SELECT Cno

FROM SC GROUP BY Cno HAVING COUNT(*)<20) 5. 查询选修课程数低于3门的学生信息。 SELECT * FROM Student WHERE Sno IN

(SELECT Sno FROM SC GROUP BY Sno

HAVING COUNT(Cno)<3) 6. 查询没有选修课程的同学的基本信息。 SELECT Student.*,Cno

FROM Student LEFT JOIN SC ON(Student.Sno=SC.Sno) WHERE Cno IS NULL

7. 查询既选修课‘3’号又选修了‘4’号课程的同学姓名、课程名及成绩。 SELECT Sname,Cname,Grade FROM Student,Course,SC

WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Student.Sno IN (SELECT Sno FROM SC

WHERE Cno=3 AND Sno IN(

SELECT Sno FROM SC WHERE Cno=4)) 8. 查询同时选修了‘操作系统’课程和‘数据库原理’课程的学生名单。 SELECT Sno FROM SC,Course

WHERE Course.Cno=SC.Cno

AND Cname='操作系统' AND Sno IN(SELECT Sno FROM SC WHERE Cno IN( SELECT Cno FROM Course

WHERE Cname='数据库原理'))

9. 查询所有同学的基本信息及选课情况（包含未选课同学的信息）。 SELECT Student.*,SC.*

FROM Student LEFT JOIN SC ON (Student.Sno=SC.Sno) 10. 列出所有参加了数据库课程考试的学生姓名和成绩。 SELECT Sname,Grade FROM Student,SC

WHERE Student.Sno=SC.Sno AND Cno IN(SELECT Cno FROM Course

WHERE Cname='数据库原理') 11. 查询没有选修“3”号课程的学生姓名和所在系。 SELECT Sname,Sdept FROM Student,SC

WHERE Student.Sno=SC.Sno AND Student.Sno not IN(SELECT Sno FROM SC WHERE Cno=3 GROUP BY Sno)

12. 查询每一课程的间接先修课，以“课程名”、“先修课名”作列名。 SELECT FIRST.Cname,SECOND.Cname FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno

13. 列出所有没有选修“数据结构”课程的学生的学号、姓名、所在院系。 SELECT Sno,Sname,Sdept FROM Student WHERE Sno NOT IN

(SELECT Sno FROM SC

WHERE Cno IN(SELECT Cno FROM Course

WHERE Cname='数据结构')) 14. 查询至少选修了“06002”号同学选修的所有课程的同学信息。 SELECT * FROM Student

WHERE Sno IN

(SELECT DISTINCT Sno FROM SC X

WHERE NOT EXISTS(SELECT * FROM SC Y

WHERE Y.Sno='06002' AND NOT EXISTS (SELECT * FROM SC Z

AND Sno!='06002') WHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno))