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

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

Rick A. Morelan is finest SQL Expert. He is very much known for his excellent book series Joes 2 Pros. His books are not only 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. I encourage all the readers to read his MUST read books Joes 2 Pros.

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 the interview but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. Most of these questions were not even programming questions but short queries with deep strategic thought. 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 3rd 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]

GrantID GrantName EmpID Amount Rank

7

Ben@MoreTechnology.com

10

41000

1

8

http://www.@-Last-U-Can-Help.com

7

25000

2

9

Thank you @.com

11

21500

3

4

Norman's Outreach NULL

21000

4

5

BIG 6's Foundation%

4

21000

4

6

TALTA_Kishan International

3

18100

6

3

Robert@BigStarBank.com

7

18100

6

2

K_Land fund trust

2

15750

8

10

Call Mom @Com

5

7500

9

1

92 Purr_Scents %% team

7

4750

10

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 3rd 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

GrantID GrantName EmpID Amount GrantRank
9 Thank you @.com 11 21500 3

List of all the Interview Questions and Answers Series blogs

Reference: Rick A. Morelan, Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. Pinal,

    Thanks for the pointers … You have a typo:
    “I encourage all the readers to read her MUST read books Joes 2 Pros.”

    • Hello ss…
      Here is a workable solution to Rick’s interview question without using the RANK() function.

      /* Create a parameter that sets the position of the
      business required value.
      **********************************************************************/
      DECLARE @nRank int;
      SET @nRank = 3;

      /* Use the TOP() expression with a derived table to return all
      rows that meet the specified business requirement.
      **********************************************************************/
      SELECT TOP (1) WITH TIES *
      FROM [Grant]
      WHERE Amount NOT IN ( SELECT TOP (@nRank – 1) WITH TIES Amount
      FROM [Grant]
      ORDER BY Amount DESC )
      ORDER BY Amount DESC;

      • Hello rstech,

        Thank you so much for the reply. I think its easier to use rank() function when there is large amounts of data to be sorted out.

        Thank you.

  2. Nice question and a great answer. Thanks for the pointer.
    I had to use the ROW_NUMBER(), RANK() AND DENSE_RANK() functions a couple of days ago in a query that’s being used to extract data from a SQL database. Very interesting functions, and very helpful.

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

    • Pinal. U have so many resourceful articles for readers and I would like to personally thank you for all ur efforts. Please keep up the good work. Thanks

  4. Hi Pinal and Rick,

    Thanks for this nice post.

    Can you please tell if there is any performance difference between the two methods shown in this post. i.e, between “Use a Common Table Expression” and “Use a Derived Table”.

    Thanks.

  5. The following performs much better in large tables by preventing fetching unneeded records:

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

  6. can we do like this
    with cte as ( select top 3 amount from grant order by amount desc ) select min(amount) from cte

    • Yes. Have you tried it?

      Alternatively, You can do it in derived table too

      select min(amount) from
      (
      select top 3 amount from grant order by amount desc
      ) as t

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s