一世界500强招应界生的面试题

Question

面试题:
1。写出建立以下两个表的语句
t_emp表
empid number 主键，not null(员工ID号)
empname varchar(32)（员工姓名）
salary varchar(32)（工资）
deptno number (部门ID)t_dept表
deptno number 主键，not null(部门ID)
deptname varchar(32)（部门名）
boss varchar(32)(部门主管姓名)
parent_id number(上级部门ID)
2。
1）找出每个部门最高工资员工姓名
2）找出每个部门工资高于所在部门平均工资员工人数
3)找出每个部门工资排名在最高前三的员工姓名
4)找出每个部门主管下面的所有员工姓名
当时我是答出来了，只是不知道正确否，大家来讨论一下嘛！！！！！！！！

Answer 1

--1.找出每个部门最高工资员工姓名
SELECT   deptno, ename
  FROM   (SELECT   deptno,
                   ename,
                   sal,
                   ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC)
                      n
            FROM   emp)
 WHERE   n = 1;--2）找出每个部门工资高于所在部门平均工资员工人数
  SELECT   deptno, COUNT (empno) ecount
    FROM   (SELECT   deptno,
                     empno,
                     sal,
                     AVG (sal) OVER (PARTITION BY deptno) s
              FROM   emp)
   WHERE   sal > s
GROUP BY   deptno;--3)找出每个部门工资排名在最高前三的员工姓名
  SELECT   deptno,
           empno,
           sal,
           n
    FROM   (SELECT   deptno,
                     empno,
                     sal,
                     ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC)
                        n
              FROM   emp)
   WHERE   n <= 3
ORDER BY   deptno, n;--4)找出每个部门主管下面的所有员工姓名
  SELECT   t1.boss, T2.EMPNAME
    FROM   (    SELECT   deptno, boss, CONNECT_BY_ROOT (parent_id) parent_id
                  FROM   t_dept
            CONNECT BY   PRIOR deptno = parent_id) t1, t_emp t2
   WHERE   t1.deptno = t2.deptno
ORDER BY   t1.boss, T2.EMPNAME;

Answer 2

我想说的是第一题如果有两个并列第一的话那用row_number() 就不正确了，可以用rank（）或者dense_rank（）
下面说下三者的区别：
dense_rank() over()当出现相同数据时会默认相同数据有一样的编号，且接下来的编号不会跳跃；
rank() over()当出现相同数据时会默认相同数据有一样的编号且接下来的编号会跳跃；
row_num() over()仅对数据进行编号，而不考虑数据是否相同。

Answer 3

刚最后一点写错了
dense_rank() over()当出现相同数据时会默认相同数据有一样的编号，且接下来的编号不会跳跃；
rank() over()当出现相同数据时会默认相同数据有一样的编号且接下来的编号会跳跃；
row_number() over()仅对数据进行编号，而不考虑数据是否相同。