Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This challenge is about finding second highest salary for each department

PLSQL Challenge 2 - Find the second highest salary for each department

PLSQL Challenge 2 - Find the second highest salary for each department

Oct 26 2011 1:33PM by Ramireddy   

Introduction

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.

Sample Data

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

Expected Results

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

Rules

  1. The output should be ordered by Salary.

Sample Script

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);

Restrictions

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

Notes

  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.

Tags:ORACLE

Ramireddy
3 · 40% · 12972
PLSQL Challenge 2 - Find the second highest salary for each department , 5.0 out of 5 based on 15 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]