09_常用统计函数习题.doc

1, 查询单科成绩最高的学生的姓名, 性别, 课程名称 按照姓名排序(多表关联,GroupBy子句) select b.stuname,b.stusex,c.classname from stuinfo b,classinfo c,scoreinfo d, (select classno,max(score) score from scoreinfo group by classno)a where d.classno=a.classno and d.score=a.score and b.stuno=d.stuno and c.classno=d.classno order by b.stuname 2, 查询单科成绩最低的学生姓名,性别 ,课程名,按照学号排序(多表关联,GroupBy子句) select b.stuname,b.stusex,c.classname from stuinfo b,classinfo c,scoreinfo d, (select classno,min(score) score from scoreinfo group by classno)a where d.classno=a.classno and d.score=a.score and b.stuno=d.stuno and c.classno=d.classno order by b.stuno 3, 查询所有科目平均分大于 80分的所有的学生的成绩, 课程名称,和姓名 按照学号排序 (多表关联 ,having 子句) select score,classname,stuname from scoreinfo b,classinfo c,stuinfo d, (select stuno, avg(score) from scoreinfo group by stuno having avg(score)=80)a where d.stuno=a.stuno and b.stuno=a.stuno and b.classno=c.classno order by d.stuno 4, 查询所有科目中,80分以上 或者 不及格的人的考试科目, 姓名 ,成绩 select classname,stuname,a.score from stuinfo b,classinfo c, (select * from scoreinfo where score=80 or score60)a where b.stuno=a.stuno and c.classno=a.classno order by classname 补充习题 要想完成这些任务，请参考附录A中的表。 确定Cooking种类中存在多少本书。 select count(isbn) cooking from books where category=cooking 显示零售价超过30.00美元的图书的数量。 select count(isbn) from books where cost=30 显示必威体育精装版出版的图书的日期。 select max(pubdate) from books 确定向客户1017售出的图书所产生的总利润。 select sum(retail-cost)*a.quantity from books b, (select isbn,quantity from orders a,orderitems b where a.customerid=1017 and a.orderid=b.orderid)a where b.isbn=a.isbn 列出客户1017 购买的最贵的图书。 select title from books a,orderitems b,orders c where c.customerid=1017 and b.orderid=c.orderid and b.isbn=a.isbn and retail = (select max(a.retail) from books a,orderitems b,orders c where c.customerid=1017 and b.orderid=c.orderid and b.isbn=a.isbn) 列出Computer种类中最便宜的图书。 select *from books where category=computer and retail= (select min(retail) from books where category=computer) 确定ORDRES表中包含的订单所产生的平均利润。 select avg( (c.retai