SQL SERVER – Interesting Interview Questions

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 few of my experience with candidates here.

Those who are scheduled for 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 following table, please write 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 '' + 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 (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – Interesting Interview Questions

  1. 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.

  2. 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];

  3. 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;

  4. Zod and Eric

    Very nice.

    Eric – particularly the one liner approach is the one I am looking for from developers.

    When they get that one, I ask them follow up question as what happens if number of class is dynamic.

    Very good.

    Regards,
    Pinal

  5. 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.

  6. @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

  7. 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. ;-)

  8. 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)

  9. 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

  10. 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.

  11. Dear Readers,

    Take this question as T-SQL practice exam rather than schema improvement problem.

    In my organization we do not ask this simple question for Schema improvement. Normalization is important but in data warehousing and BI de-normalization is equally important.

    Hope this clear now and waiting for more T-SQL solution to this problem.

    Everybody who participated here – Thanks I really see good suggestions.

    Kind Regards,
    Pinal Dave

  12. 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

  13. Pingback: SQL SERVER - Find Table Rowcount Without Using T-SQL and Without Opening Table Journey to SQL Authority with Pinal Dave

  14. 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’

  15. 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

  16. 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

  17. Pingback: SQL SERVER - Interesting Interview Questions - Revisited Journey to SQL Authority with Pinal Dave

  18. 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

  19. 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]

  20. 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

  21. 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

  22. 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.

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s