-- Programmer: Seth McNamee
-- Project: Test 2

USE employee;

-- 1

UPDATE glasses
SET optician = 'GREENBERG OPTICAL'
WHERE optician = 'Greenberg Optical';

-- 2

SELECT first_name, last_name, AVG(tool_cost) AS 'Average Tool Cost'
FROM employee e, tools t
WHERE e.payroll_number = t.fk_payroll_number
GROUP BY first_name, last_name;

-- 3

SELECT first_name, last_name, purchase_date
FROM employee JOIN glasses
	ON employee.payroll_number = glasses.fk_payroll_number
WHERE purchase_date = (SELECT MAX(purchase_date) from glasses) OR purchase_date = (SELECT MIN(purchase_date) FROM glasses);

-- 4

SELECT department_name, AVG(wages) AS 'Average Wages'
FROM department d, employee e
WHERE d.department = e.fk_department
GROUP BY department_name
HAVING SUM(wages) > 32000
ORDER BY SUM(wages) DESC;

-- 5

SELECT first_name, last_name, tool_name, tool_cost
FROM employee JOIN tools
	ON employee.payroll_number = tools.fk_payroll_number
WHERE tool_cost = (SELECT MAX(tool_cost) from tools);

-- 6

SELECT tool_name, COUNT(*) AS number_of_purchase, SUM(tool_cost) AS total_cost
FROM tools
GROUP BY tool_name
ORDER BY COUNT(*), SUM(tool_cost) DESC;

-- 7

SELECT department_name, COUNT(*) AS '# of employees', AVG(wages) AS average_wage
FROM employee e, department d
WHERE e.fk_department = d.department 
GROUP BY department_name WITH ROLLUP;

-- 8 

DELETE FROM department
WHERE department = 'CEN';

-- 9

SELECT tool_name, tool_cost
FROM tools
WHERE tool_cost > (SELECT AVG(tool_cost) FROM tools);

-- 10

SELECT department_name, first_name, last_name, MIN(purchase_date) AS oldest_purchase_date
FROM department d, employee e, tools t
WHERE d.department = e.fk_department AND e.payroll_number = t.fk_payroll_number
GROUP BY first_name, last_name
ORDER BY department_name, first_name, last_name;