数据库实验答案实验一Sql01.doc

1、查询所有业务部门的员工姓名、职称、薪水select employeeName,headShip,salaryfrom Employeewhere department=业务科2、查询名字中含有“有限”的客户姓名和所在地。select customerName,addressfrom Customerwhere customerName like %有限%3、查询出姓“王”并且姓名的最后一个字为“成”的员工select*from Employeewhere employeeName=王%成4、查询住址中含有上海或者南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。select employeeName,department,headShip,address, sex=case sex when M then 男 when F then 女 end from employeewhere (address like %上海%or address like%南昌%)and sex=F或WHERE address LIKE 上海% AND sex=F OR address LIKE 南昌% AND sex=F 5、在表sales中挑出销售金额大于等于5000元的订单。SELECT orderNo ,sum(quantity*price)sumOrderFROM OrderDetail GROUP BY orderNoHAVING sum(quantity*price)=5000ORDER BY sumOrder DESC6、选取订单金额最高的前10%的订单数据SELECT top 10 percent orderNo ,sum(quantity*price)sumOrderFROM OrderDetail GROUP BY orderNo7、查询出职务为“职员”或职务为“科长”的女员工的信息。SELECT*FROM employeeWHERE (headShip=职员 or headShip=科长) and sex=F或者WHERE headShip=职员 and sex=F or headShip=科长and sex=F8、查找订单金额高于4000的所有客户编号第一种SELECT customerNoFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNo GROUP BY a.orderNo,customerNoHAVING sum(quantity*price)=4000区别：SELECT distinct customerNoFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNo GROUP BY a.orderNo,customerNoHAVING sum(quantity*price)=4000第二种SELECT distinct CustomerNoFROM OrderMaster WHERE orderNo in ( SELECT orderNo FROM OrderDetail GROUP BY orderNo HAVING sum(quantity*price)4000 )第三种SELECT distinct CustomerNoFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNo GROUP BY CustomerNoHAVING SUM(quantity*price)4000 9、选取编号界于C0001C0004的客户编号、客户名称、客户地址。SELECT customerNo,customerName,addressFROM customerWHERE customerNo betweenC20050001andC2005000410、找出同一天进入公司服务的员工SELECT a.employeeName,a.hireDate,b.employeeName,b.hireDateFROM Employee a,Employee bWHERE a.employeeNo!=b.employeeNoand a.employeeNameb.employeeNameand a.hireDate=b.hireDateORDER BY a.hireDate或者SELECT a.employeeName,a.hireDate,b.employeeName,b.hireDateFROM Employee a,Employee bWHERE a.employeeNo!=b.employeeNo and a.employeeNameb.employeeName and year(a.hireDate)=year(b.hireDate)and month(a.hireDate)=month(b.hireDate)and day(a.hireDate)=day(b.hireDate)ORDER BY a.hireDate11、在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。1）首先计算订单主表的订单金额 UPDATE OrderMaster set orderSum=totalSumFROM OrderMaster a,(SELECT orderNo,sum(quantity*price) totalSum FROM OrderDetail GROUP BY orderNo) bWHERE a.orderNO=b.orderNo2）SELECT *FROM OrderMasterWHERE orderSumany (SELECT orderSum FROM OrderMaster WHERE salerNo=E2005002 AND orderDate=2008-01-09 )第二种方法UPDATE OrderMasterSET orderSum=XFROM OrderMaster a,(SELECT orderNo,sum(quantity*price)X FROM OrderDetail GROUP BY orderNo)bWHERE a.orderNo=b.orderNoSELECT *FROM OrderMaster WHERE orderSumany (SELECT orderSum FROM OrderMaster WHERE salerNo=E2005002 AND orderDate=2008-01-09 )12、查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额命令：select customerNo,orderNo,orderSumFROM OrderMasterWHERE customerNo IN(SELECT customerNo FROM OrderMaster a,OrderDetail b,Product c WHERE a.orderNo=b.orderNo AND ductNo=ductNo AND productName=17寸显示器) AND customerNo IN(SELECT customerNo FROM OrderMaster a,OrderDetail b,Product c WHERE a.orderNo=b.orderNo AND ductNo=ductNo AND productName=52倍速光驱)13、查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。命令：SELECT b.employeeName,b.sex,b.department,b.headShipFROM Employee a,Employee bWHERE a.department=b.department and a.employeeName=陈诗杰或SELECT b.employeeName, b.sex, b.department, b.headShipFROM (SELECT department FROM Employee WHERE employeeName=陈诗杰) a, Employee bWHERE a.department = b.department14、查询每种商品的商品编号、商品名称、订货数量和订货单价。SELECT ductNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE ductNo=ductNo 15、查询单价高于400元的商品编号、商品名称、订货数量和订货单价SELECT ductNo,productName,quantity,priceFROM Product a,OrderDetail bWHERE ductNo=ductNo AND productPrice40016、分别使用左外连接、右外连接、完整外连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。左外连接：20行SELECT ductNo,productName,quantity,priceFROM Product as a left outer join OrderDetail as b on a.ProductNo=b.ProductNo andproductPrice400右外连接：38行SELECT a.ProductNo,a.ProductName,b.quantity,b.priceFROM Product as a right outer join OrderDetail as b on a.ProductNo=b.ProductNo and productPrice400完整外连接51行SELECT a.ProductNo,a.ProductName,b.quantity,b.priceFROM Product as a full outer join OrderDetail as b on a.ProductNo=b.ProductNo and productPrice400 从上述结果可知：若表a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。17、查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd格式显示。命令：SELECT a.employeeNo,a.employeeName,性别= case a.sex when M then 男 when F then 女 end,b.ProductName,c.quantity,c.price,orderSum=quantity*price,(convert(char(20),d.orderDate,120) as orderDateFROM Employee a,Product b,OrderDetail c,OrderMaster dWHERE a.employeeNo=d.salerNo and b.ProductNo=c.ProductNo and c.orderNo=d.orderNoORDER BY employeeNo18、查找在2008年3月中有销售记录的客户编号、名称和订单总额。命令：SELECT a.CustomerNo,a.CustomerName, sum(quantity*price)orderSumFROM Customer a,OrderMaster b, orderdetail cWHERE a.CustomerNo=b.CustomerNo and b.orderNo=c.orderNo and year(OrderDate)=2008 and month(OrderDate)=3GROUP BY a.CustomerNo,a.CustomerName19、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。命令：SELECT a.CustomerNo,a.CustomerName,orderDate=isnull(convert(char(10),b.orderDate,120),日期不详),orderSumFROM Customer as a left outer join OrderMaster b on a.CustomerNo=b.CustomerNo ORDER BY a.CustomerNo,orderSum desc20、查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。命令：SELECT employeeName,CASE sex WHEN M THEN男 WHEN F THEN女 END sex, orderDate,quantity,quantity*price totalFROM Employee a,OrderMaster b,OrderDetail c,Product dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND ductNo=ductNo AND productName=32M DRAM21、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。命令：SELECT a.employeeNo,a.employeeName,a.sex,b.ProductName,c.quantity,c.price, sum(quantity*price)orderSum,d.orderDateFROM Employee a,Product b,OrderDetail c,OrderMaster dWHERE a.employeeNo=d.salerNo and d.orderNo=c.orderNo and c.ProductNo=b.ProductNoGROUP BY a.employeeNo,a.employeeName,a.sex,b.ProductName,c.quantity,c.price,d.orderDate22、查询客户姓名为“客户丙”所购货物的“客户名称”、“订单金额”、“订货日期”和“电话号码”。命令：SELECT a.CustomerName,b.orderSum,b.orderDate,a.telephoneFROM Customer a,OrderMaster bWHERE a.CustomerNo=b.CustomerNo and a.CustomerName=客户丙GROUP BY a.CustomerName,b.orderSum,b.orderDate,a.telephone/SELECT a.CustomerName, sum(quantity*price) orderSum, b.orderDate,a.telephoneFROM Customer a,OrderMaster b,orderdetail cWHERE a.CustomerNo=b.CustomerNo and b.orderNo=c.orderNo and a.CustomerName=客户丙GROUP BY a.CustomerName,orderSum,b.orderDate,a.telephone23、找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。命令：SELECT a.orderNo,sum(quantity*price)orderSumFROM OrderMaster a,Employee b,orderdetail cWHERE a.salerNo=b.employeeNo and a.orderNo=c.orderNo and b.sex=MGROUP BY a.orderNoHAVING sum(quantity*price)2000 24、查询来自上海市的客户的姓名、电话、订单号及订单金额。命令：SELECT a.CustomerName,a.telephone,orderNo,orderSumFROM Customer a,OrderMaster bWHERE a.CustomerNo=b.CustomerNo and a.address=上海市或SELECT a.CustomerName,a.telephone,b.orderNo,b.orderSumFROM Customer a,OrderMaster bWHERE a.CustomerNo=b.CustomerNo AND a.address LIKE %上海%25、 实验问题连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。查询表可以用在什么地方？使用查询表要注意哪些地方？答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。分析条件BETWEEN . AND、AND、OR等关键字的使用方法。答：BETWEEN . AND是范围查询，可用于查询属性值在某一个范围内的元组，NOT BETWEEN .AND 可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。用BETWEEN.AND能实现的查询，也可以用AND来达到。同样，用NOT BETWEENAND能实现的查询，也可以用OR来实现。请总结