數(shù)據(jù)庫系統(tǒng)原理和設計（第二版）實驗一至實驗三

...wd......wd......wd...實驗一1-1.查詢員工的姓名、職務和薪水selectemployeeName,headShip,salaryfromemployee圖1-12.查詢名字中含有“有限〞的客戶姓名和所在地selectCustomerName,addressfromCustomerwhereCustomerNamelike'%有限%'圖1-23.查詢出姓“張〞并且姓名的最后一個字為“梅〞的員工。select*fromemployeewhereemployeeNamelike'張%梅'圖1-34.查詢住址中含有上?；蚰喜呐畣T工，并顯示其姓名、所屬部門、職稱、住址，其中性別用“男〞和“女〞顯示SELECTemployeeName,department,address, isnull(convert(char(10),birthday,120),'不詳')出生日期, casesexwhen'M'then'男' when'F'then'女' endas性別fromemployeewhere〔addresslike'%上海%'oraddresslike'%南昌%'〕andsex='F'圖1-45.查詢出職務為“職員〞或職務為“科長〞的女員工的信息select*fromemployeewhere(headship='職員'orheadship='科長')andsex='F'圖1-56.選取編號不在“C20050001”和“CSelect*fromCustomerwhereCustomerNonotin('C20050001','C20050004')圖1-6圖1-67.在訂單明細表Ordermaster中挑出銷售金額大于等于5000元的訂單。updateordermastersetordersum=sum2fromordermastera,(selectorderno,sum(quantity*price)sum2fromorderdetailgroupbyorderno)bwherea.orderno=b.ordernoSelect*FromordermasterWhereordersum>=’5000’圖1-78.選取訂單金額最高的前10%的訂單數(shù)據(jù)SELECTTOP10PERCENT*fromorderdetailorderbypriceDESC圖1-89.計算一共銷售了幾種商品SELECTCOUNT(DISTINCTproductno)as種類fromorderDeta圖1-910.計算orderDetail表中每種商品的銷售數(shù)量、平均價格和總銷售量金額，并且依據(jù)銷售金額由大到小輸出。SELECTproductno商品種類,count(*)quantity,avg(price)平均價格,sum(quantity*price)金額fromorderDetailgroupbyproductnoorderby金額desc圖1-1011.按客戶編號統(tǒng)計每個客戶2008年2月的訂單總金額。selectcustomerno,ordersumfromordermasterwhereyear(orderDate)=2008andmonth(orderDate)=2圖1-1112.統(tǒng)計至少銷售了10件以上的商品編號和銷售數(shù)量。selectproductno商品編號,quantity商品數(shù)目fromorderdetailwherequantity>=10圖1-1213.統(tǒng)計在業(yè)務科工作且在1973年或1967年出生的員工人數(shù)和平均工資selectcount(*)人數(shù),avg(salary)平均工資fromEmployeewheredepartment='業(yè)務科'and(year(birthday)=1973oryear(birthday)=1967)圖1-13實驗二找出同一天進入公司工作的員工selectdistincta.employeeNo,a.employeeName,a.hireDatefromEmployeea,Employeeb

wherea.employeeNo!=b.employeeNoanda.hireDate=b.hireDate圖2-1查找與“陳詩杰〞在同一個單位工作的員工姓名、性別、部門和職務selecta.employeeName,a.sex,a.department,a.headShipfromEmployeea,Employeebwherea.department=b.departmentandb.employeeName='陳詩杰'圖2-2在employee表中查詢薪水超過員工平均薪水的員工信息select*fromEmployeeawherea.salary>(selectavg(b.salary)fromEmployeeb)圖2-3查找有銷售記錄的客戶編號名稱和訂單總額selecta.customerNo,a.customerName,b.orderNo,sum(quantity*price)orderSumfromCustomera,OrderMasterb,OrderDetailcwherea.customerNo=b.customerNoandb.orderNo=c.orderNogroupbya.customerNo,a.customerName,b.orderNo圖2-45.查詢沒有訂購商品的客戶編號和客戶名稱

