实验三：复杂的单表查询 一、实验目的 1. 掌握SELECT语...

实验三：复杂的单表查询 一、实验目的 1. 掌握SELECT语句的基本语法； 2. 掌握GROUP BY 子句的作用和使用方法； 3. 掌握HAVING子句的作用和使用方法； 4. 掌握聚集函数的作用和使用方法。 二、实验环境 已安装SQL Server 2005 或2012以及更高版本的计算机； 三、实验学时 2学时 四、实验要求 1. 了解单表的复杂查询和实现方式； 2. 完成实验报告； 五、实验原理 1. 在查询语句中用Group by子句进行分组； 2. 用Having子句对分组进行筛选。 3. 使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函数在查询结果集中生成汇总值。 六、实验示例 l 针对S_T数据库 1．计算出英语课程的平均分,最高分和最低分。 select avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分 from sc where cno='1' 2. 查询数据表SC的学生的学号、课程号及成绩，对于成绩按以下规定显示：成绩在60分以下显示为不及格；60-79之间显示为良好；80以上显示为优秀。 SELECT Sno,Cno,GradeLevel= case when Grade＜60 then '不及格' when grade＞=60 and Grade＜=79 then '良好' when grade＞=80 then '优秀' end FROM SC l 针对bookshop数据库 1、统计员工表中薪水在40000-60000之间的人数 select count(*) as 人数 from employee where salary between 40000 and 60000 2、计算图书编号为'20652'图书的平均销售单价 select avg(unit_price) from sale_item where book_no='20652' 3、找出订单总金额超过300的订单信息，并按订单总金额从大到小排序。 Select order_no,sum(qty*unit_price) tot_amt from sale_item group by order_no having sum(qty*unit_price)＞300 order by sum(qty*unit_price) desc 4、查询订购了三种以上产品的订单号。 Select order_no ,count(*) from sale_item Group by order_no Having count(*)＞=3 七、实验内容及步骤 请使用T-SQL 语句实现以下操作： l 针对S_T数据库 1. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数＞=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”； 2. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”； 3. 显示选修的课程数大于3的各个学生的选修课程数； 4. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； 5. 列出有二门以上课程（含两门）不及格的学生的学号及不及格门数； l 针对bookshop数据库 1、在员工表employee中统计员工人数。 2、统计各部门员工的员工人数及平均薪水。 3、查询销售业绩超过300元的员工编号。 4、计算每种图书的销售数量总和与平均销售单价。 5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUP BY 子句中使用CUBE关键字） 6、统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUP BY 子句中使用ROLLUP关键字） 7、计算出一共销售了几种图书。 8、显示sale_item表中每种图书的订购金额总和，并且依据销售金额由大到小排列来显示出每一种图书的排行榜。 9、显示sale_item表中销售金额在300元以上的订单的销售数量，销售金额，并按降序排序。 10、统计各个类型图书的种类数量。 11、统计各个客户的消费金额。 12、统计各个客户的订单数量。 八、出现问题及解决办法 如：某些查询操作无法执行，如何解决？