The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00

1. The output should be ordered by Salary.

CREATE SEQUENCE EmpSequence
MINVALUE 1
MAXVALUE 9999
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLC2_Employees (
	EmployeeID INT ,
	EmployeeName VARCHAR2(15),
	Department VARCHAR2(15),
	Salary NUMBER(16,2)
);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'T Cook','Finance', 40000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'D Michael','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'A Smith','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'D Adams','Finance', 15000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'M Williams','IT', 80000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'D Jones','IT', 40000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'J Miller','IT', 50000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'L Lewis','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'A Anderson','Back-Office', 25000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'S Martin','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'J Garcia','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary) 
VALUES(EmpSequence.NextVal,'T Clerk','Back-Office', 10000);

The solution should be a single query that starts with a "SELECT" or “WITH”

1. Read the Submission Guidelines given in the FAQ section to ensure that your submission follows all the required guidelines.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the solution will be executed.
3. The solution should work on Oracle 10g or later versions.