实验五_复杂查询答案{2012.4}.doc

------------------------------------------------------------------------------------------------------- (1）用子查询查询员工“张小娟”所做的订单信息。（难易程度：易） select * from ordermaster where salerno in ( select employeeno from employee where employeename=张小娟 ) ------------------------------------------------------------------------------------------------ （2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。（难易程度：易） select customerno,customername,zip from customer c where address=北京市 and not exists (select * from ordermaster o where c.customerno=o.customerno) order by zip desc 方法二： select CustomerNo,CustomerName,Zip from Customer where Address=北京市 and CustomerNo not in (select CustomerNo from OrderMaster ) order by Zip desc ----------------------------------------------------------------------------------------------------------------- （3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。 （难易程度：易） select orderno,quantity,price from orderdetail where productno in ( select productno from product where productname=32M DRAM ) ------------------------------------------------------------------------------------------------------------------- （4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。 （难易程度：易） select employeeno,employeename,sex,department from employee where department in ( select department from employee where employeeno =E2008005 ) and employeeno !=E2008005 ------------------------------------------------------------------------------------------------------------------- （5）查询既订购了品，又订购了品的客户编号，订单编号和订单金额。 （难易程度：中） （为了验证查询结果，改为：‘P2005001’和 ‘P2005002’） 方法一： select customerno,orderno,ordersum from ordermaster where orderno in ( select orderno from orderdetail where productno = P2005001 ) and orderno in ( select orderno from orderdetail where productno = P2005002 ) 方法二：可以使用表的自身连接。 Select customerno, orderno, ordersum from ordermaster where orderno in ( select a.orderno from orderdetail a,orderdetail b where a.productno=P2005001 and b.productno=P2005002 and a.or