$/*$ Create department table $*/$

CREATE TABLE department $($

dpt$\_$no$$INT PRIMARY KEY,

dpt$\_$name VARCHAR$(20)$ NOT NULL,

dpt$\_$mgrssn CHAR$(9),$

dpt$\_$mgr$\_$start$\_$date$$DATE$)$;

$/*$ Create dept$\_$locations table $*/$

CREATE TABLE dept$\_$location $($

dpt$\_$no $$INT NOT NULL,

dpt$\_$location $$VARCHAR$(20),$

$$PRIMARY KEY $($dpt$\_$no$,$ dpt$\_$location$),$

$$FOREIGN KEY $($dpt$\_$no$)$ REFERENCES department$($dpt$\_$no$))$;

$/*$ Create project table $*/$

CREATE TABLE project $($

pro$\_$num$$INT$,$

pro$\_$name$$VARCHAR$(25)$ NOT NULL,

pro$\_$location$$VARCHAR$(25),$

pro$\_$dept$\_$num $$INT$,$

$$PRIMARY KEY $($pro$\_$num$),$

$$FOREIGN KEY $($pro$\_$dept$\_$num$)$ REFERENCES department$($dpt$\_$no$))$;

$/*$ Create employee table $*/$

CREATE TABLE employee $($

emp$\_$ssn$$CHAR$(9)$ PRIMARY KEY,

emp$\_$last$\_$name$$VARCHAR$(25)$ NOT NULL,

emp$\_$first$\_$name$$VARCHAR$(25)$ NOT NULL,

emp$\_$middle$\_$name$$VARCHAR$(25),$

emp$\_$address$$VARCHAR$(50),$

emp$\_$city$$VARCHAR$(15),$

emp$\_$state$$CHAR$(2),$

emp$\_$zip$$CHAR$(9),$

emp$\_$date$\_$of$\_$birth$$DATE$,$

emp$\_$salary$$INT NOT NULL,

$$CONSTRAINT ck$\_$emp$\_$salary CHECK $($emp$\_$salary $<=85000),$

emp$\_$parking$\_$space$$INT UNIQUE,

emp$\_$gender$$CHAR$(1),$

emp$\_$dpt$\_$num$$INT$,$

emp$\_$superssn$$CHAR$(9),$

$$CONSTRAINT fk$\_$emp$\_$dpt FOREIGN KEY $($emp$\_$dpt$\_$num$)$ REFERENCES department$($dpt$\_$no$),$

$$CONSTRAINT fk$\_$emp$\_$superssn FOREIGN KEY $($emp$\_$superssn$)$ REFERENCES employee$($emp$\_$ssn$))$;

$/*$ Create assignment table $*/$

CREATE TABLE assignment $($

work$\_$emp$\_$ssn$$CHAR$(9),$

work$\_$pro$\_$num$$INT$,$

work$\_$hours INT,

work$\_$hours$\_$planned$$INT$,$

$$CONSTRAINT pk$\_$assignment PRIMARY KEY $($work$\_$emp$\_$ssn$,$ work$\_$pro$\_$num$),$

$$CONSTRAINT fk$\_$work$\_$emp$$FOREIGN KEY $($work$\_$emp$\_$ssn$)$ REFERENCES employee$($emp$\_$ssn$),$

$$CONSTRAINT fk$\_$work$\_$pro$\_$num FOREIGN KEY $($work$\_$pro$\_$num$)$ REFERENCES project$($pro$\_$num$))$;

$/*$ Create dependent table $*/$

CREATE TABLE dependent $($

dep$\_$emp$\_$ssn$$CHAR$(9),$

dep$\_$name$$VARCHAR$(40),$

dep$\_$gender$$CHAR$(1),$

dep$\_$date$\_$of$\_$birth$$DATE$,$

dep$\_$relationship$$VARCHAR$(10)$ NOT NULL,

$$CONSTRAINT pk$\_$dependent PRIMARY KEY $($dep$\_$emp$\_$ssn$,$ dep$\_$name$),$

$$CONSTRAINT fk$\_$dep$\_$emp$\_$ssn FOREIGN KEY $($dep$\_$emp$\_$ssn$)$ REFERENCES employee$($emp$\_$ssn$))$;

$1.$ 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.

$2.$ 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

$3.$ 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$)$

$4.$ 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$\_$FORM