数据库系统原理与设计(第二版)实验教程实验三

1. select top 1 orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo

order by orderSum desc

2. SELECT a.productNo,orderNo,quantity,(quantity*price) money FROM OrderDetail a,

(SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)>=3) b

WHERE a.productNo =b.productNo

ORDER BY a.productNo,quantity DESC

3. select a.employeeNo,a.employeeName,sum(quantity*price) 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(quantity*price)<5000

4. 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. 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 HA

VING sum(quantity)<10)

6. SELECT customerName,SUM(orderSum) AS totalPrice FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo GROUP BY customerName

HAVING SUM(orderSum)>=ALL( SELECT SUM(orderSum) FROM OrderMaster GROUP BY customerNo

)

7. 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

ORDER BY a.CustomerNo,sum DESC

8. SELECT employeeNo , sumOrder

FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder

FROM OrderMaster GROUP BY salerNo) b

WHERE a.employeeNo=b.salerNo AND sumOrder>40000

ORDER BY sumOrder DESC

9. 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

10. SELECT top 1 salerNo,employeeName,sum(quantity*price) SSUM FROM Employee a,OrderMaster x,OrderDetail y

WHERE x.orderNo=y.orderNo and a.employeeNo=x.salerNo GROUP BY salerNo,employeeName

ORDER BY

sum(quantity*price) DESC

11. 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. 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 top 1 salerNo,b.orderNo,orderDate, orderSum FROM Employee a,OrderMaster b,OrderDetail c

WHERE b.orderNo=c.orderNo and a.employeeNo=b.salerNo GROUP BY salerNo,b.orderNo,orderDate,orderSum ORDER BY

orderSum DESC

14. SELECT customerNo,customerName FROM Customer

WHERE NOT EXISTS( SELECT * FROM OrderMaster WHERE Customer.customerNo=OrderMaster.customerNo

)

15. 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) AS totalNum,SUM(quantity*price)/SUM(quantity) AS avgPrice

FROM OrderMaster,OrderDetail

WHERE OrderMaster.orderNo=OrderDetail.orderNo GROUP BY customerNo,productNo

ORDER BY customerNo,productNo

17

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

(3) 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。 相关子查询：

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=‘王红’)