数据库系统原理与设计第二版实验实验一到实验三探究.docxVIP

实验一1：1.selectemployeeName,headship,salaryfrom Employee2selectCustomerName,addressfromCustomerwherecustomerNamelike%有限%3select*fromemployeewhereemployeeNamelike张%梅4SELECTemployeeName,department,address,isnull(convert(char(10),birthday,120),不详)出生日期,casesexwhenMthen男whenFthen女endas性别fromemployeewhere (addresslike%上海%oraddresslike%南昌%)andsex=F5select*fromemployeewhere (headship=职员orheadship=科长)andsex=F6Select*fromCustomerwhereCustomerNonotin(7updateordermastersetordersum=sum2fromordermastera,(selectorderno,sum(quantity*price)sum2fromorderdetailgroupbyorderno)bwherea.orderno=b.ordernoSelect*FromordermasterWhereordersum=50008 SELECT?TOP?10?PERCENT?*??from?orderdetail?order?by?price?DESC实验二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.orderNo5SELECT?a.customerNo,customerName?FROM?Customer?a?WHERE?a.customerNo?NOT?IN?(SELECT?customerNo?FROM?OrderMaster?)6．selectemployeeName,casesexwhenMthen男whenFthen女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=32M DRAM)7selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from (selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNo)b)8select*fromOrderMasterwhereorderSumany(selectorderSumfromOrderMasterwheresalerNo=E20050