Silverkight Viewer for Reporting Services
Got a SQL Server or .NET question? Discuss it in the forums. (SQL Server Forums | Dot NET Forums)
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Well, we have been talking about the beginner’s version of TSQL Challenges for quite some time. We received a number of emails asking for the ETA of TSQL Beginners Challenges. Finally, we are ready to go and I am very glad to make the announcement for the first challenge.

As I always used to say, the goal of TSQL Beginner’s Challenges is to help you learn TSQL and evaluate yourself, so that you know where you are good and where you need improvements.

The goals of TSQL Challenges and TSQL Beginner’s Challenges are completely different and I have tried to differentiate them here.

The Challenge

Let us get started with the challenge. In a nutshell, 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

Here is the sample data for this challenge.

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 Output

Here is the output you need to produce from the above sample data.

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

Scripts

Run the following scripts to generate the sample data. Use the given sample data to test your solutions.

DECLARE @Employees TABLE(
	EmployeeID INT IDENTITY,
	EmployeeName VARCHAR(15),
	Department VARCHAR(15),
	Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)

Notes

  1. Use this forum to discuss your questions related to Challenge #1
  2. To be able to post questions in the forum, you need to be a member of the group TSQL Beginners Challenges. Click here to subscribe to the group.
  3. Follow the instructions in the Submission Guidelines to submit your entry.
  4. The solution should work on SQL Server 2005 and above.

Hope you will have a great time solving and learning from this challenge. I look forward to hear your suggestions and comments.

Best Regards,
Jacob Sebastian

If you like this article,  Subscribe in a reader or Subscribe by Email. Show your support by sharing this article with your friends through the services given below.

Share

Comments

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Wednesday, October 14, 2009 9:45 AM by Mudit.Gupta

Jacob,

Can we use Temp tables instead of Table Variables for this task?

thanks!

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Thursday, October 15, 2009 4:36 AM by srinivas

i was able to solve this puzzle, but where can i post my solution.

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Sunday, October 18, 2009 12:13 PM by Jacob Sebastian

Mudit,

Temp tables are not allowed.

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Sunday, October 18, 2009 12:14 PM by Jacob Sebastian

Srinivas,

Please read submission Guidelines at beyondrelational.com/.../tsql-beginner-s-challenge-submission-guidelines-and-terms.aspx

It has clear instructions on how to submit the solution.

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Friday, October 30, 2009 2:43 AM by arorasumitcs

True one Niladri.. but what you think what you did, you even send the whole explanation in comments with your solution and you blaming other peoples.

# re: TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

Tuesday, December 01, 2009 1:56 AM by Pankaj Kumar Verma

HI jacob,

I have also submitted my script of the challenge #1. Please check this and let me know is it OK.

Copyright © Beyondrelational.com