Nested queries

-- Find names of all employees who have sold over 50,000
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (SELECT works_with.emp_id
                          FROM works_with
                          WHERE works_with.total_sales > 50000);

# the part inside the bracket gets executed first and then on the basis of its result, the outside query gets executed.

-- Find all clients who are handles by the branch that Michael Scott manages
-- Assume you know Michael's ID
SELECT client.client_id, client.client_name
FROM client
WHERE client.branch_id = (SELECT branch.branch_id
                          FROM branch
                          WHERE branch.mgr_id = 102
                          LIMIT 1);                           // in case Michael manages more than one branch, use LIMIT to return only one value


 -- Find all clients who are handles by the branch that Michael Scott manages
 -- Assume you DONT'T know Michael's ID
 SELECT client.client_id, client.client_name
 FROM client
 WHERE client.branch_id = (SELECT branch.branch_id
                           FROM branch
                           WHERE branch.mgr_id = (SELECT employee.emp_id
                                                  FROM employee
                                                  WHERE employee.first_name = 'Michael' AND employee.last_name ='Scott'
                                                  LIMIT 1));


-- Find the names of employees who work with clients handled by the scranton branch
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (
                         SELECT works_with.emp_id
                         FROM works_with
                         )
AND employee.branch_id = 2;


-- Find the names of all clients who have spent more than 100,000 dollars

SELECT client.client_name, client.client_id
FROM client 
WHERE client.client_id IN (
  SELECT client_id 
  FROM ( 
   SELECT SUM(works_with.total_sales) AS Totals, client_id
   FROM works_with
   GROUP BY client_id) AS Total_client_sales
   WHERE Totals > 100000
);