SQL Interview Query for Data Analyst

[@dj_oteng]

Your tips on SQL has really improved my scripting skills in my line of work. Very practical examples. Thank you for the good work

[@deepakkumars8139]

The way you are analysing the problem 💯 💯🔥

[@luckykumar1665]

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.

[@hyderali1252]

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

[@nagaprasadreddy5029]

It can't be better than this. No words!

[@namanmehta8692]

Bhai hats off for the patience and pace you took to solve this problem. Really man🎉 impressive

[@rajkumarrajan8059]

You are my virtual master in learning SQL. You are AMAZING my dear friend!! Thanks a lot

[@techTFQ]

You're welcome . Glad it helped

[@VishalSingh-xf3bh]

The way you have explained the concept is solutable boss thanks for the video

[@vishalsonawane.8905]

Your video content always it's like new gift for all learners 🏆❣️😍

[@minhmuic]

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 :(

[@yuvasridora905]

Well said 👍

[@harichandrajaiswal8804]

I am glad that... This problem is shared with all the people.. U helped me out of this. Thank you so much 🙏 🙏

[@sungkeum1286]

Thank you for sharing knowledge as always, Thoufiq.

[@avi8016]

Detailed process and explanation Thanks a lot sir!

[@prathameshjoshi1408]

This was really an informative video. Thank you so much

[@rameshsanipayavlogs8339]

Super thoufiq

[@kalheer]

Awesome clarification - ive not used “cte” before.. makes life easier 👍🏻👍🏻

[@sakeenasammi1768]

Beautifully explained thanks toufik , keep it up😃💥

[@mohammedvahid5099]

well explained sir thnk u so mch its very usefull to me with realtime scenarios...

[@NomanKhan1886]

Your videos are the best !

[@aman_mashetty5185]

Awesome...! always love to watch your video thank for sharing the knowledge....

[@prabhusagarmohapatra8610]

Wow 😳 excellent

[@rameshsanipayavlogs8339]

Urs explanation really very very clear

[@lxrvii]

Well tackled Sir, thank you.

[@Kishor-ai]

Thanks for making this video @techTFQ ❤

[@prashansapunjabi]

Great explanation! Thank you

[@gau123rav1]

Very informative, Thank you

[@luckykumar1665]

At 26:34, we can assume ((math_correct * 1) - (math_wrong * 0.25) ) as math_Score.

[@raitup00]

Love your videos

[@nikhilavemuri955]

awesome, waiting to join your live SQL training sir. Please let me know when next batch will be started.

[@riteshchandrave6502]

Thanks and very useful

[@suprithjain719]

Good explanation Boss

[@sairaj8375]

very helpful sir

[@luckykumar1665]

Thank you for wonderful question.

[@sabaabrha7167]

Thank you and God bless you.

[@blank0227]

do know the application this guy using, sorry Im really new here

[@kevinboodhoo5833]

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;

[@shankhaneeldutta4617]

SUPERB !

[@ajaykumargaudo6685]

Superb👌👌

[@rohitsethi5696]

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

[@deepshah2137]

Amazing!!!

[@meenayegan6634]

Wonderful Video...

[@adityasasmal]

nice one

[@vineethmenon2372]

Fantastic......................

[@techTFQ]

Thank you! Cheers!

[@obedrajugantala3489]

Thanks. Very nice video. Is there a way to download/import the tables and data you have so that I can employ and practice?

[@moek7644]

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

[@SankarJankoti]

Great content , Do you have any union and union all complex queries video?

[@prajaktawagh2626]

Please make vedio on regular expression ..

[@rajeswarikapuluru3989]

Sir, please make the video on OUTPUT clause

[@shivsharma9153]

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.

[@luckykumar1665]

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.

[@shivsharma9153]

@@luckykumar1665 not possible practically to find lay off numbers for 400+ rows😅. So shall I drop those rows then?

[@sunilkumar-uo8so]

Hi dear Could you please guide me to import data in SQL server from file. Thankyou for this wonderful video.

[@ashwanikumarkaushik2531]

Not able to restore the csv files in postgres .

[@manikhandanj5681]

Can you tell me whether should include tableau or EDA in pandas jupyter projects in my resume

[@yogitasable4336]

Hello sir , can you please make video on data migration using paython . I can easily understand whatever you teach .

[@muditkaushal4645]

can you please upload this ques once again coz its not showing now after downloding

[@poojahr5670]

Not able to import csv file in to Pg admin. Always getting the error could not open file for reading, permission denied.

[@poojahr5670]

Could you please guide

[@riddhimachakraborty9538]

Such queries are asked to beginners as well? And that too for internship?

[@AtulSingh-be1jk]

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.

[@swathi3086]

@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 ?

[@shahrzadalizadeh]

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.

[@anilgandamala2444]

Hlo sir I need information well which type of skills needed to become a SQL developer

[@blank0227]

can anyone tell what the application this guy using, I'm new here so I want to know

[@shubhamagrawal7068]

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

[@Jitendrawagh20]

How get data analytics job or internship as fresher because I am learning now python, SQL, completed

[@swetasuman4498]

create statements r missing

[@j.vinodkumarkumar3683]

Hi all...how to get math_percent and science_percent in SQL server...I am getting zero for all records

[@anudeepreddy5559]

❤ 🔥

[@muazmalik1696]

how can i share my query with you

[@venkateshkoya1985]

How to run 10 SQL scripts using single script or a command line ?

[@adarshkashyap7402]

Is this is fresher interview question

[@NaveenKumar-ig6gn]

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

[@bhaktisagar4418]

Can you please share the question along with the given data ?

[@krishanukundu4565]

my solution- select l.*, stud.*except(roll_number) from `stud.student_list` l join (select r.roll_number, sum(case when subject= 'Math' and option_marked= correct_option and option_marked!= 'e' then 1 else 0 end) as maths_correct, sum(case when subject= 'Math' and option_marked!= correct_option and option_marked!= 'e' then 1 else 0 end) as maths_wrong, sum(case when subject= 'Math' and option_marked= 'e' then 1 else 0 end) as maths_yet_to_learn, sum(case when subject= 'Math' and option_marked= correct_option and option_marked!= 'e' then 1 else 0 end) as maths_score, round(cast(sum(case when subject= 'Math' and option_marked= correct_option and option_marked!= 'e' then 1 else 0 end) as decimal)* 100/sum(case when subject= 'Math' then 1 else 0 end), 2) as maths_percentage, sum(case when subject= 'Science' and option_marked= correct_option and option_marked!= 'e' 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 and option_marked!= 'e' then 1 else 0 end) as Science_score, round(cast(sum(case when subject= 'Science' and option_marked= correct_option and option_marked!= 'e' then 1 else 0 end) as decimal)* 100/sum(case when subject= 'Science' then 1 else 0 end), 2) as Science_percentage, from `stud.correct_answers` a join `stud.question_paper_code` c on a.question_paper_code= c.paper_code join `stud.student_response` r on a.question_paper_code= r.question_paper_code and a.question_number= r.question_number group by roll_number) stud on stud.roll_number= l.roll_number order by student_name

[@rajkumarrajan8059]

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

[@pishankumarshahu2305]

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

[@pulkitjain2361]

Great explanation, thanks!