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...
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.
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 UA-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!!
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 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.
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!
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!!!
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.
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🙏🏻🙌🏻 👏🏻
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!
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.
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.
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);
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.
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 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
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 :)
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.
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!
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
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)?
@@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 UA-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 UA-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 🙏🏻😀
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 :)
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. 👊🏽👍🏽
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.
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
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
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....
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.
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.
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
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.
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
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
@ 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?
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.
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 UA-cam.. working towards it.. May I know what u mean by data tools?
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.
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.
"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!"
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 .
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 ❤❤❤
Well Explained. Looking at these queries after a decade, I was searching for something which would explain in a simple language and w/o confusing! Kudos to you... 👏👏👏
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.
A Big thanks to you. you make hard thinks easy in a single video this is extremely appreciable sir. thanks a lot...your explaining each and everything without timewasting short and easy I'm not gonna forget subqueries in my whole life after watching these. this free video in my paid course i didn't learn like these again thanks a lot.........
time: 1:02:50 - 1:08:16 Dear Thoufiq Bhai, Salaame, Namastey. you know, i am a bit a new learner now; my style but.. the subquery in UPDATE, section is what i got thrilled. i can only wish to meet up personally to discuss what had happened to the script you quoted here in the time-frame what i penned above. we know, Thoufiq bhai.. that primarily the "EMPLOYEE" table has 24 odd records with it's constraints as we have designed, meaning what the way you designed!! so we did populate the "employee_history" table with Topic as: subquery in INSERT clause, fetching records from both "EMPLOYEE" AND "department" tables with that peculiar check: . how cool about that. thus, "employee_history" table too got 24 records, fine. now, here as you are narrating in this vid., to update again the "EMPLOYEE" table by: Giving a hefty 10% increment/hike to ALL those employees in "Bengaluru" location, who earned the maximum salary in their respective dept(s), ONLY. NB: consider employees in "employee_history" table. we have the DEPT_NAME='Finance' WHICH does not belong to the location='Bangalore' and count(*)=4, for this group. hence we left with 3 more groups in and those belongs to the location='Bangalore'. and our script needs to update only 3 records (or alike "dense_rank()" windows function featured..), not all the 20 employee's salary to be get hiked by 10%. can you imagine, as a novice learner like me, how much effort and time it took to handle such incidents, when finally sorting with a customized script as below: update EMPLOYEE set salary = SALARY * 1.10 where emp_id IN ( select emp_id from (select eh.emp_id as emp_id, eh.salary as salary, d.dept_name from employee_history eh join department d on eh.dept_name = d.dept_name where d.location = 'Bangalore' and eh.salary = (select MAX(eh2.salary) from employee_history eh2 where eh2.dept_name = eh.dept_name) ) EHMaxSalary ); will you please do a code review for this, if you got any leisuress. any way, thank you and to all the silent features, that helps run this platform so syncly, the UA-cam. Salaam.
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 🙏🏼
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.
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
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 )
It's impossible to have a better video than this one about subqueries, here on UA-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!!
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 :)
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.
The BEST video on subqueries on this platform. What a stuff this man have put in here. Legend!
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 best subquery video I’ve come across on UA-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 :)
The simplicity with which you broke down complex topics is a sheer gift
Keep it up
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
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!!!
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
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.
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!
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 🙏
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.
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);
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 🙏🏼
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 :)
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
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 😊🙏
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.
Best Teacher with best Teaching methods and explaining each n every topic.
Amazing content 😊👍
Thanks a ton Surbhi
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 👍
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
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.
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 🙏🏼
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 UA-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 UA-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 🙏🏻😀
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
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
hey man only person on youtube to teach sql so simplistically
Thanks buddy , glad it helps
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. 👊🏽👍🏽
I can download this video a thousand times just because of the quality of the presentation!. Good work bro.
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.
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
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
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
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 ☺️
Almighty has saved my time in exploring here and there for learning sql and landed your youtube channel directly.
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 🙏🏼
your teaching is much better than many celebrity trainers, great teaching, thank you for wonderful explanation
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
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.
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
I have attended many interviews confusion with this subquery and types and you just nail it...on the money. Thank you so much!!
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 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 ☺️🙏🏼
You are just the best, just know that i am learning alot from you. Much respect from NIGERIA
Thank you so much for your valuable info on subquery.
Your welcome Sambit 🙏🏼
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 😍
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
@ 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
It's impossible to have a better video than this one about subqueries u are the best sirr
Thank you :)
Glad this helped
Nice explanation...Helpful content...👍👍👍
Thank you Ambar 🙏🏼
I've been having such a hard time with subqueries on how to write them, understanding them, etc. You've helped a lot thanks.
thank you so much for clear explanation
Your welcome Haritha 🙏🏼
Glad you liked it
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
thanks a lot for the videos, you explain it very well!
Glad you like them bro!
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.
Thank you for the amazing content.
Your welcome bro. glad you liked it
This is a greatest ever video about subqueries, very easy to understand and well explanation. Thank you so much!
Sir please make the video on sql store procedure and trigger...
Sure bro will do it
This is the best SQL video I have ever seen. Thx
Amazing 👏
Thank you 😍
Your channel is the best bro better than Coursera courses you deserve more subscribe made concepts clear thank you so much 💥
Thank you Sree
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 UA-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 😊
This video is far far better than any videos in entire youtube
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.
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
Best video on practical Implementation of Sub Queries on UA-cam
Thank you Jatin
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
"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!"
Dataset please
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.
I m a sql freak, liked your stuff, easy n simple
Glad you like this :)
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.
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 .
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 ❤❤❤
The best Video on UA-cam for -> Correlated Subquery, Thanks
Glad you think so!
Thank you :)
Thank you a lot! Your explanation is much more clear than the explanation from my online course. God bless you!
At first i thought that the video is too long now i wanna live in it, it's very useful.
Same here. But I learnt a lot. Subqueries made easy in this video
Best SQL content so far. You have made sql easy and understandable
You are the king. To me it’s the most difficult part of learning SQL. Thank you!
Thank you Levon 🙏🏼
Glad you liked it
The best video on subquery till date. But the clip from 57:14 is 🔥👌. Wonderful explanation.
First of all thanks to explain in deeply in this concept.
You make hard things to teach in easy way.
Hats off to that.
Well Explained. Looking at these queries after a decade, I was searching for something which would explain in a simple language and w/o confusing! Kudos to you... 👏👏👏
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.
U r really a good mentor on SQL. Very good concept. I have learnt to many things from ur vedio
HI techTFQ ,You made SQL easier to understand ,I am following you for SQL ,And I understood very well .Thank you 😃
A Big thanks to you. you make hard thinks easy in a single video this is extremely appreciable sir. thanks a lot...your explaining each and everything without timewasting short and easy I'm not gonna forget subqueries in my whole life after watching these. this free video in my paid course i didn't learn like these again thanks a lot.........
Best teacher for learning SQL ...in UA-cam . Thank you guru ji 🙏🙏🙏
I must say, your teaching techniques are extraordinary. Thank you.
Glad you liked it Ayan
time: 1:02:50 - 1:08:16
Dear Thoufiq Bhai,
Salaame, Namastey.
you know, i am a bit a new learner now; my style but..
the subquery in UPDATE, section is what i got thrilled.
i can only wish to meet up personally to discuss what had happened to the script you quoted here
in the time-frame what i penned above.
we know, Thoufiq bhai.. that primarily the "EMPLOYEE" table has 24 odd records with it's constraints as we have designed, meaning what the way you designed!!
so we did populate the "employee_history" table with Topic as:
subquery in INSERT clause, fetching records from both "EMPLOYEE" AND "department" tables with that peculiar check: . how cool about that.
thus, "employee_history" table too got 24 records, fine.
now, here as you are narrating in this vid., to update again the "EMPLOYEE" table by:
Giving a hefty 10% increment/hike to ALL those employees in "Bengaluru" location, who earned the maximum salary in their respective dept(s), ONLY.
NB: consider employees in "employee_history" table.
we have the DEPT_NAME='Finance' WHICH does not belong to the location='Bangalore' and count(*)=4, for this group.
hence we left with 3 more groups in and those belongs to the location='Bangalore'.
and our script needs to update only 3 records (or alike "dense_rank()" windows function featured..), not all the 20 employee's salary to be get hiked by 10%.
can you imagine, as a novice learner like me, how much effort and time it took to handle such incidents, when finally sorting with a customized script as below:
update EMPLOYEE
set salary = SALARY * 1.10
where emp_id IN (
select emp_id
from (select eh.emp_id as emp_id, eh.salary as salary, d.dept_name
from employee_history eh
join department d
on eh.dept_name = d.dept_name
where d.location = 'Bangalore'
and eh.salary = (select MAX(eh2.salary)
from employee_history eh2
where eh2.dept_name = eh.dept_name)
) EHMaxSalary
);
will you please do a code review for this, if you got any leisuress.
any way, thank you and to all the silent features, that helps run this platform so syncly, the UA-cam.
Salaam.