实验报告
学号：
姓名：
项目名称：实验四 复杂查询（2）
一 一、实验目的
(1) 熟练掌握SQL语句的使用。
(2) 熟练使用SQL语句进行复杂的连接操作。
二、实验内容及要求
    导入实验三中保存的订单数据库OrderDB.mdf。完成之后，进行以下的实验。写出相对应的SQL语句，并给出查询结果。
1． 统计订单主表的订单金额。（用订单明细表中的订单汇总金额更新订单主表相应的订单金额属性）
select distinct from2． 查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。
3． 统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。（表头用中文显示）
4． 统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。（表头用中文显示）
5． 查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。(包括没有订购任何商品的客户)
6． 查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
7． 查订购了 “32M DRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。
8． 查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
9． 按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。
10． 查至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。日期按yyyy-mm-dd显示。
11． 查销售金额最大的客户号、客户名称和总货贷额。
12． 查销售总额少于5000元的销售员编号、姓名和销售额。
13． 查至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。
14． 查同时订购了商品编号为“P2*******”和商品编号为“P2*******”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。
15． 计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。
三、实验结果
1.统计订单主表的订单金额。
SQL语句：
UPDATE OrderMaster
SET orderSum=金额
From OrderMaster a,(SELECT orderNo,SUM(quantity*price) 金额
                    FROM OrderDetail
                    GROUP BY orderNo) b
WHERE a.orderNo=b.orderNo
查询结果：
2.查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。
SQL语句：
SELECT c.orderNo,b.customerName,a.employeeName,orderSum
FROM Employee a,Customer b,OrderMaster c
WHERE a.employeeNo=c.salerNo AND b.customerNo=c.customerNo
      AND orderSum=(SELECT MAX(orderSum)
                    FROM OrderMaster )
查询结果：
3.统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。（表头用中文显示）
SQL语句：
SELECT customerName 客户名,定单数=COUNT(a.orderNo),
      orderSum 订货总额,(orderSum/COUNT(a.orderNo)) 平均订货金额
FROM OrderDetail a,Customer b,OrderMaster c
WHERE a.orderNo=c.orderNo AND b.customerNo=c.customerNo
      AND c.customerNo='C20050001'
GROUP BY  customerName,orderSum
查询结果：
4.统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。（表头用中文显示）
SQL语句：
SELECT b.customerNo 客户号,customerName 客户名,定单数=COUNT(a.orderNo),
      orderSum 订货总额,(orderSum/(COUNT(a.orderNo))) 平均订货金额
FROM OrderDetail a,Customer b,OrderMaster c
WHERE a.orderNo=c.orderNo AND b.customerNo=c.customerNo
GROUP BY  customerName,orderSum,b.customerNo
查询结果：
5.查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。(包括没有订购任何商品的客户)
SQL语句：
SELECT customerNo,customerName
FROM customer
WHERE customerNo not in
        (SELECT a.customerNo
          FROM customer a,ordermaster b
          WHERE a.customerNo=b.customerNo
                and orderNo in
                    (SELECT orderNo
                    FROM orderdetail
                    WHERE productNo in
                            (SELECT productNo
                              FROM product
                              WHERE productName='52倍速光驱' or
                              productName='17寸显示器')))
查询结果：
6.查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
SQL语句：
SELECT DISTINCT d.customerNo,customerName
FROM Customer c,OrderMaster d
WHERE c.customerNo=d.customerNo AND d.customerNo
          IN (SELECT customerNo
              FROM customer
              WHERE customerNo not in
        (SELECT a.customerNo
          FROM customer a,ordermaster b
          WHERE a.customerNo=b.customerNo
                and orderNo in
                    (SELECT orderNo
                    FROM orderdetail
                    WHERE productNo in
                            (SELECT productNo
                              FROM product
                              WHERE productName='52倍速光驱' or
                              productName='17寸显示器'))))
