SQL SERVER – Interview Questions – Difficult SQL Puzzle

Today at my organization, we had nearly 30 interviews of DBA and .NET developers. I really enjoyed participating in the interviewing panel. It was fun to ask questions and learn so many new things from developers. I will share a few of my experiences with candidates here. Let us see a complex SQL puzzle.

Those who are scheduled to interview with me next week this is one question I will be sure asking you besides questions from my interview questions and answer series.

Question: If we have the following table, please write a script to display output below.

Output:

Solution:

Create same data –

USE DATABASE
GO
CREATE TABLE [StudentsEnroll] (
[Students] [varchar] (50) CONSTRAINT [DF_StudentsEnroll_Students] DEFAULT (''),
[Class1] [bit] NOT NULL CONSTRAINT [DF_StudentsEnroll_Class1] DEFAULT (0),
[Class2] [bit] NOT NULL CONSTRAINT [DF_StudentsEnroll_Class2] DEFAULT (0),
[Class3] [bit] NOT NULL CONSTRAINT [DF_StudentsEnroll_Class3] DEFAULT (0)
) ON [PRIMARY]
GO
INSERT INTO StudentsEnroll (Students, Class1, Class2, Class3)
VALUES ('Student1', 1, 0, 1)
GO
INSERT INTO StudentsEnroll (Students, Class1, Class2, Class3)
VALUES ('Student2', 1, 1, 1)
GO
INSERT INTO StudentsEnroll (Students, Class1, Class2, Class3)
VALUES ('Student3', 0, 1, 0)
GO
INSERT INTO StudentsEnroll (Students, Class1, Class2, Class3)
VALUES ('Student4', 1, 0, 1)
GO

Solution 1 – Using only SELECT statement

DECLARE @Col INT
SET @Col = 1
WHILE (@Col < 4)
BEGIN
EXEC('SELECT     ''Class'+@Col+' Has ''</pre>
<pre>+ CAST(COUNT(Students) AS VARCHAR(100)) + '' Students'' Results
FROM         studentsenroll
WHERE         Class'+@Col+' = 1
GROUP BY      Class'+@Col)
SET @Col = @Col + 1
CONTINUE
END

Solution 2 – Getting results in one resultset

SELECT 'Class1 has ' + CAST(COUNT(*) AS VARCHAR(10)) +' Students'
FROM StudentsEnroll
WHERE Class1 = 1
GROUP BY Class1
UNION ALL
SELECT 'Class2 has ' + CAST(COUNT(*) AS VARCHAR(10)) +' Students'
FROM StudentsEnroll
WHERE Class2 = 1
GROUP BY Class2
UNION ALL
SELECT 'Class3 has ' + CAST(COUNT(*) AS VARCHAR(10)) +' Students'
FROM StudentsEnroll
WHERE Class3 = 1
GROUP BY Class3

If you know any other method or script to achieve the same result, please share it here with your friends.

SQL in Sixty Seconds – Puzzle

If you enjoy quick and insightful tips about SQL Server, check out my SQL in Sixty Seconds video series on YouTube. These short videos are designed to help you learn SQL concepts, tricks, and troubleshooting techniques in just one minute. Subscribe and explore a growing library of content to enhance your SQL skills.

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