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:
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:
2. Get the list of items (ItemCode, ItemPrice) which are sold only in 2016.
Result:
3. Display the running total of sales for each year (Year, RunningTotalCountofSales).
Result:
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:
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:
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:
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 NULL2. 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.Sales4. 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)
) t6. 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
- Check out all of the MSSQLTips.com Interview Questions
Sergey Gigoyan (LinkedIn) is a Senior Technical Architect specializing in data and databases with more than 15 years of experience. Sergey focuses on modern data architectures, database design and development, performance tuning and optimization, high availability solutions, BI development and DW design. He has worked with SQL Server, Oracle, and PostgreSQL databases, as well as cloud-based data solutions (AWS and Azure). Sergey also has extensive experience with modern data stacks such as Snowflake and dbt.
Sergey’s experience spans various industries. He had the privilege of working with IT giants such as Oracle as a Principal Data Engineer and BlackBerry as well as innovative startups. He helped deliver complex database solutions and advanced data strategies.
Sergey is also the author of “Building a Successful Career in IT – How I Did It” where he provides actionable advice on thriving in the ever-evolving IT industry.
- MSSQLTips Awards: Champion (100+ tips) – 2024 | Author of the Year – 2020
