This task has five parts. IMPORTANT: If you do not complete this step, your work will not be graded at all. You will get a zero for the project.

Create a company database named as COMP$\_$your first name$\_$your last name with your first and last name. For example, if your name is John Smith, the database name must be COMP$\_$JOHN$\_$SMITH. Use this database to complete all of the following tasks.

Type the following statements and display the results.

USE COMP$\_$your first name$\_$your last name;

SELECT current$\_$user$(),$ now$(),$ database$()$;

Run the Company$\_$MySQL script $($provided in Brightspace$)$ to create all tables, rows, foreign keys, etc. Do NOT change anything in the script.

Add a row into the Employee table with your first and last name. IMPORTANT: See below.

emp$\_$ssn $=111111111$

emp$\_$first$\_$name $=$ your first name

emp$\_$last$\_$name $=$ your last name

emp$\_$dpt$\_$num $=1$

emp$\_$superssn $=999666666$

emp$\_$salary $=80000$

Make up the rest of the information needed to insert the row.

Add a row into the Dependent table with the following information. IMPORTANT: See below.

dep$\_$emp$\_$ssn $=111111111$

dep$\_$name $=$ find a name here

dep$\_$gender $=$ X $($make sure this is an X$)$

dep$\_$date$\_$of$\_$birth $=$ put a date here

dep$\_$relationship $=$ put SON, DAUGHTER or SPOUSE here

Display all employees from the Employee table using the SELECT statement.

Display all dependents from the Dependent table using the SELECT statement.

Write a query to display all employees with the total number of projects they work on$,$ including employees with no assignment. Display six columns$$Employee Name, department number and name, # of projects, the total actual hours, total planned hours and the difference. List the employee name as first and last name with a space in between. Sort the results by the department in ascending order, then # of projects in ascending order. Your name should be in the result list. See sample output below. $(25$ pts$)$

IMPORTANT: You need to research how to use IFNULL function. No partial credit, if the format of your results is different than provided in the sample output.

Write a query to display all employees, the total number of projects they worked on$,$ and the total $ amount of the equipment used in those projects. If an employee does not have any assignment, you still must include those employees in the results list. Display four columns$$Employee SSN$,$ Employee Name, # of Projects, and Equipment Total. Format the employee SSN as shown below. List the employee first and last name with a space between. Format the equipment total with a dollar sign as shown below. Sort the results by equipment total in ascending order. See sample output below. Your output must match the sample output. $(25$ pts$)$

IMPORTANT: You need to research how to use the FORMAT function and add $ sign in the results. No partial credit, if the format of your results is different than provided in the sample output.

Write a query to display projects that have the difference between hours planned and hours worked is more than $10.$ Display five columns. List the project number and name with a dash in between, name the column as $$Project$,$ the total hours planned as $$Hours Planned$,$ the total hours worked as $$Hours Worked$,$ the difference, and the number of employees worked in each project. Sort the results in descending order by the difference. See sample output below. Your output must match the sample output. $(25$ pts$)$

Write a query to display the manager$$s SSN$,$ manager$$s first and last name with a space between them, department number, department name, employee$$s SSN$,$ employee$$s first and last name with a space between them, and the following information about the employee's dependents: name, relationship and date of birth. The department number and name are that of the employee, not the manager. Name the columns as follows: Manager SSN$,$ Manager Name, Department, Employee SSN$,$ Employee Name, Dependent Name, Relationship and Dependent DOB. Sort the results by department number, employee SSN in all ascending order. See sample output below. $(25$ pts$)$

IMPORTANT: You need to research how to use SUBSTRING and DATE$\_$FORMAT functions.

No partial credit, if the format of your results is different than provided in the sample output.