数据库系统原理和设计（第二版）实验一至实验三

...wd......wd......wd...实验一1-1.查询员工的姓名、职务和薪水selectemployeeName,headShip,salaryfromemployee图1-12.查询名字中含有“有限〞的客户姓名和所在地selectCustomerName,addressfromCustomerwhereCustomerNamelike'%有限%'图1-23.查询出姓“张〞并且姓名的最后一个字为“梅〞的员工。select*fromemployeewhereemployeeNamelike'张%梅'图1-34.查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男〞和“女〞显示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%的订单数据SELECTTOP10PERCENT*fromorderdetailorderbypriceDESC图1-89.计算一共销售了几种商品SELECTCOUNT(DISTINCTproductno)as种类fromorderDeta图1-910.计算orderDetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额由大到小输出。SELECTproductno商品种类,count(*)quantity,avg(price)平均价格,sum(quantity*price)金额fromorderDetailgroupbyproductnoorderby金额desc图1-1011.按客户编号统计每个客户2008年2月的订单总金额。selectcustomerno,ordersumfromordermasterwhereyear(orderDate)=2008andmonth(orderDate)=2图1-1112.统计至少销售了10件以上的商品编号和销售数量。selectproductno商品编号,quantity商品数目fromorderdetailwherequantity>=10图1-1213.统计在业务科工作且在1973年或1967年出生的员工人数和平均工资selectcount(*)人数,avg(salary)平均工资fromEmployeewheredepartment='业务科'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的销售情况要求显示相应的销售员的姓名、性别、销售日期销售数量和金额其中性别用“男〞和“女〞表示selectemployeeName,casesexwhen'M'then'男'when'F'then'女'endassex,b.orderDate,c.quantity销售数量,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业务员在2008-1-9这天所接的任一张订单的金额〞的所有订单信息。select*fromOrderMasterwhereorderSum>any(selectorderSumfromOrderMasterwheresalerNo='E2005002'andorderDate='2008-1-9')图2-89.查询单价高于400元的商品编号商品名称订货数量和订货单价。selectductNo,ductName,sum(b.quantity)订货数量,b.pricefromProducta,OrderDetailbwhereductPrice>400andductNo=ductNogroupbyductNo,ductName,b.price图2-910.分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价并分析比拟检索的结果。selectductNo,ductName,sum(b.quantity)订货数量,b.pricefromProductaleftouterjoinOrderDetailbonductPrice>400andductNo=ductNogroupbyductNo,ductName,b.priceselectductNo,ductName,sum(b.quantity)订货数量,b.pricefromProductarightouterjoinOrderDetailbonductPrice>400andductNo=ductNogroupbyductNo,ductName,b.priceselectductNo,ductName,sum(b.quantity)订货数量,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.查找每个员工的销售记录要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期其中性别使用“男〞和“女〞表示日期使用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的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额、其中性别用“男〞“女〞表示。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.找出公司男业务员所接且订单金额超过2000的订单号及订单金额。selectb.orderNo,b.orderSumfromEmployeea,OrderMasterbwherea.employeeNo=b.salerNoandsex='M'andb.orderSum>2000图2-1415.查询每种商品的总销售数量及总销售金额要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。selectductno商品编号,productname商品称,sum(quantity)总销售数量,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查询订单中所订购的商品数量没有超过个的客户编号和客户名称。SELECTa.CustomerNo,CustomerNameFROMCustomeraWHEREa.CustomerNoIN( SELECTCustomerNo FROMOrderMasterb,OrderDetailc WHEREb.orderNo=c.orderNo GROUPBYCustomerNo HAVINGsum(quantity)<10)图3-57.查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。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.求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。SELECTcustomerNo,productNo,sum(quantity)quantitys,(sum(quantity*price)/sum(quantity))avgpriceFROMOrderMastera,OrderDetailbWHEREa.orderNo=b.orderNoGROUPBYcustomerNo,productNoORDERBYcustomerNo,productNo图3-911.查询订购的商品至少包含了订单“200803010001”SELECT*FROMOrderMasteraWHEREnotexists(select*f