Using the database and tables from Week Three, write queries to the specifications listed below. Note: Verify that the updates in the next three statements (4.1 - 4.3) are completed correctly by displaying the relevant columns before and after the updates run.
(4.1) Choose an EEO-1 Classification: Increase all employees' salaries that have: the selected EEO-1 classification by 10%.
(4.2) Increase all employees' salaries by 5%.
(4.3) Choose an employee from the employee table; delete this employee.
Using the database and tables from Week Three, write SQL statements to:
(4.4) How many people are salaried and how many are hourly?
(4.5) Calculate the minimum salary for all salaried employees.
(4.6) Calculate the minimum hourly rate for all hourly employees.
(4.7) Calculate the average wage (salaried or hourly) for each EEO-1 Classification.
(4.8) Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
(4.9) Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees.
The included SQL file sets up the database and runs queries that answer each of the questions listed. I looked over the questions and queries and it looks like they are all correct. For questions 4.4 and 4.7 I have suggested alternative ways of getting the information using the "group by" SQL clause. The "group by" allows the results of a query to be aggregated for similar values of a column.
Some of the queries in the included file were not needed for the questions in the problem. I marked each query in the file with a comment (//) that indicates which question it matches.
Here are the annotated queries and the alternatives:
update EMPLOYEE set salary = salary * 1.1 ...
This solution demonstrates several different queries for the provided database. These queries include select statements and various sql functions.