Could you please help me on this MySQL problem. MySQL file is attached

In order to familiarize yourself with the data in this database, perform the following steps. This will help you verify the reasonability of the results you derive from the second part of the lab.

1. Display a list of all tables in the SQL101 database;

2. Show the record layout for each table in the database.

3. Run a query that displays all records in each table in the database. Now that you are familiar with the data, use SQL queries and aggregate functions to answer the following business questions about the data:

4. What is the total allotted amount for annual salary paid to all employees?

5. What is currently the average annual salary for all employees? (Use all records.)

6. The AVG() function ignores null values, so the results above are not really accurate. Repeat the query above, but using a zero for any null values encountered. HINT: Oracle uses NVL; can you remember what the MySQL equivalent is?

7. How many employees does the company have? Use an aggregate function, not SELECT;*.

8. How many managers does the company have? Be careful. This question is not asking how many rows in the employee table have a value in the manager column? How will you make sure you only account for the number of managers, not the number of rows?

9. What are the smallest and largest salary values? Use a single query.

10. How many employees are in each department? HINT: not all employees have been assigned to a department. We want that person to be included in the report. Use the same technique you employed in step 6 above to replace the word "NULL" with "none"for the department_id if it is null. Your report should display the department_id with the total number of employees in that department. Sort the results by department_id.

11. The COO (Chief Operating Officer) saw the report you generated in step 10 above. He wants you to run a new report that simply leaves out the line that references the employee with no department. Reissue the query, you framed in step 10 above, to meet the COO's new request. HINT: If you are HAVING any difficulty with this request, review the MySQL 5.7 Reference Manual, 13.2.9 SELECT Syntax at http://dev.mysql.com/doc/refman/5.7/en/select.html. Scroll down to find the HAVING command.

12. The COO likes the new report much better. He asks you to produce a similar report that shows how much money is spent on salaries annually for each of the departments. HINT: Your query will be a bit of a hybrid of the one from steps 10 and 11.

13. Can you think of an alternative approach to step 12 without HAVING to limit results by using an aggregate function? WHERE might you come up with such an idea? If you can figure this one out, include it as the last entry in your log file.

MySQL file

CREATE DATABASE SQL101; USE SQL101; CREATE TABLE employee ( employee_id INTEGER, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, salary DECIMAL(9,2), manager INTEGER, department_id INTEGER ); CREATE TABLE department ( department_id INTEGER, name VARCHAR(30), location VARCHAR(30) ); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (28, 'Emily', 'Eckhardt', STR_TO_DATE('07-JUL-2008', '%d-%M-%Y'), 100000, NULL, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (37, 'Frances', 'Newton', STR_TO_DATE('14-SEP-2009', '%d-%M-%Y'), 75000, NULL, NULL); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (1234, 'Donald', 'Newton', STR_TO_DATE('24-SEP-2010', '%d-%M-%Y'), 80000, 28, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (7895, 'Matthew', 'Michaels', STR_TO_DATE('16-MAY-2011', '%d-%M-%Y'), 70000, 28, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6567, 'Roger', 'Friedli', STR_TO_DATE('16-MAY-2011', '%d-%M-%Y'), 60000, 28, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6568, 'Betsy', 'James', STR_TO_DATE('16-MAY-2011', '%d-%M-%Y'), 60000, 28, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6569, 'michael', 'peterson', STR_TO_DATE('03-NOV-2012', '%d-%M-%Y'), 90000, NULL, 20); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6570, 'mark', 'leblanc', STR_TO_DATE('06-MAR-2013', '%d-%M-%Y'), 65000, 6569, 20); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6571, 'Thomas', 'Jeffrey', STR_TO_DATE('27-FEB-2014', '%d-%M-%Y'), 300000, null, 30); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6572, 'Theresa', 'Wong', STR_TO_DATE('27-FEB-2014', '%d-%M-%Y'), 70000, 6571, 30); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6573, 'Lori', 'Dovichi', STR_TO_DATE('07-JUL-2014', '%d-%M-%Y'), null, 28, 10); INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id) VALUES (6574, 'Fred', 'Fardbarkle', STR_TO_DATE('09-DEC-2014', '%d-%M-%Y'), 35575, 6571, 30); INSERT INTO department (department_id, name, location) VALUES (10, 'Accounting', 'LOS ANGELES'); INSERT INTO department (department_id, name, location) VALUES (20, 'Payroll', 'NEW YORK'); INSERT INTO department (department_id, name, location) VALUES (30, 'IT', 'WASHINGTON DC'); Commit;