内容发布更新时间 : 2024/5/12 0:20:37星期一 下面是文章的全部内容请认真阅读。

数据库原理实验教材实验答案 实验二 (1) 找出同一天进入公司服务的员工。 Select a.employeeNo,a.employeeName,a.hireDate, b.employeeNo,b.employeeName from Employee a,Employee as b where a.employeeNo!=b.employeeNo and a.employeeName>b.employeeName and (a.hireDate=b.hireDate) 参考解答： SELECT DISTINCT a.hireDate, a.employeeNo, a.employeeName -- 消除重复的元组 FROM employee a, employee b WHERE a.employeeNo!=b.employeeNo --本人不跟本人连接 AND a.hireDate=b.hireDate ORDER BY a.hireDate, a.employeeNo select a.hireDate, a.employeeNo, a.employeeName, b.employeeNo, b.employeeName from employee a, employee b where a.employeeNo ( SELECT avg(salary) avgsalary FROM Employee) (4) 查找有销售记录的客户编号、名称和订单总额。 select a.customerNo,a.customerName,b.orderNo,sum(quantity*price) from Customer a,OrderMaster b,OrderDetail c where a.customerNo=b.customerNo and b.orderNo=c.orderNo group by a.customerNo,a.customerName,b.orderNo 参考解答： select a.customerNo,a.customerName,sum(orderSum) from Customer a,OrderMaster b where a.customerNo=b.customerNo group by a.customerNo,a.customerName select a.customerNo,a.customerName,b.orderNo,orderSum from Customer a,OrderMaster b where a.customerNo=b.customerNo order by a.customerNo,b.orderNo (5) 查询没有订购商品的客户编号和客户名称。 SELECT a.customerNo,customerName FROM Customer a WHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster ) (6) 使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。 SELECT employeeName,case sex when 'F' then '女' when 'M' then '男' end sex, orderDate,quantity,quantity*price 金额 FROM Employee a,OrderMaster b,OrderDetail c WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT ProductNo FROM Product WHERE ProductName='32M DRAM') 参考解答： SELECT employeeName,case sex when 'F' then '女' when 'M' then '男' end sex, orderDate,quantity,quantity*price 金额 FROM Employee a,OrderMaster b,OrderDetail c WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT ProductNo FROM Product WHERE ProductName='32M DRAM') (7) 查询OrderMaster表中订单金额最高的订单号及订单金额。 select orderNo,orderSum from OrderMaster where orderSum=(select max(orderSum) from OrderMaster) (8) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 SELECT * FROM OrderMaster WHERE orderSum>any (SELECT orderSum FROM OrderMaster WHERE salerNo='E2005002' AND orderDate='20080109' ) (9) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。 SELECT a.productNo,productName,quantity,price FROM Product a, OrderDetail b WHERE a.productNo=b.productNo AND price>400 ORDER BY productName (10) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。 左外连接命令： SELECT a.productNo,productName,quantity,price FROM Product a LEFT OUTER JOIN OrderDetail b ON a.productNo=b.productNo WHERE price>400 Select a.productNo , a.productName , b.quantity,b.price From OrderDetail As b left JOIN Product As a ON (a.productNo=b.productNo) and price>400