2024年2月21日发(作者：)

实验四 复杂查询（1）解答

实验结果

1. 查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按

邮政编码降序排序。

SQL语句：

SELECT customerNo,customerName,zip

FROM Customer

WHERE address LIKE '%北京%' and customerNo NOT IN(

SELECT customerNo

FROM OrderMaster)

ORDER BY zip DESC

查询结果：

问题：

SELECT customerNo,customerName,zip

FROM Customer

WHERE address='北京市' AND customerNo not in(select customerNo from

OrderMaster)

ORDer by zip DESC

2．查询订购了“32M DRAM”商品的订单编号、订货数量和订货单价。

SQL语句：

SELECT orderNo,quantity,price

FROM OrderDetail

WHERE productNo IN(

SELECT productNo

FROM Product

WHERE productName='32M DRAM')

查询结果：

3．查询与员工编号E2008005在同一个部门的员工编号、姓名、性别、所属部门。

SQL语句：

SELECT employeeNo,employeeName,sex,department

FROM Employee

WHERE department IN(

SELECT department

FROM Employee

WHERE employeeNo='E2008005')

查询结果：

不包括员工‘E2008005’：

SELECT employeeNo,employeeName,sex,department

FROM Employee

WHERE department IN(

SELECT department

FROM Employee

WHERE employeeNo='E2008005')

AND employeeNo!='E2008005'

4．查询既订购了P20050001商品，又订购了P20050002的商品的客户编号、订单编号和订单总额。

SQL语句：

SELECT customerNo, orderNo,orderSum

FROM OrderMaster

WHERE orderNo IN

(SELECT orderNo

FROM OrderDetail

WHERE productNo='P20050001')

AND orderNo IN

(SELECT orderNo

FROM OrderDetail

WHERE productNo='P20050002')

查询结果：

5．查询订购了“52倍速光驱”商品且订货数量介于2~4之间的订单编号、订货数量和订货金额。

SQL语句：

SELECT orderNo 订单编号,quantity 订货数量,quantity*price 订单金额

FROM OrderDetail

WHERE productNo IN(

SELECT productNo

FROM Product

WHERE productName='52倍速光驱')

AND quantity between 2 and 4

查询结果：

补充：查询订购了“52倍速光驱”商品且订货数量介于2~5之间的产品编号、订单总数量、订货平均价和订货金额。

SELECT productNo,sum(quantity) 订货数量,avg(price)订货平均价,sum(quantity*price) 订货金额

FROM OrderDetail

WHERE productNo IN(

SELECT productNo

FROM Product

WHERE productName='52倍速光驱')

AND quantity between 2 and 5

GROUP BY productNo

查询结果：

6．在订单主表中查询每个业务员的订单数量。

SQL语句：

SELECT salerNo 业务员,count(*) 订单数量

FROM OrderMaster

GROUP BY salerNo

查询结果：

7．统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。

SQL语句：

SELECT count(*) 员工人数,avg(salary) 平均工资

FROM Employee

WHERE department='业务科' and year(birthday)= 1973 or year(birthday)=1967

查询结果：

8．在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。

SQL语句：

SELECT productNo,sum(quantity) 销售数量,sum(quantity*price) 金额

FROM OrderDetail

GROUP BY productNo

ORDER BY sum(quantity*price) ASC

查询结果：

9．统计订单主表的订单金额。

SQL语句：

UPDATE OrderMaster

SET orderSum=orderDetailSum

FROM OrderMaster a,(

SELECT orderNo,SUM(quantity*price) orderDetailSum

FROM OrderDetail

GROUP BY orderNo) b

WHERE o=o

SELECT *

FROM OrderMaster

查询结果：

10. 查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。

SQL语句：

SELECT orderNo 订单编号,count(*) 订购次数

FROM orderDetail

WHERE quantity>=3

GROUP BY orderNo

HAVING count(*)>=3

查询结果：

问题：

select orderNo,count(orderNo) 订购次数

from orderDetail

group by orderNo

having count(productNo)>=3 and sum(quantity)>=3

查询结果：