sql homework.table already created. Data already inserted. please complete the questions.
Assignment: 1. Specify the following queries on the from above database schema. Also, show the result of each query as it would apply to the database. a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project. b. List the names of all employees who have a dependent with the same first name as themselves. ( no need to do this one) c. Find the names of all employees who are directly supervised by ‘Franklin Wong’. ( no need to do this one) d. For each project, list the project name and the total hours per week (by all employees) spent on the project. e. Retrieve the names of all employees who work on every project. f. Retrieve the names of all employees who do not work on any project. g. For each department, retrieve the department name and the average salary of all employees working in that department. h. Retrieve the average salary of all female employees. i. Find the names and addresses of all 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 all department managers who have no dependents. k. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. l. Suppose that we want the number of male employees in each department rather than all employees. Can we specify this query in SQL? Why or why not. DO NOT MAKE ANY CHANGES TO THE CREATE AND INSERT STATEMENT BELOW CREATE TABLE Department ( dname VARCHAR2(15) NOT NULL, dnumber NUMBER(4), mgrssn CHAR(9) NOT NULL, mgrstartdate DATE, PRIMARY KEY (dnumber), UNIQUE (dname) ); CREATE TABLE Dept_locations ( dnumber NUMBER(4), dlocation VARCHAR2(15), PRIMARY KEY (dnumber, dlocation), FOREIGN KEY (dnumber) REFERENCES Department(dnumber) ); CREATE TABLE Project ( pname VARCHAR2(15) NOT NULL, pnumber NUMBER(4), plocation VARCHAR2(15), dnum NUMBER(4) NOT NULL, PRIMARY KEY (pnumber), UNIQUE (pname), FOREIGN KEY (dnum) REFERENCES Department(dnumber) ); CREATE TABLE Employee ( fname VARCHAR2(15) NOT NULL, minit VARCHAR2(1), lname VARCHAR2(15) NOT NULL, ssn CHAR(9), bdate DATE, address VARCHAR2(30), sex CHAR, salary NUMBER(10, 2), superssn CHAR(9), dno NUMBER(4), PRIMARY KEY (ssn), FOREIGN KEY (superssn) REFERENCES Employee(ssn), FOREIGN KEY (dno) REFERENCES Department(dnumber) ); CREATE TABLE Works_on ( essn CHAR(9), pno NUMBER(4), hours NUMBER(4, 1), PRIMARY KEY (essn, pno), FOREIGN KEY (essn) REFERENCES Employee(ssn), FOREIGN KEY (pno) REFERENCES Project(pnumber) ); CREATE TABLE Dependent ( essn CHAR(9), dependent_name VARCHAR2(15), sex CHAR, bdate DATE, relationship VARCHAR2(8), PRIMARY KEY (essn, dependent_name), FOREIGN KEY (essn) REFERENCES Employee(ssn) ); ALTER TABLE Department ADD CONSTRAINT fk_department FOREIGN KEY (mgrssn) REFERENCES Employee (ssn); INSERT ALL INTO employee values('James', 'E', 'Borg', '888665555', '10-NOV-1927', '450 Stone, Houston,TX', 'M', 55000, null, null) INTO employee values('Jennifer', 'S', 'Wallace', '987654321', '20-JUN-1931', '291 Berry, Bellaire,TX', 'F', 43000, '888665555', null) INTO employee values('Franklin', 'T', 'Wong', '333445555', '08-DEC-1945', '638 Voss, Houston,TX', 'M', 40000, '888665555', null) select * from dual INSERT INTO department (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) VALUES ('Research', 5, '333445555', '22-MAY-1978'); INSERT INTO department VALUES ('Administration', 4, '987654321', '01-JAN-1985'); INSERT INTO department VALUES ('Headquarters', 1, '888665555', '19-JUN-1971'); UPDATE employee SET DNO = 5 WHERE ssn = '333445555'; UPDATE employee SET DNO = 4 WHERE ssn = '987654321'; UPDATE employee SET DNO = 1 WHERE ssn = '888665555'; INSERT INTO employee(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('John', 'B', 'Smith', '123456789', '09-JAN-1955', 'Houston,TX', 'M', 30000, '333445555', 5); INSERT INTO employee VALUES ('Alicia', 'J', 'Zelaya', '999887777', '19-JUL-1958', 'Spring,TX', 'F', 25000, '987654321', 4); INSERT INTO employee VALUES ('Ramesh', 'K', 'Narayan', '666884444', '15-SEP-1952', 'Humble,TX', 'M', 38000, '333445555', 5); INSERT INTO employee VALUES ('Joyce', 'A', 'English', '453453453', '31-JUL-1962', 'Houston, TX', 'F', 25000, '333445555', 5); INSERT INTO employee VALUES ('Ahmad', 'V', 'Jabbar', '987987987', '29-MAR-1959', 'Houston,TX', 'M', 25000, '987654321', 4); INSERT INTO employee VALUES ('New', 'V', 'Lost', '777977977', '23-MAR-1977', 'NY,NY', 'M', 20000, '987654321', 4); DELETE FROM project; INSERT INTO project(PNAME, PNUMBER, PLOCATION, DNUM) 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); DELETE FROM dept_locations; INSERT INTO dept_locations(DNUMBER, DLOCATION) 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'); DELETE from dependent; INSERT INTO dependent(ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP) VALUES ('333445555','Alice','F','05-APR-1976','Daughter'); INSERT INTO dependent VALUES ('333445555','Theodore','M','25-OCT-1973','Son'); INSERT INTO dependent VALUES ('333445555','Joy','F','03-MAY-1948','Spouse'); INSERT INTO dependent VALUES ('987654321','Abner','M','29-FEB-1932','Spouse'); INSERT INTO dependent VALUES ('123456789','Michael','M','01-JAN-1978','Son'); INSERT INTO dependent VALUES ('123456789','Alice','F', '31-DEC-1978','Daughter'); INSERT INTO dependent VALUES ('123456789','Elizabeth','F','05-MAY-1957','Spouse'); INSERT INTO dependent VALUES ('123456789','John','M','01-JAN-1980','Son'); DELETE FROM works_on; INSERT INTO works_on(ESSN, PNO, HOURS) 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 works_on VALUES ('333445555', 1, 0.0); INSERT INTO works_on VALUES ('333445555', 30, 0.0);