SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31

Rick Morelan is finest SQL Expert. He is very much known for his excellent book series Joes 2 Pros. His books are not only an inspiration to many who wants to learn SQL Server properly, but a MUST read for any SQL enthusiast. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series.

Please read the Introductory Post before continuing reading interview question and answers.

After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in interview, but but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. These questions separated the junior SQL query writers from the advanced SQL query writers. That is because most advanced interview questions combine two or more intermediate level skill sets into 1 solution.

Realizing years ago that just writing and training people for the Microsoft test was not always enough to get them where they wanted to go. Maybe half the time they would get the job so I vowed to do something extra to increase those odds. Perhaps needed to create an entire course on the how to ace SQL query interviews.

Since it seemed 80% of the interview questions were about the advanced queries that is where my focus must be directed. The end result is my SQL Query book on certification (SQL Queries Joes 2 Pros: SQL Query Techniques for Microsoft SQL Server 2008ISBN 1-4392-5318-) doubled in size to nearly 600 pages. In fact this book now adorns more interview prep then certification prep. It’s proven to be a big payoff and my students almost always get the job offer after the interview.

Let’s get on to one of the common interview questions. Most people are fully aware of how the TOP keyword works with a SELECT statement. For example if you wanted to look at the 3 top most expensive products from the [Grant] table of the sample database JProCo then this query would do the trick (note: you can download my sample database from Joes2Pros.com). Watch the video here.

SELECT TOP (3) *
FROM [Grant] ORDER BY Amount DESC

The real interview question assumes this is already easy to you and instead they want to find just the 3^rd highest Grant. In other words just the record that is in 3rd place by amount. To do this you need rank the records and then predicate for 3rd place. Let’s start off by sorting all the records of the table by highest price.

SELECT *
FROM [Grant] ORDER BY Amount DESC

The ORDER BY clause picks what records are listed first based on the field and sort order you pick. In this case we choose amount from highest to lowest. Now we want to use the power of the ORDER BY to pick the rank. Follow the RANK() with OVER() and the put the ORDER BY inside and not the end of the query. Your query and output look like the sample below.

SELECT *,
RANK() OVER(ORDER BY Amount DESC)
FROM [Grant]

This is very nice but not very neat. It a good idea to alias expression fields to let’s call it GrantRank.

SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant]

Almost done right? You can see the 3^rd highest grant is Thank you @.com for $21,500. Just 1 critera way from victory. You might think it would be as simple as saying GrantRank = 3. If you try to predicate on a field alias you will discover that the WHERE clause runs before the field expression and you get the following error.

SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] WHERE GrantRank = 3

Msg 207, Level 16, State 1, Line 5

Invalid column name ‘GrantRank’.

GrantRank is not a column until after the query has run so the where clause can’t find what you’re talking about. What can you do? Simply materialize the query like a table by using a Common Table Expression or a Derived Table. Both examples are below.

--Use a Common Table Expression
WITH CTE AS
(SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] )
SELECT * FROM CTE
WHERE GrantRank = 3
--Use a Derived Table
SELECT *
FROM
(SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] ) AS dt
WHERE GrantRank = 3

List of all the Interview Questions and Answers Series blogs

Reference: Rick Morelan, Pinal Dave (https://blog.sqlauthority.com)