queries_regex_union_join.sql

--*************************** BASIC QUERIES ****************************
--Find all employees
SELECT *
FROM employee;
--find all clients
SELECT * 
FROM client;
--Find all employees ordered by salary
SELECT *
FROM employee
ORDER BY salary;
--Find all employees descending salary
SELECT *
FROM employee
ORDER BY salary DESC;
--order all employees by sex and then last name
SELECT *
FROM employee
ORDER BY sex, last_name;
--Find the first employees on the table
SELECT *
FROM employee
LIMIT 5;
--Find only the first and last names of all employees
SELECT first_name, last-name
FROM employee;
--Change the first name and last name columns to forename and family name
SELECT first_name AS forname, last_name AS family_name
FROM employee;
--Find all the different genders 
--DISTINCT will return all the different options for sex in this case
SELECT DISTINCT sex
FROM employee;
--************SQL Functions *****************
--how many employees are in the table
SELECT COUNT(emp_id)
FROM employee;
--count only the female employee that are over 40
SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'M' AND  birth_date < '1983-10-05';
--find the average salary of all employees
SELECT AVG(salary)
FROM employee;
--find the sum salary of all employees
SELECT SUM(salary)
FROM employee;
--aggredation
--find how many males and how many females
--count only the female employee that are over 40
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;
--find the total sales of each salesman from the works_with table
SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;
--find the total spent by each client
SELECT SUM(total_sales), client_id
FROM works_with
GROUP BY client_id;
--**************regex**************
--find any client that has llc inside name
--% means any number of characters before the LLC
--bc there is no % after LLC then the name has to end with LLC bc nothing is after
SELECT *
FROM client
WHERE client_name LIKE '%LLC';
--find any branch suppliers that are in the label business
--% means any number of characters before the LLC
SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '%label%';
--find any employee born in the month of Oct
-- the _ means any single character so knowing the format of the dates we can use this regex
-- the _'s are for the year and the % is anything that follows
SELECT *
FROM employee
WHERE birth_date LIKE '____-10%';
--Find any clients who are schools or have school in their name
--the % are anything in front and anything after
SELECT *
FROM client
WHERE client_name LIKE '%school%';

--***********************************************
--****************UNIONS*************************
--***********************************************

--Find a list of employee and branch names
--with union you have to have the same number of columns so we only have one in this case
--and they need the same data type

--This is a full join union table
SELECT first_name
FROM employee
UNION
SELECT branch_name
FROM branch;

--Find a list of all clients and branch suppliers names
--you can prefix the table name to make sure it is clear where the data is coming from
SELECT client.client_name
FROM client
UNION
SELECT supplier_name
FROM branch_supplier;


--*****************************************
--***************JOIN TABLES **************
--this just adding a branch without the last two columns for future queries
INSERT INTO branch VALUES(4,'Buffalo', NULL, NULL);
SELECT * FROM branch;

--find all branches and the names of their managers
--This is an inner join, it combines rows from both tables
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mrg_id;

--find all branches and the names of their managers
--This is an left join, it combines all the rows from the first table and only the one you said from the right
--This returns all the employees from the first table and then only the matching one from the second table
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mrg_id;

--find all branches and the names of their managers
--This is an right join, it combines all the rows from the SECOND table and only the one you said from the first table
--This returns all the branches and then only the employees that match
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch.mrg_id;

--*******************************************
--*************NESTED QUERIES *************
--find all the name of employees that sold more than 30k to one client
--first find all the employee ids that have sold more than 50K
--then put that query into () and look for the first and last name of the employee 
--Need to use the keyword IN to say if the id is in the first query return
SELECT first_name, last_name
FROM employee
WHERE employee.emp_id IN (
    SELECT emp_id
    FROM works_with
    WHERE  total_sales > 30000
);

--find all clients that are handled by Micheal Scott 
-- assume you know his id

--first find the branch that Micheal Scott mangages
SELECT branch_id
FROM employee 
WHERE emp_id= 102;

--then use that to find all the clients with that as their branch id
SELECT client_name 
FROM client
WHERE client.branch_id = (
    SELECT branch_id
    FROM employee 
    WHERE emp_id= 102
);