數(shù)據(jù)庫(kù)系統(tǒng)原理設(shè)計(jì)實(shí)驗(yàn)教程實(shí)驗(yàn)2詳解

1、實(shí)驗(yàn)二題目(1) 查找有銷售記錄的客戶編號(hào)、名稱和訂單總額。select a.customerNo,a.customerName.b.orderNo,sum(quantity*price) from Customer a.OrderMaster bOrderDetail cwhere a.customerNo=b.custonierNo and b.orderNo=c.orderNogroup by a.customerNo,a.customerName.b.orderNocust ojne rNocustomerNameorde rNo（無(wú)列名）L C20050001統(tǒng)一股份有限公司2008

2、010900014600. 002C20050004五一商廈2008010900024046.003C20080001紅度股份有限公司2008010900031700. 004C20050001統(tǒng)一股份有限公司2008021900015786.005C20070002世界技術(shù)開(kāi)發(fā)公司2008021900022270. 006C20070002世界枝術(shù)開(kāi)發(fā)公司2008030100011800. 00T C20050004五一商廈2008030200011000. 008C20070003萬(wàn)萼達(dá)股份有限公司2008030900015316.009C20060002聯(lián)合股份有限公司200805090

3、0017100.0010 C20050001統(tǒng)一股份有限公司2008061200013860. 00(2) 在訂單明細(xì)表中査詢訂單金額最髙的訂單。 select top 1 orderNo,sum(quantity*price) orderSum from OrderDctail group by ordcrNo order by orderSum desc1orderWoorderSum2008050900017100.00或：select orderNo,sum(quantity*price) orderSumfrom OrderDctailgroup by orderNohaving s

4、um(quantity*price)=(select max(orderSum)from (select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo) b)(3) 查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。SELECT a.customerNo,customerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNoFROM OrderMaster)1customerNocust omerNajueC20050002興隆胺份有限公司

5、2C20050003上海生物研究室3C20060001犬地商城4C20070001南昌市電腦研制中心(3)找岀至少被訂購(gòu)3次的商品編號(hào)、訂單編號(hào)、訂貨數(shù)量和訂貨金額，并按訂貨數(shù)量 的降序排序輸出。SELECT ductNo,orderNo.quantityXquantity*price) moneyFROM OrderDetail a,(SELECT productNoFROM OrderDetailGROUP BY productNoHAVING count(*)=3) bWHERE ductNo =ductNoORDER BY ductNo.quan

6、tity DESC1productNoorderNoquantity moneyP2005000120080301000181200. 002P2005000120080109000152500. 003P200500012008010900035650. 004P200500012008030200012200. 005P200500032008030900014800. 006P200500032008021900022320. 007P200500032008010900012600. 008P2007000120080509000142000. 009P2007000120080301

7、00014600. 0010P2007000120080219000231500. 0011P200700012008030500012316.0012P200700012008021900012316.0013P2007000220080219000151250. 0014P2007000220080309000151900. 0015P2007000220080509000121200. 0016P2007000320080509000151500. 0017P200700032008030200013600. 0018P2007000320080219000131050. 00(4) 使

8、用子?xùn)嗽儾檎?6M DRAM的銷售情況，要求顯示相應(yīng)的銷售員的姓名、性別. 銷售日期、銷售數(shù)量和金額，其中性別用男”、“女”表示。SELECT employeeNamexase sexwhen F then 女when then 男end sex,orderDate,quantity,quantity*price 金額FROM Employee a.OrderMaster b,OrderDetail cWHERE a.employeeNo=b.saIerNo AND b.orderNo=c.orderNoAND c.ProductNo IN (SELECT f.ProductNoFROM Or

9、derMaster d.OrderDetail e,Product fWHERE d.orderNo=e.orderNo AND ProductName=32M DRAM)13quairtity 丟額梅梅杰瑩 外小詩(shī)應(yīng) 張張陳黃2008-01-09 00:00:00.00052500. 002008-01-09 00:00:00. 0005650.002008-03-01 00:00:00.00081200. 002008-03-02 00:00:00.0002200.00女女男女employeeName sex orderDate(5) 查詢OrderMaster表中訂單金額最髙的訂單號(hào)及訂

