SQL 实验2-3.doc

-1、查询员工“张小娟”所做的订单信息SELECT *FROM OrderMasterWHERE salerNo IN (SELECT employeeNo FROM Employee WHERE employeeName=张小娟)-2、查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按邮政编码降序排序SELECT customerNo,customerName,zipFROM CustomerWHERE customerNo NOT IN(SELECT customerNo FROM OrderMaster WHERE address LIKE%北京%)ORDER BY zip DESC-3、查询订购了“M DRAM”商品的订单编号、订货数量和订货单价；SELECT orderNo,quantity,priceFROM OrderDetailWHERE productNo IN(SELECT productNo FROM Product WHERE productName=32M DRAM) -4、查询与员工编号E2008005在同一个部门的员工编号、姓名、性别、所属部门SELECT employeeNo,employeeName,sex,departmentFROM EmployeeWHERE department IN(SELECT department FROM Employee WHERE employeeNo=E2008005)-5、查询既订购了P2005003商品，又订购了P2007001商品的客户编号、订单编号和订单金额；SELECT a.customerNo,a.orderNo,sum(quantity * price)orderpriceFROM OrderMaster a,OrderDetail bWHERE 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,customerNameFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.customerNo=c.customerNo AND c.orderNo=d.orderNo AND ductNo=ductNo AND NOT EXISTS (SELECT ductNo FROM Product WHERE productName=52倍速光驱 AND productName=17寸显示器)GROUP BY a.customerNo,customerName-7、查询订购了“键盘”商品的客户姓名、订货数量和订货日期SELECT customerName,quantity,orderDate,productNameFROM Customer a,OrderMaster b,Product c,OrderDetail dWHERE a.customerNo=b.customerNo AND b.orderNo=d.orderNo AND ductNo=ductNo AND ductNo IN(SELECT productNo FROM product WHERE productName LIKE %键盘%)-8、查询没有订购“键盘”商品的客户名称SELECT distinct customerNameFROM Customer a,OrderMaster b,Product c,OrderDetail dWHERE a.customerNo=b.customerNo AND b.orderNo=d.orderNo AND ductNo=ductNo AND ductNo NOT IN(SELECT productNo FROM product WHERE productName LIKE %键盘%)-9、查询至少销售了种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出SELECT salerNo,employeeName,productName,quantity,priceFROM Product a,OrderDetail b,OrderMaster c,Employee dWHERE ductNo=ductNo AND b.orderNo=c.orderNo AND c.salerNo=d.employeeNo AND salerNo IN (SELECT salerNo FROM OrderDetail e,OrderMaster f WHERE e.orderNo=f.orderNo GROUP BY salerNo HAVING count(distinct productNo)=5)ORDER BY salerNo-10、查询至少包含了“世界技术开发公司”所订购的商品的客户编号、客户名称、商品编号、商品名称、数量和金额SELECT a.customerNo,customerName,ductNo,productName,quantity,priceFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.customerNo=c.customerNo AND c.orderNo=d.orderNo AND ductNo=ductNo AND EXISTS (SELECT * FROM OrderDetail d,OrderMaster e,Customer f WHERE d.orderNo=e.orderNo AND e.customerNo=f.customerNo AND customerName=世界技术开发公司 AND EXISTS (SELECT * FROM OrderMaster g,OrderDetail h WHERE g.orderNo=h.orderNo)-11、查找订购了“M DRAM”商品的客户编号、客户名称、订货总数量和订货总金额SELECT a.customerNo,customerName,c.orderNo,quantity,price*quantity totalpriceFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.customerNo=c.customerNo AND c.orderNo=d.orderNo AND ductNo=ductNo AND productName=32M DRAM-12、查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品编号、商品所属类别、商 -品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出SELECT customerName 客户名称,ductNo 商品编号,productClass 商品所属类别,sum(quantity) 商品数量,sum(quantity*price) 订货金额FROM Product a,OrderDetail b,OrderMaster c,Customer dWHERE ductNo=ductNo AND b.orderNo=c.orderNo AND c.customerNo=d.customerNoGROUP BY customerName,ductNo,productClass,d.customerNoORDER BY d.customerNo,sum(quantity*price) DESC-13、按商品类别查询每类商品的订货平均单价在元（含元）以上的订货总数量、订货平均单价和订货总金额SELECT productClass,sum(quantity) totalQuantity,avg(price) avgPrice,sum(price*quantity) totalPriceFROM Product a,OrderDetail bWHERE ductNo=ductNoGROUP BY productClassHAVING avg(price)=280-14、查找至少有次销售的业务员名单和销售日期-SELECT salerNo,orderDate-FROM OrderMaster-GROUP BY salerNo,orderDate-HAVING count(orderNo)=2 不对。可以不同的销售日期。SELECT salerNo,orderDateFROM OrderMasterWHERE salerNo IN (SELECT salerNo FROM OrderMaster GROUP BY salerNo HAVING count(*)=2)ORDER BY salerNo-15、查找销售金额最大的客户名称和总货款额SELECT customerName,totalFROM(SELECT TOP 1 c.customerNo,sum(quantity*price) total FROM OrderDetail a,OrderMaster b,Customer c WHERE a.orderNo=b.orderNo AND b.customerNo=c.customerNo GROUP BY c.customerNo) x,Customer yWHERE x.customerNo=y.customerNo-16、查找销售总额少于元的销售员编号、姓名和销售额SELECT salerNo,employeeName,quantity*price 销售额FROM Employee a,OrderMaster b,OrderDetail cWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND quantity*price=3)-18、查找同时订购了商品编号为“P2007003”和商品编号为“P2007001”的商品的客户编号、 -客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出(可以在不同订单中订购)SELECT a.customerNo,customerName,ductNo,productName,quantityFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.customerNo=c.customerNo AND c.orderNo=d.orderNo AND ductNo=ductNo AND ductNo IN(SELECT ductNo FROM Product WHERE ductNo IN(p2007003,p2007001)ORDER BY a.customerNo-19、计算每一商品每月的销售金额总和，并将结果首先按销售月份