復雜查詢參考答案.doc

《復雜查詢參考答案.doc》由會員分享，可在線閱讀，更多相關《復雜查詢參考答案.doc（5頁珍藏版）》請在裝配圖網(wǎng)上搜索。

數(shù)據(jù)庫原理實驗教材實驗答案 實驗三 (1) 在訂單明細表中查詢訂單金額最高的訂單。 select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo having sum(quantity*price)= (select max(orderSum) from (select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo) b) (2) 找出至少被訂購3次的商品編號、訂單編號、訂貨數(shù)量和訂貨金額，并按訂貨數(shù)量的降序排序輸出。 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) 查找銷售總額少于5000元的銷售員編號、姓名和銷售額。 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) 找出目前業(yè)績未超過5000元的員工，并按銷售業(yè)績的降序排序輸出。 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) 查詢訂購的商品數(shù)量沒有超過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種商品的客戶編號、客戶名稱、商品編號、商品名稱、數(shù)量和金額。 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) 找出目前銷售業(yè)績超過4000元的業(yè)務員編號及銷售業(yè)績，并按銷售業(yè)績從大到小排序。 SELECT employeeNo , sumOrder FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder FROM OrderMaster GROUP BY salerNo) b WHERE a.employeeNo=b.salerNo AND sumOrder>4000 ORDER BY sumOrder DESC (9) 求每位客戶訂購的每種商品的總數(shù)量及平均單價，并按客戶號、商品號從小到大排列。 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) 查詢業(yè)績最好的的業(yè)務員號、業(yè)務員名及其總銷售金額。 SELECT salerNo,employeeName,sum(orderSum) FROM Employee a,OrderMaster b WHERE a.employeeNo=b.salerNo GROUP BY salerNo,employeeName having sum(orderSum) = (select max(orderSum) from (select sum(orderSum) orderSum from OrderMaster group by salerNo) x) (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) (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) 求每位客戶訂購的每種商品的總數(shù)量及平均單價，并按客戶號、商品號從小到大排列。 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來實現(xiàn)，而SQL中沒有全稱量詞，只能用存在量詞和取非運算來實現(xiàn)； ② 請寫出例2.51的執(zhí)行過程。 [例2.51] 查詢至少銷售了5種商品的銷售員編號、姓名、商品名稱、數(shù)量及相應的單價，并按銷售員編號排序輸出。 分析： ① 構造一個子查詢，針對外查詢中的每個銷售員，判斷其是否銷售了5種以上的商品，使用相關子查詢。 ② SQL語句為： SELECT salerNo, employeeName, productName, quantity, price FROM Employee a, OrderMaster b, OrderDetail c, Product d WHERE a.employeeNo=salerNo AND b.orderNo=c.orderNo AND c.productNo=d.productNo AND EXISTS( SELECT salerNo FROM OrderMaster e, OrderDetail f WHERE e.orderNo=f.orderNo AND a.employeeNo=salerNo GROUP BY salerNo HAVING count(distinct productNo )>=5 ) ORDER BY salerNo 答：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=’操作系統(tǒng)’） 非相關子查詢： SELECT studentNo,classNo FROM Student WHERE NOT EXISTS (SELECT * FROM Student WHERE studentName=‘王紅’)