SQL 实验2-3.docVIP

--1、查询员工“张小娟”所做的订单信息 SELECT * FROM OrderMaster WHERE salerNo IN (SELECT employeeNo FROM Employee WHERE employeeName=张小娟) --2、查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按邮政编码降序排序 SELECT customerNo,customerName,zip FROM Customer WHERE customerNo NOT IN(SELECT customerNo FROM OrderMaster WHERE address LIKE%北京%) ORDER BY zip DESC --3、查询订购了“M 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) --5、查询既订购了P2005003商品，又订购了P2007001商品的客户编号、订单编号和订单金额； SELECT a.customerNo,a.orderNo,sum(quantity * price)orderprice FROM OrderMaster a,OrderDetail b WHERE a.orderNo=b.orderNo AND (a.orderNo In(SELECT a.orderNo FROM OrderDetail WHERE productNo=P2005003))AND (a.orderNo In(SELECT a.orderNo FROM OrderDetail WHERE productNo=P2007001)) GROUP BY a.customerNo,a.orderNo --6、查询没有订购“倍速光驱”或没有订购“寸显示器”的客户编号、客户名称 SELECT a.customerNo,customerName FROM Customer a,Product b,OrderMaster c,OrderDetail d WHERE a.customerNo=c.customerNo AND c.orderNo=d.orderNo AND b.productNo=d.productNo AND NOT EXISTS (SELECT b.productNo FROM Product WHERE productName=52倍速光驱 AND productName=17寸显示器) GROUP BY a.customerNo,customerName --7、查询订购了“键盘”商品的客户姓名、订货数量和订货日期 SELECT customerName,quantity,orderDate,productName FROM Customer a,OrderMaster b,Product c,OrderDetail d WHERE a.customerNo=b.customerNo AND b.orderNo=d.orderNo AND c.productNo=d.productNo AND c.productNo IN(SELECT productNo FROM product WHERE productName LIKE %键盘%) --8、查询没有订购“键盘”商品的客户名称 SELECT distinct customerName FROM Customer a,OrderMaster b,Product c,OrderDetail d WHERE a.customerNo=b.customerNo AND b.orderNo=d.orderNo AND c.productNo=d.productNo AND c.productNo NOT IN(SELECT prod