数据库系统原理与设计(第二版)实验教程实验二.doc

1.select a.employeeNo,a.employeeName,a.hireDatefrom Employee a,Employee bwhere a.employeeNo!=b.employeeNo and a.employeeName!=b.employeeName and a.hiredate=b.hiredateorder by b.employeeNo2. SELECT employeeName,sex,department,headShipFROM EmployeeWHERE department IN(SELECT departmentFROM EmployeeWHERE employeeName=陈诗杰)3. SELECT *FROM EmployeeWHERE salary=(SELECT AVG(salary)FROM Employee)4. select a.customerNo,a.customerName,sum(quantity*price) ssumfrom Customer a,OrderMaster b,OrderDetail cwhere a.customerNo=b.customerNo and b.orderNo=c.orderNogroup by a.customerNo,a.customerName5. SELECT customerNo,customerNameFROM CustomerWHERE customerNo NOT IN(SELECT customerNoFROM OrderMaster)6 SELECT employeeName,case sex when F then 女 when M then 男 end sex, orderDate,quantity,quantity*price 金额FROM Employee a,OrderMaster b,OrderDetail cWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT f.ProductNo FROM OrderMaster d,OrderDetail e,Product f WHERE d.orderNo=e.orderNo AND ProductName=32M DRAM).7. select orderNo,orderSumfrom OrderMaster where orderSum = (select max(orderSum) from OrderMaster)8. SELECT *FROM OrderMasterWHERE orderSum (SELECT MAX(orderSum)FROM OrderMasterWHERE salerNo=E2005002 AND orderDate=2008-1-9)9. SELECT PductNo,productName,quantity,priceFROM OrderDetail,ProductWHERE OrderDductNo=PductNoAND price40010.左外连接：SELECT PductNo,productName,quantity,priceFROM OrderDetail LEFT JOIN Product ON OrderDductNo=PductNoWHERE price400右外连接：SELECT PductNo,productName,quantity,priceFROM OrderDetail RIGHT JOIN Product ON OrderDductNo=PductNoWHERE price400完整外部链接：SELECT PductNo,productName,quantity,priceFROM OrderDetail FULL JOIN Product ON OrderDductNo=PductNoWHERE price40011. SELECT Customer.customerNo,customerName,orderDate=CONVERT(char(10),orderDate,120),orderSumFROM Customer LEFT JOIN OrderMaster ON Customer.customerNo=OrderMaster.customerNoORDER BY Customer.customerNo,orderSum DESC12. SELECT Employee.employeeNo,employeeName,sex=CASE sex WHEN M THEN 男 WHEN F THEN 女 END,productName,quantity,price,totalPrice=quantity*price,orderDate=CONVERT(char(10),orderDate,120)FROM Employee,OrderMaster,OrderDetail,ProductWHERE Employee.employeeNo=OrderMaster.salerNoAND OrderMaster.orderNo=OrderDetail.orderNoAND OrderDductNo=PductNo13. SELECT employeeName, sex=CASE sex WHEN M THEN 男 WHEN F THEN 女 END, orderDate=CONVERT(char(10),orderDate,120),quantity,money=quantity*priceFROM Employee,OrderMaster,OrderDetail,ProductWHERE Employee.employeeNo=OrderMaster.salerNoAND OrderMaster.orderNo=OrderDetail.orderNoAND OrderDductNo=PductNoAND productName=16M DRAM14. SELECT orderNo,orderSumFROM Employee,OrderMasterWHERE Employee.employeeNo=OrderMaster.salerNoAND sex=M AND orderSum=200015. SELECT PductNo,productName,SUM(quantity) AS totalNum,SUM(quantity*price) AS totalPriceFROM Product,OrderDetailWHERE PductNo=OrderDductNoGROUP BY PductNo,productNameORDER BY PductNo16（1）连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。（2）查询表可以用在什么地方？使用查询表要注意哪些地方？答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。（3）分析SQL语句中的in和or关键字有何异同点？它们可以互换吗？给出实例说明。in可用于集合查询，也可用于嵌套子查询中。or则为逻辑查询，逻辑或。当in用于集合查询时，in和or之间可以相互转换；当in用于嵌套子查询时，则不可以相互转换。举例：用IN来实现：SELECT studentNo,courseNo,score FROM Score WHERE courseNo IN(001,005,003)用OR来实现：SELECT studentNo,courseNo FROM Score WHERE courseNo=001 OR courseNo=005 OR courseNo=003但是当IN 用于子查询时，此时IN不能和OR互换。举例：SELECT st