11-01 Nested Queries

example code press "t"

01

Use select statment functions as arguments. The most nested Quiery rus first, in this case,

Here, we're finding every employee's id with more than 30000 sales 

select works_with.emp_id 
from works_with
where total_sales > 30000;

02

We plug it into employee to get the names, (The most inner nested loop runs first) in this case the select statement for works_with. 

select employee.first_name , employee.last_name
from employee
where emp_id IN (
    select works_with.emp_id
    from works_with 
    where total_sales > 3000
);

03

Find branch_id Michael Scott manages and all of that branch's clients, assuming you have michael scott's id which is 102. 

select client.client_name 
from client 
where branch_id = (
    select branch.branch_id 
    from branch 
    where mgr_id = 102
);

04

If Michael is a branch manager of another branch 

INSERT INTO branch VALUES(102,'Buffalo',NULL,NULL);
INSERT INTO branch VALUES(102,'Buffalo',NULL,NULL);
update branch
set mgr_id = 102
where branch_id = 4;

update branch
set mgr_start_date = '2000-10-04'
where branch_id = 4;
select * from branch;

05

It won't work 

select client.client_name 
from client 
where branch_id = (
    select branch.branch_id 
    from branch
    where mgr_id = 102
);

06

We need to LIMIT result to 1 for it to work 

select client.client_name
from client
where branch_id = (
select branch.branch_id
from branch
where mgr_id = 102
limit 1
);