Your tips on SQL has really improved my scripting skills in my line of work. Very practical examples. Thank you for the good work
@kalheer
Жыл бұрын
Awesome clarification - ive not used “cte” before.. makes life easier 👍🏻👍🏻
@pulkitjain2361
Жыл бұрын
Great explanation, thanks!
@NomanKhan1886
Жыл бұрын
Your videos are the best !
@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
11 ай бұрын
Well said 👍
@SankarJankoti
Жыл бұрын
Great content , Do you have any union and union all complex queries video?
@muditkaushal4645
4 ай бұрын
can you please upload this ques once again coz its not showing now after downloding
@manikhandanj5681
Жыл бұрын
Can you tell me whether should include tableau or EDA in pandas jupyter projects in my resume
@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....😜
@vishalsonawane.8905
Жыл бұрын
Your video content always it's like new gift for all learners 🏆❣️😍
@deepakkumars8139
Жыл бұрын
The way you are analysing the problem 💯 💯🔥
@blank0227
Жыл бұрын
can anyone tell what the application this guy using, I'm new here so I want to know
@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
@rajkumarrajan8059
11 ай бұрын
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
@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
@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
@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
@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
@namanmehta8692
Жыл бұрын
Bhai hats off for the patience and pace you took to solve this problem. Really man🎉 impressive
@rameshsanipayavlogs8339
Жыл бұрын
Super thoufiq
@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;
@nikhilavemuri955
Жыл бұрын
awesome, waiting to join your live SQL training sir. Please let me know when next batch will be started.
@ashwanikumarkaushik2531
Жыл бұрын
Not able to restore the csv files in postgres .
@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?
@adarshkashyap7402
3 ай бұрын
Is this is fresher interview question
@sungkeum1286
Жыл бұрын
Thank you for sharing knowledge as always, Thoufiq.
@sunilkumar-uo8so
Жыл бұрын
Hi dear Could you please guide me to import data in SQL server from file. Thankyou for this wonderful video.
@j.vinodkumarkumar3683
Жыл бұрын
Hi all...how to get math_percent and science_percent in SQL server...I am getting zero for all records
@avi8016
Жыл бұрын
Detailed process and explanation Thanks a lot sir!
@anilgandamala2444
Жыл бұрын
Hlo sir I need information well which type of skills needed to become a SQL developer
@jitendrawagh07
Жыл бұрын
How get data analytics job or internship as fresher because I am learning now python, SQL, completed
@sairaj8375
8 ай бұрын
very helpful sir
@lxrvii
Жыл бұрын
Well tackled Sir, thank you.
@mohammedvahid5099
Жыл бұрын
well explained sir thnk u so mch its very usefull to me with realtime scenarios...
@luckykumar1665
Жыл бұрын
At 26:34, we can assume ((math_correct * 1) - (math_wrong * 0.25) ) as math_Score.
@riddhimachakraborty9538
Жыл бұрын
Such queries are asked to beginners as well? And that too for internship?
@harichandrajaiswal8804
Жыл бұрын
I am glad that... This problem is shared with all the people.. U helped me out of this. Thank you so much 🙏 🙏
@VishalSingh-xf3bh
Жыл бұрын
The way you have explained the concept is solutable boss thanks for the video
@aman_mashetty5185
Жыл бұрын
Awesome...! always love to watch your video thank for sharing the knowledge....
@anudeepreddy5559
8 ай бұрын
❤ 🔥
@yogitasable4336
Жыл бұрын
Hello sir , can you please make video on data migration using paython . I can easily understand whatever you teach .
@prathameshjoshi1408
Жыл бұрын
This was really an informative video. Thank you so much
@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?
@venkateshkoya1985
Жыл бұрын
How to run 10 SQL scripts using single script or a command line ?
@bluerussian8344
11 ай бұрын
Hello, I am learning SQL through your KZitem 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 KZitem channel as the source. Thank you."
@techTFQ
9 ай бұрын
Yes you can do it 👍
@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 ?
@adityasasmal
Жыл бұрын
nice one
@rajkumarrajan8059
11 ай бұрын
You are my virtual master in learning SQL. You are AMAZING my dear friend!! Thanks a lot
@techTFQ
9 ай бұрын
You're welcome . Glad it helped
@rajeswarikapuluru3989
Жыл бұрын
Sir, please make the video on OUTPUT clause
@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 ?
@vineethmenon2372
10 ай бұрын
Fantastic......................
@techTFQ
9 ай бұрын
Thank you! Cheers!
@sakeenasammi1768
Жыл бұрын
Beautifully explained thanks toufik , keep it up😃💥
@luckykumar1665
Жыл бұрын
Thank you for wonderful question.
@prajaktawagh2626
Жыл бұрын
Please make vedio on regular expression ..
@rameshsanipayavlogs8339
Жыл бұрын
Urs explanation really very very clear
@gau123rav1
Жыл бұрын
Very informative, Thank you
@nagaprasadreddy5029
Жыл бұрын
It can't be better than this. No words!
@muazmalik1696
Жыл бұрын
how can i share my query with you
@riteshchandrave6502
Жыл бұрын
Thanks and very useful
@prashansapunjabi
Жыл бұрын
Great explanation! Thank you
@swetasuman4498
Жыл бұрын
create statements r missing
@suprithjain719
Жыл бұрын
Good explanation Boss
@meenayegan6634
Жыл бұрын
Wonderful Video...
@prabhusagarmohapatra8610
Жыл бұрын
Wow 😳 excellent
@raitup00
Жыл бұрын
Love your videos
@shankhaneeldutta4617
Жыл бұрын
SUPERB !
@sabaabrha7167
Жыл бұрын
Thank you and God bless you.
@blank0227
Жыл бұрын
do know the application this guy using, sorry Im really new here
@ajaykumargaudo6685
Жыл бұрын
Superb👌👌
@deepshah2137
Жыл бұрын
Amazing!!!
@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.
Пікірлер: 82