10、單金額。 select orderNo,orderSumfrom OrderMasterwhere orderSum = (select max(orderSum)from OrderMaster)1orderNoorderSum2008050900017100.00(6)計(jì)算岀一共銷售了幾種商品。 select count(distinct productNo) from OrderDetail（無(wú)列名）115(7) 顯示OrderDetail表中每種商品的訂購(gòu)金額總和，并且依拯銷售金額由大到小排序輸 出。select productNo,sum(quantity*price) sumnio

11、neyfrom OrderDetailgroup by productNoorder by summoney descproductNo sujTononey1P200700014732. 002P200500014550. 003P200700024350. 004P200600033480. 005P200700033150.006P200500042450. 007P200500052250. 008P200800021860. 009P200600021810.0010P200500031720. 0011P200800011720. 0012P200700041710.0013P20

12、0500021700. 0014P200600011350. 0015P20080003646.00(8) 查找銷售總額大于1000元的銷售員編號(hào)、姓名和銷售額。 select a.employeeNo,a.employeeNanie,sum(quantity*price) sunmoney from Employee a.OrderDctail b.OrderMaster cwhere a.employeeNo=c.salerNo and b.orderNo=c.orderNogroup by a.employeeNo,a.eniployeeNamehaving sum(quantity*p

13、rice) 10001employe Hoemploye eNamesurinioneyE2005002張小梅10160.002E2005003張小娟9832.003E2008001陳詩(shī)杰1800.004E2008002張良9370.005E2008004李虹冰5316.00(9) 找岀目前業(yè)績(jī)未超過(guò)5000元的員工，并按銷售業(yè)績(jī)的降序排序輸出。 select employeeNoxmpIoyeeName.orderSumfrom Employee a,(select salerNo,suni(orderSum) orderSumfrom OrderMastergroup by salerNo

14、having sum(orderSum) ( SELECT avg(salary) avgsalaryFROM Employee)enpLoyeeKo sployeeKaac bmhdayaddresshireDotedepagent hndShip “gyiB2CO5OT1Oft自遽yLWW4-15 00:00:00.000料京市訝冷箔】8號(hào)3517600005 1990-0200 00:00:00. OT0WOO 502E2CO5COaFL967-01-C6 00:00:00.000巾八一九謹(jǐn) 13垮159072061341990-03-28 00:00:00.0004100.00JE2C

15、0600I陰紆yIP65-U-O 00:00:00.0001083)00】36071053521930-03-28 00:00:00.0004COO. 00aR2CO0CO1陳詩(shī)asy00：00：00.000 口西阱乞穴朱】2竹43W妄MVUly9C-12-oe 00:00:00. OOQ3200. 005E2W9OO初冰71972-10-13 00x00:00.000 貳二市中山爲(wèi) 1 號(hào)mi1990-05-28 00:00:00. COO3400.0C8E2008005F197O-U-C6 00:00:00.000 尿II市陽(yáng)關(guān)劉丈10弓MULL19P0-H-18 00:00:00.00D

16、5000.00(11) 計(jì)算每一種商品的銷售數(shù)量、平均銷售單價(jià)和總銷售金額。SELECT ProductNo,sum(quantity)數(shù)量,avg(pricc)平均單價(jià),sum(price*quantity)總額 FROM OrderDetailGROUP BY ProductNo1ProductNo數(shù)童平均單價(jià)總額P2005000120220.0000004550.002P200500024350.0000001700.003P200500038220.0000001720.004P200500047425.0000002450.005P200500056375.0000002250.00

