实验3.2 复杂的单表查询

一、实验目的

熟练掌握SELECT查询语句中的Group by 子句、Having子句的用法，以及汇总函数的使用。

二、实验原理

在查询语句中用Group by子句进行分组；

用Having子句对分组进行筛选。

使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函数在查询结果集中生成汇总值。

三、实验设备

安装有SQL SERVER 2000的计算机。

四、实验示例

1、统计表中员工的薪水在4000-6000之间的人数

select *from employee

where salarybetween 4000 and 6000;

2、计算'P0001'产品的平均销售单价

select AVG(unit_price)

from sale_item

where prod_id='P0001';

3、  找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。

Select sale_id,sum(tot_amt)

From sales

Group bysale_id

Having sum(tot_amt)>4000

Order by sum(tot_amt)desc

4、查询订购了三种以上产品的订单号。

Select order_no

From sale_item

Group byorder_no

Having count(prod_id)>3

五、实验内容

1.       在员工表employee中统计员工人数。

Select count(*)

From employee

2.       统计各部门员工的员工人数及平均薪水。

Select dept,count(emp_no),AVG(salary)

from employee

group by dept;

3.       查询销售业绩超过10000元的员工编号。

select emp_no

from employee

where salary>10000;

4.       计算每一产品销售数量总和与平均销售单价。

Select prod_id,sum(qty),AVG(unit_price)

from sale_item

group byprod_id;

5.       计算出一共销售了几种产品。

Select count(prod_id)

From sale_item

Group by order_no

6.       显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

Select prod_id, sum(qty* unit_price)

From sale_item

Group byprod_id

Order by prod_id

7.       计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

Select prod_id,sum(qty*unit_price),Year(order_date),month(order_date)

From sale_item

Group byprod_id ,Year(order_date),month(order_date)

Order by prod_id,Year(order_date),month(order_date)desc

8.       查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。

Select sale_id,sum(tot_amt),Year(order_date),month(order_date)

From sales

Group bysale_id ,Year(order_date),month(order_date)

Order bysale_id ,Year(order_date),month(order_date)desc