Subqueries in MySQL | Intermediate MySQL

[@BruceBeck-f1n]

A LOT going on in this lesson. Alex did a nice job of walking me through subqueries in MySQL. As the previous commenter suggests, I need MORE practice to really get these concepts embedded into the "gray matter" inside my head. At the very least, I intend to re-watch this video a couple of times without trying to keep up typing code, etc. I am getting a great deal of value from all of Alex's work. Thanks! 😎

[@marilowe.lifestyle]

same, it will take a few note taking as well with reasons on why we use each function. by this video i knew I shouldnt just follow along with the coding but understanding when to use the code and how to use it.

[@ATCSABChaitra]

U are the best mentor and guide

[@SophieAbrams-e3b]

Definitely the first video I started having some difficulty fully understanding. Any other resources you suggest looking at?

[@dname1802]

Just a youtube search for a subqueries in SQL will give many options. Check out one or two and you'll get the concept. And chatGPT is a great source for asking specifics.

[@brazehvic8896]

Great explanations and options. Would have been better if you included the data file link to self-practice. Remember the best way to learn is learning by doing!

[@bjrnolavskaurum3854]

True. Having the dataset to work on would be way better than watching someone else perform tasks. Hopefully Alex reads your comment.

[@cjbrown3396]

You have to follow his course along the way. Alex used the file in the beginning as the teaching series.

[@Thisisrealfia]

Just when I needed to understand more about 'subqueries', this video came up. Thanks, Alex!

[@kushalsaha6299]

Timestamp- 3:02 What if I want to see the salary also with these other informations? I can't return more than 1 column in operand. So how to find salary?

[@WilnerSumagingsing]

Subquery can be denote as a virtual table. It can be use also with JOIN clause. Thanks

[@sadooya1017]

can someone explain me why exactly do we need to add the multiple Tabs before adding the another query 1:50

[@Toyahan43]

The tabs are there just for the readability. It makes the code easier to read and understand. You could write the code in one line but it would be hard to find mistakes and wouldn't look good.

[@sadooya1017]

Thank you, i understand it better now ​@Toyahan43

[@colleenoleary2966]

At 2: 35, when you get the results of the subquery...I'm not. I'm getting the results of the full query b/c I've been hitting the bolt w/ the I on it, instead of the bolt by itself...which is what you're doing in the video, but not mentioning which bolt you're hitting and why. Please specify which one you're hitting to get the results you're getting. Also, I don't understand WHY you would do what you did from 8:00 on. What's the use case for it?

[@sepehrhaghighatpasand2933]

If you hit the bolt with I, it’s gonna run the code where your cursor is. If you want to run all the codes or want to select some lines and just get the output of them, you should click on the bolt by itself. If you select some lines and then click the bolt with I, it runs the whole code not just your selection part. In 2:35 Alex select those lines so he clicked on the bolt by itself to only get the result of those lines.

[@delhiwala8511]

Hello Sir Ji Love You Hogaya From India 🇮🇳

[@VkTolani]

we can use 3:50 self join here as well?

[@atzirimendozagtz9787]

I don't know if I missed it but does where you insert the subquery make a difference? If so, what are they so that I can better understand when I run into the need to use a subquery.

[@nickl3135]

Loving this series ! Thanks so much

[@KshitijPemmaraju]

Such lucid explanation gives me confidence to become successful data analyst, May our Good Lord Sheperd bless you with good health, wealth and eternal happiness! ✝

[@chubz155]

I am currently learning sql and I am watching your videos especially the bootcamp ones. I already finished the videos for sql, is there any platform/s you can recommend to practice what I learned from the videos? I am kind of lost on how to practice these learnings. Thank you!

[@rukayatogundipe5779]

You can practice on analyst builder or hackerrank

[@chubz155]

Thank you very much @@rukayatogundipe5779

[@HopeOverDebt]

Hacker rank and w3schools are perfect to practice what we learn from bootcamp☺

[@irdcs]

