-- drop tables

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; DROP TABLE DEPARTMENT CASCADE CONSTRAINTS; DROP TABLE PROJECT CASCADE CONSTRAINTS; DROP TABLE ASSIGNMENT CASCADE CONSTRAINTS; DROP TABLE DEPENDENT CASCADE CONSTRAINTS;

-- create and populate tables

CREATE TABLE DEPARTMENT ( DName VARCHAR(20), DNumber NUMBER(1), PRIMARY KEY (DNumber) );

INSERT INTO DEPARTMENT VALUES ('Management', 1); INSERT INTO DEPARTMENT VALUES ('Research', 2); INSERT INTO DEPARTMENT VALUES ('Engineering', 3); INSERT INTO DEPARTMENT VALUES ('Marketing', 4);

CREATE TABLE EMPLOYEE ( FirstName VARCHAR(20), LastName VARCHAR(20), ID CHAR(9), Gender CHAR(1), Salary NUMBER(6), DNo NUMBER(1), SupervisorID CHAR(9), PRIMARY KEY (ID), FOREIGN KEY (SupervisorID) REFERENCES EMPLOYEE (ID),

FOREIGN KEY (DNo) REFERENCES DEPARTMENT (DNumber) );

INSERT INTO EMPLOYEE VALUES ('Suzanne', 'Taylor', '123456789', 'F', 154000, 1, NULL); INSERT INTO EMPLOYEE VALUES ('John', 'Washington', '888666555', 'M', 120000, 1, '123456789'); INSERT INTO EMPLOYEE VALUES ('Joshua', 'Wallace', '987654321', 'M',125000, 1, '123456789'); INSERT INTO EMPLOYEE VALUES ('Amelia', 'Zelaya', '997303033', 'F', 122000, 1, '123456789'); INSERT INTO EMPLOYEE VALUES ('Salman', 'Ahmed', '987987987', 'M', 98000, 2, '888666555'); INSERT INTO EMPLOYEE VALUES ('Jim', 'Wong', '333446655', 'M', 90000, 2, '888666555'); INSERT INTO EMPLOYEE VALUES ('Maria', 'Ramirez', '707011333', 'F', 85000, 3, '888666555'); INSERT INTO EMPLOYEE VALUES ('Christine', 'Morita', '111222233', 'F', 85000, 3, '987654321'); INSERT INTO EMPLOYEE VALUES ('Ramesh', 'Narayanan', '689689689', 'M', 88000, 3, '987654321'); INSERT INTO EMPLOYEE VALUES ('Joanne', 'Aoki', '543212345', 'F', 54000, 4, '997303033'); INSERT INTO EMPLOYEE VALUES ('Steve', 'Jones', '246824688', 'M', 60000, 4, '997303033'); INSERT INTO EMPLOYEE VALUES ('Jenny', 'Tutone', '867530999', 'F', 54000, 4, '997303033');

CREATE TABLE PROJECT ( PName VARCHAR(20), PNumber NUMBER(2), PLocation VARCHAR(20),

PRIMARY KEY (PNumber) );

INSERT INTO PROJECT VALUES ('Automation', 11, 'Houston'); INSERT INTO PROJECT VALUES ('Training', 22, 'Denver'); INSERT INTO PROJECT VALUES ('Public Relations', 33, 'New York'); INSERT INTO PROJECT VALUES ('Compliance', 44, 'New York'); INSERT INTO PROJECT VALUES ('ProductA', 55, 'New York'); INSERT INTO PROJECT VALUES ('ProductB', 66, 'Chicago'); INSERT INTO PROJECT VALUES ('ProductC', 77, 'Chicago');

CREATE TABLE ASSIGNMENT ( EID CHAR(9), PNo NUMBER(2), Hours NUMBER(2,0), PRIMARY KEY (EID, PNo),

FOREIGN KEY (EID) REFERENCES EMPLOYEE (ID),

FOREIGN KEY (PNo) REFERENCES PROJECT(PNumber) );

