Comp_Data_1_Part5

Hello. In the last file, we performed some basic queries into our database. We will go forward with some new topics in this file.
We will functions and wildcards.

##### At first let's use some FUNCTIONS.

-----Find the number of employees-----
SELECT COUNT(emp_id)
FROM employee;

----Find how many employees have supervisors----
SELECT COUNT(super_id)
FROM employee;

----Find the number of female employees born after 1970----
SELECT COUNT(emp_id)
FROM employee
WHERE sex='F' AND birth_date>'1970-12-31';

-----Find the average of all employee salaries----
SELECT AVG(salary)
FROM employee;

-----Find the sum of all employee salaries----
SELECT SUM(salary)
FROM employee;

----Find out the number of male and female employees---
SELECT COUNT(sex),sex
FROM employee
GROUP BY sex;

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

----Find out the total amount of money spent by each client----
SELECT client_id, SUM(total_sales)
FROM works_with
GROUP BY client_id;

#####Let's look at wildcards. We will '%' sign and '_' sign. '%' sign means any number of characters while '_' sign means
a single character.

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

---Find any branch suppliers who has labels in their name---
SELECT * FROM branch_supplier
WHERE supplier_name LIKE '%Labels%';

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

----Find any employee born in January---
SELECT * FROM employee
WHERE birth_date LIKE '____01%';

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