TSQL Challenges for Beginners

TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations

The Challenge

This challenge invites you to play with date calculations. A lot of people are scared of date calculations and this challenge aims to help them to fight their fear!

This challenge is to find the date based on year, month, day of the week and weekday number. Say for example, if the question is to find the date of 2nd Sunday of January 2010, the answer should be '2010-01-10'.

Sample Input Data

Yr    Mon    Dy  Dyno
-------------------------------
2010  Jan  Sun    2
2005  Jan  Mon    3
1995  Feb  Sun    1
2000  Feb  Wed    4
1982  Mar  Tue    2
2010  Mar  Tue    8

Expected Output

Yr     Mon    Dy   Dyno  Date
----------------------------------------------
1982    Mar    Tue    2   1982-03-09
1995    Feb    Sun    1   1995-02-05
2000    Feb    Wed    4   2000-02-23
2005    Jan    Mon    3   2005-01-17
2010    Jan    Sun    2   2010-01-10
2010    Mar    Tue    8   Invalid Date

Script

Here is the script to generate the sample input data.

DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)
INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
    SELECT 2010,'Jan','Sun',2 UNION ALL
	SELECT 2005,'Jan','Mon',3 UNION ALL
	SELECT 1995,'Feb','Sun',1 UNION ALL
	SELECT 2000,'Feb','Wed',4 UNION ALL
	SELECT 1982,'Mar','Tue',2 UNION ALL
	SELECT 2010,'Mar','Tue',8

SELECT * FROM @tbl

Notes

1. The goal is to find the correct date based on the Week Day number regardless of the starting week day of the month. Say, for example January, 2010 starts from Friday and hence Sunday on '2010-01-03' cannot be considered as the 2nd Sunday, although it is in the second week. It will be considered as the 1st Sunday.
2. If the Date does not exist show it as 'Invalid Date'.
3. The solution should be a single query that starts with "SELECT" or ";WITH"
4. The output should be sorted on the Date result.
5. Use this forum to discuss your questions related to Challenge 2
6. 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.
7. Follow the instructions in the Submission Guidelines to submit your entry.
8. The solution should work on SQL Server 2005 and above.
9. Last date for submitting your entries is 15 March 2010, Midnight GMT.

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

First Editorial - T-SQL Challenges Beginners

  I would like to welcome all of you to very first editorial for T-SQL Challenges for Beginners. T-SQL Challenges began with the aim to help community to come out of regular mind set of just reading articles online. There is plenty of reading material available online, but there are very few that can make us use our brain cells.

T-SQL Challenges are very well received in community, and today, we are receiving more than 200 responses for every challenge in a very short time. The real challenge is how to keep everybody involved. T-SQL Challenges is focused and encourage experts to bring out the best from them. T-SQL challenges for Beginners share the same enthusiasm from everyone.

Of course, this challenge can be attempted by everyone and just not beginners. However, just like we say “There is a child in everyone,” – in the exactly same way, I would like to say that “There is a beginner in everyone.” We may be expert in certain areas, but quite often we face a barrier to attempt something new and different. I believe that T-SQL Challenges can initiate the Beginners to break that very barrier. Note that the term beginner is used in a broader sense.

Here, Beginners refer to the new innovators. A problem can be easily resolved by writing simple T-SQL; however, what is most important is that an innovative thought can resolve an age old issue. T-SQL has many dimensions, and each dimension is equally important. These dimensions are creativity, performance, data modeling techniques, readability and many more. A solution, which can be simply solved by cursor, can be re-written using a set-based solution; this will lead to the scoring of some points in the area of performance and best practices.

I want to promise one more thing here that just like T-SQL Challenges, this challenge series for beginners will be focused on building a community, which helps each other. This is an open community, and everyone is welcome to stay as long as they wish. From my experience, I know that this is addictive, but I would let you decide the same.

What can you do to help this community effort?

· Participate in challenge and solve them.

· Read the solutions from others and learn new tricks.

· Spread the word for the challenges.

· Participate by submitting new challenges.

· Last but not least, send your feedback.

One more very interesting point is that if you win the challenge and if you present a very innovative solution, you will receive a special certificate from T-SQL challenge founder Jacob Sebastian and myself. On my side, I promise to feature the same as an article on my blog SQLAuthority.com.

Let me see what you have got? Over to very first challenge now...

Pinal Dave

TSQL Challenges for Beginners – The team – Interview with Tejas Shah

TSQL Challenges for Beginners is about to roll out and we are very happy to introduce the team of volunteers that run this interesting program. The team consists of a number of SQL Server experts and enthusiasts from all over the world, having experience in diverse domains and who understands the pulses of real life TSQL problems.

One of the key persons in this project is Tejas Shah, a SQL Server expert from Ahmedabad. Tejas is a winner of TSQL Challenges, an active SQL Server blogger and a contributor at MSDN SQL Server forums.

Tejas Shah

Jacob: Tejas, could you give a short description of yourself?

Tejas: I am working with Excellence Infonet as a Team Leader and have 5+ years of experience in Microsoft Technologies like .Net(1.1, 2.0, 3.5), SQL SERVER 2000/2005/2008. I have designed many web based application from small to enterprise scale using .Net, SQL, AJAX, XSLT and also worked on SharePoint development using WSS 3.0 and MOSS 2007, share point designer 2007. Check www.SQLYoga.com frequently for tips, tricks, commentary and ideas on SQL Server.

Jacob: Why did you decide to be part of 'TSQL Challenges for Beginners'

Tejas: TSQL Challenge is a very good community project for an endless journey of SQL Programming.
I like to contribute to the TSQL Challenge to get experience of lots of human beings who are sharing their ideas towards challenges and giving their maximum efforts. This is the reason why I decide to be part of this TSQL Community.

Jacob: Who, according to you will be benefited by 'TSQL Challenges for Beginners'?

Tejas: This will be beneficial to all who want to learn or develop their skills in SQL SERVER. It’s not only for SQL SERVER beginners but experienced developers & readers also can get benefits by trying to solve these challenges because there are lots of small queries / doubts which when we face, then only comes in our thoughts.

Jacob: Your message to the SQL Server developers around the world

Tejas: As per the name suggests it's not only for beginners, other SQL Server developers can also solve these TSQL challenges and recall their knowledge.
Let's share the knowledge by solving the TSQL challenges and be part of "TSQL challenges for Beginners".