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.
| TableName: | StudentsEnroll | ||
| Students | Class1 | Class2 | Class3 |
| Student1 | 1 | 0 | 1 |
| Student2 | 1 | 1 | 1 |
| Student3 | 0 | 1 | 0 |
| Student4 | 1 | 0 | 1 |
Output:
| Class1 has 3 Students | |
| Class2 has 2 Students | |
| Class3 has 3 Students |
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)
36 Comments. Leave new
DECLARE @Class1 int, @Class2 int, @Class3 int,
@Class1Total int, @Class2Total int, @Class3Total int
SET @Class1Total = 0
SET @Class2Total = 0
SET @Class3Total = 0
DECLARE student_CURSOR CURSOR FOR
SELECT Class1, Class2, Class3
FROM StudentsEnroll
OPEN student_CURSOR
FETCH next FROM student_CURSOR
INTO @Class1, @Class2, @Class3
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Class1Total = @Class1Total + @Class1
SET @Class2Total = @Class2Total + @Class2
SET @Class3Total = @Class3Total + @Class3
FETCH next FROM student_CURSOR
INTO @Class1, @Class2, @Class3
END
CLOSE student_CURSOR
DEALLOCATE student_CURSOR
SELECT ‘Class1 has ‘ + cast(@Class1Total AS varchar(10)) + ‘ students’
UNION ALL
SELECT ‘Class2 has ‘ + cast(@Class2Total AS varchar(10)) + ‘ students’
UNION ALL
SELECT ‘Class3 has ‘ + cast(@Class3Total AS varchar(10)) + ‘ students’
Do I get the job? Haaaa.
How about a 1 liner?
select
‘Class1 has ‘ + cast(sum(cast([Class1] as int)) as varchar(10)) + ‘ students.\n’
+ ‘Class2 has ‘ + cast(sum(cast([Class2] as int)) as varchar(10)) + ‘ students.\n’
+ ‘Class3 has ‘ + cast(sum(cast([Class3] as int)) as varchar(10)) + ‘ students.\n’
from [StudentsEnroll];
In case there is a chance that the number of classes may change over time, here is a dynamic approach:
declare @cls nvarchar(100);
declare c cursor fast_forward for
select
c.name
from
sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
where
t.[name] = ‘StudentsEnroll’
and c.[name] ‘Students’;
open c;
fetch next from c into @cls;
while @@fetch_status = 0
begin
declare @cmd nvarchar(500);
set @cmd = ‘select ”’ + @cls + ‘ has ” + cast(count([Students]) as varchar(10)) + ” students.”’
+ ‘ from [StudentsEnroll] where [‘ + @cls + ‘] 0 group by [‘ + @cls + ‘]’;
print @cmd;
exec sp_sqlexec @cmd;
fetch next from c into @cls;
end
close c;
deallocate c;
can you tell me how this works
Why not juse use a pivot? If you’d like, I can write the query.
It amazes me that people are still using cursors as their first answer to everything… the are a very (very!) few situations were one may argue a cursor is applicable, this is most certainly not one of them. If Zod provided that as an answer, I would end the interview right there.
Besides that, the schema in the original question is flawed. How about bonus points to the interviewee that points out that it is a bad design to have classes listed columnwise like this. Having to do dynamic sql to get around this design flaw is only a “good” answer if somehow you’re stuck with a bad schema.
@Pinal
how about this ?
select CLASS+ ‘ has ‘+convert(varchar(10),count(orders ))+’ Students ‘ ‘Output’ from (
SELECT students, Class, Orders
FROM
(SELECT students, class1, class2,class3
FROM studentsenroll ) p
UNPIVOT
(Orders FOR Class IN
(class1, class2, class3 )
)AS unpvt) X where orders = 1 group by class
Thanks,
IM
If Zod provided that as an answer, I would end the interview right there.
Uh, Pinal asked for other ways to do it, not the best way to do it. So, if you were giving the interview, I think I’d walk out right here since you didn’t understand Pinal’s question. ;-)
DECLARE @opXml as XML
SET @opXml = ‘<ClassStud value=”‘ + REPLACE(
(SELECT
‘Class1 has ‘ + CAST(SUM(CAST(Class1 AS SMALLINT)) AS VARCHAR(4)) + ‘ students’ ,
‘,Class2 has ‘ + CAST(SUM(CAST(Class2 AS SMALLINT)) AS VARCHAR(4)) + ‘ students’ ,
‘,Class3 has ‘ + CAST(SUM(CAST(Class3 AS SMALLINT)) AS VARCHAR(4)) + ‘ students’
FROM [StudentsEnroll]
FOR XML PATH(”) ), ‘,’, ‘” /><ClassStud value=”‘) + ‘” />’
SELECT x.value(‘@value’, ‘varchar(100)’) AS [output]
FROM @opXml.nodes(‘/ClassStud’) as p(x)
How about this:
declare @tbl table (i int)
insert into @tbl (i)
select 1
union
select 2
union
select 3
select
case when i = 1 then ‘Class 1 has ‘ + cast(sum(cast (class1 as int)) as varchar(100)) + ‘ Students’ else
case when i = 2 then ‘Class 2 has ‘ + cast(sum(cast (class2 as int)) as varchar(100)) + ‘ Students’ else
case when i = 3 then ‘Class 3 has ‘ + cast(sum(cast (class3 as int)) as varchar(100)) + ‘ Students’ else ” end
end
end
from [StudentsEnroll]
join @tbl t on i <= 3
group by i
I’d be concerned about any organisation that asked a question like that. The table design is terrible, and if it was more normalized, there wouldn’t be any need to come up with clever solutions.
Of course, if the question asked how you would improve the schema and then return the output that would be acceptable.
Hi all,
Good to see you here and very very thanks for sharing the knowledge.
So anyone can u please tell me about the difference between table variables and the cursors..
which is good one and where??
Thanks
Regards Prakash
select ‘class1 has ‘ + cast((select count(*) from StudentsEnroll where class1=1 group by class1) as char(3)) + ‘Students’
union
select ‘class2 has ‘ + cast((select count(*) from StudentsEnroll where class2=1 group by class2) as char(3)) + ‘Students’
union
select ‘class3 has ‘ + cast((select count(*) from StudentsEnroll where class3=1 group by class3) as char(3)) + ‘Students’
Thanks for the nice tricks.
Imran is correct in that PIVOT is not the answer (as surmised) but UNPIVOT.
Here’s a bit cleaner version of UNPIVOT that is closer to the asked for end result…
SELECT Class,
CAST(‘has’ as char(3)) as ‘has’,
SUM(CAST(Breakout as tinyint)) as ‘X’,
CAST(‘students.’ as char(9)) as ‘students.’
FROM (
SELECT Class, Breakout, Students FROM
(
SELECT Students, Class1, Class2, Class3
FROM StudentsEnroll
) se_inner
UNPIVOT (Breakout FOR Class IN (Class1, Class2, Class3)) AS denorm
) se_outer
GROUP BY Class ORDER BY Class
hi i am new to sql server and its very intresting to see such good questions.
i would participate int hese discussions and post my comments for the queries.
Thanks
jagadesh
hi pinal,
I am a beginner in sql server…can u give me any suggestions in his regard..
Thanks
Jagadesh
SELECT ‘Class1 has ‘+ CONVERT(nvarchar(10),sum(CASE class1 WHEN 1 THEN 1 ELSE 0 end))+’ students’
FROM StudentsEnroll se union ALL
SELECT ‘Class2 has ‘+ CONVERT(nvarchar(10),sum(CASE class2 WHEN 1 THEN 1 ELSE 0 end))+’ students’
FROM StudentsEnroll se union ALL
SELECT ‘Class3 has ‘+ CONVERT(nvarchar(10),sum(CASE class3 WHEN 1 THEN 1 ELSE 0 end))+’ students’
FROM StudentsEnroll se
SELECT ‘Class1 Has ‘+CAST(SUM(CAST(Class1 AS INT)) AS VARCHAR(10))+’ Students’+CHAR(10)+
‘Class2 Has ‘+CAST(SUM(CAST(Class2 AS INT)) AS VARCHAR(10))+’ Students’+CHAR(10)+
‘Class3 Has ‘+CAST(SUM(CAST(Class3 AS INT)) AS VARCHAR(10))+’ Students’+CHAR(10)
FROM [StudentsEnroll]
SELECT
UP.Class + ‘ has ‘ + CONVERT(varchar, SUM(CONVERT(int, UP.Total))) + ‘ Students’ [Output]
FROM
(
SELECT
Students [Enrolled],
Class1,
Class2,
Class3
FROM
StudentsEnroll
) SE
UNPIVOT (Total FOR Class IN (Class1, Class2, Class3)) UP
GROUP BY
UP.Class
Hi Pinal,
This place is really gud for all the issues related to sql.
I just saw the query above so just tried this way
select ‘Class1 has ‘ + cast(count(nullif(class1,0))as varchar(10)) +’ students’ from StudentsEnroll union
select ‘Class2 has ‘+ cast(count(nullif(class2,0))as varchar(10)) + ‘ students’ from StudentsEnroll union
select ‘Class3 has ‘+cast(count(nullif(class3,0))as varchar(10)) +’ students’from StudentsEnroll
Its the same way as you have written i have just modified the query by using NULLIF so where clause would’nt be used.