数据库的查询和视图

实验三：数据库的查询和视图
实验目的：
加深对嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合的操作方法；掌握视图的用法，加深对视图作用的理解。

实验内容：
使用IN、比较符、ANY或ALL和EXISTS操作符和聚集函数、分组函数、集合函数进行查询，进行嵌套查询操作；创建、修改和删除视图，利用视图进行查询。

实验步骤：
一. 使用带IN谓词的子查询
1.查询与’刘晨’在同一个系学习的学生的信息:
2.查询选修了课程名为’信息系统’的学生的学号和姓名:
3.查询选修了课程’1’和课程’2’的学生的学号:
二. 使用带比较运算的子查询
4.查询比’刘晨’年龄小的所有学生的信息:
三. 使用带Any, All谓词的子查询
5.查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;
6.查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄:
7.查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄:
四. 使用带Exists谓词的子查询和相关子查询
8.查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:
9.查询所有选修了1号课程的学生姓名:
10.查询没有选修了1号课程的学生姓名:
11.查询选修了全部课程的学生姓名:
11. 查询至少选修了学生95002选修的全部课程的学生的学号:
12. 求没有人选修的课程号cno和cnamecname:
13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程14*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade):
五. 使用聚集函数：
15. 计算1号课程的学生的平均成绩, 最高分和最低分:
16. 查询’信息系’(IS)学生”数据结构”课程的平均成绩:
17*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade):
18*. 求成绩低于该门课程平均成绩的学生的成绩信息(sno,cno,grade)
六. 分组查询
19. 查询各系的学生的人数并按人数从多到少排序:
20. 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:
21. 查询选修了3门课程已上的学生的学号和姓名:
22. 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数:
23. 查询至少选修了2门课程的学生的平均成绩:
24. 查询平均分超过80分的学生的学号和平均分:
25. 查询”信息系”(IS)中选修了5门课程以上的学生的学号:
七集合查询
26. 查询数学系和信息系的学生的信息;
27. 查询选修了1号课程或2号课程的学生的学号:
视图部分：
说明：对视图的创建、修改和删除均可在可视化界面下操作。

在SQL Server企业管理器中，依次选择数据库――XSGL――视图――鼠标右击――
新建视图。

然后通过单击按钮或右击鼠标第一格的空白区域，选择弹出菜单中的“添加表”。

然后拖拽主键到外键，建立关联，点击字段左边的方框选择输出字段。

图5-1
仅以命令方式来操作：
一、视图的创建：
1．创建信息系学生信息的视图：
2. 创建信息系选修了1号课程的学生的视图:
3. 建立信息系选修了1号课程且成绩在90分以上的学生的视图:
4. 创建一个反映学生出生年份的视图:
5. 将所有女生的记录定义为一个视图:
6. 将所有学生的学号和他的平均成绩定义为一个视图:
二、视图结构的修改：
6. 将视图F_student修改为信息系的所有女士的视图
说明: 视图结构的修改的AS 后的Select语句与创建视图的完全一致, 引入结构修改的目的是为了避免与视图相关的数据库对象的变换, 如触发器, 关联等
三、查询视图
7. 在信息系的学生视图中查询年龄小于20岁的学生:
8. 查询信息系选修了1号课程的学生:
9. 在视图S_G中查询平均成绩在90分以上的学生的学号和平均成绩:
四、更新视图：
10．将信息系学生视图IS_Student中学号为”95002”的学生姓名改为”刘辰”:
11. 向信息系学生视图IS_Student中插入一个新的学生记录, 学号为95029,姓名为”赵新”, 年龄为20岁:
12. 删除信息系学生视图IS_Student中学号为95004的学生的记录:
五、删除视图：
13. 删除视图IS_S1:
drop view IS_S1。