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
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
- Use this forum to discuss your questions related to Challenge #1
- 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.
- Follow the instructions in the Submission Guidelines to submit your entry.
- The solution should work on SQL Server 2005 and above.
- Last date for submitting your entries is 31st October 2009, Midnight GMT.
Challenge Moderator
![4TC6XLGO727Z[1] 4TC6XLGO727Z[1]](http://beyondrelational.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/tcb/4TC6XLGO727Z1_5F00_thumb_5F00_1A7B0D3B.jpg)
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
Submission Guidelines Make sure that your solution uses a SET based approach to solve the problem. Make sure that you create a .sql file with your solution. The file should be named as 'firstname_lastname_tbc_challengenumber.sql". Replace firstname...