SQL-实验2-2.doc

1、1-1、查询住址在上海的员工所做的订单，结果输出员工编号、姓名、住址、订单编号、客户编号和订单日期，并按客户编号排序输出；SELECT employeeNo,employeeName,address,orderNo,customerNo,orderDateFROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNo AND address LIKE %上海%ORDER BY customerNo -2、查找订购了“M DRAM”的商品的客户编号、客户名称、订单编号、订货数量和订货金额，并按客户编号排序输出；SELECT b.customer

2、No,customerName,a.orderNo,quantity,quantity*price totalpriceFROM OrderMaster a,Customer b,OrderDetail c,Product dWHERE a.orderNo=c.orderNo AND a.customerNo=b.customerNo AND c.productNo=d.productNo AND productName=32M DRAMORDER BY customerNo-3、查找与“张小梅”在同一个部门工作的员工姓名、所属部门、性别和出生日期，并按所属部门排序输出SELECT emplo

3、yeeName,a.department,sex,birthdayFROM Employee a,(SELECT department FROM Employee WHERE employeeName=张小梅 ) bWHERE a.department=b.departmentORDER BY department-4、查询年出生的员工所负责的订单，输出结果为员工编号、姓名、所属部门、订单编号、客户名称、订单日期、按员工编号排序输出；SELECT a.employeeNo 员工编号,a .employeeName 姓名,a. department 所属部门,b.orderNo 订单编号,c.c

4、ustomerName 客户名称, b.orderDate 订单日期FROM Employee a,OrderMaster b,Customer cWHERE b.customerNo=c.customerNo and a.employeeNo=b.salerNo AND YEAR(a.birthday)=1973ORDER BY employeeNo-5、查询单张订单中销售数量大于的商品编号、商品名称、数量和单价；SELECT a.productNo,productName,quantity,price FROM Product a,OrderDetail bWHERE quantity4

5、AND a.productNo=b.productNo-6、查询每个客户订购商品的订单信息，输出结果为客户编号、客户名称、商品编号、商品名称、数量、单价和金额；SELECT a.customerNo 客户编号,a .customerName 客户名称,b. productNo 商品编号,b.productName 商品名称,c .quantity 数量, c.price 单价, d.orderSum 金额FROM Customer a,Product b,OrderDetail c,OrderMaster dWHERE b.productNo=c.productNo AND a.custome

6、rNo=d.customerNo AND 2d.orderNo=c.orderNo-7、查找“ 倍速光驱”的销售情况，要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额，其中性别用男、女显示，销售日期以yyyy-mm-dd格式显示。SELECT a.employeeName 姓名,a .sex 性别,b. orderDate 销售日期,c .quantity 销售数量,quantity*price 销售金额FROM Employee a,OrderMaster b,OrderDetail c ,Product dWHERE a.employeeNo=b.salerNo AND b.or

7、derNo=c.orderNo AND d.productNo=c.productNo AND d.productName=52倍速光驱-8、查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额；SELECT a.orderNo,b.customerName,c.employeeName,priceFROM OrderMaster a,Customer b,Employee c,OrderDetail dWHERE orderSum=(SELECT max(orderSum)FROM OrderMaster)AND a.customerNo=b.customerNo AND c.e

8、mployeeNo=a.salerNo AND a.orderNo=d.orderNo-9、查询“ 倍速光驱”商品的订购数量、订购平均价和订购总金额；SELECT b.quantity,avg(quantity*price) avgprice,sum(quantity*price) totalpriceFROM OrderMaster a,OrderDetail b,Product cWHERE a.orderNo=b.orderNo AND c.productNo=b.productNo AND productName=52倍速光驱GROUP BY b.quantity-10、查询订购了“倍

9、速光驱” 商品且订货数量介于4之间的订单编号、订货数量和订货金额；SELECT orderNo,quantity,quantity*priceFROM OrderDetail a, Product bWHERE a.productNo=b.productNo AND productName=52倍速光驱 AND (quantity BETWEEN 2 AND 4)-11、在订单主表中查询每个业务员的订单数量；SELECT salerNo,count(orderNo)订单数量FROM OrderMasterGROUP BY salerNo-12、统计在业务科工作且在年或年出生的员工人数和平均工资

10、；SELECT count(employeeNo)员工人数, avg(salary)平均工资FROM EmployeeWHERE department=业务科 AND (YEAR(birthday)=1973 OR YEAR(birthday)=1967)3-13、在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出；SELECT productNo,sum(quantity)销售数量,sum (price)销售金额FROM OrderDetailGROUP BY productNoORDER BY sum(price)-14、统计客户号为“C2005001” 的客户的订单数

11、、订货总额和平均订货金额；SELECT count(b.orderNo)订单数,sum (price)订货总额,avg(quantity*price) 平均订货金额FROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNo AND a.customerNo=C2005001-15、统计每个客户的订单数、订货总额和平均订货金额；SELECT a.customerNo,count(b.orderNo)订单数,sum( price)订货总额,avg(quantity*price)平均订货金额FROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNoGROUP BY a.customerNo-16、已知每个订单可订购多种商品，查询所订购的商品种类在种（含种）以上并且其中至少有种（含种）商品的数量在件（含件）以上的订单编号。-查询订单中至少包含种（含种）以上商品的订单编号及订购次数，且订购的商品数量在件（含件）以上。SELECT orderNo,count(productNo)商品种类, count(orderNo) 商品数量FROM OrderDetailGROUP BY orderNoHAVING count(productNo)=3 AND count(orderNo)=3