SQL> CREATE TABLE employee( FName char(20), MInit char(1), LName char(20), SSN NUMBER(9), PRIMARY KEY (SSN), BDate DATE, Address CHAR(100), Sex CHAR(1), Salary NUMBER(8), SuperSSN NUMBER(9), DNO NUMBER(2) ); Table created. SQL> CREATE TABLE department( DName char(20), DNumber NUMBER(2), MGRSSN NUMBER(9), MGRSTARTDATE DATE ); Table created. SQL> CREATE TABLE dept_locations( DNumber NUMBER(2), DLocation char(20), PRIMARY KEY (DNumber,DLocation) ); Table created. SQL> CREATE TABLE project( PName CHAR(20), PNumber NUMBER(2), PLocation char(20), DNum NUMBER(2), PRIMARY KEY (PNumber) ); Table created. SQL> CREATE TABLE works_on( ESSN NUMBER(9), PNo NUMBER(2), Hours NUMBER(2), PRIMARY KEY(ESSN,PNo) ); Table created. SQL> CREATE TABLE dependent( ESSN NUMBER(9), Dependent_Name char(20), Sex CHAR(1), BDate DATE, Relationship char(20), PRIMARY KEY (ESSN, Dependent_Name) ); Table created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('John','B','Smith',123456789,DATE '1965-01-09','731 Fondren, Houston, TX','M',30000,33344555,5); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Franklin','T','Wong',333445555,DATE '1955-12-08','638 Voss, Houston, TX','M',40000,888665555,5); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Alicia','J','Zelaya',999887777,DATE '1968-01-19','3321 Castle, Spring, TX','F',25000,987654321,4); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Jennifer','S','Wallace',987654321,DATE '1941-06-20','291 Berry, Bellaire, TX','F',43000,888665555,4); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Ramesh','K','Narayan',666884444,DATE '1962-09-15','975 Fire Oak, Humble, TX','M',38000,333445555,5); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Joyce','A','English',453453453,DATE '1972-07-31','5631 Rice, Houston, TX','F',25000,333445555,5); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('Ahmad','V','Jabbar',987987987,DATE '1969-03-29','980 Dallas, Houston, TX','M',25000,987654321,4); 1 row created. SQL> insert into employee (FName,MInit,LName,SSN,BDate,Address,Sex,Salary,SuperSSN,DNO) values ('James','E','Borg',888665555,DATE '1937-11-10','450 Stone, Houston, TX','M',55000,null,1); 1 row created. SQL> insert into dept_locations (DNumber,DLocation) values (1,'Houston'); 1 row created. SQL> insert into dept_locations (DNumber,DLocation) values (4,'Stafford'); 1 row created. SQL> insert into dept_locations (DNumber,DLocation) values (5,'Bellaire'); 1 row created. SQL> insert into dept_locations (DNumber,DLocation) values (5,'Sugarland'); 1 row created. SQL> insert into dept_locations (DLocation) values ('Houston'); insert into dept_locations (DLocation) values ('Houston') * ERROR at line 1: ORA-01400: cannot insert NULL into ("EPCR2F"."DEPT_LOCATIONS"."DNUMBER") SQL> insert into department (DName,DNumber,MGRSSN,MgrStartDate) values ('Research',5,333445555, DATE '1988-05-22'); 1 row created. SQL> insert into department (DName,DNumber,MGRSSN,MgrStartDate) values ('Administration',4,987654321, DATE '1995-01-01'); 1 row created. SQL> insert into department (DName,DNumber,MGRSSN,MgrStartDate) values ('Headquarters',1,888665555, DATE '1981-06-19'); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (123456789,1,32.5); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (123456789,2,7.5); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (666884444,3,40.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (453453453,1,20.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (453453453,2,20.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (333445555,2,10.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (333445555,3,10.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (333445555,10,10.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (333445555,20,10.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (999887777,30,30.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (999887777,10,10.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (987987987,10,35.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (987987987,30,5.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (987654321,30,20.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (987654321,20,15.0); 1 row created. SQL> insert into works_on (ESSN,PNO,HOURS) values (888665555,20,null); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (333445555,'Alice','F', DATE '1986-04-05','DAUGHTER'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (333445555,'Theodore','M', DATE '1983-10-25','SON'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (333445555,'Joy','F', DATE '1958-05-03','SPOUSE'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (987654321,'Abner','M', DATE '1942-02-28','SPOUSE'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (123456789,'Michael','M', DATE '1988-01-04','SON'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (123456789,'Alice','F', DATE '1988-12-30','DAUGHTER'); 1 row created. SQL> insert into dependent (ESSN,Dependent_Name,Sex,BDate,Relationship) values (123456789,'Elizabeth','F', DATE '1967-05-05','SPOUSE'); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('ProductX',1,'Bellaire',5); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('ProductY',2,'Sugarland',5); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('ProductZ',3,'Houston',5); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('Computerization',10,'Stafford',4); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('Reorganization',20,'Houston',1); 1 row created. SQL> insert into project (PName,PNumber,Plocation,DNUM) values ('Newbenefits',30,'Stafford',4); 1 row created. SQL> select * from employee; FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- Franklin T Wong 333445555 08-DEC-55 638 Voss, Houston, TX M 40000 888665555 5 John B Smith 123456789 09-JAN-65 731 Fondren, Houston, TX FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- M 30000 33344555 5 Alicia J Zelaya 999887777 19-JAN-68 3321 Castle, Spring, TX F 25000 987654321 4 FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- Jennifer S Wallace 987654321 20-JUN-41 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh K Narayan 666884444 15-SEP-62 FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 31-JUL-72 5631 Rice, Houston, TX FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- F 25000 333445555 5 Ahmad V Jabbar 987987987 29-MAR-69 980 Dallas, Houston, TX M 25000 987654321 4 FNAME M LNAME SSN BDATE -------------------- - -------------------- ---------- --------- ADDRESS -------------------------------------------------------------------------------- S SALARY SUPERSSN DNO - ---------- ---------- ---------- James E Borg 888665555 10-NOV-37 450 Stone, Houston, TX M 55000 1 8 rows selected. SQL> select * from department; DNAME DNUMBER MGRSSN MGRSTARTD -------------------- ---------- ---------- --------- Research 5 333445555 22-MAY-88 Administration 4 987654321 01-JAN-95 Headquarters 1 888665555 19-JUN-81 SQL> select * from dept_locations; DNUMBER DLOCATION ---------- -------------------- 1 Houston 4 Stafford 5 Bellaire 5 Sugarland SQL> select * from project; PNAME PNUMBER PLOCATION DNUM -------------------- ---------- -------------------- ---------- ProductX 1 Bellaire 5 ProductY 2 Sugarland 5 ProductZ 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford 4 6 rows selected. SQL> select * from works_on; ESSN PNO HOURS ---------- ---------- ---------- 123456789 1 33 123456789 2 8 666884444 3 40 453453453 1 20 453453453 2 20 333445555 2 10 333445555 3 10 333445555 10 10 333445555 20 10 999887777 30 30 999887777 10 10 ESSN PNO HOURS ---------- ---------- ---------- 987987987 10 35 987987987 30 5 987654321 30 20 987654321 20 15 888665555 20 16 rows selected. SQL> select * from dependent; ESSN DEPENDENT_NAME S BDATE RELATIONSHIP ---------- -------------------- - --------- -------------------- 333445555 Alice F 05-APR-86 DAUGHTER 333445555 Theodore M 25-OCT-83 SON 333445555 Joy F 03-MAY-58 SPOUSE 987654321 Abner M 28-FEB-42 SPOUSE 123456789 Michael M 04-JAN-88 SON 123456789 Alice F 30-DEC-88 DAUGHTER 123456789 Elizabeth F 05-MAY-67 SPOUSE 7 rows selected. ------------------------------------------ a) SQL> SELECT Fname, LName FROM employee, works_on WHERE Dno = 5 AND Ssn = Essn AND Hours > 10 AND Pno IN (SELECT pnumber FROM project WHERE Pname = 'ProductX'); 2 FNAME LNAME -------------------- -------------------- John Smith Joyce English b) SQL> SELECT Fname, LName FROM employee, dependent WHERE Fname = Dependent_Name AND Ssn = Essn; no rows selected c) SQL> SELECT Fname, Lname FROM employee WHERE Supersnn IN (Select Ssn FROM employee WHERE Fname = 'Franklin' AND Lname ='Wong'); FNAME LNAME -------------------- -------------------- Ramesh Narayan Joyce English d) SQL> SELECT Pname, SUM(Hours) FROM project, works_on WHERE Pnumber = Pno; PNAME SUM(HOURS) -------------------- ---------- ProductX 52.5 ProductY 57.5 ProductZ 50 Computerization 55 Reorganization 25 Newbenefits 55 e) SQL> SELECT Fname, LName FROM employee WHERE Ssn IN (SELECT Essn FROM works_on WHERE COUNT(Essn) IN (SELECT COUNT(*) FROM project)); no rows selected f) SQL> SELECT Fname, Lname FROM employee WHERE NOT EXISTS (SELECT * FROM works_on WHERE Ssn = Essn); no rows selected g) SQL> SELECT Dname,AVG(Salary) FROM employee,department where dno = dnumber; DNAME AVG(SALARY) ------------------- ----------- Research 33250 Administration 31000 Headquarters 55000 h) SQL> SELECT AVG(Salary) FROM employee WHERE Sex = 'F'; AVG(SALARY) ----------- 31000 i) SQL> SELECT Fname, Minit, Lname from employee where SSN in (SELECT ESSN from works_on where Pno in (SELECT UNIQUE Pnumber from Project,dept_locations WHERE Plocation != Dlocation AND Plocation = 'Houston' AND Dnum = Dnumber)); FNAME M LNAME -------------------- - -------------------- Franklin T Wong Ramesh K Narayan j) SQL> SELECT E.Ssn FROM employee E, employee M WHERE E.Ssn = M.Superssn AND E.Ssn IN ((SELECT Ssn FROM employee WHERE NOT EXISTS (SELECT * FROM Dependent WHERE Ssn = Essn))); SSN ---------- 888665555 888665555