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