Since I started my new job I've been running away from subqueries. Whenever the query reached a certain level of complexity I referred it to the data scientist, this week I faced a challenge in which I will not be able to escape from the subqueries. I watched several videos and none reached this level of detail, mainly explanation. It will help me a lot. Thank you!!! Eternally grateful.
I haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!! SQL learners need a teacher like you.
@@techTFQ u r such a true teacher.. who explains so clearly..n all concepts r actually easily understandable..learning so much from ur knowledge n way of teaching n explaining..thanks a lot...
Man you are the most humble teacher i ever saw, 1) You make hard things look easy 2) You know exactly where people will have doubts and the pain of those ( Not only you revive that with solutions but also give precautions ) 3) You explain things again and again without any hesitation or showing an attitude which i love the most! Everyone can see how much you want to make things understandable and easy for all. I'm really looking forward for a ' Whole Advance SQL statements using Windows function and their applications' Video Using Timestamps, extract, rank in those cases is quite tricky and I'm sure you are an Aid to those! KUDOS To You With All The Respect! Keep doing the work you do, no one else is at this level and Humbleness 🤜🏻
Hi Atharva , It’s really pleasing to read such feedback. Thank you very very much for your kind words.. truly appreciate it and I am so happy to know you find value in my contents 🙏🏼🙏🏼🙏🏼☺️
Can we all take a moment to appreciate this guy, providing such amazing content for free? Hats off to you Sir. These videos are extremely useful for students like me :)
It's impossible to have a better video than this one about subqueries, here on TH-cam. I challenge someone to present me something at this level, of didactics, examples and duration. THANK YOU TEACHER! WILL FOLLOW YOU IN ALL YOUR VIDEOS!
For a first-timer in coding, I can say that I have understood how coding works and its all because of your brilliant way of not just explaining the queries but also explaining how SQL processes the code, which is truly amazing. Thank you and God bless!!
I think what's most effective about your tutorials is you don't just execute simple sql statements to illustrate a point, you actually provide real problems and demonstrate your "approach" with solving them--they're almost like "word problems" from primary school. 🤭 This has helped me understand greatly how to breakdown (future) problems.
You are one of the best SQL tutor👍 I have seen. The best part is, you take one topic and cover everything in that (Full meals 😁). No need search any other content. Super helpful... Bro!
I have always used ORM tools to generate SQL queries, since I was afraid about complex queries. After watching your video three times, I feel quite confident to go deeper and write down complex native SQL commands. I wish you the best for such a thoughtful content!
40:20 I believe you have made a mistake solving this question. The total sales is not just adding up the price column, but we must multiply the price column and the quantity column to calculate how much each store made as total sales. In other words, (Quantity * Price = Total Sales). Here is my code: select store_name, sum(quantity*price) as total_sales from sales group by store_name having total_sales > avg(quantity*price); Here is the longer version: select * from (select store_name, sum(quantity*price) as total_sales from sales group by store_name) as a join (select avg(quantity*price) as avg_sales from sales) as b on a.total_sales > b.avg_sales Thanks
@@AbhishekChoudharyB If you started studying now aiming to get a job, change your plans. I have been looking for a job as a Data Analyst since two years and never landed a job yet. I live in the US.
@@أبويزيد-ض5ي That is sad to hear brother... What kind of certificates do you have on your resume? do you have a portfolio? Btw your arab name flipped my text LOL
@@02artiom I have a portfolio website that has some nice projects on it. I have a Bachelor’s Degree in Statistics I have IBM Professional Data Analyst Certificate. I have some certifications in SQL and Excel. If you are starting now, I advise you to run away and pick a different career because things are getting even worse. If you are still a student, pick a medical career because you will always find a job in healthcare.
@@AbhishekChoudharyBif you see the values of quantity and price carefully u would observe the price given is not the individual product price but price of the quantity of product sold
Hi. Your videos are great. Thanks for the effort you are making just for us to learn. I really appreciate that. While solving the UPDATE related question, I think the answer should be this: update employee e set salary=(select max(eh.salary) * 0.1 + e.salary from employee_history eh where e.dept_name=eh.dept_name) where e.dept_name in (select dept_name from department where location='Bangalore') and emp_id in (select emp_id from employee_history);
This is an awesome tutorial. Initially, I thought the examples were going to be redundant, but you cleared my doubts by covering the diverse uses of subqueries. Thanks for this video!
Never come across a better SQL teacher like you, every point sooo detailed! This is like a goldmine for SQL. Kudos to you for curating such awesome content!!!
Bhaiya, your videos are just awesome. I was not good in sql. But I have started clearing interviews. Explanation is very nice and clear. Keep it up. Now step by step I will try to do it. Windows function video was really helpful. My concept is clear now.
I had learnt sql from various sources...but no one can match up the with your tutorial. And I prepared for the job by watching your tutorial and successfully got the job.
Superb! Thank YOU Taufiq for sharing this video. I could not find a better explanation of correlated subquery anywhere else. Now, I am very clear about this topic. I appreciate all your help!
super explanation haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!! Brilliant tutor thank you SQL learners need a teacher like you.
Sir, could you please make a video on elucidating when to use Join over Sub queries or vice-versa? Indeed, this is one of the major drawback for the beginner like me to deal with it in the initial stage.
Subscribed! I have watched tons videos on subqueries, that one made almost 100 percent clear this concept. I said "almost" just because I am not smart enough to make to the full comprehension
A much needed one. Subqueries are the ones which i encounter the most in my work. And this video really helped me understand it even more clearly. Thank you so much Brother for yet another helpful video🙏🏻🙌🏻 👏🏻
I haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. such a great! really thank you so much i have cleard my all doubts
@@techTFQ I enjoyed it! I do have some thoughts tho but it will take me time to compile those and send. It was a crazy day. I think subqueries are the hardest thing left for me in SQL except that I need to get more practice with window functions and also the with clause. But for subqueries I always feel like there is so much more to them. This video is helpful but I’d like to see a more general one to explain the various use cases for the placement of subqueries in the various clauses. Your real world experience such as when you mention that this is something we do all the time or this is something that is really not recommended to do and then you explain why - those comments are very helpful bc I’ve never been in this field and you are a pro so those pro tips are so valuable. So the use cases for the different placements in select, from, and where and also the performance for those for me would be also useful to have a solid understanding of. You did explain use case and performance in some detail for correlated subqueries in this one which was very useful, but I personally would like to have a better understanding of when the subquery in the from vs select vs where clause is the best choice vs forgetting the subquery altogether and going with the with clause. So, in the real world how often do you use subqueries vs the with clause and best use cases for each. Maybe a video to encompass all of that. And I think that making it very clear that any subquery in the from clause must have an alias is something that beginners don’t necessarily always know so that could perhaps be emphasized. I love that you spent an hour explaining subqueries but I could see another video stepping back in more general terms being also very useful. I hope this was helpful. Thanks for all of your good work. I also like the new background. 😊
@techTFQ I also want to add that I’m honored that you asked for feedback. I’m not sure if that was what you were looking for. I hope you have a nice night. 🙏🏻
Hi Laura, I loved this feedback. Kind of gave me an idea for a future video which should clear your doubts about where to use subquery and when to use it. And not just subquery but in general how to apply any SQL concepts when solving problem using SQL. But first let me thank you for this amazing feedback because I am sure this took you a very long time to compile and write them down. I completely understand your point of view and its something that a few others have asked me as well. But the thing is that, the only way you can clearly understand "where to use a subquery and when to use it and what concept of SQL to use and not use when solving a problem", all these will get clear only with experience. You need to solve many many queries, only then you will get an understanding of what to use when. But yes, I can frame some tips and tricks which can give you a better idea of what subqueries to use and where to use them and when to use them based on what kind of requirement.. Just that I can explain these by hopefully solving different types of SQL queries. Let me try to plan a video revolving this and come up with some tricks and tips. And thank you for noticing the new background 😀 Bought a new camera and some lights and just starting to learn how to make better videos. Hopefully my video visuals and editing will get better with time. Have a great day Laura and Thanks again for your amazing suggestion 🙏
@@techTFQ Thank You for taking the time to write such a detailed reply, Thoufiq. I’m so glad the TH-cam algorithm finally got some sense and started steering me to your channel. I’ve learned so much from you and I really appreciate your encouragement and guidance. It’s true what they say: You are the best SQL teacher on TH-cam. I can’t believe how your subscribers have grown since I found you at under 7K. This is a testament to your knowledge and experience and not only that but your way of explaining and your patience and clarity too, along with humility, which is rare and valuable as well. I hope and trust that all of your aspirations will become reality! And thank you again for your hard work. I’m so glad the feedback was helpful. I do so appreciate your feedback and tips for me too. Have a great night and hugs to your family 🙏🏻😀
Splendid Video Thoufiq !! 👏👏Neat and clear explanation of all the concepts. Types of Queries you did framed to solve in this video is like a cherry on the cake. Many thanks for considering our request and working on the same. TechTFQ and Thoufiq will surely be my go-to channel/person for SQL hereafter. I wish you should be recognized and appreciated by many subscribers very soon. Please keep doing the great work 😊🙏
Thank you so much Kaviya for your amazing feedback :) I truly appreciate you taking time to leave this feedback. I am so glad you liked the content and than you so very much for your kind wishes :)
You are really good. I go through all your SQL videos whenever I need to go for interviews, and it always works for me. Here I am back again to watch your videos as I am in middle of a job search! Wish me luck. Thank you so much for these wonderful videos, they are the best!
Subqueries are so tricky to me but this is by far the most detailed and comprehensive explanation on subquery that I've seen! Thank you soooooo much for that. Quick question on the total sales example, shouldn't we use SUM(quantity * price) to get total sales instead of just SUM(price)?
i have gone through hundreds of videos on Joins but unable to get the knowledge. With just one video i became expert in joins :) this is because of you sir . Keep up the good work. I will be following your videos going forward. excellent explaination without any confusion.
We can say that this is the most requested video by all subscribers and I took a course from a website but the way you explain this content is much better than any paid course I love your videos and the way to explain all this. Much appreciated sir.
Man...what an explanation. I have attended paid courses for SQL but never understood the concepts so clearly. Thank you so much and i will be soon joing your 3 Aug batch.
best content quality on this plateform ....amazing sir and god bless you sir and thanku so much for sharing your knowledge so deeply and nicely...it really helped a lot of students
well detailed and great explanation of an important topic in SQL! I think there might be a mistake in the question with the number of units sold by each store. the average is (3+2+11+6)/4 and is far greater from 2 which you remarked :)
It is 2.2 because it is the avg of quantity from the entire sales table.. You avg of 5.5 is the avg when considering the group by query .. Difficult to explain in comment but if you read the query statement properly and try it out yourself, you will come to know
@@techTFQ True but it defies logic, if we have multiple entries of the same store, you cant take average without summing from individual stores, moreover, this cant be just ignored because it is for learning purpose, the query will become more involved, to a more attentive student current case seems confusing
I noticed this right away as well. I am very much an SQL newbie but the average of 2.2 is taking the full sales table into account with multiple store rows and not aggregating the total sales for each store. It is incorrect. You have to take the average using the group by clause.
@ 1:06:06 , in line number 164, I think it should set salary = (select salary + (max(salary*0.1)) Each Employee's salary should be incremented from his own salary and not from the maximum salary. The maximum salary is to be considered only for calculation of the increment. Am I right?
Saving this to my folder and I am sure I m going to watch this multiple times... Going to be very useful... Thanks Btw, could you have a video explaining how to analyze the performance of different queries that accomplish same result but performance is different due to the way it is handled
@@techTFQ certainly, like all the videos you make. It really shows how much efforts you put into it to make it very clear to everyone. I'm in no way connected to sqls at work, but got interested at after seeing your videos.
for the UPDATE sql at 1:07:00, you have shown: SET salary = (select MAX(salary) + MAX(salary) * 0.1). This should be 'SET salary = salary + (select MAX(salary) * 0.1' .....). Otherwise, each employee will get a MAX salary in their department + 10% which may not be correct and all the employees will get the same salary+10% in their department. Please correct me if I'm wrong. Great video, thanks.
Hi, thanks for the informative video. At 28:11, alternate solution is select * from employee emp join (select dept_id,avg(salary) as avg_sal from employee group by dept_id) avg_sal on emp.dept_id = avg_sal.dept_id and emp.salary > avg_sal.avg_sal
Your explanation is amazing, really love watching your videos as I get to learn so much and they’re not boring either. Thank you so much for sharing this. Keep up the amazing work. 👊🏽👍🏽
scalar subquery multiple row subquery correlated subquery 37:49 Nested Subquery 46:24 with clause 46:38 different clauses in SQL where subquery is used select from where having 50:50 subquery inside select clause avoid using subquery with SELECT clause 56:32 sql commands which allow subquery INSERT UPDATE 1:03:30 DELETE 1:08:29
where were you my angel? I have attended multiple trainings for SQL but nobody explains the concept in the way you do!! Quality content with practical examples. Hats off to you for helping so many IT professionals to understand SQL in depth.
Best SQL video i have been through...The Way making us to understand and the language used can help even non tech guys like me to understand easily...Thank you so much... keep doing....
Hi , what an amazing effort!! THNAKS A MILLION!! Totally worth it. Also, kindly share the content notes if you have created for these stuff as it will be easy to practise alongside
At 54:21, if we calculate the average unit sold by 4 departments = (11+2+6+3)/4 = 5.5, just confused by the result we are getting from the query at 54:55 select avg(quantity) from sales; here we got 2 as avg....... can we use this one if not then please explain? I am confused. select avg(avg_count.quantity) from (select store_name, sum(quantity) as quantity from sales group by store_name) as avg_count
You are a great teacher 🎉 wonderful method of teaching 😀 pls try to be Active on this platform because a teacher like u is need for everyone.. If u know sql data tools kindly post it when u r free.. Thanks for the great work♥️ Because free education is the world's best education 🎈
Thank you Nisha ☺️🙏🏼 I am glad you value my contents so much .. and yeah I want to post more video on TH-cam.. working towards it.. May I know what u mean by data tools?
"Fantastic explanation! Your teaching style made the concept of subqueries so much clearer. I really appreciate the time and effort you put into breaking down the material. Thanks for another insightful and helpful video!"
What’s wrong with you man.. who said I deleted playlist? I have renamed the playlist hence may be the old link with old playlist name does not work.. just go to the channel and see all playlist there.. At least think twice before posting such comments
@@techTFQ SORRY BROTHER , COULD YOU OFFER YOUR COURSE FOR FREE , BECAUSE I AM VERY ENTHUSIASTIC TO LEARN SQL, PYTHON .RIGHT NOW I AM A STUDENT , COULD YOU HELP ME BROTHER. RIGHT I AM STRUGGLING WITHOUT JOB.
super video on SQL subqueries . A complete take on subqueries with practical examples. Definitely the Best Video on whole youtube even better than paid classes .
You are really excellent at making complex things so easy. I really liked the way you take part-by-part queries and combine them. I subscribed to you, sir.
plz bro keep this playlist free in the future also.No other tutorial regarding sql comes near to your level.The way u have taught us is absolutely mesmerizing and crsytal clear also.Huge thanks .Keep shining bro ❤❤❤
I'm just in 1/3 of this video but I need to smash like button right now because your video perfectly teach in easy way many concepts I thought were much harder to learn. Keep doing content like this brother.
At 1:06:00 You set the salary = (select max(salary) + (max(salary) * 0.1) I read that each employee should get a 10% raise based on the employee with the highest salary. If I read this right, this code sets each salary to the highest employee's salary + 10% which is different that giving a 10% raise based on the highest salary which would be: salary = (select salary + (max(salary) * 0.1). Using the first line sets all employees to be making the same money as the highest employee + 10%. Please clarify.
When calculating "total sales" you forgot to take the quantity of sold items into account... 00:39:43 row #7 in your table: store_id: 3, ..., quantity: 4, price: 4400 Your query to calculate total sales for each store: SELECT store_name, sum(price) AS total_sales FROM sales GROUP BY store_name And you got that total sales for store_id 3 is 7500. While even that one row gives us 17600 = 4400 *4
8:20 - Scaler subquery ( only fetches 1 row & 1 column
11:55 - Multiple Row subquery
type 1 - multiple row, multiple column
18:30 - type 2 - multiple row, single column
22:18 - Co-related subqueries
37:29 - Nested subquery
46:33 - Different Clause where subquery is allowed. ( select, from, where, having )
56:30 - SQL commands that allow subquery ( insert, update, delete )
Since I started my new job I've been running away from subqueries. Whenever the query reached a certain level of complexity I referred it to the data scientist, this week I faced a challenge in which I will not be able to escape from the subqueries. I watched several videos and none reached this level of detail, mainly explanation. It will help me a lot. Thank you!!! Eternally grateful.
I haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!!
SQL learners need a teacher like you.
Thank you Rohit. glad you liked it :)
True
@@techTFQ u r such a true teacher.. who explains so clearly..n all concepts r actually easily understandable..learning so much from ur knowledge n way of teaching n explaining..thanks a lot...
Thanks a lot Rooja 🙏🏼
Man you are the most humble teacher i ever saw,
1) You make hard things look easy
2) You know exactly where people will have doubts and the pain of those ( Not only you revive that with solutions but also give precautions )
3) You explain things again and again without any hesitation or showing an attitude which i love the most! Everyone can see how much you want to make things understandable and easy for all.
I'm really looking forward for a ' Whole Advance SQL statements using Windows function and their applications' Video
Using Timestamps, extract, rank in those cases is quite tricky and I'm sure you are an Aid to those!
KUDOS To You With All The Respect!
Keep doing the work you do, no one else is at this level and Humbleness 🤜🏻
Hi Atharva ,
It’s really pleasing to read such feedback. Thank you very very much for your kind words.. truly appreciate it and I am so happy to know you find value in my contents 🙏🏼🙏🏼🙏🏼☺️
Can we all take a moment to appreciate this guy, providing such amazing content for free? Hats off to you Sir. These videos are extremely useful for students like me :)
YES ,REALLY AWESOME VIDEO.
Q
Who care
In he shared the file that he used in this video if yes pls share the link
Has done really a great job, like a perfect point of revision before the exam
It's impossible to have a better video than this one about subqueries, here on TH-cam. I challenge someone to present me something at this level, of didactics, examples and duration. THANK YOU TEACHER! WILL FOLLOW YOU IN ALL YOUR VIDEOS!
Thank you for the amazing feedback bro.
I am so glad you found this helpful :)
I 101% agreed Bruno. I love the way he explained
@@techTFQ You are rocking bro
Awesome
You said didactics!!!!! Awesome.
And I concur!!!
For a first-timer in coding, I can say that I have understood how coding works and its all because of your brilliant way of not just explaining the queries but also explaining how SQL processes the code, which is truly amazing. Thank you and God bless!!
The BEST video on subqueries on this platform. What a stuff this man have put in here. Legend!
I think what's most effective about your tutorials is you don't just execute simple sql statements to illustrate a point, you actually provide real problems and demonstrate your "approach" with solving them--they're almost like "word problems" from primary school. 🤭 This has helped me understand greatly how to breakdown (future) problems.
One of the finest tutor I have ever seen, Going with the pace where the every concept is covered within a short time.
You are one of the best SQL tutor👍 I have seen. The best part is, you take one topic and cover everything in that (Full meals 😁). No need search any other content. Super helpful... Bro!
Thank you Vedantha for this amazing feedback :)
Really motivates me to make more :)
The best subquery video I’ve come across on TH-cam. Simple and easy to comprehend.
Thank you Hafeez. glad you liked it :)
I have always used ORM tools to generate SQL queries, since I was afraid about complex queries. After watching your video three times, I feel quite confident to go deeper and write down complex native SQL commands. I wish you the best for such a thoughtful content!
Thank you Mahdi :)
Stopped Surfing in internet about SQL and simply following your videos now. Its such a clear content that you deliver for us free. Thank you Sir!
The simplicity with which you broke down complex topics is a sheer gift
Keep it up
40:20
I believe you have made a mistake solving this question.
The total sales is not just adding up the price column, but we must multiply the price column and the quantity column to calculate how much each store made as total sales. In other words, (Quantity * Price = Total Sales).
Here is my code:
select store_name, sum(quantity*price) as total_sales from sales
group by store_name
having total_sales > avg(quantity*price);
Here is the longer version:
select * from (select store_name, sum(quantity*price) as total_sales from sales
group by store_name) as a
join (select avg(quantity*price) as avg_sales from sales) as b
on a.total_sales > b.avg_sales
Thanks
I had the same doubt.
I was wondering why no one pointed that out.
@@AbhishekChoudharyB
If you started studying now aiming to get a job, change your plans. I have been looking for a job as a Data Analyst since two years and never landed a job yet. I live in the US.
@@أبويزيد-ض5ي That is sad to hear brother... What kind of certificates do you have on your resume? do you have a portfolio? Btw your arab name flipped my text LOL
@@02artiom
I have a portfolio website that has some nice projects on it.
I have a Bachelor’s Degree in Statistics
I have IBM Professional Data Analyst Certificate.
I have some certifications in SQL and Excel.
If you are starting now, I advise you to run away and pick a different career because things are getting even worse.
If you are still a student, pick a medical career because you will always find a job in healthcare.
@@AbhishekChoudharyBif you see the values of quantity and price carefully u would observe the price given is not the individual product price but price of the quantity of product sold
hey man only person on youtube to teach sql so simplistically
Thanks buddy , glad it helps
Hi. Your videos are great. Thanks for the effort you are making just for us to learn. I really appreciate that. While solving the UPDATE related question, I think the answer should be this:
update employee e
set salary=(select max(eh.salary) * 0.1 + e.salary
from employee_history eh
where e.dept_name=eh.dept_name)
where e.dept_name in (select dept_name from department where location='Bangalore')
and emp_id in (select emp_id from employee_history);
This is an awesome tutorial. Initially, I thought the examples were going to be redundant, but you cleared my doubts by covering the diverse uses of subqueries. Thanks for this video!
Never come across a better SQL teacher like you, every point sooo detailed! This is like a goldmine for SQL. Kudos to you for curating such awesome content!!!
This video was amazing, i really thing the free code camp audience would go nuts for this video, if you ever thing about donating this series
Bhaiya, your videos are just awesome. I was not good in sql. But I have started clearing interviews. Explanation is very nice and clear. Keep it up. Now step by step I will try to do it. Windows function video was really helpful. My concept is clear now.
Thank you Jayeeta for your kind words :)
Really happy to know my videos have helped 🙏
I had learnt sql from various sources...but no one can match up the with your tutorial. And I prepared for the job by watching your tutorial and successfully got the job.
Superb! Thank YOU Taufiq for sharing this video. I could not find a better explanation of correlated subquery anywhere else. Now, I am very clear about this topic. I appreciate all your help!
I am glad to hear that Rajiv :)
super explanation haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!! Brilliant tutor thank you
SQL learners need a teacher like you.
Sir, could you please make a video on elucidating when to use Join over Sub queries or vice-versa? Indeed, this is one of the major drawback for the beginner like me to deal with it in the initial stage.
Subscribed! I have watched tons videos on subqueries, that one made almost 100 percent clear this concept. I said "almost" just because I am not smart enough to make to the full comprehension
A much needed one. Subqueries are the ones which i encounter the most in my work. And this video really helped me understand it even more clearly. Thank you so much Brother for yet another helpful video🙏🏻🙌🏻 👏🏻
I am glad you liked it bro 😍
Thank you ☺️
Please what kind of work? I hardly find jobs where you have to make constant queries to a database.
I haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. such a great! really thank you so much i have cleard my all doubts
Yes!! So glad you covered this in such a detailed video. Can’t wait to watch! Thank you so much!
Your welcome Laura 🙏🏼
Do give me your honest feedback after you watch the video..
@@techTFQ I enjoyed it! I do have some thoughts tho but it will take me time to compile those and send. It was a crazy day. I think subqueries are the hardest thing left for me in SQL except that I need to get more practice with window functions and also the with clause. But for subqueries I always feel like there is so much more to them. This video is helpful but I’d like to see a more general one to explain the various use cases for the placement of subqueries in the various clauses. Your real world experience such as when you mention that this is something we do all the time or this is something that is really not recommended to do and then you explain why - those comments are very helpful bc I’ve never been in this field and you are a pro so those pro tips are so valuable. So the use cases for the different placements in select, from, and where and also the performance for those for me would be also useful to have a solid understanding of. You did explain use case and performance in some detail for correlated subqueries in this one which was very useful, but I personally would like to have a better understanding of when the subquery in the from vs select vs where clause is the best choice vs forgetting the subquery altogether and going with the with clause. So, in the real world how often do you use subqueries vs the with clause and best use cases for each. Maybe a video to encompass all of that. And I think that making it very clear that any subquery in the from clause must have an alias is something that beginners don’t necessarily always know so that could perhaps be emphasized. I love that you spent an hour explaining subqueries but I could see another video stepping back in more general terms being also very useful. I hope this was helpful. Thanks for all of your good work. I also like the new background. 😊
@techTFQ I also want to add that I’m honored that you asked for feedback. I’m not sure if that was what you were looking for. I hope you have a nice night. 🙏🏻
Hi Laura, I loved this feedback. Kind of gave me an idea for a future video which should clear your doubts about where to use subquery and when to use it. And not just subquery but in general how to apply any SQL concepts when solving problem using SQL.
But first let me thank you for this amazing feedback because I am sure this took you a very long time to compile and write them down. I completely understand your point of view and its something that a few others have asked me as well.
But the thing is that, the only way you can clearly understand "where to use a subquery and when to use it and what concept of SQL to use and not use when solving a problem", all these will get clear only with experience. You need to solve many many queries, only then you will get an understanding of what to use when.
But yes, I can frame some tips and tricks which can give you a better idea of what subqueries to use and where to use them and when to use them based on what kind of requirement.. Just that I can explain these by hopefully solving different types of SQL queries.
Let me try to plan a video revolving this and come up with some tricks and tips.
And thank you for noticing the new background 😀
Bought a new camera and some lights and just starting to learn how to make better videos. Hopefully my video visuals and editing will get better with time.
Have a great day Laura and Thanks again for your amazing suggestion 🙏
@@techTFQ Thank You for taking the time to write such a detailed reply, Thoufiq. I’m so glad the TH-cam algorithm finally got some sense and started steering me to your channel. I’ve learned so much from you and I really appreciate your encouragement and guidance. It’s true what they say: You are the best SQL teacher on TH-cam. I can’t believe how your subscribers have grown since I found you at under 7K. This is a testament to your knowledge and experience and not only that but your way of explaining and your patience and clarity too, along with humility, which is rare and valuable as well. I hope and trust that all of your aspirations will become reality! And thank you again for your hard work. I’m so glad the feedback was helpful. I do so appreciate your feedback and tips for me too. Have a great night and hugs to your family 🙏🏻😀
Wow, Thoufiq!! Look at you go, almost 200K! Well deserved, my friend, and so happy for your success!
Thank you Laura ☺️☺️☺️
Always special to read your well wishes 🙏🏼
Best Teacher with best Teaching methods and explaining each n every topic.
Amazing content 😊👍
Thanks a ton Surbhi
Almighty has saved my time in exploring here and there for learning sql and landed your youtube channel directly.
Splendid Video Thoufiq !! 👏👏Neat and clear explanation of all the concepts.
Types of Queries you did framed to solve in this video is like a cherry on the cake.
Many thanks for considering our request and working on the same.
TechTFQ and Thoufiq will surely be my go-to channel/person for SQL hereafter.
I wish you should be recognized and appreciated by many subscribers very soon. Please keep doing the great work 😊🙏
Thank you so much Kaviya for your amazing feedback :)
I truly appreciate you taking time to leave this feedback.
I am so glad you liked the content and than you so very much for your kind wishes :)
@@techTFQ Pleasure is all mine 😊🙏
You are really good. I go through all your SQL videos whenever I need to go for interviews, and it always works for me. Here I am back again to watch your videos as I am in middle of a job search! Wish me luck. Thank you so much for these wonderful videos, they are the best!
Thank you Sneha and wishing you all the best 👍
Subqueries are so tricky to me but this is by far the most detailed and comprehensive explanation on subquery that I've seen! Thank you soooooo much for that. Quick question on the total sales example, shouldn't we use SUM(quantity * price) to get total sales instead of just SUM(price)?
I'm guessing the price column represents total price and not price per unit.
i have gone through hundreds of videos on Joins but unable to get the knowledge. With just one video i became expert in joins :) this is because of you sir . Keep up the good work. I will be following your videos going forward. excellent explaination without any confusion.
We can say that this is the most requested video by all subscribers and I took a course from a website but the way you explain this content is much better than any paid course I love your videos and the way to explain all this. Much appreciated sir.
Most awaited video I was waiting for...thank you sir.. following you on LinkedIn ❤️
Thank you Pankaj 🙏🏼
I hope you like this one
@@techTFQ yes sir it helped me a lot ❣️
I am glad to hear that ☺️
Man...what an explanation. I have attended paid courses for SQL but never understood the concepts so clearly. Thank you so much and i will be soon joing your 3 Aug batch.
Glad you liked it 🙏🏼
best content quality on this plateform ....amazing sir and god bless you sir and thanku so much for sharing your knowledge so deeply and nicely...it really helped a lot of students
Thank you Shaffu 🙏🏼
Glad you liked it
I can download this video a thousand times just because of the quality of the presentation!. Good work bro.
well detailed and great explanation of an important topic in SQL!
I think there might be a mistake in the question with the number of units sold by each store.
the average is (3+2+11+6)/4 and is far greater from 2 which you remarked :)
well noted! even i made that observation
Yes, I dont know why he made this mistake because in the avg(prive) exercise he said that you have to group them and then calculate the avg().
@@hnull99 in that example he said across all stores
I also observe that as well 👍
I think that's a mistake
We should be left with 2 stores
Yes ! As the avg price for all stores coming as 2200.0
You are just the best, just know that i am learning alot from you. Much respect from NIGERIA
Timestamp 55:30
The average will be 5.5 (11+2+6+3/4) .
But the query resulted as 2.2.
Any thoughts?
Even I have the same thoughts
It is 2.2 because it is the avg of quantity from the entire sales table..
You avg of 5.5 is the avg when considering the group by query ..
Difficult to explain in comment but if you read the query statement properly and try it out yourself, you will come to know
Thanks for the explanation 🙌
@@techTFQ True but it defies logic, if we have multiple entries of the same store, you cant take average without summing from individual stores, moreover, this cant be just ignored because it is for learning purpose, the query will become more involved, to a more attentive student current case seems confusing
I noticed this right away as well. I am very much an SQL newbie but the average of 2.2 is taking the full sales table into account with multiple store rows and not aggregating the total sales for each store. It is incorrect. You have to take the average using the group by clause.
This is the best SQL video I have ever seen. Thx
@ 1:06:06 , in line number 164, I think it should set salary = (select salary + (max(salary*0.1))
Each Employee's salary should be incremented from his own salary and not from the maximum salary. The maximum salary is to be considered only for calculation of the increment.
Am I right?
Nope.. the question was to provide increment based on the max salary not considering each employee salary
@@techTFQ , so based on your logic, Sir, everyone from a department will be having equal salary, right?
Exactly
your teaching is much better than many celebrity trainers, great teaching, thank you for wonderful explanation
Saving this to my folder and I am sure I m going to watch this multiple times... Going to be very useful... Thanks
Btw, could you have a video explaining how to analyze the performance of different queries that accomplish same result but performance is different due to the way it is handled
Thank you Vela :) I hope you like it.
As for performance of different queries, ill make separate video about it.
@@techTFQ certainly, like all the videos you make. It really shows how much efforts you put into it to make it very clear to everyone. I'm in no way connected to sqls at work, but got interested at after seeing your videos.
Amazing vela , I am so glad to read this ..
Thank you 🙏🏼
Thank you so much for your valuable info on subquery.
Your welcome Sambit 🙏🏼
for the UPDATE sql at 1:07:00, you have shown: SET salary = (select MAX(salary) + MAX(salary) * 0.1). This should be 'SET salary = salary + (select MAX(salary) * 0.1' .....). Otherwise, each employee will get a MAX salary in their department + 10% which may not be correct and all the employees will get the same salary+10% in their department. Please correct me if I'm wrong. Great video, thanks.
This is an amazing video..thank you for helping students like me!!!!!!!!!❤❤❤❤❤❤❤❤
I am so glad to hear that Shiv ..
Thanks for your feedback and I am glad this helped ☺️🙏🏼
Hi, thanks for the informative video.
At 28:11, alternate solution is
select * from employee emp
join (select dept_id,avg(salary)
as avg_sal from employee
group by dept_id) avg_sal
on emp.dept_id = avg_sal.dept_id and emp.salary > avg_sal.avg_sal
Your explanation is amazing, really love watching your videos as I get to learn so much and they’re not boring either. Thank you so much for sharing this. Keep up the amazing work. 👊🏽👍🏽
scalar subquery
multiple row subquery
correlated subquery
37:49 Nested Subquery
46:24 with clause
46:38 different clauses in SQL where subquery is used
select
from
where
having
50:50 subquery inside select clause
avoid using subquery with SELECT clause
56:32 sql commands which allow subquery
INSERT
UPDATE 1:03:30
DELETE 1:08:29
Thalaiva u r great... learned more thanks 🤞🙌
haha Thank you buddy 😍
where were you my angel? I have attended multiple trainings for SQL but nobody explains the concept in the way you do!! Quality content with practical examples. Hats off to you for helping so many IT professionals to understand SQL in depth.
Wanna ask what system management did he use in this video and what do use ?
@@rainbow100_ Pg admin on postgres
Sir, please provide us with the same table are you using during the written query.
Thanks for uploading this amazing video.
Yeah I’ll share all these scripts in my discord server .. so you can join my discord using the link in video description
Ok Thanks again 👏
@@techTFQ Sir,when you share the script on Discord server. as I just checked I didn't found out script there
It’s there in announcement channel..
U can ask other in discord if u can’t find it
Best SQL video i have been through...The Way making us to understand and the language used can help even non tech guys like me to understand easily...Thank you so much... keep doing....
Glad you liked it Rajesh
Nice explanation...Helpful content...👍👍👍
Thank you Ambar 🙏🏼
I have attended many interviews confusion with this subquery and types and you just nail it...on the money. Thank you so much!!
Hi , what an amazing effort!! THNAKS A MILLION!! Totally worth it. Also, kindly share the content notes if you have created for these stuff as it will be easy to practise alongside
Thank you :)
I have shared the dataset and all the scripts in my discord. link to join my discord is in video description
This video is far far better than any videos in entire youtube
thank you so much for clear explanation
Your welcome Haritha 🙏🏼
Glad you liked it
thanks a lot for the videos, you explain it very well!
Glad you like them bro!
Take a bow to these guy for him amazing work. Thank You Toufiq for all your videos. Please continue your work. will always support you
Sir please make the video on sql store procedure and trigger...
Sure bro will do it
IT IS ONE OF THE BEST VIDEOS ABOUT SUBQUERY. THE EXPLANATION WAS CRISP AND CLEAR. THANKS FOR ALL YOUR EFFORTS
Thank you Damodar.. glad you liked it
Thank you for the amazing content.
Your welcome bro. glad you liked it
you are a GEM. I never find a place to learn all these variations of sub query. Much useful video. Thank you so much for this wonderful lecture.
Amazing 👏
Thank you 😍
At 54:21, if we calculate the average unit sold by 4 departments = (11+2+6+3)/4 = 5.5,
just confused by the result we are getting from the query at 54:55
select avg(quantity) from sales; here we got 2 as avg.......
can we use this one if not then please explain? I am confused.
select avg(avg_count.quantity)
from (select store_name, sum(quantity) as quantity from sales group by store_name) as avg_count
Please can you share the dataset
I have shared the dataset in my discord channel.
you can join my discord using the link present in video description
The best video on subquery till date. But the clip from 57:14 is 🔥👌. Wonderful explanation.
You are a great teacher 🎉 wonderful method of teaching 😀 pls try to be Active on this platform because a teacher like u is need for everyone..
If u know sql data tools kindly post it when u r free..
Thanks for the great work♥️
Because free education is the world's best education 🎈
Thank you Nisha ☺️🙏🏼
I am glad you value my contents so much .. and yeah I want to post more video on TH-cam.. working towards it..
May I know what u mean by data tools?
@@techTFQ Data tools like SQL SERVER INTEGRATION SERVICES (SSIS), ANALYSIS SERVICES(SSAS),REPORTING SERVICES (SSRS).
Thanks for your reply 🎈
Oh ok got it..
Noted and let me consider the suggestion
@@techTFQ Thanks 😊
"Fantastic explanation! Your teaching style made the concept of subqueries so much clearer. I really appreciate the time and effort you put into breaking down the material. Thanks for another insightful and helpful video!"
CAN I KNOW THE REASON WHY U HAVE DELETED PLAYLIST ?
What’s wrong with you man.. who said I deleted playlist?
I have renamed the playlist hence may be the old link with old playlist name does not work.. just go to the channel and see all playlist there..
At least think twice before posting such comments
@@techTFQ SORRY BROTHER , COULD YOU OFFER YOUR COURSE FOR FREE , BECAUSE I AM VERY ENTHUSIASTIC TO LEARN SQL, PYTHON .RIGHT NOW I AM A STUDENT , COULD YOU HELP ME BROTHER. RIGHT I AM STRUGGLING WITHOUT JOB.
Thanks alot for ur contribution@@techTFQ
super video on SQL subqueries . A complete take on subqueries with practical examples. Definitely the Best Video on whole youtube even better than paid classes .
Dataset please
HI techTFQ ,You made SQL easier to understand ,I am following you for SQL ,And I understood very well .Thank you 😃
It's impossible to have a better video than this one about subqueries u are the best sirr
Thank you :)
Glad this helped
You are really excellent at making complex things so easy. I really liked the way you take part-by-part queries and combine them. I subscribed to you, sir.
Best video on practical Implementation of Sub Queries on TH-cam
Thank you Jatin
THE BEST SQL TUTORIAL AVAILABLE ON YT 💯💯💯
Fantastic! This video even helps professionals SQL writer like me who needs a quick refresher!
You are the king. To me it’s the most difficult part of learning SQL. Thank you!
Thank you Levon 🙏🏼
Glad you liked it
best explanation i have ever seen , keep it up , it was very helpful to know that there are endless possibilities to solve a complex problem.
plz bro keep this playlist free in the future also.No other tutorial regarding sql comes near to your level.The way u have taught us is absolutely mesmerizing and crsytal clear also.Huge thanks .Keep shining bro ❤❤❤
First of all thanks to explain in deeply in this concept.
You make hard things to teach in easy way.
Hats off to that.
U r really a good mentor on SQL. Very good concept. I have learnt to many things from ur vedio
I'm just in 1/3 of this video but I need to smash like button right now because your video perfectly teach in easy way many concepts I thought were much harder to learn. Keep doing content like this brother.
I've been having such a hard time with subqueries on how to write them, understanding them, etc. You've helped a lot thanks.
Wonderfully explained. God bless you, Thoufiq!
At 1:06:00 You set the salary = (select max(salary) + (max(salary) * 0.1)
I read that each employee should get a 10% raise based on the employee with the highest salary.
If I read this right, this code sets each salary to the highest employee's salary + 10% which is different that giving a 10% raise based on the highest salary which would be: salary = (select salary + (max(salary) * 0.1). Using the first line sets all employees to be making the same money as the highest employee + 10%.
Please clarify.
When calculating "total sales" you forgot to take the quantity of sold items into account...
00:39:43 row #7 in your table: store_id: 3, ..., quantity: 4, price: 4400
Your query to calculate total sales for each store:
SELECT store_name, sum(price) AS total_sales
FROM sales
GROUP BY store_name
And you got that total sales for store_id 3 is 7500. While even that one row gives us 17600 = 4400 *4
U are the best tutor I have never seen in my life
Perfectly explained every usage of subquery. Great work..Thoufiq..!
Thank you ☺️ 🙏🏼
Thanks BILLIANT
for the sql CURRY !!!!!!!!!!!!!!!!!
You deserve Millions of subscribers. You will do it soon we believe on you sir.
The best Video on TH-cam for -> Correlated Subquery, Thanks
Glad you think so!
Thank you :)