万常选数据库实验参考答案.docx
- 文档编号:9491504
- 上传时间:2023-02-04
- 格式:DOCX
- 页数:41
- 大小:25.26KB
万常选数据库实验参考答案.docx
《万常选数据库实验参考答案.docx》由会员分享,可在线阅读,更多相关《万常选数据库实验参考答案.docx(41页珍藏版)》请在冰豆网上搜索。
万常选数据库实验参考答案
万常选数据库实验参考答案
SETDATEFORMATYMD
GO
--选择数据库
USEOrderDB
GO
--实验一:
简单查询
--1.1查询所有业务部门的员工姓名、职务、薪水。
SELECTemployeeName,headShip,salary
FROMEmployee
--1.2查询名字中含有“有限”的客户姓名和所在地。
SELECTcustomerName,address
FROMCustomer
--1.3查询出姓“王”并且姓名的最后一个字为“成”的员工。
SELECT*
FROMEmployee
WHEREemployeeNameLIKE'王%成'
--1.4查询住址中含有上海或南昌的女员工,并显示其姓名、所属部门、职称、住址,其中性别用“男”和“女”显示。
SELECTemployeeNameAS姓名,departmentAS所属部门,headShipAS职称,addressAS住址,CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'ENDAS性别
FROMEmployee
--1.5在表sales中挑出销售金额大于等于5000元的订单。
SELECT*
FROMOrderMaster
WHEREorderSum>5000
--1.6选取订单金额最高的前10%的订单数据。
SELECTTOP(10)PERCENT*
FROMOrderMaster
ORDERBYorderSum
--或
SELECTTOP10PERCENTorderNo,SUM(quantity*price)AStotalPrice
FROMOrderDetail
GROUPBYorderNO
ORDERBYSUM(quantity*price)DESC
--1.7查询出职务为“职员”或职务为“科长”的女员工的信息。
SELECT*
FROMEmployee
WHEREsex='女'AND(headShip='职员'ORheadShip='科长')
--1.8查找订单金额高于4000的所有客户编号。
SELECTcustomerNo
FROMOrderMaster
WHEREorderSum>4000
--1.9选取编号介于C20050001~C20050004的客户编号、客户名称、客户地址。
SELECTcustomerNo,customerName,address
FROMCustomer
WHEREcustomerNoBETWEEN'C20050001'AND'C20050004'
--1.10找出同一天进入公司服务的员工。
SELECT*
FROMEmployee
ORDERBYhireDate
--1.11在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
SELECT*
FROMOrderMaster
WHEREorderSum>(
SELECTMAX(orderSum)
FROMOrderMaster
WHEREsalerNo='E2005002'ANDorderDate='2008-1-9'
)
--或
SELECT*
FROMOrderMaster
WHEREorderSum>ALL(
SELECTorderSum
FROMOrderMaster
WHEREsalerNo='E2005002'ANDorderDate='2008-1-9'
)
--1.12查询既订购了“52倍速光驱”商品,又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
SELECTcustomerNo,orderNo,orderSum
FROMOrderMaster
WHEREorderNoIN(
SELECTo1.orderNo
FROMOrderDetailo1,OrderDetailo2
WHEREo1.orderNo=o2.orderNo
ANDo1.productNoIN(
SELECTproductNo
FROMProduct
WHEREproductName='52倍速光驱'
)ANDo2.productNoIN(
SELECTproductNo
FROMProduct
WHEREproductName='17寸显示器'
)
)
--1.13查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
SELECTemployeeName,sex,department,headShip
FROMEmployee
WHEREdepartmentIN(
SELECTdepartment
FROMEmployee
WHEREemployeeName=
'陈诗杰'
)
--1.14查询每种商品的商品编号、商品名称、订货数量和订货单价。
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetail,Product
WHEREOrderDetail.productNo=Product.productNo
--1.15查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetail,Product
WHEREOrderDetail.productNo=Product.productNo
ANDprice>400
--1.16分别用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价,并分析比较检索的结果。
--左外连接
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailLEFTJOINProductONOrderDetail.productNo=Product.productNo
WHEREprice>400
--另一种理解
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailLEFTJOINProductONOrderDetail.productNo=Product.productNoANDprice>400
--右外连接
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailRIGHTJOINProductONOrderDetail.productNo=Product.productNo
WHEREprice>400
--另一种理解
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailRIGHTJOINProductONOrderDetail.productNo=Product.productNoANDprice>400
--完整外部连接
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailFULLJOINProductONOrderDetail.productNo=Product.productNo
WHEREprice>400
--另一种理解
SELECTProduct.productNo,productName,quantity,price
FROMOrderDetailFULLJOINProductONOrderDetail.productNo=Product.productNoANDprice>400
--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,Product
WHEREEmployee.employeeNo=OrderMaster.salerNo
ANDOrderMaster.orderNo=OrderDetail.orderNo
ANDOrderDetail.productNo=Product.productNo
--1.18查找在2008年3月中有销售记录的客户编号、名称和订单总额。
SELECTCustomer.customerNo,customerName,totalPrice=SUM(orderSum)
FROMCustomer,OrderMaster
WHERECustomer.customerNo=OrderMaster.customerNo
ANDYEAR(orderDate)=2008ANDMONTH(orderDate)=3
GROUPBYCustomer.customerNo,customerName
--1.19使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额,其中订货日期不要显示时间,日期格式为yyyy-mm-dd,按客户编号排序,同一客户再按订单金额降序排序输出。
SELECTCustomer.customerNo,customerName,orderDate=CONVERT(char(10),orderDate,120),orderSum
FROMCustomerLEFTJOINOrderMasterONCusto
mer.customerNo=OrderMaster.customerNo
ORDERBYCustomer.customerNo,orderSumDESC
--1.20查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额,其中性别用“男”、“女”表示。
SELECTemployeeName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'END,orderDate=CONVERT(char(10),orderDate,120),quantity,money=quantity*price
FROMEmployee,OrderMaster,OrderDetail,Product
WHEREEmployee.employeeNo=OrderMaster.salerNo
ANDOrderMaster.orderNo=OrderDetail.orderNo
ANDOrderDetail.productNo=Product.productNo
ANDproductName='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,Product
WHEREEmployee.employeeNo=OrderMaster.salerNo
ANDOrderMaster.orderNo=OrderDetail.orderNo
ANDOrderDetail.productNo=Product.productNo
--1.22查询客户姓名为“客户丙”所购货物的客户名称、订单金额、订货日期和电话号码。
SELECTcustomerName,orderSum,orderDate,telephone
FROMCustomer,OrderMaster
WHERECustomer.customerNo=OrderMaster.customerNo
ANDcustomerName='客户丙'
--1.23找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。
SELECTorderNo,orderSum
FROMEmployee,OrderMaster
WHEREEmployee.employeeNo=OrderMaster.salerNo
ANDsex='M'ANDorderSum>=2000
--1.24查询来自上海市的客户的姓名、电话、订单号及订单金额。
SELECTcustomerName,telephone,orderNo,orderSum
FROMCustomer,OrderMaster
WHERECustomer.customerNo=OrderMaster.customerNo
ANDaddressLIKE'上海市%'
--实验二:
复杂查询
--2.1查找有销售记录的客户编号、名称和订单总额。
SELECTCustomer.customerNo,customerName,SUM(orderSum)AStotalPrice
FROMCustomer,OrderMaster
WHERECustomer.customerNo=OrderMaster.customerNo
GROUPBYCustomer.customerNo,customerName
--2.2在订单明细表中查询订单金额最高的订单。
SELECT*
FROMOrderDetail
WHEREorderNoIN(
SELECTorderNo
FROMOrderDetail
GROUPBYorderNo
HAVINGSUM(quantity*price)>=ALL(
SELECTSUM(quantity*price)
FROMOrderDetail
GROUPBYorderNo
)
)
--2.3查询没有订购商品的客户编号和客户名称。
SELECTcustomerNo,customerName
FROMCustomer
WHEREcustomerNoNOTIN(
SELECTcustomerNo
FROMOrderMaster
)
--2.4找出至少被订购3次的商品编号、订单编号、订货数量和订货金额,并按订货数量的降序排序输出。
SELECTproductNo,orderNo,quantity,quantity*priceAStotalPrice
FROMOrderDetail
WHEREproductNoIN(
SELECTproductNo
FROMOrderDetail
GROUPBYproductNo
HAVINGCOUNT(*)>=3
)
OR
DERBYquantityDESC
--2.5使用子查询查找“16MDRAM”的销售情况,要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额,其中性别用“男”、“女”表示。
SELECTemployeeName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'END,orderDate,quantity,totalPrice=quantity*price
FROMEmployee,OrderMaster,OrderDetail
WHEREEmployee.employeeNo=OrderMaster.salerNo
ANDOrderMaster.orderNo=OrderDetail.orderNo
ANDproductNoIN(
SELECTproductNo
FROMProduct
WHEREproductName='16MDRAM'
)
--2.6查询sales表中订单金额最高的订单号及订单金额。
SELECTorderNo,orderSum
FROMOrderMaster
WHEREorderSum>=ALL(
SELECTorderSum
FROMOrderMaster
)
--2.7计算出一共销售了几种商品。
SELECTCOUNT(DISTINCTproductNo)ASproductNum
FROMOrderDetail
--2.8显示OrderDetail表中每种商品的订购金额总和,并且依据销售金额由大到小排序输出。
SELECTproductNo,SUM(quantity*price)AStotalPrice
FROMOrderDetail
GROUPBYproductNo
ORDERBYSUM(quantity*price)DESC
--2.9查找销售总额少于1000元的销售员编号、姓名和销售额。
SELECTemployeeNo,employeeName,SUM(orderSum)AStotalPrice
FROMEmployee,OrderMaster
WHEREEmployee.employeeNo=OrderMaster.salerNo
GROUPBYemployeeNo,employeeName
HAVINGSUM(orderSum)<1000
--2.10找出目前业绩未超过5000元的员工,并按销售业绩的降序排序输出。
SELECTEmployee.employeeNo,employeeName,SUM(orderSum)AStotalPrice
FROMEmployee,OrderMaster
GROUPBYEmployee.employeeNo,employeeName
HAVINGSUM(orderSum)<5000
ORDERBYSUM(orderSum)DESC
--2.11在Employee表中查询薪水超过员工平均薪水的员工信息。
SELECT*
FROMEmployee
WHEREsalary>=(
SELECTAVG(salary)
FROMEmployee
)
--2.12计算每一种商品的销售数量、平均销售单价和总销售金额。
SELECTproductNo,SUM(quantity)AStotalNum,SUM(price*quantity)/SUM(quantity)ASavgPrice,SUM(price*quantity)AStotalPrice
FROMOrderDetail
GROUPBYproductNo
--2.13查找至少有3次销售的业务员名单和销售日期。
SELECTsalerNo,orderDate
FROMOrderMaster
WHEREsalerNoIN(
SELECTsalerNo
FROMOrderMaster
GROUPBYsalerNo
HAVINGCOUNT(*)>=3
)
--2.14用存在量词查找没有订货记录的客户名称。
SELECTcustomerNo
FROMCustomer
WHERENOTEXISTS(
SELECT*
FROMOrderMaster
WHERECustomer.customerNo=OrderMaster.customerNo
)
--2.15查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。
SELECTcustomerNo,customerName
FROMCustomer
WHEREcustomerNoIN(
SELECTcustomerNo
FROMOrderMaster
WHEREorderNoIN(
SELECTorderNo
FROMOrderDetail
WHEREquantity<10
)
)
--2.16在销售明细表中按商品编号进行汇总,统计每种商品的销售数量和金额。
SELECTproductNo,SUM(quantity)AStotalNum,SUM(quantity*price)AStotalPrice
FROMOrd
erDetail
GROUPBYproductNo
--2.17按客户编号统计每个客户2008年2月的订单总金额。
SELECTcustomerNo,SUM(orderSum)AStotalPrice
FROMOrderMaster
WHEREYEAR(orderDate)=2008ANDMONTH(orderDate)=2
GROUPBYcustomerNo
--2.18查找订单金额高于8000的所有客户编号。
SELECTcustomerNo
FROMOrderMaster
WHEREorderSum>=8000
--2.19显示每种商品的销售金额总和,并以销售金额由大到小输出。
SELECTproductNo,SUM(price*quantity)AStotalPrice
FROMOrderDetail
GROUPBYproductNo
ORDERBYSUM(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 万常选 数据库 实验 参考答案