Comp_Data_1_Part7

Hi, now we will see Nested Queries, ON DELETE(deleting entries from database, when they have foreign key associated to them) etc.

----Find names of all employees who have sold over 30000 to a single client.

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>30000
);

--------Find all clients who are handled by the branch that Michael Scott manages. Very interesting!!!

SELECT client.client_name
FROM client
WHERE client.branch_id IN(
    SELECT branch.branch_id
    FROM branch
    WHERE branch.mgr_id IN(
        SELECT employee.emp_id
        FROM employee
        WHERE (employee.first_name,employee.last_name)=('Michael','Scott')
    )
);

---Find the names of employees who work with clients handled by the scranton branch. You do not know the branch id.----

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.client_id IN(
        SELECT client.client_id
        FROM client
        WHERE client.branch_id IN(
            SELECT branch.branch_id
            FROM branch
            WHERE branch.branch_name='Scranton'
        )
    )
)
ORDER BY employee.emp_id;

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

SELECT client.client_name
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
);

######ON DELETE

DELETE FROM branch
WHERE branch_id=2;

SELECT * FROM branch_supplier;