上机实验 实验六

顾客所下订单的产品总数量1.过订单的客户编号,联系人名，公司名，地址；selectcustomerid,CONTACTNAME,COMPANYNAME,addressfromcustomerswherenotexists(select*fromorderswherecustomers.customerid=orders.customerid)selectcustomerid,CONTACTNAME,COMPANYNAME,addressfromcustomerswherecustomerid notselectcustomeridfromorders)用相关子查询,查找订货总数量超过5000selectEMPLOYEEID,LASTNAME,FIRSTNAMEfromEMPLOYEESwhereexists(select fromorders,orderdetailswhereorders.orderid=orderdetails.orderidandorders.EMPLOYEEID=EMPLOYEEs.EMPLOYEEIDgroupbyemployeeidhavingsum(Quantity)>5000)用不相关子查询,查找订货总数量超过5000selectEMPLOYEEID,LASTNAME,FIRSTNAMEfromEMPLOYEESwhereexists(select fromorders,orderdetailswhereorders.orderid=orderdetails.orderidgroupbyemployeeidhavingsum(Quantity)>5000)用相关子查询，查找订货总数量大于1200的产品信息select*from productswhereexists(select ductid=ductidgroupbyproductidhavingsum(quantity)>1200)用不相关子查询，查找订货总数量大于1200select*from productswhereproductidselect productidfromorderdetailsgroupbyproductidhavingsum(quantity)>1200)找出完成订单个数最多的员工的员工号selectemployeeidfromemployeeswhereemployeeidin(selectemployeeidfromordersgroupbyemployeeidhavingcount(orderid)>=all(selectcount(orderid)fromordersgroupbyemployeeid))找出完成定单selectLastName,FirstName,EmployeeIDfromemployeeswhereemployeeidin(selectemployeeidfromordersgroupbyemployeeidhavingcount(orderid)>=all(selectcount(orderid)fromordersgroupbyemployeeid))量最多的职工姓、名、编号用不相关子查询，查出公司名首字母为顾客所下订单的产品总数量selectsum(Quantity)fromOrderDetailswhereOrderIDin(selectOrderIDfromOrder