1.表S(s#,t#,score):s#是学生号,t#是课程号,score是分数。现在查找出每个每门课程的平均分,并按分数升序排序,若平均分相同则按课程号降序排序
select t#,avg(score) from s group by t# order by avg(score),t# desc;
2.emp表、dept表
现需查询出如下结果:
salary_range employees
0
2000
s>4000 3
SQL> select case when sal<2000 then '02000 and sal<4000 then '20004000 then 's>4000' end sal,count(*) from emp group by case when sal<2000 then '02000 and sal<4000 then '20004000 then 's>4000' end;
或者
SQL> select salary_range,count(*) from (select case when sal<2000 then '02000 and sal<=4000 then '20004000 then 's>4000' end salary_range from emp) group by salary_range;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26823568/viewspace-720741/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26823568/viewspace-720741/