Tutorial Select Queries

-- Nested Queries

-- Find all clients who are handled by the branch
-- that Michael Scott manages
-- Assume you know Michael's ID
SELECT client.client_name
FROM client
WHERE client.branch_id = (
    SELECT branch.branch_id
    FROM branch
    WHERE branch.mgr_id = 102
    LIMIT 1
);
-- 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
);

-- Joins

-- Find all branches and the names of their managers
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

INSERT INTO branch VALUES (4,'Buffalo',NULL,NULL);

SELECT * FROM branch;

-- Unions

-- Find a list of all money spent or earned by the company
SELECT salary AS Spent_Earned
FROM employee
UNION
SELECT total_sales
FROM works_with;
-- Find a list of all clients & branch suppliers' names
SELECT client_name, client.branch_id
FROM client
UNION
SELECT supplier_name, branch_supplier.branch_id
FROM branch_supplier;

-- Find a list of employee and branch names
SELECT first_name AS Company_Names
FROM employee
UNION
SELECT branch_name
FROM branch;

-- -WildCards---

-- Find any clients who are schools
SELECT * FROM client
WHERE client_name LIKE '%school';

-- Find any employee born in october
SELECT * FROM employee
WHERE birth_day LIKE '____-10%';

-- Find any branch suppliers who are in the label business
SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '% Label%';

-- Find any clients who are an LLC
SELECT *
FROM client
WHERE client_name LIKE '%LLC';

-- GROUP BY---

-- Find the total sales of each salesman
SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;

-- Find out how many males an females there are
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;

-- WHERE---

-- Find the sum of all employee's salaries
SELECT SUM(salary)
FROM employee
WHERE sex = 'M';

-- Find the average of all employee's salaries
SELECT AVG(salary)
FROM employee
WHERE sex = 'M';

-- Find the number of female employees born after 1970
SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'F' AND birth_day > '1970-01.01';
Select * from employee;