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

实验一1：1.selectemployeeName,headship,salaryfromEmployee2selectCustomerName,addressfromCustomerwherecustomerNamelike'%有限%'3select*fromemployeewhereemployeeNamelike'张%梅'4SELECTemployeeName,department,address,isnull(convert(char(10),birthday,120),'不详')出生日期,casesexwhen'M'then'男'when'F'then'女'endas性别fromemployeewhere(addresslike'%上海%'oraddresslike'%南昌%')andsex='F'5select*fromemployeewhere(headship='职员'orheadship='科长')andsex='F'6Select*fromCustomerwhereCustomerNonotin('C20050001','C20050004')7updateordermastersetordersum=sum2fromordermastera,(selectorderno,sum(quantity*price)sum2fromorderdetailgroupbyorderno)bwherea.orderno=b.ordernoSelect*FromordermasterWhereordersum='5000'8SELECTTOP10PERCENT*fromorderdetailorderbypriceDESC实验二1selecta.employeeNo,a.employeeName,a.hireDatefromemployeea,Employeebwherea.employeeNo!=b.employeeNoanda.employeeName!=b.employeeNameanda.hiredate=b.hireDateorderbyb.employeeName2selecta.employeeName,a.sex,a.department,a.headShipfromEmployeea,Employeebwherea.department=b.departmentandb.employeeName='陈诗杰'3．select*fromEmployeeawherea.salary(selectavg(b.salary)fromEmployeeb)4selecta.customerNo,a.customerName,b.orderNo,sum(quantity*price)orderSumfromCustomera,OrderMasterb,OrderDetailcwherea.customerNo=b.customerNoandb.orderNo=c.orderNogroupbya.customerNo,a.customerName,b.orderNo5SELECTa.customerNo,customerNameFROMCustomeraWHEREa.customerNoNOTIN(SELECTcustomerNoFROMOrderMaster)6．selectemployeeName,casesexwhen'M'then'男'when'F'then'女'endassex,b.orderDate,c.quantity销售数量,c.quantity*c.price金额fromEmployeea,OrderMasterb,OrderDetailcwherea.employeeNo=b.salerNoandb.orderNo=c.orderNoandc.productNoin(selectf.productNofromOrderMasterd,OrderDetaile,Productfwhered.orderNo=e.orderNoandproductName='32MDRAM')7selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from(selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNo)b)8select*fromOrderMasterwhereorderSumany(selectorderSumfromOrderMasterwheresalerNo='E2005002'andorderDate='2008-1-9')9selecta.productNo,a.productName,sum(b.quantity)订货数量,b.pricefromProducta,OrderDetailbwherea.productPrice400andb.productNo=a.productNogroupbya.productNo,a.productName,b.price10selecta.productNo,a.productName,sum(b.quantity)订货数量,b.pricefromProductaleftouterjoinOrderDetailbona.productPrice400andb.productNo=a.productNogroupbya.productNo,a.productName,b.priceselecta.productNo,a.productName,sum(b.quantity)订货数量,b.pricefromProductarightouterjoinOrderDetailbona.productPrice400andb.productNo=a.productNogroupbya.productNo,a.productName,b.priceselecta.productNo,a.productName,sum(b.quantity)订货数量,b.pricefromProductafullouterjoinOrderDetailbona.productPrice400andb.productNo=a.productNogroupbya.productNo,a.productName,b.price11selecta.customerNo客户编号,customerName客户名称,convert(char(10),orderdate,120)销售日期,ordersum销售金额fromordermasteraleftouterjoincustomerbon(a.customerno=b.customerno)orderbya.customerno,ordersumdesc12selecta.employeeNo,a.employeeName,casesexwhen'F'then'女'when'M'then'男'Endsex,b.productName,d.quantity,d.price,d.quantity*d.price金额,orderDate=convert(char(10),orderDate,120)fromEmployeea,Productb,OrderMasterc,OrderDetaildwherea.employeeNo=c.salerNoandb.productNo=d.productNoandc.orderNo=d.orderNo14selectb.orderNo,b.orderSumfromEmployeea,OrderMasterbwherea.employeeNo=b.salerNoandsex='M'andb.orderSum2000实验三1；selecttop1orderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNoorderbyorderSumdesc2；SELECTa.productNo,orderNo,quantity,(quantity*price)moneyFROMOrderDetaila,(SELECTproductNoFROMOrderDetailGROUPBYproductNoHAVINGcount(*)=3)bWHEREa.productNo=b.productNoORDERBYa.productNo,quantityDESC3：selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoneyfromEmployeea,OrderDetailb,OrderMastercwherea.employeeNo=c.salerNoandb.orderNo=c.orderNogroupbya.employeeNo,a.employeeNamehavingsum(quantity*price)50004：selectemployeeNo,employeeName,orderSumfromEmployeea,(selectsalerNo,sum(orderSum)orderSumfromOrderMastergroupbysalerNohavingsum(orderSum)5000)bwherea.employeeNo=b.salerNoorderbyorderSumdesc5SELECTa.CustomerNo,CustomerNameFROMCustomeraWHEREa.CustomerNoIN(SELECTCustomerNoFROMOrderMasterb,OrderDetailcWHEREb.orderNo=c.orderNoGROUPBYCustomerNoHAVINGsum(quantity)10)6：SELECTcustomerName,SUM(orderSum)AStotalPriceFROMCustomer,OrderMasterWHERECustomer.customerNo=OrderMaster.customerNoGROUPBYcustomerNameHAVINGSUM(orderSum)=ALL(SELECTSUM(orderSum)FROMOrderMasterGROUPBYcustomerNo)7SELECTa.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,sumDESC8：SELECTsalerNo,sum(ordersum)totalfromorderMastergroupbysalerNohavingsum(orderSum)4