SQL Server Interview Questions for Developers on Querying

By:   |   Comments (17)   |   Related: More > Professional Development Interview Questions Developer


Problem

At some point, we need to interview senior-level database developers and check their SQL query knowledge. In these cases, it is important to ask questions corresponding to their experience in this field to be able to evaluate whether their knowledge fits your expectations. Additionally, often there is a limited time for the interview, so it's vital to choose a few advanced (but not too complicated) questions that cover major aspects of the subject. The definition of the questions should be clear so the interviewee can concentrate on the solutions rather than wasting time understanding the requirements. Also, I believe a sample schema of the tables should be simple in order not to overload the candidates with a complicated structure.

In this article, the questions will be defined for an advanced SQL querying interview and the answers are provided. It will be useful for interviewers, as well as for developers to brush up on their own skills. Please note that this is not a full set of database interview questions as it only tests query skills. Additional questions related to stored procedures, functions, triggers, transactions and more, should be asked.

Solution

Before defining the questions and providing the answers, we need to create a sample and simple environment. Let's assume we have a schema with 2 tables "Item" and "Sales". The first table is a description of the items and the second table shows the items' sales by date. The diagram of the schema is the following:

schema

The structure of the tables is the following:

USE master
GO

CREATE DATABASE TestDB
GO

USE [TestDB]
GO

--Tables creation
CREATE TABLE [dbo].[Item]
(
   [ItemID]    [int] NOT NULL,
   [ItemCode]  [nchar](5) NOT NULL,
   [ItemPrice] [money] NULL,
   CONSTRAINT  [PK_Item] PRIMARY KEY CLUSTERED (ItemID)
)
CREATE UNIQUE NONCLUSTERED INDEX [UIX_Item_ItemCode] ON [dbo].[Item]([ItemCode] ASC)
 

CREATE TABLE [dbo].[Sales]
(
   [SalesID]  [int] IDENTITY(1,1) NOT NULL,
   [ItemID]   [int] NOT NULL,
   [SoldDate] [date] NOT NULL,
   CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED (SalesID)
) 
 
ALTER TABLE [dbo].[Sales]  WITH CHECK ADD  CONSTRAINT [FK_Sales_Item] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Item] ([ItemID])
 
ALTER TABLE [dbo].[Sales] CHECK CONSTRAINT [FK_Sales_Item]

Here is sample data.

 -- insert rows into dbo.Item
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (1, N'A0010', 17455.2900)
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (2, N'B0020', 24500.0000)
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (3, N'C0030', 12450.3200)
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (4, N'D0040', 37784.0000)
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (5, N'E0050', 128000.0000)
INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (6, N'F0060', 92000.0000)

