SQL SERVER – Interesting Interview Questions – Difficult SQL Puzzle

SQL SERVER - Interesting Interview Questions - Difficult SQL Puzzle 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.

Solarwinds
TableName: StudentsEnroll
Students Class1 Class2 Class3
Student1101
Student2111
Student3010
Student4101

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation
Next Post
SQL SERVER – Interesting Interview Questions – Part 2 – Puzzle

Related Posts

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]

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Hi Lopa,

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

    Regards,
    Pinal Dave

    Reply
  • 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

    Reply
  • 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

    Reply
  • thanks pinal for providing good platform for learing
    sql

    this site very helpful for me

    keep it up……

    Reply
  • 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

    Reply
  • 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

    Reply

Leave a Reply

Menu