at 17:54 no need to add sr.option_marked "e" since we already have sr.option_marked = ca.correct_option condition because "e" is available in ca.correct_option table.
with cteScore as ( select r.roll_number , r.question_paper_code , sum(case when r.option_marked = c.correct_option then 1 else 0 end) as correct , sum(case when r.option_marked not in ('e') and r.option_marked c.correct_option then 1 else 0 end) as wrong , sum(case when r.option_marked = 'e' then 1 else 0 end) as yet_learn from student_response as r inner join correct_answers as c on r.question_paper_code = c.question_paper_code and r.question_number = c.question_number group by r.roll_number, r.question_paper_code ) , cteTotalScore as ( select s.roll_number , q.[subject] , s.correct , s.wrong , s.yet_learn from cteScore as s inner join question_paper_code as q on s.question_paper_code = q.paper_code ) , cteCorrect as ( select roll_number, [Math], [Science] from ( select roll_number, [subject], correct from cteTotalScore ) as s pivot ( max(correct) for [subject] in ([Math],[Science]) ) as x ) , cteWrong as ( select roll_number, [Math], [Science] from ( select roll_number, [subject], wrong from cteTotalScore ) as s pivot ( max(wrong) for [subject] in ([Math],[Science]) ) as x ) , cteYetLearn as ( select roll_number, [Math], [Science] from ( select roll_number, [subject], yet_learn from cteTotalScore ) as s pivot ( max(yet_learn) for [subject] in ([Math],[Science]) ) as x ) select c.Roll_number , s.Student_name , s.Class , s.Section , s.School_name , c.Math as Math_correct , w.Math as Math_wrong , l.Math as Math_yet_to_learn , c.Math as Math_score , cast((c.Math*100.00/(c.Math+w.Math+l.Math)) as decimal(19,2)) as Math_percentage , c.Science as Science_correct , w.Science as Science_wrong , l.Science as Science_yet_learn , c.Science as Science_score , cast((c.Science*100.00/(c.Science+w.Science+l.Science)) as decimal(19,2)) as Science_percentage from cteCorrect as c inner join cteWrong as w on c.roll_number = w.roll_number inner join cteYetLearn as l on c.roll_number = l.roll_number inner join student_list as s on c.roll_number = s.roll_number --where c.roll_number in (10159,10114, 10215, 10052,10201) order by roll_number;
Toufiq bro ...........very thanks to you for making this format of video which is even understood well by beginners also. keep updating us in this way...we don't even need to join in any other classes....😜
We can solve it without using cte as well .Here is my solution (MySQL) : - SELECT sr.roll_no, sl.student_name, sl.class, sl.section, SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) AS math_correct, SUM(CASE WHEN subject = 'Math' AND option_marked != correct_option AND option_marked != 'e' THEN 1 ELSE 0 END) AS math_wrong, SUM(CASE WHEN subject = 'Math' AND option_marked = 'e' THEN 1 ELSE 0 END) AS math_yet_to_learn, SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) AS math_score, ROUND(SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) * 100 / SUM(IF(subject = 'Math', 1, 0)),2) AS math_percentage, SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) AS science_correct, SUM(CASE WHEN subject = 'Science' AND option_marked != correct_option AND option_marked != 'e' THEN 1 ELSE 0 END) AS science_wrong, SUM(CASE WHEN subject = 'Science' AND option_marked = 'e' THEN 1 ELSE 0 END) AS science_yet_to_learn, SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) AS science_score, ROUND(SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) * 100 / SUM(IF(subject = 'Science', 1, 0)),2) AS science_percentage FROM student_response AS sr INNER JOIN correct_answer AS ca ON sr.question_paper_code = ca.question_paper_code AND sr.question_number = ca.question_number INNER JOIN student_list AS sl ON sr.roll_no = sl.roll_no INNER JOIN question_paper_code AS qpc ON sr.question_paper_code = qpc.paper_code GROUP BY 1,2,3,4
Hey! This was an amazing video. I followed all the steps and everything worked out fine. Just to practice more, I tried to IMPORT the RESULTS that came out of the queries that you ran but I was receiving an error. "ERROR: extra data after last expected column CONTEXT: COPY final_interview_result_query, line 2: "10159,Ada,8,A,Google Public School,4,28,8,4,10,11,38,11,11,18.33". For your Next Video, Can you film how to import end results back to PostGreSql? Maybe, with different datasets. Thank you
Hello, I am learning SQL through your TH-cam videos, and they have been incredibly helpful. May I summarize the content from your videos and post it on my blog for personal study purposes? I will credit your TH-cam channel as the source. Thank you."
Sorry I am digressing a bit but a quick question, please? I am currently analysing an employees layoff dataset available on kaggle. It has 1574 records and 9 columns, one of the columns name "Total_laid_off" (how many employees were laid off from the organisation,datatype: Int), it has 442 records with missing values. what should I do in this case to treat the missing values? should I replace them with median value or shall I drop missing values from the dataset? I have a similar question for another column name "percentage of employees laid off" (percentage of employees fired out of the total workforce). in this case, also shall I replace 552 missing values with the median percentage value? What could be the best course of action? I personally thought I should replace the missing values with median values because dropping so many rows would result in a good amount of loss of information.
Hi Thoufiq, I really appreciate the effort you put into this video, thank you. I think there is something wrong with the following paragraph on your blog: COUNT: Whereas the below query will return the maximum salary for each department in the employee table: SELECT dept_id, COUNT(emp_id) as no_of_emp_per_dept FROM employee GROUP BY dept_id; I think you mean query will return number of employees in each dep not the maximum salary.
Hi.. Suppose,i have a table 'A' with five child table namely 'B','C','D','E','F'. Now,i like to delete the records of 'A' directly without deleting the child table one by one. Here,the problem is that out of those five child tables one or more can have also child tables.So,it is very difficult to go one by one and delete the all child tables followed by parent table. Also,there is possibilities of adding one or more child table to any of those tables described above in later point of time.In that case my query which is developed at this moment will not work and i would get error of ''INTEGRITY CONSTRAINT VIOLATION''. Also,there is no provision of ''on delete cascade'' as per our available data model and also it can't be modified right now. So,please kindly help me to resolve the issue of deleting parent record without deleting the immediate child tables one by one and also the associated child tables of the immediate child tables if any one by one.
Hi bro , i don't know whether this comment will reach to you or not... But hope it will reach.. Can you please explain Piza delivery status query...?? Recently in KPMG test i got this question and unable to find answer for this... And which logic we can use for it... Please make one video on this
with Student_with_marks as ( select sr.roll_number as Roll_number, sr.question_number as Question_number, sr.option_marked as marked_Option, sr.question_paper_code as Question_paper_code, qpc.class as class, qpc.subject as Subject from [dbo].[student_response] sr join question_paper_code qpc on qpc.paper_code=sr.question_paper_code ), include_correct_answers as ( Select qpc.subject, ca.question_number, ca.question_paper_code, ca.correct_option from correct_answer ca join question_paper_code qpc on qpc.paper_code=ca.question_paper_code ) select swm.Roll_number, sl.student_name, sl.school_name, sl.class, sl.section, count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) as Math_correct, count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Math' then 1 else null end) - count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) as Math_Incorrect, count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) as Math_yet_to_learn, convert(decimal(10,2),100*count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) / (count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) + count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Math' then 1 else null end)+ count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) )) as Mat_percent, count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end) as Science_correct, count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Science' then 1 else null end) - count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end) as Science_Incorrect, count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end) as Science_yet_to_learn, convert(decimal(10,2), 100*count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end) / ( count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end) +count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Science' then 1 else null end) +count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end) )) as Science_Percent from Student_with_marks swm join include_correct_answers ica on swm.question_paper_code=ica.question_paper_code and swm.question_number=ica.question_number join student_list sl on sl.roll_number=swm.roll_number group by swm.Roll_number, sl.student_name,sl.school_name,sl.class,sl.section I tried the above problem using SQL Server. Thank you I have learnt SQL by watching and practicing your video, you are very helpful keep posting such amazing videos
SELECT sl.roll_number, student_name, sl.class, section, school_name, SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1 ELSE 0 END) AS math_correct, SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'math' THEN 1 ELSE 0 END) AS mathwrong, SUM(CASE WHEN option_marked = 'E' AND subject = 'math' THEN 1 ELSE 0 END) AS math_yet, SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1 ELSE 0 END) AS math_score, CAST((SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1.0 ELSE 0 END) * 100) / (SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1.0 ELSE 0 END) + SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'math' THEN 1 ELSE 0 END) + SUM(CASE WHEN option_marked = 'E' AND subject = 'math' THEN 1.0 ELSE 0 END)) AS numeric(10, 2)) AS Math_percent, SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) AS SCI_correct, SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'Science' THEN 1 ELSE 0 END) AS Sciwrong, SUM(CASE WHEN option_marked = 'E' AND subject = 'Science' THEN 1 ELSE 0 END) AS SCI_yet, SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) AS SCI_score, CAST((SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) * 100) / (SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) + SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'Science' THEN 1 ELSE 0 END) + SUM(CASE WHEN option_marked = 'E' AND subject = 'Science' THEN 1 ELSE 0 END)) AS numeric(10, 2)) AS SCI_percent FROM [#student_list] AS sl INNER JOIN [#student_response] AS sr ON sl.roll_number = sr.roll_number INNER JOIN [#correct_answer] AS ca ON sr.question_number = ca.question_number AND ca.question_number = sr.question_number INNER JOIN [#question_paper_code] AS qpc ON qpc.paper_code = ca.question_paper_code AND qpc.class = sl.class AND qpc.paper_code = sr.question_paper_code GROUP BY sl.roll_number, student_name, sl.class, section, school_name ORDER BY sl.roll_number
Your tips on SQL has really improved my scripting skills in my line of work. Very practical examples. Thank you for the good work
thank, what i learn from your youtube channel is greater than 4 year in college which i need to pay around 50.000 usd for tuition fee :(
Well said 👍
at 17:54 no need to add sr.option_marked "e" since we already have sr.option_marked = ca.correct_option condition because "e" is available in ca.correct_option table.
The way you are analysing the problem 💯 💯🔥
with cteScore
as (
select r.roll_number
, r.question_paper_code
, sum(case when r.option_marked = c.correct_option then 1 else 0 end) as correct
, sum(case when r.option_marked not in ('e') and r.option_marked c.correct_option then 1 else 0 end) as wrong
, sum(case when r.option_marked = 'e' then 1 else 0 end) as yet_learn
from student_response as r
inner join correct_answers as c on r.question_paper_code = c.question_paper_code
and r.question_number = c.question_number
group by r.roll_number, r.question_paper_code
)
, cteTotalScore
as (
select s.roll_number
, q.[subject]
, s.correct
, s.wrong
, s.yet_learn
from cteScore as s
inner join question_paper_code as q on s.question_paper_code = q.paper_code
)
, cteCorrect
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], correct
from cteTotalScore
) as s
pivot
(
max(correct) for [subject] in ([Math],[Science])
) as x
)
, cteWrong
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], wrong
from cteTotalScore
) as s
pivot
(
max(wrong) for [subject] in ([Math],[Science])
) as x
)
, cteYetLearn
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], yet_learn
from cteTotalScore
) as s
pivot
(
max(yet_learn) for [subject] in ([Math],[Science])
) as x
)
select c.Roll_number
, s.Student_name
, s.Class
, s.Section
, s.School_name
, c.Math as Math_correct
, w.Math as Math_wrong
, l.Math as Math_yet_to_learn
, c.Math as Math_score
, cast((c.Math*100.00/(c.Math+w.Math+l.Math)) as decimal(19,2)) as Math_percentage
, c.Science as Science_correct
, w.Science as Science_wrong
, l.Science as Science_yet_learn
, c.Science as Science_score
, cast((c.Science*100.00/(c.Science+w.Science+l.Science)) as decimal(19,2)) as Science_percentage
from cteCorrect as c
inner join cteWrong as w on c.roll_number = w.roll_number
inner join cteYetLearn as l on c.roll_number = l.roll_number
inner join student_list as s on c.roll_number = s.roll_number
--where c.roll_number in (10159,10114, 10215, 10052,10201)
order by roll_number;
The way you have explained the concept is solutable boss thanks for the video
Toufiq bro ...........very thanks to you for making this format of video which is even understood well by beginners also. keep updating us in this way...we don't even need to join in any other classes....😜
You are my virtual master in learning SQL.
You are AMAZING my dear friend!! Thanks a lot
You're welcome . Glad it helped
Bhai hats off for the patience and pace you took to solve this problem. Really man🎉 impressive
Your video content always it's like new gift for all learners 🏆❣️😍
This was really an informative video. Thank you so much
create table student_list
(
roll_number int,
student_name varchar(50),
class int,
section varchar(2),
school_name varchar(40)
)
create table correct_answer
( question_paper_code int ,
question_number int,
correct_option varchar(1)
)
create table student_response
(
roll_number int,
question_paper_code int,
question_number int,
option_marked varchar(1)
)
create table question_paper_code
( paper_code int ,
class int,
Subject varchar(10)
)
use etl to store data from excel file
well explained sir thnk u so mch its very usefull to me with realtime scenarios...
I am glad that... This problem is shared with all the people.. U helped me out of this. Thank you so much 🙏 🙏
Thank you for sharing knowledge as always, Thoufiq.
Awesome clarification - ive not used “cte” before.. makes life easier 👍🏻👍🏻
Detailed process and explanation
Thanks a lot sir!
awesome, waiting to join your live SQL training sir. Please let me know when next batch will be started.
Great explanation, thanks!
It can't be better than this. No words!
Thanks for making this video @techTFQ ❤
Beautifully explained thanks toufik , keep it up😃💥
We can solve it without using cte as well .Here is my solution (MySQL) : -
SELECT
sr.roll_no,
sl.student_name,
sl.class,
sl.section,
SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) AS math_correct,
SUM(CASE WHEN subject = 'Math' AND option_marked != correct_option AND option_marked != 'e' THEN 1 ELSE 0 END) AS math_wrong,
SUM(CASE WHEN subject = 'Math' AND option_marked = 'e' THEN 1 ELSE 0 END) AS math_yet_to_learn,
SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) AS math_score,
ROUND(SUM(CASE WHEN subject = 'Math' AND option_marked = correct_option THEN 1 ELSE 0 END) * 100 / SUM(IF(subject = 'Math', 1, 0)),2) AS math_percentage,
SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) AS science_correct,
SUM(CASE WHEN subject = 'Science' AND option_marked != correct_option AND option_marked != 'e' THEN 1 ELSE 0 END) AS science_wrong,
SUM(CASE WHEN subject = 'Science' AND option_marked = 'e' THEN 1 ELSE 0 END) AS science_yet_to_learn,
SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) AS science_score,
ROUND(SUM(CASE WHEN subject = 'Science' AND option_marked = correct_option THEN 1 ELSE 0 END) * 100 / SUM(IF(subject = 'Science', 1, 0)),2) AS science_percentage
FROM student_response AS sr
INNER JOIN correct_answer AS ca ON sr.question_paper_code = ca.question_paper_code AND sr.question_number = ca.question_number
INNER JOIN student_list AS sl ON sr.roll_no = sl.roll_no
INNER JOIN question_paper_code AS qpc ON sr.question_paper_code = qpc.paper_code
GROUP BY 1,2,3,4
Thanks. Very nice video. Is there a way to download/import the tables and data you have so that I can employ and practice?
Awesome...! always love to watch your video thank for sharing the knowledge....
Super thoufiq
Thank you and God bless you.
do know the application this guy using, sorry Im really new here
At 26:34, we can assume ((math_correct * 1) - (math_wrong * 0.25) ) as math_Score.
Well tackled Sir, thank you.
very helpful sir
Very informative,
Thank you
Urs explanation really very very clear
Thank you for wonderful question.
Wow 😳 excellent
Thanks and very useful
Your videos are the best !
Hey! This was an amazing video. I followed all the steps and everything worked out fine. Just to practice more, I tried to IMPORT the RESULTS that came out of the queries that you ran but I was receiving an error. "ERROR: extra data after last expected column
CONTEXT: COPY final_interview_result_query, line 2: "10159,Ada,8,A,Google Public School,4,28,8,4,10,11,38,11,11,18.33". For your Next Video, Can you film how to import end results back to PostGreSql? Maybe, with different datasets. Thank you
Great content , Do you have any union and union all complex queries video?
Hi dear
Could you please guide me to import data in SQL server from file.
Thankyou for this wonderful video.
Good explanation Boss
nice one
Love your videos
Fantastic......................
Thank you! Cheers!
Hello,
I am learning SQL through your TH-cam videos, and they have been incredibly helpful. May I summarize the content from your videos and post it on my blog for personal study purposes? I will credit your TH-cam channel as the source.
Thank you."
Yes you can do it 👍
Wonderful Video...
Please make vedio on regular expression ..
Sir, please make the video on OUTPUT clause
Such queries are asked to beginners as well? And that too for internship?
SUPERB !
Amazing!!!
Superb👌👌
Sorry I am digressing a bit but a quick question, please?
I am currently analysing an employees layoff dataset available on kaggle. It has 1574 records and 9 columns, one of the columns name "Total_laid_off" (how many employees were laid off from the organisation,datatype: Int), it has 442 records with missing values. what should I do in this case to treat the missing values? should I replace them with median value or shall I drop missing values from the dataset?
I have a similar question for another column name "percentage of employees laid off" (percentage of employees fired out of the total workforce). in this case, also shall I replace 552 missing values with the median percentage value?
What could be the best course of action?
I personally thought I should replace the missing values with median values because dropping so many rows would result in a good amount of loss of information.
is not there any way to derive the required missing data from other available columns?
median can't justify the actual scenarios of the layoff.
@@luckykumar1665 not possible practically to find lay off numbers for 400+ rows😅. So shall I drop those rows then?
Hi Thoufiq,
I really appreciate the effort you put into this video, thank you.
I think there is something wrong with the following paragraph on your blog:
COUNT:
Whereas the below query will return the maximum salary for each department in the employee table:
SELECT dept_id, COUNT(emp_id) as no_of_emp_per_dept
FROM employee
GROUP BY dept_id;
I think you mean query will return number of employees in each dep not the maximum salary.
can you please upload this ques once again coz its not showing now after downloding
Hlo sir I need information well which type of skills needed to become a SQL developer
Hello sir , can you please make video on data migration using paython . I can easily understand whatever you teach .
Can you tell me whether should include tableau or EDA in pandas jupyter projects in my resume
can anyone tell what the application this guy using, I'm new here so I want to know
Hi..
Suppose,i have a table 'A' with five child table namely 'B','C','D','E','F'.
Now,i like to delete the records of 'A' directly without deleting the child table one by one.
Here,the problem is that out of those five child tables one or more can have also child tables.So,it is very difficult to go one by one and delete the all child tables followed by parent table.
Also,there is possibilities of adding one or more child table to any of those tables described above in later point of time.In that case my query which is developed at this moment will not work and i would get error of ''INTEGRITY CONSTRAINT VIOLATION''.
Also,there is no provision of ''on delete cascade'' as per our available data model and
also it can't be modified right now.
So,please kindly help me to resolve the issue of deleting parent record without deleting the immediate child tables one by one and also the associated child tables of the immediate child tables if any one by one.
@atul Singh In a similar situation, I had used on delete cascade but I understand you couldn't use it.
How did you do it ?
Hi bro , i don't know whether this comment will reach to you or not... But hope it will reach.. Can you please explain Piza delivery status query...?? Recently in KPMG test i got this question and unable to find answer for this... And which logic we can use for it... Please make one video on this
Can you please share the question along with the given data ?
How get data analytics job or internship as fresher because I am learning now python, SQL, completed
Hi all...how to get math_percent and science_percent in SQL server...I am getting zero for all records
❤ 🔥
How to run 10 SQL scripts using single script or a command line ?
Not able to import csv file in to Pg admin. Always getting the error could not open file for reading, permission denied.
Could you please guide
Is this is fresher interview question
Not able to restore the csv files in postgres .
how can i share my query with you
create statements r missing
with Student_with_marks as (
select
sr.roll_number as Roll_number,
sr.question_number as Question_number,
sr.option_marked as marked_Option,
sr.question_paper_code as Question_paper_code,
qpc.class as class,
qpc.subject as Subject
from [dbo].[student_response] sr
join question_paper_code qpc on qpc.paper_code=sr.question_paper_code
),
include_correct_answers as (
Select
qpc.subject,
ca.question_number,
ca.question_paper_code,
ca.correct_option
from
correct_answer ca
join question_paper_code qpc on qpc.paper_code=ca.question_paper_code
)
select
swm.Roll_number,
sl.student_name,
sl.school_name,
sl.class,
sl.section,
count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) as Math_correct,
count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Math' then 1 else null end) -
count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) as Math_Incorrect,
count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) as Math_yet_to_learn,
convert(decimal(10,2),100*count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) /
(count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Math' then 1 else null end) +
count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Math' then 1 else null end)+
count (case when swm.marked_Option='e' and swm.Subject = 'Math' then 1 else null end) )) as Mat_percent,
count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end) as Science_correct,
count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Science' then 1 else null end) -
count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end) as Science_Incorrect,
count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end) as Science_yet_to_learn,
convert(decimal(10,2), 100*count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end) /
(
count(case when swm.marked_Option=ica.correct_option and swm.Subject = 'Science' then 1 else null end)
+count(case when swm.marked_Optionica.correct_option and swm.Subject = 'Science' then 1 else null end)
+count(case when swm.marked_Option='e' and swm.Subject = 'Science' then 1 else null end)
)) as Science_Percent
from Student_with_marks swm
join include_correct_answers ica on swm.question_paper_code=ica.question_paper_code and swm.question_number=ica.question_number
join student_list sl on sl.roll_number=swm.roll_number
group by swm.Roll_number, sl.student_name,sl.school_name,sl.class,sl.section
I tried the above problem using SQL Server.
Thank you
I have learnt SQL by watching and practicing your video, you are very helpful
keep posting such amazing videos
SELECT sl.roll_number, student_name, sl.class, section, school_name, SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1 ELSE 0 END) AS math_correct,
SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'math' THEN 1 ELSE 0 END) AS mathwrong, SUM(CASE WHEN option_marked = 'E' AND subject = 'math' THEN 1 ELSE 0 END)
AS math_yet, SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1 ELSE 0 END) AS math_score, CAST((SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1.0 ELSE 0 END)
* 100) / (SUM(CASE WHEN option_marked = correct_option AND subject = 'math' THEN 1.0 ELSE 0 END) + SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'math' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN option_marked = 'E' AND subject = 'math' THEN 1.0 ELSE 0 END)) AS numeric(10, 2)) AS Math_percent, SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END)
AS SCI_correct, SUM(CASE WHEN option_marked correct_option AND option_marked 'E' AND subject = 'Science' THEN 1 ELSE 0 END) AS Sciwrong, SUM(CASE WHEN option_marked = 'E' AND
subject = 'Science' THEN 1 ELSE 0 END) AS SCI_yet, SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) AS SCI_score, CAST((SUM(CASE WHEN option_marked = correct_option AND
subject = 'Science' THEN 1 ELSE 0 END) * 100) / (SUM(CASE WHEN option_marked = correct_option AND subject = 'Science' THEN 1 ELSE 0 END) + SUM(CASE WHEN option_marked correct_option AND
option_marked 'E' AND subject = 'Science' THEN 1 ELSE 0 END) + SUM(CASE WHEN option_marked = 'E' AND subject = 'Science' THEN 1 ELSE 0 END)) AS numeric(10, 2)) AS SCI_percent
FROM [#student_list] AS sl INNER JOIN
[#student_response] AS sr ON sl.roll_number = sr.roll_number INNER JOIN
[#correct_answer] AS ca ON sr.question_number = ca.question_number AND ca.question_number = sr.question_number INNER JOIN
[#question_paper_code] AS qpc ON qpc.paper_code = ca.question_paper_code AND qpc.class = sl.class AND qpc.paper_code = sr.question_paper_code
GROUP BY sl.roll_number, student_name, sl.class, section, school_name
ORDER BY sl.roll_number
Great explanation! Thank you