SQL Interview Questions and answers Part 19 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 19 | Scenario based SQL interview Question
It is one of most popular Scenario based SQL interview Question.

Input :- StudentInfo Table has four columns namely StudentName, English, Maths and Science

Problem Statements :- Write SQL to turn the columns English, Maths and Science into rows. It should display Marks for each student for each subjects as shown below

Follow me on Social Networking Sites :
Twitter:
Instagram :

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ITJunction4All
#ScenarioBasedSQLInterviewQuestion #SQLUnpivot

[sachinshetty]

Create Table studentinfo(
studentname Varchar(20),
english int,
maths int,
science int);

insert into studentinfo values ('David', 85, 90, 88);
insert into studentinfo values ('John', 75, 85, 80);
insert into studentinfo values ('Tom', 83, 80, 92);

[rhushishinde]

Nice...will you be able make one video on complex queries.... Example... Finding the run times of any process... Having start and end time info in different rows in same table... This kind of logical tricky sqls

[anupamaagarwal]

Hi sunil,
Excellent video.

I have a query related to pivot function.
When do we use sum and count aggregate while pivoting. Please help me.

I was also trying to pivot the reverse from solution to question table for practice and got confused either to use sum or count. However here is my query :
Select studentname, english, maths, science from
( Select studentname, subjects, marks from studentinfo )
Pivot
( Count(marks)
For subjects IN ( 'english', ' maths', 'science')

[harshrr]

select * from
studentinfo
UNPIVOT
(
marks for subjects in (English, Maths, Science)
) as unpvt

[imranpathan]

Provide Input Please.. i am Watching u r Video ..so please every Session please provide input..NICE VIDEO

[AtifImamAatuif]

select StudentName, 'English' as Subjects, English as Marks from StudentInfo
union all select StudentName, 'Maths' as Subjects, Maths as Marks from StudentInfo
union all select StudentName, 'Science' as Subjects, Science as Marks from StudentInfo
order by StudentName;

[ankushjain]

Other Alternative way when UNPIVOT syntax does not hit your mind
Select Studentname, 'English' as English, English as Marks from un
UNION ALL
Select StudentName, 'Math' as Math, math from un
UNION ALL
Select Studentname, 'Science' as Science, science from un

[soumeshkayast]

SELECT
STU_NAME,
SUBJECT,
SCORE
FROM SCENARIO_19
UNPIVOT(SCORE FOR SUBJECT IN (ENGLISH, MATHS, SCIENCE)) AS X;

[truptisarak]

Hi Sunil, I have new question could you u help me on it

[prajjwaljaiswal]

In MySQl PIVOT and UNPIVOT are not working. So,

Select StudentName,
'English' as Subjects, English as Marks
from studentinfo
union all
Select StudentName,
'Maths' as Subjects, Maths as Marks
from studentinfo
union all
Select StudentName,
'Science' as Subjects, Science as Marks
from studentinfo
order by StudentName;