上機(jī)實驗 實驗六

顧客所下訂單的產(chǎn)品總數(shù)量1.過訂單的客戶編號,聯(lián)系人名，公司名，地址；selectcustomerid,CONTACTNAME,COMPANYNAME,addressfromcustomerswherenotexists(select*fromorderswherecustomers.customerid=orders.customerid)selectcustomerid,CONTACTNAME,COMPANYNAME,addressfromcustomerswherecustomerid notselectcustomeridfromorders)用相關(guān)子查詢,查找訂貨總數(shù)量超過5000selectEMPLOYEEID,LASTNAME,FIRSTNAMEfromEMPLOYEESwhereexists(select fromorders,orderdetailswhereorders.orderid=orderdetails.orderidandorders.EMPLOYEEID=EMPLOYEEs.EMPLOYEEIDgroupbyemployeeidhavingsum(Quantity)>5000)用不相關(guān)子查詢,查找訂貨總數(shù)量超過5000selectEMPLOYEEID,LASTNAME,FIRSTNAMEfromEMPLOYEESwhereexists(select fromorders,orderdetailswhereorders.orderid=orderdetails.orderidgroupbyemployeeidhavingsum(Quantity)>5000)用相關(guān)子查詢，查找訂貨總數(shù)量大于1200的產(chǎn)品信息select*from productswhereexists(select ductid=ductidgroupbyproductidhavingsum(quantity)>1200)用不相關(guān)子查詢，查找訂貨總數(shù)量大于1200select*from productswhereproductidselect productidfromorderdetailsgroupbyproductidhavingsum(quantity)>1200)找出完成訂單個數(shù)最多的員工的員工號selectemployeeidfromemployeeswhereemployeeidin(selectemployeeidfromordersgroupbyemployeeidhavingcount(orderid)>=all(selectcount(orderid)fromordersgroupbyemployeeid))找出完成定單selectLastName,FirstName,EmployeeIDfromemployeeswhereemployeeidin(selectemployeeidfromordersgroupbyemployeeidhavingcount(orderid)>=all(selectcount(orderid)fromordersgroupbyemployeeid))量最多的職工姓、名、編號用不相關(guān)子查詢，查出公司名首字母為顧客所下訂單的產(chǎn)品總數(shù)量selectsum(Quantity)fromOrderDetailswhereOrderIDin(selectOrderIDfromOrder