这道数据库的面试题难道我了

[supermen_G]

[qiaobianhongye]

--找到员工工资最高的前三名 使用分页 select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3; --找到员工薪水大于本部门平均薪水的员工 select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t where e2.deptno=t.deptno and e2.sal>t.a --统计每年入职人数 select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')

[卖水果的net]

select * from ( select * from t order by salary desc ) where rownum <= 3

[卖水果的net]

select * from t where salary > (select avg(salary) from t)

[卖水果的net]

select sum(case when yyyy = 2015 then 1 else 0 end) C2015, … … from t

[qq_28398475]

--1 select rownum,a.empno,a.ename,a.sal from (select * from scott.emp order by sal desc) a where rownum>0 and rownum<4; --2 select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno); --3 select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);

[qq_28398475]

select rownum,a.empno,a.ename,a.sal from (select * from scott.emp order by sal desc) a where rownum>0 and rownum<4; select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno); select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);

[bw555]

使用分析函数，异常强大 印象中从oracle9或oarcle10开始支持 百度下，你会发现新大陆

[碧水幽幽泉]

--找到员工工资最高的前三名 使用分页
select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3;
--找到员工薪水大于本部门平均薪水的员工
select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t
where e2.deptno=t.deptno
and e2.sal>t.a
--统计每年入职人数
select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')

[金木犀]

select  * from (select salary,rownum rn from  (select * from emp order BY salary desc) ) where rn<=3;

select * from emp,(select job_id,avg(min_salary)from emp group by empno)
where salary > (select avg(salary) from emp);