TSQL Challenges for Beginners

Editorial: T-SQL Challenges Goals and Grades

T-SQL Challenges have been extremely popular, and today, we are proud that we have the T-SQL Challenge for Beginners. As mentioned earlier in the editorial, the goal of these challenges is very simple.

T-SQL Challenges – It is not necessary that you should know the solution; however, it is expected that you try to solve this challenges and improve your SQL skills. Eventually, you will be improving your understanding of SQL by resolving such challenges.

T-SQL Challenges for Beginners – It is also expected that all the experienced Developers and DBA can easily solve these puzzles. Well, if you cannot, then let us take this as tutorial and improve the skills. If you have at least one year of exposure to T-SQL, you will be able to resolve these simple puzzles easily.

As the goal to T-SQL Challenges for Beginners are little different from T-SQL Challenges, after much thought, the team has come up with very different grading system for the same. We expect that there will be many more entries to Challenges for Beginners, and we want to recognize all those who have presented a correct solution.

Best Solution: Out of all the entries, we will pick the best solution. The solution will be judged mainly on the basis of the performance, query cost or any other optimization parameter.

Unique Solution: In a class, there is always at least one child who has an innovative way to solve problems. It is not necessary that it should be the best solution. Possibly, the solution picked as the unique solution may be the “expensive” route to solve the problem. This unique solution will be evaluated on the basis of the learning value in the solution. If solution has a unique method or interesting usage of common terms and statement, it will qualify in this category. In other words, this solution is like SQL Expert’s Pick

Correct Solution: In this category, we will list the names of all those who have solved this puzzle. We really do not want your learning and experience to go unnoticed. Currently, we are working out on a ranking system, which will help you to keep track of your progress as well.

I hope you all have understood the grading system of T-SQL Challenges for Beginners. Please share your thoughts and suggestions. I sincerely hope that all those who are reading this editorial will participate in T-SQL Challenges.

Regards,

Pinal Dave
http://blog.sqlauthority.com

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.
5. Last date for submitting your entries is 31st October 2009, Midnight GMT.

Challenge Moderator

Tejas Shah is the Moderator for this challenge. Challenge Moderator is the person who will help you out with your questions related to the given challenge. View Profile|Blog.

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

TSQL Beginner's Challenge - Submission Guidelines and Terms