( please copy the solution in the word file and make it as attachment), Thank you.

1. Submit one .sql file that creates and interacts with a simple database. You must do each of the following:

(1) Create 6 tables as shown in Figure 5.5. You need to create/include constraintsat a minimum, primary and foreign keys! (

2) Insert values into the tables as shown in Figure 5.6.

(3) Use the command SELECT to show the entire contents of a table.

(4) Implement SQL for the following:

(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.

(b) List the names of employees who have a dependent with the same first name as themselves.

(c) Find the names of employees that are directly supervised by 'Franklin Wong'.

(d) For each project, list the project name and the total hours per week (by all employees) spent on that project.

(e) Retrieve the names of employees who work on every project.

(f) Retrieve the names of employees who do not work on any project.

(g) For each department, retrieve the department name, and the average salary of employees working in that department.

(h) Retrieve the average salary of all female employees.

(i) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston.

(j) List the last names of department managers who have no dependents.

(5) Drop all tables.

Notes:

TEST your code in a DBMS such as XAMPP. Use the template code baseline for the comment syntax and update the fields for NAME, date, and MySQL if you tested on another relational database management system.

Make sure you DROP all your tables successfully. Once all SQL commands have been executed there should be no tables or data remaining

. You are submitting ONLY ONE .sql file to Canvas which I will run on my local DBMS XAMPP installation.

The following is the information that we need it:

-- NAME / date / CS2300 -- This code should be tested in MySQL (technically MariaDB) on XAMPP -- (1) Create the tables

-- (2) Populate the tables -- order will be important due to referential integrity!

INSERT INTO Employee VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5); INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO Employee VALUES ('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4); INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO Employee VALUES ('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5); INSERT INTO Employee VALUES ('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5); INSERT INTO Employee VALUES ('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4); INSERT INTO Employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, null, 1);

INSERT INTO Department VALUES ('Research', 5, 333445555, '1988-05-22'); INSERT INTO Department VALUES ('Administration', 4, 987654321, '1995-01-01'); INSERT INTO Department VALUES ('Headquarters', 1, 888665555, '1981-06-19');

INSERT INTO Dept_Locations VALUES (1, 'Houston'); INSERT INTO Dept_Locations VALUES (4, 'Stafford'); INSERT INTO Dept_Locations VALUES (5, 'Bellaire'); INSERT INTO Dept_Locations VALUES (5, 'Sugarland'); INSERT INTO Dept_Locations VALUES (5, 'Houston');

INSERT INTO Works_On VALUES (123456789, 1, 32.5); INSERT INTO Works_On VALUES (123456789, 2, 7.5); INSERT INTO Works_On VALUES (666884444, 3, 40.0); INSERT INTO Works_On VALUES (453453453, 1, 20.0); INSERT INTO Works_On VALUES (453453453, 2, 20.0); INSERT INTO Works_On VALUES (333445555, 2, 10.0); INSERT INTO Works_On VALUES (333445555, 3, 10.0); INSERT INTO Works_On VALUES (333445555, 10, 10.0); INSERT INTO Works_On VALUES (333445555, 20, 10.0); INSERT INTO Works_On VALUES (999887777, 30, 30.0); INSERT INTO Works_On VALUES (999887777, 10, 10.0); INSERT INTO Works_On VALUES (987987987, 10, 35.0); INSERT INTO Works_On VALUES (987987987, 30, 5.0); INSERT INTO Works_On VALUES (987654321, 30, 20.0); INSERT INTO Works_On VALUES (987654321, 20, 15.0); INSERT INTO Works_On VALUES (888665555, 20, null);

INSERT INTO Project VALUES ('ProductX', 1, 'Bellaire', 5); INSERT INTO Project VALUES ('ProductY', 2, 'Sugarland', 5); INSERT INTO Project VALUES ('ProductZ', 3, 'Houston', 5); INSERT INTO Project VALUES ('Computerization', 10, 'Stafford', 4); INSERT INTO Project VALUES ('Reorganization', 20, 'Houston', 1); INSERT INTO Project VALUES ('Newbenefits', 30, 'Stafford', 4);

INSERT INTO Dependent VALUES (333445555, 'Alice', 'F', '1986-04-05', 'Daughter'); INSERT INTO Dependent VALUES (333445555, 'Theodore', 'M', '1983-10-25', 'Son'); INSERT INTO Dependent VALUES (333445555, 'Joy', 'F', '1958-05-03', 'Spouse'); INSERT INTO Dependent VALUES (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'); INSERT INTO Dependent VALUES (123456789, 'Michael', 'M', '1988-01-04', 'Son'); INSERT INTO Dependent VALUES (123456789, 'Alice', 'F', '1988-12-30', 'Daugther'); INSERT INTO Dependent VALUES (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');

-- (3) Display the contents of a table

5.5 Schema diagram for the COMPANY relational database schema. EMPLOYEE Fname Min Lname Ssn Bdate Address Sex Salary Super ssn Dno DEPARTMENT Dname Dnumber Mgr ssn Mgr start date DEPT LOCATIONS Dnumbe Dlocation PROJECT Pname Pnumber Plocation Dnunm WORKS ON Essn Pno Hours DEPENDENT Essn Dependent name Sex Bdate Relationship