17、6P200600014325.0000001350.007P200600027265.0000001810.008P2006000312285.0000003480.009P2007000115293.2000004732.0010P2007000212410.0000004350.0011P2007000311283.3333333150.0012P200700045340.0000001710.0013P200800017220.0000001720.0014P200800027265.0000001860.0015P200800033244.000000646. 00(12) 查找至少有

18、3次銷售的業(yè)務(wù)員需單和銷售日期。SELECT cniployeeName .orderDateFROM Employee a.OrderMaster b,(SELECT salerNoFROM OrderMasterGROUP BY salerNoHAVING count(*)=3) cWHERE a.employeeNo =b.salerNo AND b.salerNo =c.salerNo1orderDat e張小梅2008-01-09 00:00:00.0002張小梅2008-01-09 00:00:00.0003張小梅2008-06-12 00:00:00.000(13) 用存在量詞查

19、找沒(méi)有訂貨記錄的客戶需稱。SELECT customerNanieFROM Customer cWHERE NOT EXISTS(SELECT * FROM OrderMaster aWHERE a.customcrNo=c.customerNo )1cust ojaerNane興隆股份有限公司2上海生物研究室3丈地商城4南昌市電腦研制中心(14) 查詢訂單中所訂購(gòu)的商品數(shù)量沒(méi)有超過(guò)10個(gè)的客戶編號(hào)和客戶需稱。SELECT a.CustomcrNo.CustomerNameFROM Customer aWHERE a.CustomerNo IN (SELECT CustomerNoFROM O

20、rderMaster b.OrdcrDetail cWHERE b.orderNo=c.orderNoGROUP BY CustomerNoHAVING sum(quantity)8000cust omerNo(18) 顯示每種商品的銷售金額總和，并依銷售金額由大到小輸出。SELECT productNo 商品編 #sum(quantity*price)1 銷售金額, FROM OrderDetailGROUP BY productNoORDER BY sum(quantity*price)I商品編號(hào)銷售金額1P20080003646. 002P200600011350. 003P200500

21、021700. 004P200700041710. 005P200800011720. 006P200500031720. 007P200600021810. 008P200800021860. 009P200500052250. 0010P200500042450. 0011P200700033150. 0012P200600033480. 0013P20070002 4350. 0014P200500014550. 0015P200700014732. 00(19) 查找銷售金額最大的客戶名稱和總貨款。SELECT customerNanie ,sum(orderSum)FROM Orde

22、rMaster a.Customer bWHERE a.customerNo=b.customerNoGROUP BY a.customerNoxustomerNameHAVING sum(orderSum)=(SELECT max(orderSum)FROM(SELECT customerNo,suni(orderSum) orderSumFROM OrderMasterGROUP BY customerNo)c)cust omerNane（無(wú)列名）1統(tǒng)-股份有限公司14246. 00(20) 查找至少銷售了 3種商品的客戶編號(hào)、客戶名稱.商品編號(hào).商品名稱、數(shù)量和 金額。SELECT a.

23、CustomerNoCustomerName.b.ProductNo,ProductName,quantity,sum(quantity*price) sumFROM Customer a.Product b.OrderMaster c,OrderDetail dWHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.ordcrNoand b.ProductNo=d.ProductNo andEXISTS (SELECT CustomerNoFROM OrderMaster e.OrderDetail fWHERE e.ordcrNo=f.orderNo

24、 and a.customerNo=c.customerNoGROUP BY CustomerNoHAVING count(distinct ProductNo)=3)GROUP BY a.CustomerNo.CustomerName.b.ProductNo,ProductName,quantityORDER BY a.CustomerNo,sum DESCCust oinerNoCustomerWaneProductNo ProductNamequantitysum1C20050001統(tǒng)一臉?lè)萦邢薰綪2005000132M :DRAM52500. 002C20050001統(tǒng)一般份有限公司

