# SQL SERVER – Interesting Interview Questions – Difficult SQL Puzzle Today at my organization, we had nearly 30 interviews scheduled of DBA and .NET developers. I really enjoyed participating in interviewing panel. It was really 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 difficult 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 &lt; 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 here with your friends.

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

#### Related Posts

• cvdev
December 18, 2008 2:21 am

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]

• meh
December 19, 2008 2:34 am

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

• January 5, 2009 12:44 pm

Hi Pinal,

I am a beginner in SQL can you please give me suggestion as to how i can improve on it?.
I am very much interested in learning SQL Server.

Thanks,
Jerry

• Anu
May 19, 2009 5:16 pm

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.

• Kode
June 10, 2009 2:44 pm

declare @count int
declare @name varchar(30)
declare @stmt nvarchar(500)
set @stmt = ”
select @count = count(*) from sys.columns where object_id = object_id(‘studentsenroll’) and name ‘Students’
Declare c1 cursor
For select Name From sys.columns where object_id = object_id(‘studentsenroll’) and name ‘Students’
Open c1
fetch next from c1 into @name
while @@fetch_status = 0
Begin
if ( @count > 1)
set @stmt = @stmt + ‘select ”’ + @name + ‘ has ” + cast(sum(cast(‘ + @name + ‘ as int)) as varchar) +” students ” From studentsenroll union all ‘
else
set @stmt = @stmt + ‘select ”’ + @name + ‘ has ” + cast(sum(cast(‘ + @name + ‘ as int)) as varchar) +” students ” From studentsenroll ‘
SET @count = @count-1
fetch next from c1 into @name
End
exec sp_executesql @stmt
close c1
deallocate c1

• RakeshA
September 21, 2009 10:49 pm

create table xs(sname nvarchar(5),class1 int,class2 int,class3 int)

insert into xs values(‘stu1’,1,0,1)
insert into xs values(‘stu2’,1,0,1)
insert into xs values(‘stu3’,0,1,0)
insert into xs values(‘stu4’,1,0,1)

select sname+’has ‘+ cast((class1+class2+class3) as nvarchar(10))+’students’ from xs

• Sandor
December 5, 2009 1:14 am

select classname + ‘ has ‘ + Cast(total As Nvarchar(2)) + ‘ students’ from
(
select
sum(class1) class1,
sum(class2) class2,
sum(class3) class3
from studentsenroll
) p
unpivot
( Total for classname in (class1,class2,class3)) as S

• Lopa
January 22, 2010 4:57 am

Hi Pinal,

You are doing a great job.

I may sound low, but could you please explain me the where clause of the above query. Why the class+’@col’ value is always constant?

Thanks,
Lopa

• Pinal Dave
January 22, 2010 3:59 pm

Hi Lopa,

In expression class + ‘@col’ what is class and what is value in @col? Please provide details about your query.

Regards,
Pinal Dave

• Lopa
January 22, 2010 8:33 pm

Probably I’m asking this doubt very late.

You asked to write a query about student enrollment where there are three classes ; Class1 , Class2 and Class3 and 4 students as Student1, Student2, Student3 and Student4. And there is a table StudentEnroll which has the data which depicts which student is enrolled to which class.

The format of the output should be The Class 1 has 3 students and so on; for all the classes.

You have provided the followin solution

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

Now, I can understand that you are considering only those students who have been enrolled and that's the reason the class'+@col+' = 1 is a constant value.

This can be easily done by using the Sum function. But, you have mentioned that if the scenario is dynamic then we have to use the Group By function. Could you please explain me the Dynamic scenario.

I'm sorry if I sound stupid.

Regards,
Lopa

• INDIA
October 15, 2010 2:47 pm

declare @total int
set @total =0
select ‘CLASS 1 HAS ‘ + case when class1=1 then CAST (SUM(@total+1)AS VARCHAR(10)) + ‘ STUDENTS’ end from StudentsEnroll
WHERE Class1 =1
group by Class1
UNION ALL

select ‘CLASS 2 HAS ‘ + case when class2=1 then CAST (SUM(@total+1)AS VARCHAR(10)) + ‘ STUDENTS’ end from StudentsEnroll
WHERE Class2 =1
group by Class2

UNION ALL

select ‘CLASS 3 HAS ‘ + case when class3=1 then CAST (SUM(@total+1)AS VARCHAR(10)) + ‘ STUDENTS’ end from StudentsEnroll
WHERE Class3 =1
group by Class3

• INDIA
October 15, 2010 2:49 pm

thanks pinal for providing good platform for learing
sql

this site very helpful for me

keep it up……

• Nikash
June 4, 2012 7:14 pm

select ‘student1 has’+ CONVERT(varchar(10), SUM(class1) )+’staudent’
from StudentsEnroll

union all

select ‘student2 has’+ CONVERT(varchar(10), SUM(class2) ) +’staudent’
from StudentsEnroll

union all

select ‘student3 has’+ CONVERT(varchar(10), SUM(class3)) +’staudent’
from StudentsEnroll

• Andrew Wickham (@awickham)
June 22, 2016 12:23 am

I used UNPIVOT.

SELECT
Class + ‘ has ‘ + CAST(COUNT(1) AS varchar(5)) + ‘ students’
FROM (
SELECT
Students,
Enrolled,
Class
FROM StudentsEnroll p
UNPIVOT (Enrolled FOR Class IN (Class1, Class2, Class3)) as unpiv
) sub
WHERE Enrolled = 1
GROUP BY Class