查询结果：
7.查订购了 “32M DRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。
SQL语句：
SELECT b.customerNo,customerName,quantity,总金额=quantity*price
FROM OrderDetail a,Customer b,OrderMaster c,Product d
WHERE d.productNo=a.productNo AND a.orderNo=c.orderNo
      AND b.customerNo=c.customerNo AND productName='32M DRAM'
查询结果：
8.查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
SQL语句：
SELECT customerName,productClass,quantity,orderSum
FROM OrderDetail a,Customer b,OrderMaster c,Product d
WHERE d.productNo=a.productNo AND a.orderNo=c.orderNo
      AND b.customerNo=c.customerNo
ORDER BY b.customerNo,orderSum DESC
查询结果：
9.按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。
SQL语句：
SELECT productNo,订货总数量=SUM(quantity),
      订货平均单价=SUM(price)/COUNT(*),订货总金额=sum(quantity*price)
FROM OrderDetail
GROUP BY productNo
HAVING SUM(price)/COUNT(*)>=280
查询结果：
10.查至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。日期按yyyy-mm-dd显示。
SQL语句：
SELECT employeeNo,employeeName,orderNo,
      convert(char(10),orderDate,120) orderDate
FROM Employee a,OrderMaster b,
    (SELECT salerNo
      FROM OrderMaster
      GROUP BY salerNo
      HAVING COUNT(*)>=2) c
WHERE a.employeeNo=b.salerNo AND b.salerNo=c.salerNo
GROUP BY employeeNo,employeeName,orderNo,orderDate
ORDER BY employeeName
查询结果：
11.查销售金额最大的客户号、客户名称和总货贷额。
SQL语句：
SELECT TOP 1 c.customerNo,customerName,MAX(总货贷额) 总货贷额
FROM (SELECT customerNo,SUM(orderSum) 总货贷额
      FROM OrderMaster
      GROUP BY customerNo) a,OrderMaster b,Customer c
WHERE a.customerNo=b.customerNo AND b.customerNo=c.customerNo
GROUP BY customerName,c.customerNo
ORDER BY 总货贷额DESC
查询结果：
12.查销售总额少于5000元的销售员编号、姓名和销售额。
SQL语句：
SELECT salerNo,employeeName,orderSum
FROM Employee a,OrderMaster b
WHERE a.employeeNo=b.salerNo
GROUP BY orderSum,salerNo,employeeName
HAVING orderSum<5000
查询结果：
13.查至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。
SQL语句：
SELECT b.customerNo,customerName,d.productNo,productName,quantity,金额=quantity*price
FROM orderDetail a,Customer b,OrderMaster c,Product d
WHERE d.productNo=a.productNo AND a.orderNo=c.orderNo
      AND b.customerNo=c.customerNo
      AND b.customerNo
      IN(SELECT customerNo
        FROM(SELECT customerNo,COUNT(DISTINCT productNo) product
              FROM (SELECT customerNo,productNo
                    FROM orderDetail e,OrderMaster f
                    WHERE e.orderNo=f.orderNo) g
              GROUP BY customerNo
              HAVING COUNT(DISTINCT productNo)>=3) h)
查询结果：
14.查同时订购了商品编号为“P2*******”和商品编号为“P2*******”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。
SQL语句：
SELECT c.customerNo,customerName,d.productNo,productName,quantity
FROM OrderDetail a,Customer b,OrderMaster c,Product d
WHERE d.productNo=a.productNo AND a.orderNo=c.orderNo
      AND b.customerNo=c.customerNo
        AND d.productNo='P20070001'
        AND a.orderNo IN(SELECT orderNo
                        FROM OrderDetail
                        WHERE productNo='P20070002')
ORDER BY customerNo
查询结果：
15.计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。
SQL语句：
select productNo,销售月份=month(orderDate),销售次数=count(*),销售金额总和=sum(quantity*price)
from OrderDetail a,OrderMaster b
where a.orderNo=b.orderNo
group by productNo,month(orderDate)
order by MONTH(orderDate),销售金额总和DESC
查询结果：