实验3 SQL的高级查询

实验3 SQL的高级查询

一、实验目的

1．继续掌握基本的SELECT查询及其相关子句的使用

2．掌握复杂的SELECT查询、如多表查询，子查询，连接，分组和嵌套查询

3．掌握SQL中的集合并运算union

4．掌握SQL中元组的插入、修改、删除操作（insert,update,delete）。

二、预备知识：

SQL中的连接操作：

假设R与K是基本数据表。基本表的连接操作可以分为五类：

●内连接 R inner join K on <条件> 只返回满足条件的行

●左外连接 R left join K on<条件>返回满足条件的行及左表R中所有的行。如果左表的某条

记录在右表中没有匹配记录，则在查询结果中右表的所有选择属性列用NULL填充。

●右外连接 R right join K on <条件>返回满足条件的行及右表K中所有的行

●完全外连接 R full join K on <条件>返回满足条件的行及左右表R，K所有的行。当某条记录

在另一表中没有匹配记录，则在查询结果中对应的选择属性列用NULL填充。

●交叉连接R CROSS JOIN Ｋ：相当于广义笛卡尔积。不能加筛选条件。

三、实验环境

1.个人计算机或局域网

2.Windows 2000操作系统

3.SQL Server 2000数据库管理系统

四、实验步骤

1.启动查询分析器，选择学生选课数据库

2.在学生选课数据库中分别使用SQL命令完成指定任务。

五、实验内容：

1．查询所有学生的情况以及他们选修的课程和得分

2．查询所有学生的姓名以及他们选修的课程名和得分

3．检索已经选了课程的学生的姓名、选修课程和成绩（提示：使用inner join）

4．检索每个学生的姓名，选修课程和成绩，没有选修的同学也列出。

5．列出所有学生所有可能的选课情况（提示：使用cross join）

6．检索每个学生的姓名、选修课程，没有选修的同学和没有被选修的课程也列出

7．求学生的总人数和平均年龄

8．求选修了各课程的学生的人数

9．对计算机系的学生按课程列出选修了该课程的学生的人数

10．查询每个学生的平均成绩

11．求选修课程超过或等于2门的学生的学号

12．查询学生的学号、姓名、所有学生所有课程的最高成绩

13．查询学生的学号、课程号及对应成绩与所有学生中所有课程的最高成绩的差值

14．查询陈小红同学的学号及所选修的课程号

15．查询没有选修C02课程的学生姓名

16．查找所有成绩高于平均成绩的学生的学号

17．求选修了C02课程的学生的姓名

18．查询选修了课程C01,C02,C03的学生的学号

19．求选修了学生王小宏所选修的课程的那些学生的学号

20．检索没有选修课程C01的所有学生的姓名

21．检索不学C02课程的学生姓名与年龄

22．检索成绩小于任何一个选修C02课程的学生的成绩的学生学号

23．求没有选修C03课程的学生的姓名（提示：此处使用not exists,效果与not in 相同）24．求选修了‘VB’课程的学生的学号和姓名

25．给陈小红的成绩加2分

26．删除陈小红的所有选修记录

27．查找学号为J0401的成绩、全部课程的平均成绩，以及每门成绩与全部课程的平均成绩之间的距离

28．把目前为止还没有选修课程的学生的学号插入关系SC中。

29．把课程名为VB的成绩从基本表SC中删除

30．把C04课程中小于该课程平均成绩的成绩记录从基本表SC中删除

31．将最高成绩的学生的成绩减少20分

32．将前5名最高成绩的学生的成绩减少20分

33．按照成绩从高到低排序，将前10%名学生的成绩减少20分

34．查找最高成绩超过给定学生平均成绩10分的学生

35．求选修了课程C02或C04的学生的学号，姓名，课程号。

36．求选修了课程C02或C04的学生的学号、姓名，不包含重复的记录行

37．求选修了课程C02或C04的学生的学号、姓名，包括重复记录行（提示：使用union all）38．求选修了课程C01，C02，C03的学生的学号，姓名，不包含重复记录行

39．求选修了课程C02和C04的学生的学号和姓名

40．求选修了课程C02但没有选修课程C04的学生的学号和姓名。