SQL SERVER – Interesting Interview Questions – Revisited

I really enjoyed users participation in my previous question. Read SQL SERVER – Interesting Interview Questions before continuing reading this article. This interview question was about user participation and about how good and how different you can come with your T-SQL script. What I really liked is that many users took this test seriously and did their best to answer. I really want to congratulate all the readers who have attempted to answer this question.

As I have said earlier it did not matter what is the database structure, but it mattered what should be the good database architecture design. Here it only mattered if you can write T-SQL based on question.

Following readers have got correct answer.

Zod

Eric

Imran Mohammed

bhadeliaimran

fly

pom

I really want to thank and congratulate readers who have answered this question correct. I would suggest that you book mark this article as well original article for your personal reference. In future if you need ever recommendation regarding any interview I will be happy to list this articles for you in reference.

Now let us see their solution in detail.Please go over interview question again before continuing reading following comments.

Solution byZod

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'
GO


Solution by Eric

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

Solution by Imran Mohammed

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

Solution by bhadeliaimran

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)

Solution by fly

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

Solution by pom

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'

Other two solution proposed earlier in original article.

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

Now the real question is which of this articles are favorite solution of yours. Please leave your choice along with reason in comment.

I promise if you can not write solution but if you understand the proposed solution in this article you will usually pass any database T-SQL problem in interviews. Additionally, if you are looking for job in SQL Server related area please find your right job here. Make sure to read all the interview questions and answers for SQL Server 2008 before you appear for interview.

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