復(fù)雜查詢實驗報告

1、 復(fù)雜查詢實驗報告江培健 101404215 10計教（2）班(1) 查找有銷售記錄的客戶編號、名稱和訂單總額。select a.customerno,customername, sum(quantity*price) ordersumfrom ordermaster a,orderdetail b,customer cwhere b.orderno=a.orderno and c.customerno=a.customerno group by a.customerno,customernameorder by a.customerno,ordersum desc(2) 在訂單明細表中查詢訂

2、單金額最高的訂單(3) select top 1 orderno,sum(quantity*price)訂單金額from orderdetailgroup by ordernoorder by 訂單金額 desc3查詢沒有訂購商品的客戶編號和客戶名稱select customerno,customernamefrom customer where customerno not in (select customerno from ordermaster)(4) (4) 找出至少被訂購3次的商品編號、訂單編號、訂貨數(shù)量和訂貨金額，并按訂貨數(shù)量的降序排序輸出。select productno ,o

3、rderno ,quantity ,quantity *price 訂貨金額from orderdetail where productno in(select productno from orderdetail group by productno having count(*)=3)order by productno desc (5) (5) 使用子查詢查找16m dram的銷售情況，要求顯示相應(yīng)的銷售員的姓名、性別，銷售日期、銷售數(shù)量和金額(6)select employeename,case sex(7) when f then 女(8) when m then 男 end sex

4、,(9) orderdate,quantity,quantity*price 金額(10)from employee a,ordermaster b,orderdetail c(11)where a.employeeno=b.salerno and b.orderno=c.orderno(12) and c.productno in (13) select f.productno(14) from ordermaster d,orderdetail e,product f(15) where d.orderno=e.orderno and productname=32m dram)(6)(7)

5、 (6) 查詢ordermaster表中訂單金額最高的訂單號及訂單金額select orderno ,ordersum from ordermaster where ordersum =(select max(ordersum ) from ordermaster )(7) 計算出一共銷售了幾種商品select count(*)種類from (select distinct productno from orderdetail)a(8) 顯示orderdetail表中每種商品的訂購金額總和，并且依據(jù)銷售金額由大到小排序輸出。select productno ,sum(quantity *pri

6、ce ) 訂購金額from orderdetail group by productno order by 訂購金額 desc(9) 查找銷售總額大于1000元的銷售員編號、姓名和銷售額select salerno,employeename,sum(ordersum)totalfrom ordermaster a,employee bwhere employeeno=salerno and ordersum1000group by salerno,employeename(10) 找出目前業(yè)績未超過5000元的員工，并按銷售業(yè)績的降序排序輸出select employeeno,employee

7、name,sumorderfrom (select employeeno,employeename from employee)x left join (select salerno,sum(sumorder) sumorder from (select salerno,sumorder=quantity*price from ordermaster a left outer join orderdetail b on a.orderno=b.orderno)m group by salerno)y on x.employeeno=y.salerno where sumorder(select

8、 avg(salary) avgsalary from employee where department=業(yè)務(wù)科 or department=財務(wù)科 or department=辦公室)(12) 計算每一種商品的銷售數(shù)量、平均銷售單價和總銷售金額select productno,sum(quantity)銷售數(shù)量,avg(price)平均銷售單價,sum(quantity*price)總銷售金額from orderdetailgroup by productno(13) 查找至少有3次銷售的業(yè)務(wù)員名單和銷售日期select employeename,orderdatefrom orderma

9、ster a left join employee b on b.employeeno=a.salerno where salerno=(select salerno from ordermaster group by salerno having count(*)2)(14) 查詢訂單中所訂購的商品數(shù)量沒有超過10個的客戶編號和客戶名稱select x.customerno,customernamefrom ordermaster x,customer ywhere x.customerno=y.customerno and orderno in (select orderno from orderdetail group by orderno having sum(quantity)40000order by sumorder desc一、 (17) 求出每位客戶的總訂購金額，顯示出客戶號及總訂購金額，并按總訂購金額降序排列。select x.customerno,sum(quantity*price)訂購金額from customer x,(select a.orderno,quantity,price,customerno from orderdetail a left join ordermaster b on a.orderno