数据库原理 复杂查询

数据库原理 复杂查询

学习中 做题记录

(1) 在订单明细表中查询订单金额最高的订单。

select orderNO,sum(quantityprice) as ordersum
from OrderDetail a group by orderNo
having sum(quantityprice)=
(select max(ordersum) from (select orderNO,sum(quantity*price) as ordersum
from OrderDetail group by orderNo)a)

/思路：查询订单金额最高的订单 每个订单号 有不同的商品 不同个商品的单价不同
所以首先，每种商品的金额是quantityprice
按照订单号 进行分组 sum(quantity*price) group by orderDetail
然后在这个表单中找到 金额最高的那一个订单 聚合函数只能搭配having使用 不能用于WHERE */

(2) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
select orderNo,a.productNo,quantity,quantityprice ordersum
from OrderDetail a,(select productNo from OrderDetail group by productNo having COUNT()>=3) b
where a.productNo=b.productNo
order by quantity desc

/思路：找出被订购3次以上的商品编号，意思就是产品编号出现三次以上的？将查询出来的表与商品明细表的productNo相关联/

(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。
select a.employeeNo,a.employeeName,sum(quantityprice) sunmoney
from Employee a,OrderDetail b,OrderMaster c
where a.employeeNo=c.salerNo and b.orderNo=c.orderNo
group by a.employeeNo,a.employeeName
having sum(quantityprice)<5000

(4) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。
select employeeNo,employeeName,orderSum
from Employee a,(select salerNo,sum(orderSum) orderSum
from OrderMaster
group by salerNo
having sum(orderSum)<5000) b
where a.employeeNo=b.salerNo
order by orderSum desc

(5) 查询订购的商品数量没有超过10个的客户编号和客户名称。
SELECT a.CustomerNo,CustomerName
FROM Customer a
WHERE a.CustomerNo IN ( SELECT CustomerNo
FROM OrderMaster b,OrderDetail c
WHERE b.orderNo=c.orderNo
GROUP BY CustomerNo
HAVING sum(quantity)<10)

(6) 查找订货金额最大的客户名称和总货款。
SELECT customerName ,sum(orderSum)
FROM OrderMaster a,Customer b
WHERE a.customerNo=b.customerNo
GROUP BY a.customerNo,customerName
HAVING sum(orderSum)=(SELECT max(orderSum)
FROM(SELECT customerNo,sum(orderSum) orderSum
FROM OrderMaster
GROUP BY customerNo)c)

(7) 查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。
SELECT a.CustomerNo,CustomerName,b.ProductNo,
ProductName,quantity,sum(quantity*price) sum
FROM Customer a,Product b,OrderMaster c,OrderDetail d
WHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo
and b.ProductNo=d.ProductNo and
EXISTS (
SELECT CustomerNo
FROM OrderMaster e,OrderDetail f
WHERE e.orderNo=f.orderNo and a.customerNo=e.customerNo
GROUP BY CustomerNo
HAVING count(distinct ProductNo)>=3)
GROUP BY a.CustomerNo,CustomerName,b.ProductNo,
ProductName,quantity

(8) 找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。
select a.employeeNo,sumorder
from Employee a,(select salerNo,sum(ordersum) sumorder from OrderMaster group by salerNo having sum(orderSum)>4000) b
where a.employeeNo=b.salerNo order by sumorder desc
这道题和之前上面的一道类似 ，做一个总销售额>4000的做一个查询表 然后将他们的业务员编号关联起来

(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。
select customerNo,productNo,sum(quantity) sumquantity,sum(price*quantity)/sum(quantity) avgprice
from OrderDetail a,OrderMaster b
where a.orderNo=b.orderNo
group by customerNO,productNo
order by customerNo,productNo asc
商品明细表里面是一个订单号里 购买的不同商品及其单价
maste表里是订单号 买家id 销售员id 以及 该订单的总花费

(10) 查询业绩最好的的业务员号、业务员名及其总销售金额。
select b.salerNo,a.employeeName,sum(orderSum)
from Employee a,OrderMaster b
where a.employeeNo=b.salerNo
group by b.salerNo,a.employeeName
having sum(orderSum)=
(select max(ordersum) from
(select salerNo,sum(orderSum) ordersum from OrderMaster group by salerNo) c

(11) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。
SELECT *
FROM OrderMaster a
WHERE not exists
(select *
from OrderDetail y
where orderNo=‘200803010001’ and not exists
(select *
from OrderDetail z
where y.productNo=z.productNo and
a.orderNo=z.orderNo))

(12) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。
SELECT a.customerNo,customerName,address
FROM OrderMaster a,Customer b
WHERE a.customerNo=b.customerNo
GROUP BY a.customerNo,customerName,address
HAVING sum(orderSum)>=(SELECT sum(orderSum)
FROM OrderMaster
WHERE customerNo=‘C20070002’
GROUP BY customerNo)

(13) 查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。
SELECT salerNo,b.orderNo,orderDate, orderSum
FROM Employee a,OrderMaster b
WHERE a.employeeNo=b.salerNo
and orderSum =(select max(orderSum)
from OrderMaster)

另一种解法：
select a.salerNo,a.orderNo,a.orderDate,a.orderSum
from OrderMaster a,( select salerNo,sum(orderSum)总订单金额
from OrderMaster
group by salerNo
having sum(orderSum)=(select max(ordersum1) from(
select y.salerno,sum(y.ordersum) ordersum1
from ordermaster y
group by y.salerno )c))b
where a.salerNo=b.salerNo

(14) 用存在量词查找没有订货记录的客户名称。
SELECT customerName
FROM Customer c
WHERE NOT EXISTS
(SELECT * FROM OrderMaster a
WHERE a.customerNo=c.customerNo )

(15) 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
Select customerNo,orderNo,orderSum
from OrderMaster
where customerNo in
(select customerNo
from OrderMaster a,OrderDetail b,Product c
where a.orderNo=b.orderNo and b.productNo=c.productNo and
productName=‘52倍速光驱’)
and customerNo in (select customerNo
from OrderMaster a,OrderDetail b,Product c
where a.orderNo=b.orderNo and
b.productNo=c.productNo and
productName=‘17寸显示器’)

(16) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。
SELECT customerNo,productNo,sum(quantity) quantitys,
(sum(quantity*price)/sum(quantity)) avgprice
FROM OrderMaster a,OrderDetail b
WHERE a.orderNo=b.orderNo
GROUP BY customerNo,productNo
ORDER BY customerNo,productNo

(17) 实验问题：
① 存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。
答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现；

② 请写出例2.51的执行过程。
答：1. 首先将表Employee a, OrderMaster b, OrderDetail c, Product d进行连接
2. 对连接后的记录，取出员工编号，判断是否至少销售了5种商品
3. 如果是，将salerNo, employeeName, productName, quantity, price这五个值作为输出结果
4．如果不是，舍弃该连接记录
5. 取下一条连接记录，转2，直到所有的连接记录处理完毕
6. 最后将结果输出

③ 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。
答：
相关子查询：
SELECT studentName,classNo
FROM Student x
WHERE EXISTS
(SELECT * FROM Score a,Course b
WHERE a.courseNo=b.courseNo
AND a.studentNo=x.studentNo AND courseName=’操作系统’）
非相关子查询：
SELECT studentNo,classNo
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Student
WHERE studentName=‘王红’)