OMG, I was struggling with Nested subqueries, but now I certainly have a better insight about it. Thank you so much and the person who shared your video in a forum.
If in client there was not mng_id, is that a good query for the last exercise? SELECT client_id, client_name FROM client WHERE client_id IN ( SELECT client_id FROM works_with WHERE emp_id IN( SELECT emp_id FROM employee WHERE super_id IN ( SELECT mgr_id FROM branch WHERE mgr_id IN ( SELECT emp_id FROM employee WHERE employee.first_name = 'Michael' AND employee.last_name = 'Scott' ) ) OR (first_name = 'Michael' AND last_name = 'Scott') ) )
Assuming you don't know Michael Scott's ID: -- Clients handled by Michael Scott's branch SELECT client_name FROM client WHERE branch_id IN (SELECT branch_id FROM branch WHERE mgr_id IN (SELECT emp_id FROM employee WHERE first_name = "Michael" AND last_name = "Scott")); -- Or you can do SELECT client_name FROM client WHERE branch_id IN (SELECT branch_id FROM branch WHERE mgr_id IN (SELECT emp_id FROM employee WHERE CONCAT(first_name, " ", last_name) = "Michael Scott"));
Yes, a very good question. I would have used a table join for the first example in this tutorial, but as I'm relatively new to SQL, I assumed there was either a good reason not to, OR he was merely trying to demonstrate a principle.
for second query, we could also use SELECT client.client_id, client.client_name FROM client WHERE branch_id IN ( SELECT employee.branch_id FROM employee WHERE employee.emp_id = 102 );
Hi, thanks for the video! Sorry I don't get it why in the first example you were using 'IN', while in the 2nd example you were using '='? Can you explain a little detail, please? Thanks.
I liked the vid. You explain very well! However, isn't it a video for subqueries? I'm new to SQL but i'm under the impression you would have to go one step inner to get a nested query, right?
Explanation are helpful but I do have one question.. isn't the first question to find out employees for a single client.. not sure the query address the question
I guess he means all employees who have sold +3k to one client (not a specific client). So if we have an employee who sold 1500 to a client and 1500 to another client should not be counted
Please I have a few questions which I need help with. For these I have the tables ready but I am not sure if my queries are right. All the tables that I need are in section 1 which are done and completed. But for the queries from these tables are so confusing. I want to show you what I have done could you please show me if they are right . Please reply me if you can help me. I will send you all the queries I have done in the morning if you are ready to help me. Thank you God bless you. Kind regards Arslan
believe it or not our database design lecturer did not use practical example like this and just read of what syntaxes does from textbook. holy i just understood it in a minute!
May I asked why you would not use an IN with the client branch ID in the where clause, I don’t understand why parenthesis would used in this sense. Thank you
Couldnt you use a join to accomplish the same thing? SELECT employee.first_name, works_with.total_sales FROM employee JOIN works_with ON employee.emp_id = works_with.emp_id WHERE works_with.total_sales >= 30000;
So, is it bad I didn't recognize the "re-brand" and my first thought was come on some guy stole "Mike's" video?? And, here I was going to subscribe. After a brief look at the videos, I realized my mistake and pounded that sub button. Because anyone with a hunger for computer knowledge this is a good place to start. The tutorials are simple and easy to follow for beginners and more advanced users.
In the very first subquery example, how would you display the client id as well along with employee's first and last name? I am happy if it repeats the name.
if you dont assume you know michael scott id cant you just do it through employee table? for exp select client.client_name from client where client.branch_id = ( select employee.branch_id from employee where first_name='Michael' and last_name='Scott' //you could use LIKE instead of = limit 1);
this is the code i came up with for the second question, can anyone tell me if it is correct select works_with.emp_id from works_with where works_with.emp_id in ( select client.client_name from client where client.branch_id = 2 );
I have an exam tomorrow this whole nested queries thing can get pretty complicated if you dont under stand the basics of the dos and don'ts and when to use certain functions with nested queries.
Very clearly explained. I'm just getting to grips with SQL, so forgive my ignorance! Would you use a nesting approach if you wanted to retrieve relational data for over 1000 items using Oracle where you can't input more than 1K items in a single search? Or is there a more efficient way of querying large numbers of inputs using Developer? Hope that makes sense :-)
i'm making an ecommerce website already made a db for it, but we are expanding so the nested model sounds like the best solution due to the filters and all that, but after watching this and making those huge queries (not difficult tho, just thinking about the whole mantainance and all the what would happen ifs), i feel like hierarchy model could be better, (can't work with mongodb since our hosting doesn't support it), what do you recommend me?
@@EzeChiappero well now I got it , the different between the equal sign (=) and the 'IN' syntax is in it's implementation. Using 'IN' means we can look for value in a range of value, for example we can use : fruit IN ("apple","pineapple","orange"), it looks better than fruit = "apple" or fruit = "pineapple" or fruit = "orange". Hope that helps!
Writing the queries separately and then combining them made the logic of nested queries much easier to understand. Nice vid!
Indeed .
This guys way of teaching is amazing
Couldn’t figure out my problem.. watched this video.. then solved my problem in less than 2 mins. Thx dude!
I feel Jim and Dwight sell more than Michael at any given time.
Great video!
OMG, I was struggling with Nested subqueries, but now I certainly have a better insight about it. Thank you so much and the person who shared your video in a forum.
If in client there was not mng_id, is that a good query for the last exercise?
SELECT client_id, client_name
FROM client
WHERE client_id IN (
SELECT client_id
FROM works_with
WHERE emp_id IN(
SELECT emp_id
FROM employee
WHERE super_id IN (
SELECT mgr_id
FROM branch
WHERE mgr_id IN (
SELECT emp_id
FROM employee
WHERE employee.first_name = 'Michael' AND employee.last_name = 'Scott'
)
) OR (first_name = 'Michael' AND last_name = 'Scott')
)
)
YES!!!! Thank you. This was really clear. Taking my first database class via Zoom, needing all the help I can get lol!
Assuming you don't know Michael Scott's ID:
-- Clients handled by Michael Scott's branch
SELECT client_name FROM client WHERE branch_id IN
(SELECT branch_id FROM branch WHERE mgr_id IN
(SELECT emp_id FROM employee WHERE first_name = "Michael" AND last_name = "Scott"));
-- Or you can do
SELECT client_name FROM client WHERE branch_id IN
(SELECT branch_id FROM branch WHERE mgr_id IN
(SELECT emp_id FROM employee WHERE CONCAT(first_name, " ", last_name) = "Michael Scott"));
Came for nested queries, stayed for the dataset. Love it thanks
in these cases would be more simple just using the join statement
Can we also just use an inner join?
Select * from employee inner join works_with where total_sales > 30,000 ?
I'm new to this stuff and wondering the same thing..
Any answers?
yes it gives the same result
Any nested correlated query can be rewritten as a normal query, it says in the book Fundamentals of Database Design.
Please make mor vedios on Database we need it!
Anyone having problems viewing this video in particular?
yes, I can only watch this video on 360p
When is it more appropriate to use a nested query over a join? Or is it preference?
Great question
Anyone?
literally my question and I can't find an answer either :)
Yes, a very good question. I would have used a table join for the first example in this tutorial, but as I'm relatively new to SQL, I assumed there was either a good reason not to, OR he was merely trying to demonstrate a principle.
Great video, but how dare you not even include Dwight in the employee table
Great video, just one problem though. You do not have Dwight in the employee table. :)
Thank you! Your explanation is so clear!
my approach:
SELECT client.client_name
FROM client
WHERE client.branch_id=(
SELECT employee.branch_id
FROM employee
WHERE first_name='Michael'
);
Hi, I liked working on the data camp, are there any websites with a lot of tables and questions to solve more problems and practice?
Lol love The Office examples. Great informative video
for second query, we could also use
SELECT client.client_id, client.client_name
FROM client
WHERE branch_id IN (
SELECT employee.branch_id FROM employee
WHERE employee.emp_id = 102
);
hey mike, thanks for explaining the complex topic in SQL nested query!
Hi, thanks for the video! Sorry I don't get it why in the first example you were using 'IN', while in the 2nd example you were using '='? Can you explain a little detail, please? Thanks.
Can you do this if your trying to pull from separate dbos? Or do I have to create a view with the info from each dbo I need?
I liked the vid. You explain very well! However, isn't it a video for subqueries? I'm new to SQL but i'm under the impression you would have to go one step inner to get a nested query, right?
THANK YOU! BEST tutorial out there! Had no idea what was going on it class for the past 40 minutes and you sorted it in 10 😂
Hahaha
Explanation are helpful but I do have one question.. isn't the first question to find out employees for a single client.. not sure the query address the question
I guess he means all employees who have sold +3k to one client (not a specific client). So if we have an employee who sold 1500 to a client and 1500 to another client should not be counted
Can I just say a HUGE thank you as I was stuck on this one coursework question
my profs couldn't even explain this as clear and you, and i paid her for the class.........
Please I have a few questions which I need help with.
For these I have the tables ready but I am not sure if my queries are right. All the tables that I need are in section 1 which are done and completed. But for the queries from these tables are so confusing. I want to show you what I have done could you please show me if they are right . Please reply me if you can help me. I will send you all the queries I have done in the morning if you are ready to help me. Thank you
God bless you.
Kind regards
Arslan
Fantastic videos, all of them. Thank you for doing this.
Awesome explanation for nested or subqueries. Thanks so much!
can you give more questions for nested queries?
believe it or not our database design lecturer did not use practical example like this and just read of what syntaxes does from textbook. holy i just understood it in a minute!
May I asked why you would not use an IN with the client branch ID in the where clause, I don’t understand why parenthesis would used in this sense. Thank you
Life was good when we were giraffe academy's students
Pls can I have the data u work on I have your whole video on SQL but I need your pdf
nobody uses where subqueries just write a nested left join lazy people maybe
Great tutorials man. I have an internship coming up in just over a week. These videos are making me feel prepped for it.
How'd that go
i need this with calculated columns. any suggestions?
Can't watch the video... There is an error when I try to play it.
thanks, wonderful session.
Couldnt you use a join to accomplish the same thing?
SELECT employee.first_name, works_with.total_sales
FROM employee
JOIN works_with
ON employee.emp_id = works_with.emp_id
WHERE works_with.total_sales >= 30000;
He is teaching about nested queries. Later you'll get to know that we can use common table expressions as well instead of writing subqueries.
Can you make video of if condition and while loop as well please
So, is it bad I didn't recognize the "re-brand" and my first thought was come on some guy stole "Mike's" video?? And, here I was going to subscribe. After a brief look at the videos, I realized my mistake and pounded that sub button. Because anyone with a hunger for computer knowledge this is a good place to start. The tutorials are simple and easy to follow for beginners and more advanced users.
Just 30 minutes ago, I upload a doubt. With the help of a mate, I got the answer right the way, great community!
6:11 why it is called foreign key?
In the very first subquery example, how would you display the client id as well along with employee's first and last name? I am happy if it repeats the name.
if you dont assume you know michael scott id cant you just do it through employee table?
for exp
select client.client_name from client
where client.branch_id = (
select employee.branch_id from employee
where first_name='Michael' and last_name='Scott'
//you could use LIKE instead of =
limit 1);
Very good breakdown, thank you!
Love ♥ this tutorial
Keep it up!
These can be done by using JOIN.
Why I can't play this video?
I come here from the google data analytics professional certificate and this video allowed me to understand this new concept. Thank you very much.
this is the code i came up with for the second question, can anyone tell me if it is correct
select works_with.emp_id
from works_with
where works_with.emp_id in
(
select client.client_name
from client
where client.branch_id = 2
);
This made me understand the concept of nested queries better and quicker. The approach you conveyed within 5 mins is effective. Thank you so much
I have an exam tomorrow this whole nested queries thing can get pretty complicated if you dont under stand the basics of the dos and don'ts and when to use certain functions with nested queries.
Awesome video, thanks so much
Thank you. It really helps!
8:04
can we write 'in' instead of "="?
Such a great tutorial.
Thanks, man!
you have helped alot .. clear and understandable explaination
thnk u so much sir
Very helpful. Than you.
omg, this is better than my professor
I don't understand fully the direfence between 'IN' statement and '=' command
*difference
Taught me something my teacher couldn't, appreciate it
Would it make a huge difference if the branch manager query was the outer query, and the client withing the nested query?
Can you do another video on a nested query but with group by? I am trying to use SQL to count adverse events for a clinical trial.
Very clearly explained. I'm just getting to grips with SQL, so forgive my ignorance! Would you use a nesting approach if you wanted to retrieve relational data for over 1000 items using Oracle where you can't input more than 1K items in a single search? Or is there a more efficient way of querying large numbers of inputs using Developer? Hope that makes sense :-)
This was lit 🔥 Thank u bruh...
Subscribed!!! Great videos with C and now SQL.
i'm making an ecommerce website already made a db for it, but we are expanding so the nested model sounds like the best solution due to the filters and all that, but after watching this and making those huge queries (not difficult tho, just thinking about the whole mantainance and all the what would happen ifs), i feel like hierarchy model could be better, (can't work with mongodb since our hosting doesn't support it), what do you recommend me?
actually these queries are too easy. Make pls a more complicated query video
nice video i want this types of more scenerio base questions solutions
Thanks for simplifying the logic behind this!
Thank you very much bro....
This was so helpful! Thanks
Very Well Explained Sir ♥️♥️ Thank You
I feel I should be contributing to your channel of how beneficial it is to me, thank you, Mike!
Can you create a video with nested queries and joins at the same time?
u are so good tbh , very clear
Being an office fanatic....i understood it better🤣👍👌
You are a Star...
Great examples, how can one play with that data (online practice
Love how you used people from the Office!!!
Thank you. Help a lot
To make it simple, why don't use "in" in all cases?
nice explanation 😇 sir
I'm still confused about the difference between the 'IN' and the '=' , any explanations? thanks in advance. Great video!
Me too, same doubt
@@EzeChiappero well now I got it , the different between the equal sign (=) and the 'IN' syntax is in it's implementation. Using 'IN' means we can look for value in a range of value, for example we can use : fruit IN ("apple","pineapple","orange"), it looks better than fruit = "apple" or fruit = "pineapple" or fruit = "orange".
Hope that helps!
@@danielhermawan4054 Thanks!! I'm doing exercises right now and I'm sure that this advice will make sense for me later on. Thanks a lot for the reply!
@@danielhermawan4054 In the first exercise after reading your comment I get it. Thanks, Daniel!! Great community!!
Cool! You learned something today haha, I hope you'll do better and better!✨🔥
Great content, thanks.
Thank you so much dude
Thank you!
I want to validate data from a table A and insert into another table B if value matches from A, how can we achieve it ?
why not just use join?
Great explanation!!!
I wish I can give a million likes for this video. Thank you.
We can use inner join
Great video 👍🏽
Great video! Step by step, clear, organized! good job! Just subscribed!
Great video!!!👏