INSERT INTO ASSIGNMENT VALUES ('123456789', 33, 10); INSERT INTO ASSIGNMENT VALUES ('888666555', 33, 20); INSERT INTO ASSIGNMENT VALUES ('987654321', 33, 16); INSERT INTO ASSIGNMENT VALUES ('997303033', 33, 5); INSERT INTO ASSIGNMENT VALUES ('123456789', 22, 5); INSERT INTO ASSIGNMENT VALUES ('888666555', 22, 15); INSERT INTO ASSIGNMENT VALUES ('333446655', 22, 8); INSERT INTO ASSIGNMENT VALUES ('246824688', 22, 10); INSERT INTO ASSIGNMENT VALUES ('867530999', 22, 5); INSERT INTO ASSIGNMENT VALUES ('987654321', 77, 10); INSERT INTO ASSIGNMENT VALUES ('987987987', 77, 20); INSERT INTO ASSIGNMENT VALUES ('333446655', 77, 45); INSERT INTO ASSIGNMENT VALUES ('987654321', 55, 5); INSERT INTO ASSIGNMENT VALUES ('707011333', 55, 5); INSERT INTO ASSIGNMENT VALUES ('111222233', 55, 10); INSERT INTO ASSIGNMENT VALUES ('543212345', 55, 12); INSERT INTO ASSIGNMENT VALUES ('867530999', 55, 5); INSERT INTO ASSIGNMENT VALUES ('333446655', 55, 8); INSERT INTO ASSIGNMENT VALUES ('987654321', 66, 5); INSERT INTO ASSIGNMENT VALUES ('987987987', 66, 5); INSERT INTO ASSIGNMENT VALUES ('333446655', 66, 5); INSERT INTO ASSIGNMENT VALUES ('707011333', 66, 5); INSERT INTO ASSIGNMENT VALUES ('123456789', 66, 5);

CREATE TABLE DEPENDENT ( EID CHAR(9), FirstName VARCHAR(20), Gender CHAR(1), Relationship VARCHAR(8),

PRIMARY KEY (EID, FirstName),

FOREIGN KEY (EID) REFERENCES EMPLOYEE (ID) );

INSERT INTO DEPENDENT VALUES ('333446655', 'Alice', 'F', 'Daughter'); INSERT INTO DEPENDENT VALUES ('333446655', 'Jim', 'M','Son'); INSERT INTO DEPENDENT VALUES ('333446655', 'Joy', 'F', 'Spouse'); INSERT INTO DEPENDENT VALUES ('707011333', 'Albergto', 'M', 'Son'); INSERT INTO DEPENDENT VALUES ('707011333', 'Luisa', 'F', 'Daughter'); INSERT INTO DEPENDENT VALUES ('123456789', 'Jack', 'M', 'Son'); INSERT INTO DEPENDENT VALUES ('123456789', 'Alice', 'F', 'Daughter'); INSERT INTO DEPENDENT VALUES ('123456789', 'Paul', 'M', 'Spouse'); INSERT INTO DEPENDENT VALUES ('543212345', 'Michael', 'M', 'Spouse'); INSERT INTO DEPENDENT VALUES ('689689689', 'Priya', 'F', 'Daughter'); INSERT INTO DEPENDENT VALUES ('246824688', 'Sandy', 'F', 'Daughter');

-- display contents of tables

SELECT * FROM EMPLOYEE; SELECT * FROM DEPARTMENT; SELECT * FROM PROJECT; SELECT * FROM ASSIGNMENT; SELECT * FROM DEPENDENT;

COMMIT;

1. List the full names of all employees who are directly supervised by a male employee.

2. Display the average salary of all employees in the Engineering department.

3. Give a list of the IDs and last names of all employees who are assigned to some project located in Chicago, ordered alphabetically by the last name.

4. For each project, display the project number, the project name, and the total hours spent on that project. List the projects from the one with the largest total hours to the smallest. (Note: It is okay to have either NULLs or zeroes in the query output for those projects that have no one assigned to them, but those projects should be included.)