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.
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....😜
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;
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
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
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.. 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 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 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
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.
Your tips on SQL has really improved my scripting skills in my line of work. Very practical examples. Thank you for the good work
The way you are analysing the problem 💯 💯🔥
It can't be better than this. No words!
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....😜
The way you have explained the concept is solutable boss thanks for the video
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 🏆❣️😍
At 26:34, we can assume ((math_correct * 1) - (math_wrong * 0.25) ) as math_Score.
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 👍
Detailed process and explanation
Thanks a lot sir!
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.
This was really an informative video. Thank you so much
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;
well explained sir thnk u so mch its very usefull to me with realtime scenarios...
Awesome clarification - ive not used “cte” before.. makes life easier 👍🏻👍🏻
Super thoufiq
Well tackled Sir, thank you.
Beautifully explained thanks toufik , keep it up😃💥
Thanks for making this video @techTFQ ❤
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
Awesome...! always love to watch your video thank for sharing the knowledge....
Thank you and God bless you.
do know the application this guy using, sorry Im really new here
Wow 😳 excellent
Thank you for wonderful question.
Very informative,
Thank you
Urs explanation really very very clear
Great explanation! Thank you
Your videos are the best !
Thanks and very useful
awesome, waiting to join your live SQL training sir. Please let me know when next batch will be started.
very helpful sir
Thanks. Very nice video. Is there a way to download/import the tables and data you have so that I can employ and practice?
Love your videos
Good explanation Boss
Superb👌👌
Wonderful Video...
SUPERB !
Fantastic......................
Thank you! Cheers!
Hi dear
Could you please guide me to import data in SQL server from file.
Thankyou for this wonderful video.
nice one
Great content , Do you have any union and union all complex queries video?
Amazing!!!
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
Please make vedio on regular expression ..
Sir, please make the video on OUTPUT clause
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
Not able to restore the csv files in postgres .
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?
Can you tell me whether should include tableau or EDA in pandas jupyter projects in my resume
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
can you please upload this ques once again coz its not showing now after downloding
Hi all...how to get math_percent and science_percent in SQL server...I am getting zero for all records
Such queries are asked to beginners as well? And that too for internship?
Hlo sir I need information well which type of skills needed to become a SQL developer
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 ?
Hello sir , can you please make video on data migration using paython . I can easily understand whatever you teach .
create statements r missing
How get data analytics job or internship as fresher because I am learning now python, SQL, completed
can anyone tell what the application this guy using, I'm new here so I want to know
How to run 10 SQL scripts using single script or a command line ?
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.
❤ 🔥
how can i share my query with you
Is this is fresher interview question
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 ?
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, thanks!