25、P20050005謹(jǐn)盤(pán)31800. 003C20050001統(tǒng)一臉?lè)萦邢薰綪2005000217寸顯示器31500. 004C20050001統(tǒng)一股份有限公司P200T000252倍速光駆51250. 005C20050001統(tǒng)一股份有限公司P200500043. 5寸軟驅(qū)21200. 006C20050001統(tǒng)一股份有限公司P20060003PentiujulOOCPTJ41080. 001C20050001統(tǒng)一股份有限公司P20070003計(jì)算機(jī)宇典31050. 008C20050001統(tǒng)一般份有限公司P20080002碩泰克SLK8AN-RL主板3780. 009C20050001統(tǒng)一

26、股份有限公司P200700049600bit t/siJD制解調(diào)2660. 0010C20050001統(tǒng)一般份有限公司P20050003120G：B硬盤(pán)2600. 0011C20050001統(tǒng)一般份有限公司P20060002網(wǎng)卡2560. 0012C20050001統(tǒng)一臉?lè)萦邢薰綪20080003龍基777FT純平顯示器1330. 0013C20050001統(tǒng)一般份有限公司P20080001Pent iui注板2320. 0014C20050001統(tǒng)一股份有限公司P2007000L1G DDR2316-0015C20050001統(tǒng)一股份有限公司P20060001EGA顯示卡1300. 001

27、6C20050004五一葫廈P20080001Pent iwr主板51400. 0017C20050004五一商廈P20060002網(wǎng)卡51250. 0018C20050004五一商廈P20080002碩泰克SLK8AN-RL主板41080. 0019C20050004壬一商廈P20070003計(jì)算機(jī)字典3600. 0020C20050004五一商廈P20080003龍基777FT純平顯示戢2316. 0021C20050004五一商廈P2005000217寸顯示蠱1200. 0022C20050004五一商廈P2005000132M DRAM2200. 0023C20060002聯(lián)臺(tái)股檜有限

28、公司P20060003PentiwnlOOCPU82400. 0024C20060002聯(lián)合臉?lè)萦邢薰綪200700011G DDR42000.0025C20060002聯(lián)合般份有限公司P20070003計(jì)算機(jī)字典51500.0026C20060002聯(lián)合股份有限公司P20070Q025 2佶速光駆21200.0021C20070002世界捜術(shù)開(kāi)發(fā)公司P200700011G DDR31500.0028C20070002世界拽術(shù)開(kāi)發(fā)公司P2005000132N DRAM81200.0029C20070002世界技術(shù)開(kāi)發(fā)公司P200700011G DDR4600. 0030C20070Q02世界

29、技術(shù)開(kāi)發(fā)公司P20050005鍵盤(pán)3450. 0031C20070002世界技術(shù)開(kāi)發(fā)公司P2005000312OGB硬盤(pán)2320. 0032C20070003萬(wàn)事達(dá)股份有限公司P200700025 2倍速光驅(qū)51900.0033C20070003萬(wàn)事達(dá)股份有限公司P2005000435寸軟驅(qū)51250.0034C20070003萬(wàn)事達(dá)股份有限公司P200700049600bits/s調(diào)制解調(diào)31050.0035C20070003萬(wàn)事達(dá)股份有限公司P2005000312OGB硬盤(pán)4800. 0036C20070003萬(wàn)事達(dá)股份有限公司P200700011G DDR2316. 00(21) 找岀目

