復(fù)雜查詢(xún)參考答案.doc

《復(fù)雜查詢(xún)參考答案.doc》由會(huì)員分享，可在線閱讀，更多相關(guān)《復(fù)雜查詢(xún)參考答案.doc（5頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)教材實(shí)驗(yàn)答案 實(shí)驗(yàn)三 (1) 在訂單明細(xì)表中查詢(xún)訂單金額最高的訂單。 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) 找出至少被訂購(gòu)3次的商品編號(hào)、訂單編號(hào)、訂貨數(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) 查找銷(xiāo)售總額少于5000元的銷(xiāo)售員編號(hào)、姓名和銷(xiāo)售額。 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è)績(jī)未超過(guò)5000元的員工，并按銷(xiāo)售業(yè)績(jī)的降序排序輸出。 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) 查詢(xún)訂購(gòu)的商品數(shù)量沒(méi)有超過(guò)10個(gè)的客戶(hù)編號(hào)和客戶(hù)名稱(chēng)。 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) 查找訂貨金額最大的客戶(hù)名稱(chēng)和總貨款。 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) 查找至少訂購(gòu)了3種商品的客戶(hù)編號(hào)、客戶(hù)名稱(chēng)、商品編號(hào)、商品名稱(chēng)、數(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) 找出目前銷(xiāo)售業(yè)績(jī)超過(guò)4000元的業(yè)務(wù)員編號(hào)及銷(xiāo)售業(yè)績(jī)，并按銷(xiāo)售業(yè)績(jī)從大到小排序。 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) 求每位客戶(hù)訂購(gòu)的每種商品的總數(shù)量及平均單價(jià)，并按客戶(hù)號(hào)、商品號(hào)從小到大排列。 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) 查詢(xún)業(yè)績(jī)最好的的業(yè)務(wù)員號(hào)、業(yè)務(wù)員名及其總銷(xiāo)售金額。 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) 查詢(xún)訂購(gòu)的商品至少包含了訂單“200803010001”中所訂購(gòu)商品的訂單。 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) 查詢(xún)總訂購(gòu)金額超過(guò)“C20070002”客戶(hù)的總訂購(gòu)金額的客戶(hù)號(hào)、客戶(hù)名及其住址。 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) 查詢(xún)總銷(xiāo)售金額最高的銷(xiāo)售員編號(hào)、訂單編號(hào)、訂單日期和訂單金額。 SELECT salerNo,b.orderNo,orderDate, orderSum FROM Employee a,OrderMaster b WHERE a.employeeNo=b.salerNo and orderSum =(select max(orderSum) from OrderMaster) (14) 用存在量詞查找沒(méi)有訂貨記錄的客戶(hù)名稱(chēng)。 SELECT customerName FROM Customer c WHERE NOT EXISTS (SELECT * FROM OrderMaster a WHERE a.customerNo=c.customerNo ) (15) 查詢(xún)既訂購(gòu)了“52倍速光驅(qū)”商品，又訂購(gòu)了“17寸顯示器”商品的客戶(hù)編號(hào)、訂單編號(hào)和訂單金額。 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倍速光驅(qū)) 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) 求每位客戶(hù)訂購(gòu)的每種商品的總數(shù)量及平均單價(jià)，并按客戶(hù)號(hào)、商品號(hào)從小到大排列。 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) 實(shí)驗(yàn)問(wèn)題： ① 存在量詞與集合運(yùn)算IN、連接運(yùn)算和全稱(chēng)量詞之間的關(guān)系如何？它們可以互相替換嗎？給出你的理由。 答：存在量詞EXISTS可以用連接運(yùn)算或集合運(yùn)算IN來(lái)實(shí)現(xiàn)，而SQL中沒(méi)有全稱(chēng)量詞，只能用存在量詞和取非運(yùn)算來(lái)實(shí)現(xiàn)； ② 請(qǐng)寫(xiě)出例2.51的執(zhí)行過(guò)程。 [例2.51] 查詢(xún)至少銷(xiāo)售了5種商品的銷(xiāo)售員編號(hào)、姓名、商品名稱(chēng)、數(shù)量及相應(yīng)的單價(jià)，并按銷(xiāo)售員編號(hào)排序輸出。 分析： ① 構(gòu)造一個(gè)子查詢(xún)，針對(duì)外查詢(xún)中的每個(gè)銷(xiāo)售員，判斷其是否銷(xiāo)售了5種以上的商品，使用相關(guān)子查詢(xún)。 ② SQL語(yǔ)句為： 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進(jìn)行連接 2. 對(duì)連接后的記錄，取出員工編號(hào)，判斷是否至少銷(xiāo)售了5種商品 3. 如果是，將salerNo, employeeName, productName, quantity, price這五個(gè)值作為輸出結(jié)果 4．如果不是，舍棄該連接記錄 5. 取下一條連接記錄，轉(zhuǎn)2，直到所有的連接記錄處理完畢 6. 最后將結(jié)果輸出 ③ 存在量詞一般用在相關(guān)子查詢(xún)中，請(qǐng)分別給出存在量詞用在相關(guān)子查詢(xún)和非相關(guān)子查詢(xún)的查詢(xún)例子。 答： 相關(guān)子查詢(xún)： 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)’） 非相關(guān)子查詢(xún)： SELECT studentNo,classNo FROM Student WHERE NOT EXISTS (SELECT * FROM Student WHERE studentName=‘王紅’)