SELECTa.customerNo,customerNameFROMCustomeraWHEREa.customerNoNOTIN(SELECTcustomerNoFROMOrderMaster)圖2-56.使用子查詢查找32MDRAM的銷售情況要求顯示相應的銷售員的姓名、性別、銷售日期銷售數(shù)量和金額其中性別用“男〞和“女〞表示selectemployeeName,casesexwhen'M'then'男'when'F'then'女'endassex,b.orderDate,c.quantity銷售數(shù)量,c.quantity*c.price金額fromEmployeea,OrderMasterb,OrderDetailcwherea.employeeNo=b.salerNoandb.orderNo=c.orderNoandductNoin(selectductNofromOrderMasterd,OrderDetaile,Productfwhered.orderNo=e.orderNoandproductName='32MDRAM')圖2-67.查詢OrderMaster表中訂單金額最高的訂單號及訂單金額selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from(selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNo)b)圖2-78.在訂單主表中查詢訂單金額大于“E2005002業(yè)務員在2008-1-9這天所接的任一張訂單的金額〞的所有訂單信息。select*fromOrderMasterwhereorderSum>any(selectorderSumfromOrderMasterwheresalerNo='E2005002'andorderDate='2008-1-9')圖2-89.查詢單價高于400元的商品編號商品名稱訂貨數(shù)量和訂貨單價。selectductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.pricefromProducta,OrderDetailbwhereductPrice>400andductNo=ductNogroupbyductNo,ductName,b.price圖2-910.分別使用左外連接、右外連接、完整外部連接查詢單價高于400元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價并分析比擬檢索的結(jié)果。selectductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.pricefromProductaleftouterjoinOrderDetailbonductPrice>400andductNo=ductNogroupbyductNo,ductName,b.priceselectductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.pricefromProductarightouterjoinOrderDetailbonductPrice>400andductNo=ductNogroupbyductNo,ductName,b.priceselectductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.pricefromProductafullouterjoinOrderDetailbonductPrice>400andductNo=ductNogroupbyductNo,ductName,b.price圖2-1011.使用左外連接查找每個客戶的客戶編號、名稱、訂貨日期、訂單金額、其中訂貨日期不顯示時間日期格式為yyyy-mm-dd按客戶編號排序同一客戶再按訂單金額降序排序輸出。selecta.customerno客戶編號,customername客戶名稱,convert(char(10),orderdate,120)銷售日期,ordersum銷售金額fromordermasteraleftouterjoincustomerbon(a.customerno=b.customerno)orderbya.customerno,ordersumdesc12.查找每個員工的銷售記錄要求顯示銷售員的編號、姓名、性別、商品名稱、數(shù)量、單價、金額和銷售日期其中性別使用“男〞和“女〞表示日期使用yyyy-mm-dd格式顯示。selecta.employeeNo,a.employeeName,casesexwhen'F'then'女'when'M'then'男'Endsex,ductName,d.quantity,d.price,d.quantity*d.price金額,orderDate=convert(char(10),orderDate,120)fromEmployeea,Productb,OrderMasterc,OrderDetaildwherea.employeeNo=c.salerNoandductNo=ductNoandc.orderNo=d.orderNo圖2-1213.查詢16MDRAM的銷售情況要求顯示相應的銷售員的姓名、性別、銷售日期、銷售數(shù)量和金額、其中性別用“男〞“女〞表示。selecta.employeeName,casesexwhen'F'then'女'when'M'then'男'endassex,b.orderDate,c.quantity,c.price*c.quantity金額fromEmployeea,OrderMasterb,OrderDetailc,Productdwherea.employeeNo=b.salerNoandb.orderNo=c.orderNoandductNo=ductNoandductName='16MDRAM'

圖2-1314.找出公司男業(yè)務員所接且訂單金額超過2000的訂單號及訂單金額。selectb.orderNo,b.orderSumfromEmployeea,OrderMasterbwherea.employeeNo=b.salerNoandsex='M'andb.orderSum>2000圖2-1415.查詢每種商品的總銷售數(shù)量及總銷售金額要求顯示出商品編號、商品名稱、總數(shù)量及總金額，并按商品號從小到大排列。selectductno商品編號,productname商品稱,sum(quantity)總銷售數(shù)量,sum(quantity*price)總銷售金額fromproducta,orderdetailbwhereductno=ductnogroupbyductno,productnameorderbyductno圖2-15實驗三在訂單明細表中查詢訂單金額最高的訂單。selecttop1orderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNoorderbyorderSumdesc圖3-13.查找銷售總額少于5000元的銷售員編號、姓名和銷售額。selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoneyfromEmployeea,OrderDetailb,OrderMastercwherea.employeeNo=c.salerNoandb.orderNo=c.orderNogroupbya.employeeNo,a.employeeNamehavingsum(quantity*price)<5000圖3-3查詢訂單中所訂購的商品數(shù)量沒有超過個的客戶編號和客戶名稱。SELECTa.CustomerNo,CustomerNameFROMCustomeraWHEREa.CustomerNoIN( SELECTCustomerNo FROMOrderMasterb,OrderDetailc WHEREb.orderNo=c.orderNo GROUPBYCustomerNo HAVINGsum(quantity)<10)圖3-57.查找至少銷售了3種商品的客戶編號、客戶名稱、商品編號、商品名稱、數(shù)量和金額。SELECTa.CustomerNo,CustomerName,b.ProductNo,ProductName,quantity,sum(quantity*price)sumFROMCustomera,Productb,OrderMasterc,OrderDetaildWHEREa.CustomerNo=c.CustomerNoandc.orderNo=d.orderNoandb.ProductNo=d.ProductNoandEXISTS(SELECTCustomerNoFROMOrderMastere,OrderDetailfWHEREe.orderNo=f.orderNoanda.customerNo=e.customerNoGROUPBYCustomerNoHAVINGcount(distinctProductNo)>=3)GROUPBYa.CustomerNo,CustomerName,b.ProductNo,ProductName,quantityORDERBYa.CustomerNo,sumDESC圖3-79.求每位客戶訂購的每種商品的總數(shù)量及平均單價，并按客戶號、商品號從小到大排列。SELECTcustomerNo,productNo,sum(quantity)quantitys,(sum(quantity*price)/sum(quantity))avgpriceFROMOrderMastera,OrderDetailbWHEREa.orderNo=b.orderNoGROUPBYcustomerNo,productNoORDERBYcustomerNo,productNo圖3-911.查詢訂購的商品至少包含了訂單“200803010001”SELECT*FROMOrderMasteraWHEREnotexists(select*f