实验二 参考答案.doc

实验二 多表查询实验目的与要求（1）了解查询的概念和方法； （2）掌握多表连接的方法；（3）掌握嵌套查询的基本方法；（4）掌握SELECT语句在多表查询中的应用。实验内容：1、利用企业管理器在数据库OrderDB中建立数据表，包括员工表，客户表，商品表，订单主表，订单明细表。这5张表的结构和数据如下：表1 员工表Employee的结构属性含义属性名数据类型员工编号employeeNochar(8)员工姓名employeeNamevarchar(10)性别sexchar(1)出生日期birthdaydatetime住址addressvarchar(50)电话telephonevarchar(20)雇佣日期hiredatedatetime所属部门departmentvarchar(30)职务headshipvarchar(10)薪水salarynumeric(8,2)表2 客户表Customer的结构属性含义属性名数据类型客户号customerNochar(9)客户名称customerNamevarchar(40)客户住址addressvarchar(40)客户电话telephonevarchar(20)邮政编码zipchar(6)表3 商品基本信息表Product的结构属性含义属性名数据类型商品编号productNochar(9)商品名称productNamevarchar(40)商品类别productClassvarchar(20)商品定价productPricenumeric(7,2)表4 订单主表OrderMaster的结构属性含义属性名数据类型订单编号orderNochar(12)客户号customerNochar(9)员工编号employeeNochar(8)订单日期orderDatedatetime订单金额orderSumnumeric(9,2)发票号码invoiceNochar(10)表5 订单明细表OrderDetail的结构属性含义属性名数据类型订单编号orderNochar(12)商品编号productNochar(9)销售数量quantityint成交单价pricenumeric(7,2)表6 员工表Employee的数据employeeNoemployeeNamesexbirthdayaddresstelephonehireDatedepartmentheadShipsalaryE2005001喻自强M1965-04-15 南京市青海路18号138176050082005-02-06 财务科科长5800.80E2005002张小梅F1973-11-01 上海市北京路8号136074050162005-03-28 业务科职员2400.00E2005003张小娟F1973-03-06 上海市南京路66号137073050252005-03-28 业务科职员2600.00E2005004张露F1967-01-05 南昌市八一大道130号159072051342005-03-28 业务科科长4100.00E2005005张小东M1973-09-03 南昌市阳明路99号156071052432005-03-28 业务科职员1800.00E2006001张辉M1965-11-01 南昌市青山路100号136077053522006-03-28 办公室主任4000.00E2006002韩梅F1973-12-11 上海市浦东大道6号138078054612006-11-20 业务科职员2600.00E2006003刘风F1973-05-21 江西财经大学5栋1-101室159078055782006-11-20 业务科职员2500.00E2007001吴浮萍M1973-09-12 南昌市高新开发区12号null2007-06-28 业务科职员2500.00E2007002高代鹏M1973-01-02 南昌市高新开发区56号null2007-11-28 办公室文员2000.00E2008001陈诗杰M1968-01-06 江西财经大学12栋3-304室null2008-01-06 财务科出纳3200.00E2008002张良M1972-02-16 上海市福州路135号null2008-02-12 业务科职员2700.00E2008003黄梅莹F1972-05-15 上海市九江路88号null2008-02-12 业务科职员3100.00E2008004李虹冰F1972-10-13 南昌市中山路1号null2008-02-12 业务科职员3400.00E2008005张小梅F1970-11-06 深圳市阳关大道10号null2008-11-18 财务科会计5000.00表7 客户表Customer的数据customerNocustomerNameaddresstelephonezipC20050001统一股份有限公司天津市022-3566021220012C20050002兴隆股份有限公司天津市022-3562452220301C20050003上海生物研究室北京市010-2121000108001C20050004五一商厦上海市021-4532187210100C20060001大地商城北京市010-1165152100803C20060002联合股份有限公司上海市021-4568451210100C20070001南昌市电脑研制中心南昌市0791-4412152330046C20070002世界技术开发公司上海市021-4564512210230C20070003万事达股份有限公司天津市022-4533141220400C20080001红度股份有限公司北京市010-5421585100800表8 商品基本信息表Product的数据productNoproductNameproductClassproductPriceP2005000132M DRAM内存80.70P2005000217寸显示器显示器700.00P20050003120GB硬盘存储器300.00P200500043.5寸软驱设备35.00P20050005键盘设备100.60P20060001VGA显示卡显示器200.60P20060002网卡设备66.00P20060003Pentium100CPU处理器1200.00P200700011G DDR内存256.00P2007000252倍速光驱设备200.00P20070003计算机字典图书100.00P200700049600bits/s调制调解设备320.00P20080001Pentium 主板主板890.00P20080002硕泰克SL-K8AN-RL 主板主板1100.00P20080003龙基777FT纯平显示器显示器900.00表9 订单明细表OrderDetail的数据orderNoproductNoquantityprice200801090001P20050001580.70200801090001P200500023700.00200801090001P200500032300.00200801090002P20060002566.00200801090002P200800012890.00200801090002P200800032900.00200801090003P20050001580.70200801090003P200600013200.60200802190001P2006000341200.00200802190001P200700012256.00200802190001P200700025200.00200802190002P200500032300.00200802190002P200500053100.60200803010001P20050001880.70200803010001P200700014256.00200803020001P20050001280.70200803020001P200500021700.00200803020001P200700033100.00200803090001P200500034300.00200803090001P20050004535.00200803090001P200700012256.00200803090001P200700025200.00200803090001P200700043320.00200805090001P2006000381200.00200805090001P200700011256.00200805090001P200700022200.00200805090001P200700035100.00200806120001P200500021700.00200806120001P200500033300.00200806120001P20050004235.00200806120001P200500053100.60200901010001P200500022700.00200901010001P200500041135.00200901010001P200500059100.60200901010001P2008000221100.00201206070001P2005000310300.00201206070001P20060002566.00201206070001P2006000341200.00201206070002P20060002666.00201206070002P200700012256.00201206070002P200700022200.00201206070002P200800013890.00表10 订单主表OrderMaster的数据orderNocustomerNoemployeeNoorderDateorderSuminvoiceNo200801090001C20050001E20050022008-01-09 3103.50I000000001200801090002C20050004E20050032008-01-09 3910.00I000000002200801090003C20080001E20050022008-01-09 1005.30I000000003200802190001C20050001E20050032008-02-19 6312.00I000000004200802190002C20070002E20080022008-02-19 901.80I000000005200803010001C20070002E20070012008-03-01 1669.60I000000006200803020001C20050004E20080032008-03-02 1161.40I000000007200803090001C20070003E20080042008-03-09 3847.00I000000008200805090001C20060002E20080022008-05-09 10756.00I000000009200806120001C20050001E20050022008-06-12 1971.80I000000010200901010001C20050001E20050022009-01-01 4890.40I000000011201206070001C20050004E20050032012-06-07 8130.00I000000012201206070002C20070002E20080022012-06-07 3978.00I0000000132、根据上面的数据库，完成以下查询操作：（1）查询同一天进入公司工作的员工。select hiredate,employeeNameFrom Employee aWhere exists(select * from Employee bwhere a.hiredate=b.hiredate and a.employeeno!=b.employeeno)group by hiredate,employeeName（2）查询与“陈诗杰”在同一部门工作的员工姓名、性别、部门和职务。select a.employeeName,a.department,a.headShip,a.sexFrom Employee a,Employee bwhere a.department=b.department and b.employeeName=陈诗杰（3） 查询薪水超过员工平均薪水的员工信息。select *From Employeewhere salary(select AVG(salary)from Employee)（4） 查询有销售记录的客户编号、名称和订单总额。select a.customerno,customerName,SUM(orderSum) 总额From Customer a,OrderMaster bwhere a.customerNo=b.customerNogroup by a.customerNo,customerName（5） 查询没有订购商品的客户编号和名称。select customerno,customerNameFrom Customer where customerNo not in(select customerNo from OrderMaster)（6） 使用子查询来统计32M DRAM的销售情况，要求显示相应的销售员的姓名、销售日期、销售数量和金额。select employeeName,orderDate,quantity,orderSum,sex=case sexwhenFthen女whenMthen男endfrom Employee a,OrderMaster b,OrderDetail cwhere employeeno=salerno and b.orderNo=c.orderNo and productNo in (select productNo from Product where productName=32M DRAM)（7） 查询OrderMaster表中订单金额最高的订单号及订单金额。select ordersum,ordernofrom OrderMasterwhere orderSum=(select MAX(orderSum)from OrderMaster)（8） 查询订单金额大于“E2005002”业务员在2008-1-9这天所接的任一张订单的金额的所有订单信息。select *from OrderMasterwhere orderSumall(select orderSum from Ordermaster where salerNo=E2005002 and orderDate=2008/1/9)（9） 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。select a.productno,productClass,quantity,pricefrom Product a,OrderDetail bwhere a.productNo=b.productNo and price400（10） 使用左外连接查找每个客户的客户编号、名称、订货日期、订货金额，其中订货日期不显示时间，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。select a.customerNo,a.customerName,isnull (convert(char(10),ordersum,120),) ordersum,isnull (convert(char(10),orderdate,120),) orderdatefrom Customer a left join OrderMaster bon a.customerNo=b.customerNoorder by customerNo,ordersum desc（11） 查询每个员工的销售记录，要求显示销售员的编号、姓名、商品名称、数量、单价、金额和销售日期（日期使用yyyy-mm-dd格式显示）。select salerNo,employeeName,c.productName,quantity,price,orderSum,ISNULL(CONVERT(char(10),orderdate,120),) 日期from Employee a,OrderMaster b,Product c,OrderDetail dwhere a.employeeNo=b.salerNo and b.orderNo=d.orderNo and c.productNo=d.productNoorder by salerNo（12） 使用连接查询来统计32M DRAM的销售情况，要求显示相应的销售员的姓名、销售日期、销售数量和金额。（13）（13）查询公司男业务员所接且订单金额超过2000元的订单号和订单金额。select b.orderNo,SUM(quantity*price) ordersumfrom Employee a,OrderMaster b,OrderDetail cwhere a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex=Mgroup by b.orderNohaving SUM(quantity*price)2000（14）查询每种商品的总销售数量及总销售金额，要求显示出商品的编号、商品名称、总数量及金额，并按商品号从小到大排列。select a.productNo,a.productName,SUM(quantity)totalquantity,SUM(quantity*price) totalsumfrom Product a,OrderDetail bwhere a.productNo=b.productNogroup by a.productNo,a.productNameorder by a.productNo实验要求：（1）结合相关知识，操作规范正确。（2）认真书写实验报告，如实填写各项实验内容，尤其是执行信息。（3）完成任务后，继续验证其它与数据库和表创建有关的任务(书或资料上的)。