实验五

实验五实验名称：复杂的单表查询一、实验目的熟练掌握SELECT查询语句中的Groupby子句、Having子句的用法，以及汇总函数的使用。二、实验原理在查询语句中用Groupby子句进行分组；用Having子句对分组进行筛选。使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函数在查询结果集中生成汇总值。三、实验设备安装有SQLSERVER2000的计算机。四、实验示例1、统计表中员工的薪水在40000-60000之间的人数selectcount(*)as人数fromemployeewheresalarybetween40000and600002、计算'P0001'产品的平均销售单价selectavg(unit_price)fromsale_itemwhereprod_id='P0001'3、找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。Selectsale_id,sum(tot_amt)fromsalesgroupbysale_idhavingsum(tot_amt)40000orderbysum(tot_amt)desc4、查询订购了三种以上产品的订单号。Selectorder_nofromsale_itemGroupbyorder_noHavingcount(*)3五、实验内容1、在员工表employee中统计员工人数。2、统计各部门员工的员工人数及平均薪水。3、查询销售业绩超过10000元的员工编号。4、计算每一产品销售数量总和与平均销售单价。5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUPBY子句中使用CUBE关键字）6、统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUPBY子句中使用ROLLUP关键字）7、计算出一共销售了几种产品。8、显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。9、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。答案：--1、在员工表employee中统计员工人数。selectcount(emp_no)as员工总人数fromemployeego--2、统计各部门员工的员工人数及平均薪水。selectdeptas部门名,count(emp_no)员工人数,avg(salary)as平均薪水fromemployeegroupbydeptgo--3、查询销售业绩超过10000元的员工编号。selecta.emp_noas员工编号,b.tot_amtas销售业绩fromemployeea,salesbwheretot_amt10000go--4、计算每一产品销售数量总和与平均销售单价。selectprod_id,sum(qty)as销售数量总和,avg(unit_price)as平均销售单价fromsale_itemgroupbyprod_idgo--5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUPBY子句中使用CUBE关键字）selectdept,sex,avg(salary)fromemployeegroupbycube(sex,dept)go--6、统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUPBY子句中使用ROLLUP关键字）selectdept,sex,avg(salary)fromemployeegroupbyrollup(sex,dept)go--7、计算出一共销售了几种产品。selectcount(prod_id)as产品数fromproduct--8、显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。selectprod_id,sum(qty*unit_price)as订购金额总和fromsale_itemgroupbyprod_idorderby订购金额总和descgo--9、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。selectprod_idas产品编号,month(order_date)as月份,sum(qty*unit_price)as销售金额总和fromsale_itemgroupbymonth(order_date),prod_idorderbymonth(order_date),prod_idgo--10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。Selectsale_idas业务员编号,month(order_date)as月份,sum(tot_amt)as业绩fromsalesgroupbysale_id,month(order_date)orderbysale_id,month(order_date)descgo