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 < 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)
36 Comments. Leave new
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,
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
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.
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
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
How about this:
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
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
Hi Lopa,
In expression class + ‘@col’ what is class and what is value in @col? Please provide details about your query.
Regards,
Pinal Dave
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
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
thanks pinal for providing good platform for learing
sql
this site very helpful for me
keep it up……
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
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