数据库SQL查询语句实验报告(2)

where address ='上海市' and a.CustomerNo =b.CustomerNo and b.orderNo =c.orderNo group by CustomerName ,telephone ,b.orderNo 结果：

实验二题目

首先使用命令：

UPDATE OrderMaster SET orderSum =total FROM OrderMaster a, (SELECT orderNo ,sum(quantity *price ) as total FROM OrderDetail GROUP BY orderNo ) b WHERE a.orderNo =b.orderNo

更新表OrderMaster 中 orderSum 的值。

（1）查找有销售记录的客户编号、名称和订单总额。 命令：

SELECT a.CustomerNo ,CustomerName ,sum(orderSum ) Sum FROM Customer a,OrderMaster b

WHERE a.CustomerNo =b.CustomerNo and orderSum !=0 GROUP BY a.CustomerNo ,CustomerName 结果：

(2) 在订单明细表中查询订单金额最高的订单。 命令： SELECT *

FROM OrderMaster

WHERE orderSum =(SELECT max(orderSum ) FROM OrderMaster ) 结果：

(3) 查询没有订购商品的客户编号和客户名称。

SELECT a.CustomerNo ,CustomerName , FROM Customer a WHERE NOT EXISTS ( SELECT * FROM OrderMaster b,OrderDetail c WHERE a.CustomerNo =b.CustomerNo and b.orderNo =c.orderNo ) GROUP BY a.CustomerNo ,CustomerName 结果：

(4) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。 命令：

SELECT productNo ,orderNo ,quantity ,quantity *price 订货金额 FROM OrderDetail

WHERE productNO IN( SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)>=3) ORDER BY productNo desc 结果：

(5) 使用子查询查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。 命令：

SELECT employeeName , CASE sex WHEN'F' THEN '女' WHEN 'M' THEN '男' END AS 性别, orderDate ,quantity ,sum(quantity *price )as 金额 FROM Employee a,OrderMaster b,OrderDetail c

WHERE employeeNo =salerNo and b.orderNo =c.orderNo and ProductNo in (SELECT ProductNo FROM Product WHERE ProductName ='16M DRAM')

结果：

(6) 查询sales表中订单金额最高的订单号及订单金额。 命令：

SELECT orderNo ,orderSum FROM OrderMaster

WHERE orderSum =(SELECT max(orderSum ) FROM OrderMaster ) 结果：

(7) 计算出一共销售了几种商品。 命令：

SELECT count(distinct productNo ) 商品种类 FROM OrderDetail 结果：

(8) 显示OrderDetail表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。 命令：

SELECT productNo ,sum(quantity *price ) 订购金额 FROM OrderDetail GROUP BY productNo

ORDER BY 订购金额 DESC 结果：

(9) 查找销售总额少于1000元的销售员编号、姓名和销售额。 命令：

SELECT employeeNo ,employeeName ,sum(orderSum ) 销售额

FROM OrderMaster a RIGHT OUTER JOIN Employee b ON salerNo =employeeNo WHERE employeeNo NOT IN ( SELECT salerNo FROM OrderMaster WHERE salerNo NOT IN( SELECT salerNo FROM OrderMaster GROUP BY salerNo HAVING sum(orderSum )<1000)) GROUP BY employeeNo ,employeeName 结果：

(10) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。 命令：

SELECT employeeNo ,employeeName ,sum(orderSum ) 销售额

FROM OrderMaster a RIGHT OUTER JOIN Employee b ON salerNo =employeeNo WHERE salerNo IN( SELECT salerNo FROM OrderMaster GROUP BY salerNo HAVING sum(orderSum )<5000) GROUP BY employeeNo ,employeeName ORDER BY 销售额 DESC 结果：

(11) 在Employee表中查询薪水超过员工平均薪水的员工信息。 命令：

SELECT employeeNo ,employeeName ,salary FROM Employee WHERE salary >( SELECT avg(salary ) FROM Employee )

GROUP BY employeeNo ,employeeName ,salary

结果：

(12) 计算每一种商品的销售数量、平均销售单价和总销售金额。 命令：

SELECT productNo ,count(quantity ) 销售数量,avg(price ) 平均销售单价,sum(quantity *price ) 总销售额

FROM OrderDetail GROUP BY productNo ORDER BY productNo 结果：

(13) 查找至少有3次销售的业务员名单和销售日期。 命令：

SELECT employeeName ,orderDate FROM Employee a,OrderMaster b,( SELECT salerNo FROM OrderMaster GROUP BY salerNo HAVING count(*)>=3) c

WHERE employeeNo =b.salerNo and b.salerNo =c.salerNo GROUP BY employeeName ,orderDate 结果：

(14) 用存在量词查找没有订货记录的客户名称。 命令：

SELECT CustomerName FROM Customer a WHERE NOT EXISTS ( SELECT CustomerNo FROM OrderMaster b WHERE a.CustomerNo =b.CustomerNo )

结果：

(15) 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。 命令：

SELECT a.CustomerNo ,a.CustomerName FROM Customer a WHERE EXISTS ( SELECT * FROM OrderDetail b,OrderMaster c WHERE a.CustomerNo =c.CustomerNo and b.orderNo =c.orderNo GROUP BY a.CustomerNo HAVING sum(quantity )<10) 结果：

(16) 在销售明细表中按商品编号进行汇总，统计每种商品的销售数量和金额。 命令：

SELECT productNo ,sum(quantity ) 销售数量,sum(quantity *price ) 金额 FROM OrderDetail GROUP BY productNo 结果：

(17) 按客户编号统计每个客户2008年2月的订单总金额。 命令：

SELECT a.CustomerNo ,orderSum ,orderDate

FROM Customer a LEFT OUTER JOIN OrderMaster b ON a.CustomerNO =b.CustomerNo WHERE year(orderDate )='2008' and month(orderDate )='2' 结果：

(18) 查找定单金额高于8000的所有客户编号。 命令：

SELECT CustomerNo

FROM OrderDetail as a,OrderMaster as b WHERE a.orderNo =b.orderNo GROUP BY CustomerNo

HAVING sum(quantity *price )>8000 结果：

(19) 显示每种商品的销售金额总和，并依销售金额由大到小输出。 命令：

SELECT productNo ,sum(price *quantity ) 销售金额 FROM OrderDetail GROUP BY productNo

ORDER BY 销售金额 desc 结果：

(20) 查找销售金额最大的客户名称和总货款。 命令：

SELECT top 1 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 CustomerN …… 此处隐藏：3335字，全部文档内容请下载后查看。喜欢就下载吧 ……