30、前業(yè)績(jī)超過(guò)232000元的員工編號(hào)和姓需。SELECT employeeNamc, sumOrderFROM Employee a.(SELECT salerNo,sum(orderSum) sumOrderFROM OrdcrMaster GROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sum0rdcr232000employe&Najw sumOrder(22) 找出目前銷售業(yè)績(jī)超過(guò)40000元的業(yè)務(wù)員編號(hào)及銷售業(yè)績(jī)，并按銷售業(yè)績(jī)從大到 小排序。SELECT employeeNo, sumOrderFROM Employee a,(S

31、ELECT salerNo,sum(ordcrSum) sumOrderFROM OrdcrMasterGROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sum0rder40000ORDER BY sumOrder DESC(23) 求出每位客戶的總訂購(gòu)金額，顯示出客戶號(hào)及總訂購(gòu)金額，并按總訂購(gòu)金額降序 排列。SELECT customerNo ,sum(orderSum)FROM OrderMasterGROUP BY customerNoORDER BY sum(orderSum) DESC1oust omerNo無(wú)列名）C200500

32、0114246. 002C200600027100. 003C200700035316. 004C200500045046. 005C200700024070. 006C200800011700. 00(24) 求每位客戶訂購(gòu)的每種商品的總數(shù)量及平均單價(jià)，并按客戶號(hào).商品號(hào)從小到大 排列。SELECT customcrNo,productNo,sum(quantity) quantitys,(suni(quantity*price)/suin(quantity) avgpriceFROM OrderMaster a.OrdcrDetail bWHERE a.orderNo=b.orderNoG

33、ROUP BY customcrNductNoORDER BY customerNo,productNo1cust omerNoproductNoquantitysavgpriceC20050001P200500015500.0000002C20050001P200500023500.0000003C20050001P200500032300. 0000004C20050001P200500042600.0000005C20050001P200500053600.0000006C20050001P200600011300.0000007C20050001P200600022280.0

34、000008C20050001P200S00034270.0000009C20050001P200T00012158. 00000010C20050001P200T00025250.000000liC20050001P200T00033350.00000012C20050001P200700042330.00000013C20050001P200800012160.00000014C20050001P200800023260.00000015C20050001P200800031330. 00000016C20050004P200500012100.00000017C20050004P2005

35、00021200.00000018C20050004P200600025250.00000019C20050004P200700033200.00000020C20050004P200800015280. 00000021C20050004P200800024270.00000022 C20050004P200800032158.00000023 C20060002P200600038300.00000024 C20060002P200700014500.00000025 C20060002aP200700022600.00000026 C20060002P200700035300.00000

36、027 C20070002P200500018150.00000028 C20070002P200500032160.00000029 C20070002P200500053150.00000030 C20070002P200700017300.00000031C20070003P200500034200.00000032 C20070003P200500045250.00000033 C20070003P200700012158.00000034 C20070003P200700025380.00000035 C20070003P200700043350.00000036 C20080001

37、P200500015130.0000003T C20080001P200600013350.000000(25) 查詢業(yè)績(jī)最好的的業(yè)務(wù)員號(hào)、業(yè)務(wù)員需及其總銷售金額。SELECT top 1 salerNoxmployeeNanie,sum(quantity*price)FROM Employee a.OrderMaster x.OrderDetail yWHERE x.orderNo=y.orderNo and a.employeeNo=x.salerNoGROUP BY salerNoxmployeeNameORDER BY sum(quantity*price) DESC1salerNoe

38、mploye eName (無(wú)列名)E2005002張小梅10160.00或：SELECT salerNo.empIoyeeName.sum(orderSum)FROM Employee a.OrderMaster bWHERE a.employeeNo=b.salerNoGROUP BY salerNoxmployeeNamehaving sum(orderSum) = (select max(orderSum)from (select sum(orderSum) orderSumfrom OrdcrMastergroup by salerNo) x)(26) 查詢訂購(gòu)的商品至少包含了訂單“

39、200803010001”中所訂購(gòu)商品的訂單。SELECT *FROM OrdcrMaster aWHERE not exists(select *from OrderDetail ywhere orderNo=20080301000 V and not exists(select *from OrderDetail zwhere ductNo=ductNo anda.orderNo=z.orderNo)1orderNocust onerNosalerNoorderDate：orderSmitwoiceNo200803010001 C20070002E20080012008

40、-03-01 00:00:00.0001800. 001000000006(27)求每種商品的總銷售數(shù)量及總銷售金額，要求顯示出商品編號(hào).商品名稱、總數(shù)量及總金額，并按商品號(hào)從小到大排列。SELECT ductNo 商品編號(hào)productName 商品名稱；qlySum，總數(shù)気,totalSum 總金 額FROM Product a,(SELECT productNo.sum(quantity) qtySum,sum(quantity*price) totalSumFROM OrderDetailGROUP BY productNo)bWHERE ductNo=d

41、uctNoORDER BY ductNo1商品編號(hào)商品名稱總數(shù)量|總金額P2005000132M DM204550. 002P2005000217寸顯示器41700. 003P20050003120GB硬盤(pán)81720. 004P200500043. 5寸軟驅(qū)72450. 005P20050005擁盤(pán)62250. 006P20060001GA顯示卡41350. 007P20060002網(wǎng)卡71810.008P20060003PentiumlOOCPU123480. 009P200700011G DDR154732. 0010P2007000252倍速光驅(qū)124350. 00iiP200

42、70003計(jì)算機(jī)字典113150.0012P200700049600bits/siig 制解調(diào)51710.0013P20080001Pentiruni 板71720. 0014P20080002碩泰克SL-K8M-RL主板71860. 0015P20080003龍S777FT純平顯示器3646.00(28) 查詢總訂購(gòu)金額超過(guò)“C2OO7OOO2”客戶的總訂購(gòu)金額的客戶號(hào)、客戶名及其住址。SELECT a.customerNo,customerName.addressFROM OrderMaster a,Customer bWHERE a.customcrNo=b.customerNoGROU

43、P BY a.customerNo,custonierNanie,addressHAVING sum(orderSum)=(SELECT sum(ordcrSum)FROM OrderMasterWHERE customerNo=,C20070002,GROUP BY customerNo)custojnerNo custonterNameaddress.n .n 津海海海津 天上上上天1 C20050001統(tǒng)一股份有限公司2 C20050004 五一商廈3 C20060002聯(lián)臺(tái)股份有限公司4 C20070002世界技術(shù)開(kāi)發(fā)公司5 C20070003萬(wàn)事達(dá)股份有限公司(29) 査詢銷售金額最

44、髙的銷售員編號(hào)、訂單編號(hào).訂單日期和訂單金額。SELECT top 1 salerNo,b.orderNo.orderDate, orderSumFROM Employee a.OrderMaster b.OrderDetail cWHERE b.ordcrNo=c.orderNo and a.employeeNo=b.salerNoGROUP BY salerNo,b.orderNo,orderDate,orderSumORDER BY orderSum DESC1salerNoorderNoorderDaieorderSwuE2008002 2008050900012008-05-09 0

45、0:00:00. 0007100.00或：SELECT salerNo,b.orderNo,orderDate, orderSumFROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNoand orderSum =(select max(orderSum)from OrderMaster)(30) 實(shí)驗(yàn)問(wèn)題 存在量詞與集合運(yùn)算in、連接運(yùn)算和全稱量詞之間的關(guān)系如何？它們可以互相替 換嗎？給出你的理由。答：存在量詞EXISTS可以用連接運(yùn)算或集合運(yùn)算IN來(lái)實(shí)現(xiàn)，而SQL中沒(méi)有全稱 量詞，只能用存在量詞和取非運(yùn)算來(lái)實(shí)現(xiàn)： 給岀SQL語(yǔ)句實(shí)現(xiàn)分組聚集操作的執(zhí)行過(guò)程。答：1,首先執(zhí)ft FROM語(yǔ)句，查找到各表數(shù)據(jù)2, 苴次執(zhí)行WHERE語(yǔ)句，對(duì)數(shù)據(jù)進(jìn)行篩選以及表與表的連接3, 然后執(zhí)行GROUP BY語(yǔ)句，將數(shù)據(jù)劃分組4, 使用聚集函數(shù)進(jìn)行計(jì)算5, 使用HAVING語(yǔ)句進(jìn)行