数据库系统原理与设计实验教程实验1

数据库系统原理与设计实验教程实验1实验一 简单查询在订单数据库中，完成如下的查询：查询所有业务部门的员工姓名、职称、薪水。selectemployeeName,headShip,salaryfromemployeewheredepartment='业务科'查询名字中含有“有限”的客户姓名和所在地。selectCustomerName,addressfromCustomerwhereCustomerNamelike'% 有限%'查询出姓“王”并且姓名的最后一个字为“成”的员工。select*fromemployeewhereemployeeNamelike'王%成'没有结果查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。selectemployeeName,department,headship,address,sex=Casesexwhen'M'then'男'when'F'then'女'endfrom employeewhereaddresslike'%上海%'oraddresslike'%南昌%'andsex='F'在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。selectorderNofromOrderDetailgroupbyorderNohavingsum(quantity*price)>=10000(6)选取订单金额最高的前 10%的订单数据。SELECTTOP10PERCENTorderNoFROMOrderdetailGROUPBYorderNoORDERBYsum(quantity*price)DESC查询出职务为“职员”或职务为“科长”的女员工的信息。select*fromemployeewhere(headship='职员'orheadship='科长')andsex='F'(8)查找定单金额高于 8000的所有客户编号。1）查询总金额高于8000元的客户编号selectCustomerNofromOrderMastera,Orderdetailbwherea.orderNo=b.orderNogroupbyCustomerNohavingsum(quantity*price)>80002）查找定单金额高于 8000的所有客户编号selectCustomerNofromOrderMasterwhereorderNoin(selectorderNofromOrderDetailgroup byorderNohavingsum(quantity*price)>8000)(9) 选取编号界于“C20050001”和“C20050004”的客户编号、客户名称、客户地址。selectCustomerNo,CustomerName,addressfromCustomerwhereCustomerNo between'C20050001'and'C20050004'找出同一天进入公司服务的员工。Selecta.employeeNo,a.employeeName,b.employeeNo,b.employeeNamefromEmployeea,Employeeasbwhere a.employeeNo!=b.employeeNo anda.employeeName>b.employeeNameand(a.hireDate=b.hireDate)在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。1）首先计算订单主表的订单金额updateOrderMastersetorderSum=totalSumfromOrderMastera,(selectorderNo,sum(quantity*price)totalSumfromOrderDetailgroup byorderNo)bwherea.orderNO=b.orderNo2）SELECT*FROMOrderMasterWHEREorderSum>any(SELECTorderSumFROMOrderMasterWHEREsalerNo='E2005002'ANDorderDate='20080109')查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。SelectcustomerNo,orderNo,orderSumfromOrderMasterwherecustomerNoin(selectcustomerNofrom OrderMaster a,OrderDetailb,Productcwherea.orderNo=b.orderNductNo=ductNoandproductName='52倍速光驱')andcustomerNoin(selectcustomerNofromOrderMastera,OrderDetailb,Productcwherea.orderNo=b.orderNductNo=ductNoandproductName='17寸显示器')查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。Selectb.employeeName,b.sex,b.department,b.headShipfromEmployeea,Employeebwhere a.department=b.department anda.employeeName='陈诗杰'查询每种商品的商品编号、商品名称、订货数量和订货单价。SELECTductNo,productName,quantity,priceFROMProducta,OrderDetailbWHEREductNo=ductNoORDERBYproductName,price查询单价高于400元的商品编号、商品名称、订货数量和订货单价。SELECTductNo,productName,quantity,priceFROMProducta,OrderDetailbWHEREductNo=ductNoANDprice>400ORDERBYproductName分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。左外连接命令：SELECTductNo,productName,quantity,priceFROM Product aLEFT OUTER JOINOrderDetailbONductNo=ductNoWHEREprice>400SductNo,ductName,b.quantity,b.priceFromOrderDetailAsbleftJOINProductAsaON(ductNo=ductNo)andprice>400OductNo右外连接命令：SELECTductNo,productName,quantity,priceFROM ProductaRIGHT OUTER JOINOrderDetailbONductNo=ductNoWHEREprice>400SductNo,ductName,b.quantity,b.priceFromOrderDetailAsbRIGHTJOINProductAsaON(ductNo=ductNo)andprice>400OductNo全连接命令：SELECTductNo,productName,quantity,priceFROM Product aFULL OUTER JOINOrderDetailbONductNo=ductNoWHEREprice>400SductNo,ductName,b.quantity,b.priceFromOrderDetailAsbfullJOINProductAsaON(ductNo=ductNo)andprice>400OductNo(18)分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订单编号，并分析比较检索的结果。SELECTa.customerNo,customerName,orderSum,orderNoFROMCustomeraleftOUTERJOINOrderMasterbONa.customerNo=b.customerNoSELECTa.customerNo,customerName,orderSum,orderNoFROM Customer aright OUTER JOINOrderMasterbONa.customerNo=b.customerNoSELECTa.customerNo,customerName,orderSum,orderNoFROM Customer afull OUTER JOINOrderMasterbONa.customerNo=b.customerNo从上述结果可知：若表 a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。SELECTsalerNo,employeeName,CASEsexWHEN'M'THEN' 男'WHEN'F'THEN'女'END性别,productName,quantity,price,orderSum,convert(char(10),orderDate,120)orderDateFROMEmployeea,OrderMasterb,OrderDetailc,ProductdWHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDductNo=ductNoORDERBYsalerNo查找在2008年3月中有销售记录的客户编号、名称和订单总额。SELECTa.customerNo,customerName,orderSumFROMOrderMastera,CustomerbWHEREyear(orderDate)=2008ANDmonth(orderDate)=3ANDa.customerNo=b.customerNo使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。SELECTa.customerNo,customerName,convert(char(10),orderDate,120)orderDate,orderSumFROMCustomeraLEFTOUTERJOINOrderMasterbONa.customerNo=b.customerNoORDERBYa.customerNo,orderSumdesc查找16MDRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。SELECTemployeeName,CASEsexWHEN 'M'THEN'男'WHEN 'F'THEN'女'ENDsex,orderDate,quantity,quantity*pricetotalFROMEmployeea,OrderMasterb,OrderDetailc,ProductdWHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDductNo=ductNoANDproductName='16MDRAM'查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。selecta.employeeNo,a.employeeName,a.sex,d.ProductName,quantity,price,price*quantitytotal,orderDatefrom Employee a,OrderMasterb,OrderDetailc,Productdwhere a.employeeNo=b.salerNo ANDb.orderNo=c.orderNo ANDc.ProductNo=d.ProductNo查询客户姓名为“客户丙”所购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。SELECTCustomerName,orderSum,orderDate,telephoneFROMCustomera,OrderMasterbWHERE a.CustomerNo=b.CustomerNo ANDa.CustomerName='客户丙'找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。SELECTorderNo,orderSumFROM(SELECTorderNo,orderSumFROMOrderMastera,EmployeebWHERE a.salerNo=b.employeeNob.sex='M')cWHEREordersum>2000

AND或：SELECTorderNo,orderSumFROM OrderMastera,EmployeebWHERE a.salerNo=b.employeeNob.sex='M' andordersum>2000

AND查询来自上海市的客户的姓名、电话、订单号及订单金额。SELECTCustomerName,telephone,orderNo,orderSumFROMCustomera,OrderMasterbWHERE a.CustomerNo=b.CustomerNo ANDa.addressLIKE'%上海%'实验问题①连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。②查询表可以用在什么地方？使用查询表要注意哪些地方？答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。③分析条件BETWEEN...AND、AND、OR等关键字的使用方法。答：BETWEEN...AND 是范围查询，可用于查询属性值在某一个范围内的元组，NOTBETWEEN ⋯.AND可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。用BETWEEN⋯..AND能实现的查询，也可以用AND来达到。同样，用NOTBETWEEN⋯AND能实现的查询，也可以用OR来实现。④请总结SQL语句中的单表查询语句的使用方法。⒈查询所有列SELECT*

FROM[

表名]2.查询指定列SELECT[字段列表

]FROM

[表名]消除重复元组：SELECT Distinct[字段列表]FROM [表名]4.查询经过计算的列SELECT【字段及字段表达式】FROM【表名】指定