-- insert rows into dbo.Sales 
SET IDENTITY_INSERT [dbo].[Sales] ON 
 
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (1, 1, CAST(N'2016-01-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (2, 1, CAST(N'2016-02-22' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (3, 3, CAST(N'2016-03-17' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (4, 5, CAST(N'2016-04-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (5, 3, CAST(N'2017-01-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (6, 1, CAST(N'2017-02-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (7, 2, CAST(N'2016-05-18' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (8, 1, CAST(N'2016-06-22' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (9, 1, CAST(N'2016-07-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (10, 4, CAST(N'2017-03-10' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (11, 4, CAST(N'2017-04-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (12, 1, CAST(N'2016-07-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (13, 3, CAST(N'2017-05-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (14, 1, CAST(N'2017-05-12' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (15, 1, CAST(N'2017-06-09' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (16, 4, CAST(N'2016-08-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (17, 3, CAST(N'2016-09-30' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (18, 2, CAST(N'2016-09-18' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (19, 4, CAST(N'2016-10-22' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (20, 2, CAST(N'2016-10-17' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (21, 6, CAST(N'2016-11-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (22, 3, CAST(N'2017-07-23' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (23, 1, CAST(N'2017-07-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (24, 1, CAST(N'2017-08-28' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (25, 1, CAST(N'2017-09-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (26, 2, CAST(N'2017-10-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (27, 2, CAST(N'2017-10-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (28, 3, CAST(N'2017-11-25' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (29, 1, CAST(N'2017-11-23' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (30, 2, CAST(N'2017-12-24' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (31, 1, CAST(N'2017-12-09' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (32, 3, CAST(N'2019-01-11' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (33, 1, CAST(N'2016-12-13' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (34, 1, CAST(N'2019-02-23' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (35, 4, CAST(N'2019-02-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (36, 4, CAST(N'2019-03-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (37, 2, CAST(N'2019-03-28' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (38, 3, CAST(N'2019-04-16' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (39, 4, CAST(N'2019-05-03' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (40, 6, CAST(N'2018-06-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (41, 3, CAST(N'2016-12-04' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (42, 3, CAST(N'2018-01-22' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (43, 1, CAST(N'2018-02-01' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (44, 2, CAST(N'2019-07-10' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (45, 3, CAST(N'2019-08-08' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (46, 3, CAST(N'2018-03-20' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (47, 3, CAST(N'2018-04-16' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (48, 4, CAST(N'2019-09-12' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (49, 4, CAST(N'2019-10-18' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (50, 3, CAST(N'2019-11-15' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (51, 2, CAST(N'2019-12-23' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (52, 4, CAST(N'2019-12-02' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (53, 4, CAST(N'2018-05-16' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (54, 3, CAST(N'2018-07-12' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (56, 2, CAST(N'2018-08-16' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (57, 1, CAST(N'2018-09-19' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (58, 1, CAST(N'2018-10-18' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (59, 2, CAST(N'2018-11-11' AS Date))
INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (60, 1, CAST(N'2018-12-22' AS Date))
 
SET IDENTITY_INSERT [dbo].[Sales] OFF

Questions

Having defined our schema, we are ready to formulate our questions.

1. Get the list of items (ItemCode, ItemPrice) which are not sold in 2019.

Result:

result set

2. Get the list of items (ItemCode, ItemPrice) which are sold only in 2016.

Result:

result set

3. Display the running total of sales for each year (Year, RunningTotalCountofSales).

Result:

result set

4. Get the quarterly count of sold items in 2018 and 2019 in a separate column for each quarter (Year, FirstQuarter, SecondQuarter, ThirdQuarter, ForthQuarter)

Result:

result set

5. Display the sold items' counts per year with the previous year's sold items counts and growth (in percent) from previous year's sales count (Year, SoldItemsCount, PreviousSoldItemsCount, Growth(%)). If there is no information about previous year's sales, display 0.

Result:

result set

6. Update the Items table to increase the price of the item with the third highest price by 1000. Data in Item table is the following after the update.

Result:

result set

Answers

The answers to the questions above are provided below. While there can be different solutions for each task, only one answer has been provided for each question.  If you have different solutions, please enter them in the comments at the end of this article.

1. The list of items which are not sold in 2019.

SELECT i.ItemCode, i.ItemPrice
FROM dbo.Item i
LEFT JOIN dbo.Sales s ON i.ItemID=s.ItemID AND YEAR(s.SoldDate)=2019
WHERE s.SoldDate IS NULL

2. The list of items that are sold only in 2016.

SELECT ItemCode, ItemPrice
FROM dbo.Item 
WHERE ItemID IN
(
   SELECT ItemID
   FROM dbo.Sales
   EXCEPT 
   SELECT ItemID
   FROM dbo.Sales
   WHERE YEAR(SoldDate)<>2016
)

3. Running total count of sales for each year.

SELECT 
   DISTINCT YEAR(SoldDate) AS [Year], 
   COUNT(SalesID) OVER(ORDER BY YEAR(SoldDate)) AS RunningTotalCountofSales
FROM dbo.Sales

4. Quarterly count of sold items in 2018 and 2019.

SELECT 
   YEAR(SoldDate) AS [Year],
   SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=1 THEN 1 ELSE 0 END) AS FirstQuarter,
   SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=2 THEN 1 ELSE 0 END) AS SecondQuarter,
   SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=3 THEN 1 ELSE 0 END) AS ThirdQuarter,
   SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=4 THEN 1 ELSE 0 END) AS ForthQuarter
FROM dbo.Sales
WHERE YEAR(SoldDate) BETWEEN 2018 AND 2019
GROUP BY YEAR(SoldDate)

5. Sales count per year and sales growth as compared with the previous year.

SELECT 
   [Year],
   SoldItemsCount, 
   ISNULL(LAG(SoldItemsCount) OVER(ORDER BY [Year]),0) AS PreviousSoldItemsCount,
   ISNULL(((SoldItemsCount-LAG(SoldItemsCount) OVER(ORDER BY [Year]))*100)/LAG(SoldItemsCount) OVER(ORDER BY [Year]),0) AS 'Growth(%)'
FROM
(
   SELECT YEAR(SoldDate) AS [Year], COUNT(*) AS SoldItemsCount
   FROM dbo.Sales
   GROUP BY YEAR(SoldDate)
) t

6. Update 3 rd highest priced item by 1000.

UPDATE ItemWith3rdPrice
SET ItemPrice = ItemPrice + 1000
FROM
(
   SELECT TOP 1 ItemID, ItemPrice 
   FROM
      (
      SELECT TOP 3 ItemID, ItemPrice 
      FROM dbo.Item 
      ORDER BY ItemPrice DESC
      ) t
   ORDER BY ItemPrice
) ItemWith3rdPrice 

SELECT * FROM dbo.Item

Conclusion

While interviewing a senior developer for SQL querying, it is essential to define advanced questions by using simple schemas and clear definitions. The questions, in turn, should be defined in a way that includes the main areas of the subject. This will be helpful in terms of saving time and effectively testing the candidates' knowledge.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, December 1, 2020 - 5:51:28 PM - Carolina Ragazzon Back To Top (87867)
I was also puzzled by the results and some comments on several of the exercises.
For the time being, I will only comment on exercise 1.
I am not a fan of left joins. I chose the inner join as well. Yet, I supposed that it was the total amount and not the individual amount to be shown.
Yes, I agree, I have to read the description over and over again. That is why definitions are so important!
Thanks for the exercises.

Monday, August 31, 2020 - 10:05:55 PM - Sergey Gigoyan Back To Top (86396)
ScottPletcher,

Sure, using functions in WHERE and JOIN clauses can affect performance. Agreed.

Thank you,

Sergey

Sunday, August 30, 2020 - 10:34:24 PM - Jeff Moden Back To Top (86393)
@ScottPletcher

Ah, careful now, Scott. I'm pretty sure that, when used on a table column as criteria, is never SARGable. I believe you're thinking about using CAST or CONVERT of a DATETIME to a DATE datatype to drop the time element which, although still a little slower than doing it the right way (which you posted) is SARGable (as of 2012, IIRC).

Friday, August 28, 2020 - 11:53:38 AM - ScottPletcher Back To Top (86384)
I'd be rather leery of any "senior developer" that repeatedly used functions on columns in WHERE and/or JOIN clauses. Yes, SQL can convert specifically a YEAR() function to make it sargable anyway.

Instead, the standard, best-practice approach is to do this:
LEFT JOIN dbo.Sales s ON i.ItemID=s.ItemID AND s.SoldDate >= '20190101' AND s.SoldDate < '20200101'

Indeed, since sargability is so important, there should be a q that tests whether a prospective employee would use a function, for example ISNULL(), in a WHERE. The q could be to include status if it is NULL or 1 to see if they write:
WHERE (status IS NULL OR status = 1)
rather than
WHERE ISNULL(status, 1) = 1

Thursday, August 27, 2020 - 6:52:37 PM - Sergey Gigoyan Back To Top (86375)
#6 problem:
Your solution is also correct John and there can be some other solutions as well. As OFFSET and FETCH are introduced in SQL Server 2012, I personally have chosen a solution that works for older versions of SQL Server as well.

Thanks,
Sergey

Thursday, August 27, 2020 - 6:41:26 PM - Sergey Gigoyan Back To Top (86374)
John,
Running total count of sales for the year means the count of sold items before that year, including that year's sales as well. Your query returns the following result:
Year RunningTotalCountofSales
2016 16
2017 17
2018 12
2019 14

These cannot be running total counts as running total counts can only increase year by year (or at least remain the same, if there are no sales in that year).

Thanks,
Sergey Gigoyan

Thursday, August 27, 2020 - 3:06:54 PM - Blanca Obregon Back To Top (86373)
Yep, Sergey, that is exactly what I understood!

Kiel, I hope that now you understand why your queries are not answering Sergey's questions and why he is using left joins and all that stuff.

Thursday, August 27, 2020 - 1:31:14 PM - John Back To Top (86372)
#6 problem

For a Sr Dev person perhaps the following would be more appropriate. Not saying the provided solution is wrong, but here is another veriation.

update dbo.Item
set ItemPrice += 1000
where itemid = (select ItemID from item order by ItemPrice desc offset 2 rows fetch next 1 row only)

Thursday, August 27, 2020 - 12:49:19 PM - John Back To Top (86371)
If I am not mistaken the problem number 3 may be invalid. The windowing should be partitioned and not ordered. When ordered, the total count exceeds the total number of records for all items. which is 59.

SELECT DISTINCT YEAR(SoldDate) AS [Year], COUNT(*) OVER(partition by YEAR(SoldDate)) AS RunningTotalCountofSales
FROM dbo.Sales

I could be wrong.

Wednesday, August 26, 2020 - 10:51:46 PM - Sergey Gigoyan Back To Top (86369)
Blanca,

Thank you for the comment.
"1. The list of items that are not sold in 2019" means the items that were never sold in 2019. This includes items sold before and after 2019 as well as never sold items.
"2. The list of items that are sold only in 2016" includes the items sold only in 2016. Items that are sold before or after 2016 as well as never sold items should not be included.

On the one hand, I agree that the definitions of the tasks are laconic but on the other hand, you would probably agree that they are precise and clear. What I believe is that wordiness will only make the process of understanding the task quite complicated.

Thanks,
Sergey

Wednesday, August 26, 2020 - 12:48:18 PM - Blanca Obregon Back To Top (86366)
I think that the problem here is "interpretation".
For question 1 Sergey wants to get the list of products that we have sold in other years but 2019
and
For question 2 the list of products that have been sold only in 2016, meaning said that we have never ever sold them before!

Sergey, am I correct?

Tuesday, August 11, 2020 - 4:32:36 PM - Sergey Gigoyan Back To Top (86284)
Kiel,

These could be good solutions but not for the above-defined tasks. Your queries return quite different results and not the correct result sets. As you have interviewed candidates and attended interviews, I'm sure you know that it is very important to correctly get the idea of the task and provide a solution that strictly meets the task's requirements.

Thanks,
Sergey Gigoyan

Tuesday, August 11, 2020 - 3:12:26 PM - Kiel Back To Top (86283)
Sure!

Answer 1: I would go with something like this...
SELECT i.ItemCode, i.ItemPrice
FROM dbo.Item i
INNER JOIN dbo.Sales s ON i.ItemID=s.ItemID
WHERE YEAR(s.SoldDate) != 2019

This would be more performant in higher row count databases as well as minimize locking potential if you are planning update/delete.

Answer 2: I would write it like...
SELECT i.ItemCode, i.ItemPrice
FROM dbo.Item i
INNER JOIN dbo.Sales s
ON i.ItemID = s.ItemID AND YEAR(s.SoldDate) = 2016

This would also be more preformant in a higher row count database. Now, you mention in the problem that you are looking for items sold ONLY in 2016...in which case I would need to rewrite this to compare the ItemID's where YEAR(SoldDate) = 2016 and ItemID does not exist in the distinct selection of ItemID's sold in other years.

Good article though, I've sat on both sides of the interview process and love to see where other folks' heads are at regarding the process of screening.

Tuesday, August 11, 2020 - 2:09:04 PM - Sergey Gigoyan Back To Top (86282)
Ronald,

In this example, only the items with ItemID=5 and ItemID=6 were not sold in 2019.
The result shows these two items. All others were sold.

Thanks,
Sergey Gigoyan

Tuesday, August 11, 2020 - 2:01:19 PM - Sergey Gigoyan Back To Top (86281)
Kiel,

Please feel free to share your solutions.

Thanks,
Sergey Gigoyan

Saturday, August 8, 2020 - 10:14:10 PM - Ronald Back To Top (86258)
I don't know if am wrong but the questions and answers are totally different. For example example question one. In the table there very many items not sold 2019 but the shows 1 tem in the result

Friday, August 7, 2020 - 10:50:18 PM - Kiel Back To Top (86256)
I'm curious on problem 1 why you chose to left join and them check for null sold dates? In a large db this could be less performant than an inner join and then specifying the year != to 2019 in the where clause. Using the left join you are essentially checking the sold date 2 times, in the join logic and in the where clause still checking every entry in the sales table.

The same kind of question for problem 2...why subquery in the where clause where an inner join with join logic including the sold date year as 2016?














get free sql tips
agree to terms