Honestly it was too confusing to me (I'm starting from scratch) so I'm gonna have to learn this somewhere before I continue with the course. So frustrating 😅.

[@raphaelmarkd]

Thank you Alex!

[@Evgeny-p8s]

Excellent explanation, thanks

[@RubaShu]

Thanks alex for the easy explanation.. just a question, when you wanted to compare the salaries to avg salary, we also can use Partition by instead of s subquery right?

[@MoustafaElhagri-qx5db]

I thought of that in the first glance as well, but when you think about it you will find that "Partition by" will get you the average salary based on a criteria for example "Gender" but not the average of all the records. If you get another look at, let me know.

[@RubaShu]

@@MoustafaElhagri-qx5db Yes 100%! after a second look turned out you're right.. but a window function without any partition would do the same as the subquery? ( over() ) i just tried it and it worked

[@MoustafaElhagri-qx5db]

@@RubaShu I tried your way and it actually works. That was nice, thanks!

[@chooselife2388]

I'm having trouble understanding this one. "WHERE dept_id = 1" Why does it return 1,3,4,5,6,12 ? TIA

[@georgia_edits23]

1,3,4,5,6,12 are all in the column called employee_id and their dept_id is = 1

[@Glaivee]

Haha it makes me confuse as well because dept _id coloumn didn't show up, but then i realize that was employee id not dept id

[@sillygoose3308]

ALEX!!! Awesome guide!! This video is very easy to understand for beginners which i appreciate. Just a quick question on the aggregate function AVG in the subquiery, Why doesn't it require a GROUP BY statement?

[@Nikopup]

The order of operations on the back end causes a syntax error I believe

[@Toyahan43]

What is the purpose of the later half of the subquery?

[@FGC_Amu]

I have a question, is it not better to use JOINS instead of subqueries?. From what I understand so far.

[@marcel-andrehille7064]

As far as I understand it, yes. A subquery seems to be executed recursively. Please correct, if I am mistaken. But in the event of being executed recursively, there will be a tremendous performance impact on large data sets

[@rajkumarrajan8059]

Great!!!

[@paragupmanyu2891]

I don't understand why we haven't used bonus condition in same case statement

[@FGC_Amu]

I asked myself that question as well!. Its because Ben Wyatt already has a salary > 50000. So technically he is getting a 7% and a 10% bonus!. He only gets a 10% bonus since he is in the FINANCE DEPARTMENT. Maybe its a flaw in the example or a misinterpretation I dont know. So thats why you add another CASE statement otherwise it counts it as >50000 and dept_id 6.

[@yanhardichandrawan8938]

thankyou for the video!

You are a great tutor

[@syedfarhaan1998]

Thank you sir 💟

[@emmanuelamukamara4931]

Great guy

[@bsarath438]

please provide dataset it really helps to practice

[@hillarykimtai7834]

he provided the dataset at the beginning of the series

[@tigistberhe8499]

Hello Alex I couldn't download my SQL anyone help me please

[@globelistyt]

what error does it show?

[@cozimbatman4100]

Need more info

[@k.chriscaldwell4141]

Thank you.

[@maclaudeamumbwe2663]

Lord, subqueries was tough grasp

[@yeniramirez911]

Thank you so much :D

[@Kikkerv11]

CTEs are so much more straightforward.

[@karimullasyed3695]

Hiii im getting zero in subquries

[@karimullasyed3695]

Alex please respond

[@karimullasyed3695]

9.14 sec the answer which was getting for you not getting same output for me i have type same thing what ew have teach in this video please guide me alex

[@chinsinang8887]

@@karimullasyed3695 Hi, you might want to paste your query and output here for others to advice.

[@maulanadoni21]

you should use AS in the max(age) subquery

[@sailikulkarni6065]

where can i practice SQL section wise?

[@marchosias2071]

i fried my brain trying to do subquery within subquery within a query but i managed to do it in the end🤓🤕

[@sujansapkota0504]

select salary,(select avg(salary) from employee_salary ) avgSalary, case when salary>avgSalary then 'This employee is earning good' end as label from employee_salary ; when executing this code it says there is no column as avgSalary while I renamed the output coming from subquery as avgSalary, can we not use alias while comparing it with original column?

[@NK-qb4bp]

u shouldnt use case statement in the presence of subquery

[@paulperez2005]

Why could you not have been my teacher in uni huh?

[@clarke6814]

✅

[@knygrs]

done. a bit confusing. i need to look up to other youtube video for better and simple explanation