实验五-复杂查询-实验报告.docxVIP

实验五 复杂查询1实验目的与要求(1) 熟练掌握SQL语句的使用。(2) 熟练使用SQL语句进行连接操作。2 实验内容在订单明细表中查询订单金额最高的订单。select a.*from orderdetail a,ordermaster bwhere a.orderno=b.orderno and ordersum=(selectmax(ordersum)from ordermaster)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。select productno 商品编号,orderno 订单编号,quantity 订货数量,quantity*price 订货金额from orderdetailwhere productno in(select productnofrom orderdetailgroupby productno havingcount(*)=3)orderby quantity desc查找销售总额少于5000元的销售员编号、姓名和销售额。select a.salerno 销售员编号,b.employeename 姓名,sum(ordersum)销售额from ordermaster a,employee bwhere a.salerno=b.employeenogroupby a.salerno,b.employeenamehavingsum(ordersum)5000找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。select salerno 销售员编号,sum(ordersum)销售业绩from ordermastergroupby salernohavingsum(ordersum)=5000orderbysum(ordersum)desc查询订购的商品数量没有超过10个的客户编号和客户名称。select a.customerno 客户编号,customername 客户名称from customer a,ordermaster b,orderdetail cwhere a.customerno=b.customerno and b.orderno=c.ordernogroupby a.customerno,customernamehavingsum(quantity)=10查找订货金额最大的客户名称和总货款。select customerno 客户名称,sum(ordersum)总货款from ordermastergroupby customernohavingsum(ordersum)=(selectmax(sumorder)from(select customerno,sum(ordersum)as sumorderfrom ordermastergroupby customerno)b)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。select a.customerno 客户编号,customername 客户名称,b.productno 商品编号,productname 商品名称,quantity 数量,quantity*price 金额from customer a,product b,ordermaster c,orderdetail dwhere a.customerno=c.customerno and b.productno=d.productno and c.orderno=d.ordernoand d.orderno in(select orderno from orderdetailgroupby ordernohavingcount(productno)=3)orderby a.customerno找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。select salerno 销售员编号,sum(ordersum)销售业绩from ordermastergroupby salernohavingsum(ordersum)4000orderbysum(ordersum)desc求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select customerno 客户编号,productno 商品编号,sum(quantity)总数量,avg(price)平均单价from ordermaster a,orderdetail bwhere a.orderno=b.ordernogroupby customerno,productnoorderby customerno,productno查询业绩最好的的业务员号、业务员名及其总销售金