復(fù)雜查詢(xún)實(shí)驗(yàn)報(bào)告

1、 復(fù)雜查詢(xún)實(shí)驗(yàn)報(bào)告江培健 101404215 10計(jì)教（2）班(1) 查找有銷(xiāo)售記錄的客戶編號(hào)、名稱(chēng)和訂單總額。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) 在訂單明細(xì)表中查詢(xún)訂

2、單金額最高的訂單(3) select top 1 orderno,sum(quantity*price)訂單金額from orderdetailgroup by ordernoorder by 訂單金額 desc3查詢(xún)沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱(chēng)select customerno,customernamefrom customer where customerno not in (select customerno from ordermaster)(4) (4) 找出至少被訂購(gòu)3次的商品編號(hào)、訂單編號(hào)、訂貨數(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) 使用子查詢(xún)查找16m dram的銷(xiāo)售情況，要求顯示相應(yīng)的銷(xiāo)售員的姓名、性別，銷(xiāo)售日期、銷(xiāo)售數(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) 查詢(xún)ordermaster表中訂單金額最高的訂單號(hào)及訂單金額select orderno ,ordersum from ordermaster where ordersum =(select max(ordersum ) from ordermaster )(7) 計(jì)算出一共銷(xiāo)售了幾種商品select count(*)種類(lèi)from (select distinct productno from orderdetail)a(8) 顯示orderdetail表中每種商品的訂購(gòu)金額總和，并且依據(jù)銷(xiāo)售金額由大到小排序輸出。select productno ,sum(quantity *pri

6、ce ) 訂購(gòu)金額from orderdetail group by productno order by 訂購(gòu)金額 desc(9) 查找銷(xiāo)售總額大于1000元的銷(xiāo)售員編號(hào)、姓名和銷(xiāo)售額select salerno,employeename,sum(ordersum)totalfrom ordermaster a,employee bwhere employeeno=salerno and ordersum1000group by salerno,employeename(10) 找出目前業(yè)績(jī)未超過(guò)5000元的員工，并按銷(xiāo)售業(yè)績(jī)的降序排序輸出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=財(cái)務(wù)科 or department=辦公室)(12) 計(jì)算每一種商品的銷(xiāo)售數(shù)量、平均銷(xiāo)售單價(jià)和總銷(xiāo)售金額select productno,sum(quantity)銷(xiāo)售數(shù)量,avg(price)平均銷(xiāo)售單價(jià),sum(quantity*price)總銷(xiāo)售金額from orderdetailgroup by productno(13) 查找至少有3次銷(xiāo)售的業(yè)務(wù)員名單和銷(xiāo)售日期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) 查詢(xún)訂單中所訂購(gòu)的商品數(shù)量沒(méi)有超過(guò)10個(gè)的客戶編號(hào)和客戶名稱(chēng)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) 求出每位客戶的總訂購(gòu)金額，顯示出客戶號(hào)及總訂購(gòu)金額，并按總訂購(gòu)金額降序排列。select x.customerno,sum(quantity*price)訂購(gòu)金額from customer x,(select a.orderno,quantity,price,customerno from orderdetail a left join ordermaster b on a.orderno