oracle 错题汇总

在Oracle数据库中，如果你在GROUP BY子句中指定了列，那么SELECT子句中除了聚合函数外的每个列都必须在GROUP BY子句中列出。

1.查询出每个部门的编号、名称、位置、部门人数、平均工资。

SELECT
    d.deptno AS "部门编号",
    d.dname AS "部门名称",
    d.loc AS "部门位置",
    COUNT(e.deptno) AS "部门人数",
    AVG(e.sal) AS "平均工资"
FROM
    dept d
LEFT JOIN
    emp e ON d.deptno = e.deptno
GROUP BY
    d.deptno, d.dname, d.loc;

 

2.查询emp中工资排名前5名的员工信息,工资按照由高到低排序。

select ename, job, sal
from (
    select ename, job, sal
    from emp
    order by sal desc
)
where rownum <= 5;

3. 查询emp表中工资排名第5-10名的员工姓名、工资，工资按照由高到低排序。

select ename, sal
from (
    select ename, sal, rownum as rnum
    from (
        select ename, sal
        from emp
        order by sal desc
    )
)
where rnum between 5 and 10;

 select ename, sal, rownum as rnum
    from (
        select ename, sal
        from emp
        order by sal desc
    )

 

4. 查询出有3个以上下属的主管级员工信息（自关联）

select * from emp e where (select count(*) from emp where e.empno=mgr)>2

 

 5.使用开窗函数查询emp中各部门的工资总和。

partition的从字面上看是分区、分块的意思，所以partition by其实就是根据某个字段将数据分块，然后可以对该分块数据再做查询（包括聚合查询）。

select deptno, sum(sal) over (partition by deptno) as total_salary
from emp;

select deptno, sum(sal)  as total_salary
from emp
group by deptno;

６.查询各部门工资高于所在部门平均工资的员工数量并按照部门号升序排序。

select a.deptno,count(*) as total from emp a,(select deptno,avg(sal) as
avgsal from emp group by deptno) b
where a.deptno=b.deptno and a.sal>b.avgsal
group by a.deptno
order by a.deptno;