实验二、 基本表和视图的连接查询 统计查询和嵌套查询

实验二 复杂查询

一、实验大纲

实验目的：

1. 掌握连接的内连接，外连接的概念 2. 熟练掌握连接查询的操作实现

3. 熟练掌握嵌套查询、统计查询的操作实现

4. 掌握GROUP BY子句、HAVING子句、ORDER BY子句的用法 5. 掌握（NOT）IN、（NOT）EXISTS等谓词的用法

6. 掌握SUM、AVG、COUNT、MAX、MIN等集合函数的用法 实验要求：

1. 查找财务科平均工资>3000的男职工信息 2. 查找基本工资大于2000的职工的详细信息 3. 查询每个职工及其所在部门情况 4. 列出所有女职工的住址

5. 列出其职工的平均工资大于财务科职工平均工资的科室名称 6. 查找和钱念在同一个科室的职工信息

7. 查找其他科室中比人事科某职工工龄长的职工姓名和年龄 8. 查找年龄在50到55岁女职工的信息

9. 查找平均工资大于2000的部门名称及平均工资 10. 查找人事科的最高工资

11. 查找财务科的最小工资的职工名单，并计算该科工资总和 12. 利用自连接和外连接（左连接和右连接）实现以上操作。 13. 分别用企业管理器和查询分析器实现所有操作任务。 14. 利用表别名实现以上操作 实验环境： SQL Server 2000 二、实验指导

说明：本示例用例的表结构如下所示：

Student (sno, sname, sex，sage，dept, oldgrade, advisor) Teacher (tno, tname, dept, salary, title) Course (cno, cname, descry, dept, credit) SC (sno, cno, grade)

以上数据库表的含义为： Student—学生：sno（学号），sname（学生姓名），sex（性别），sage(年龄)，dept（系），oldgrade（高考成绩），advisor（导师）

Teacher—教师：tno（教师编号），tname（教师姓名），dept（所在系），salary（工资），title（职称）

Course—课程：cno（课程号），cname（课程名），descry（课程说明），dept（开课系），credits（学分）

SC—成绩：sno（学号），cno（课程号），grade（成绩） 1.利用企业管理器实现查询

如果是多表查询，只需在表显示区，在右键弹出式菜单中选择“添加表”即可，同时显示两个表间的关联关系，如图1所示。

图1

若要进行条件查询，只需在对应列名的“准则”栏中写入相应的条件即可，如在姓名sname的“准则”栏中写入=“李进”。

具体示例参见“利用新建视图进行查询”。 2.利用新建视图进行查询 建立视图的方法见实验一。

[例1]查询课程编号为205323，成绩大于80分的学生姓名。

图2

添加表Student 和表SC，其中选择显示字段Student.sname和SC.cno、SC.grade。在列cno的“准则”中输入=‘205323’，在grade列的“准则”中输入>80。如图2所示，结果显示在下端显示结果区中。

3.利用T-SQL实现查询

［例2］查找选修了105323号课程的学生姓名。 SELECT Student.sno, sname, cno FROM Student, Course

Where Student.sno=Course.sno and Course.cno=’105323’； [例3]查看所有的学生及其选课信息。 SELECT *

FROM Student, SC

WHERE Student.sno=SC.sno；

或（利用内连接） SELECT *

FROM Student INNER JOIN SC ON Student.sno=SC.sno；

[例4]分别利用左连接和右连接实现上例，观察异同。 （利用左连接） SELECT *

FROM Student, SC

WHERE Student.sno*=SC.sno; 或

SELECT *

FROM Student LEFT JOIN SC ON Student.sno=SC.sno；

（利用右连接） SELECT *

FROM Student, SC

WHERE Student.sno=*SC.sno； 或

SELECT *

FROM Student RIGHT JOIN SC ON Student.sno=SC.sno；

[例5]查找工资不在2000到3000的教师姓名，按工资排序。 SELECT tname FROM Teacher

WHERE salary NOT BETWEEN 2000 AND 3000 ORDER BY salary；

[例6]查找高考平均成绩大于700分的学生所在院系。 SELECT dept，AVG（oldgrade）as 平均成绩 FROM Student GROUP BY dept

HAVING 平均成绩>700；

[例7]查看所有部门的平均高考成绩，并按平均成绩排序。 SELECT dept，AVG(oldgrade) AS 平均成绩 FROM Student GROUP BY dept

ORDER BY 平均成绩；

[例8]查找所有学生的学号及成绩，并计算总成绩。 SELECT sno，grade FROM SC

COMPUTE SUM(grade)；

[例9]查看每个部门教师的最高工资。 SELECT dept，MAX(salary) FROM Teacher

GROUP BY dept； 三、实验报告模板

? 写出所有用例数据；

? 写出用企业管理器进行查询的操作步骤； ? 显示每种查询代码及最终结果；

? 实验过程中所遇到的问题及解决方法。