复杂查询实验报告

1、 复杂查询实验报告江培健 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次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。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的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额(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表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。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) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出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=业务科 or department=财务科 or department=办公室)(12) 计算每一种商品的销售数量、平均销售单价和总销售金额select productno,sum(quantity)销售数量,avg(price)平均销售单价,sum(quantity*price)总销售金额from orderdetailgroup by productno(13) 查找至少有3次销售的业务员名单和销售日期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) 查询订单中所订购的商品数量没有超过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