给出各部门工资第二高的雇员名字、工资及所在部门

--雇员表

CREATE TABLE E(

ID NUMBER(5),

NAME VARCHAR(10),

SALARY NUMBER(10),

DEPARTMENTID NUMBER(5)

);

INSERT INTO E VALUES(1,'MANDY',10000,1);

INSERT INTO E VALUES(2,'HENRY',21000,2);

INSERT INTO E VALUES(3,'TOM',15000,2);

INSERT INTO E VALUES(4,'MIKE',20000,1);

SELECT * FROM E;

--部门表

CREATE TABLE D(

ID NUMBER(5),

NAME VARCHAR(10)

);

INSERT INTO D VALUES(1,'SALEA');

INSERT INTO D VALUES(2,'SALEB');

SELECT * FROM D;

--各部门工资第二高的雇员名字、工资及所在部门

SELECT ENAME

      ,SALARY

      ,DNAME

  FROM

       (

         SELECT E.NAME AS ENAME

               ,E.SALARY

               ,D.NAME AS DNAME,

                DENSE_RANK() OVER(PARTITION BY DEPARTMENTID ORDER BY SALARY) AS RANKING

           FROM E E

           JOIN D D 

             ON E.DEPARTMENTID = D.ID)

          WHERE RANKING = 2;