数据库系统原理与设计实验答案

SETDATEFORMATYMDGO--选择数据库USEOrderDBGO--实验一：简单查询--1.1查询所有业务部门的员工姓名、职务、薪水。SELECTemployeeName,headShip,salaryFROMEmployee--1.2查询名字中含有“有限”的客户姓名和所在地。SELECTcustomerName,addressFROMCustomer--1.3查询出姓“王”并且姓名的最后一个字为“成”的员工。SELECT*FROMEmployeeWHEREemployeeNameLIKE'王%成'--1.4查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。SELECTemployeeNameAS姓名,departmentAS所属部门,headShipAS职称,addressAS住址,CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'ENDAS性别FROMEmployee--1.5在表sales中挑出销售金额大于等于5000元的订单。SELECT*FROMOrderMasterWHEREorderSum5000--1.6选取订单金额最高的前10%的订单数据。SELECTTOP(10)PERCENT*FROMOrderMasterORDERBYorderSum--或SELECTTOP10PERCENTorderNo,SUM(quantity*price)AStotalPriceFROMOrderDetailGROUPBYorderNOORDERBYSUM(quantity*price)DESC--1.7查询出职务为“职员”或职务为“科长”的女员工的信息。SELECT*FROMEmployeeWHEREsex='女'AND(headShip='职员'ORheadShip='科长')--1.8查找订单金额高于4000的所有客户编号。SELECTcustomerNoFROMOrderMasterWHEREorderSum4000--1.9选取编号介于C20050001~C20050004的客户编号、客户名称、客户地址。SELECTcustomerNo,customerName,addressFROMCustomerWHEREcustomerNoBETWEEN'C20050001'AND'C20050004'--1.10找出同一天进入公司服务的员工。SELECT*FROMEmployeeORDERBYhireDate--1.11在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。SELECT*FROMOrderMasterWHEREorderSum(SELECTMAX(orderSum)FROMOrderMasterWHEREsalerNo='E2005002'ANDorderDate='2008-1-9')--或SELECT*FROMOrderMasterWHEREorderSumALL(SELECTorderSumFROMOrderMasterWHEREsalerNo='E2005002'ANDorderDate='2008-1-9')--1.12查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。SELECTcustomerNo,orderNo,orderSumFROMOrderMasterWHEREorderNoIN(SELECTo1.orderNoFROMOrderDetailo1,OrderDetailo2WHEREo1.orderNo=o2.orderNoANDo1.productNoIN(SELECTproductNoFROMProductWHEREproductName='52倍速光驱')ANDo2.productNoIN(SELECTproductNoFROMProductWHEREproductName='17寸显示器'))--1.13查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。SELECTemployeeName,sex,department,headShipFROMEmployeeWHEREdepartmentIN(SELECTdepartmentFROMEmployeeWHEREemployeeName='陈诗杰')--1.14查询每种商品的商品编号、商品名称、订货数量和订货单价。SELECTProduct.productNo,productName,quantity,priceFROMOrderDetail,ProductWHEREOrderDetail.productNo=Product.productNo--1.15查询单价高于400元的商品编号、商品名称、订货数量和订货单价。SELECTProduct.productNo,productName,quantity,priceFROMOrderDetail,ProductWHEREOrderDetail.productNo=Product.productNoANDprice400--1.16分别用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。--左外连接SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailLEFTJOINProductONOrderDetail.productNo=Product.productNoWHEREprice400--另一种理解SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailLEFTJOINProductONOrderDetail.productNo=Product.productNoANDprice400--右外连接SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailRIGHTJOINProductONOrderDetail.productNo=Product.productNoWHEREprice400--另一种理解SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailRIGHTJOINProductONOrderDetail.productNo=Product.productNoANDprice400--完整外部连接SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailFULLJOINProductONOrderDetail.productNo=Product.productNoWHEREprice400--另一种理解SELECTProduct.productNo,productName,quantity,priceFROMOrderDetailFULLJOINProductONOrderDetail.productNo=Product.productNoANDprice400--1.17查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd格式显示。SELECTemployeeNo,employeeName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'END,productName,quantity,price,quantity*priceASmoney,orderDate=ISNULL(CONVERT(char(10),orderDate,120),'日期不详')FROMEmployee,OrderMaster,OrderDetail,ProductWHEREEmployee.employeeNo=OrderMaster.salerNoANDOrderMaster.orderNo=OrderDetail.orderNoANDOrderDetail.productNo=Product.productNo--1.18查找在2008年3月中有销售记录的客户编号、名称和订单总额。SELECTCustomer.customerNo,customerName,totalPrice=SUM(orderSum)FROMCustomer,OrderMasterWHERECustomer.customerNo=OrderMaster.customerNoANDYEAR(orderDate)=2008ANDMONTH(orderDate)=3GROUPBYCustomer.customerNo,customerName--1.19使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。SELECTCustomer.customerNo,customerName,orderDate=CONVERT(char(10),orderDate,120),orderSumFROMCustomerLEFTJOINOrderMasterONCustomer.customerNo=OrderMaster.customerNoORDERBYCustomer.customerNo,orderSumDESC--1.20查找16MDRAM的销售情况，要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额，其中性别用“男”、“女”表示。SELECTemployeeName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'END,orderDate=CONVERT(char(10),orderDate,120),quantity,money=quantity*priceFROMEmployee,OrderMaster,OrderDetail,ProductWHEREEmployee.employeeNo=OrderMaster.salerNoANDOrderMaster.orderNo=OrderDetail.orderNoANDOrderDetail.productNo=Product.productNoANDproductName='16MDRAM'--1.21查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。SELECTEmployee.employeeNo,employeeName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'END,productName,quantity,price,totalPrice=quantity*price,orderDate=CONVERT(char(10),orderDate,120)FROMEmployee,OrderMaster,OrderDetail,ProductWHEREEmployee.employeeNo=OrderMaster.salerNoANDOrderMaster.orderNo=OrderDetail.orderNoANDOrderDetail.productNo=Product.productNo--1.22查询客户姓名为“客户丙”所购货物的客户名称、订单金额、订货日期和电话号码。SELECTcustomerName,orderSum,orderDate,telephoneFROMCustomer,OrderMasterWHERECustomer.custom