SQL Company Queries

1. Create the tables for the COMPANY database in Oracle SQL. Make sure that all the constraints are enforced. The script given to you will not compile because of circular references. You need to modify the script to make it work. Provide a solution with the least number of changes to the schema given to you.

2. After creating the tables, populate the COMPANY database with the data. Again, the SQL insert statements can be downloaded from the Blackboard link company_insert.htm. Once again, the script given to you will not execute due to circular references. Provide a solution with fewest changes to the SQL statements provided to you. You should not make any changes to the schema created in problem 1 to solve this problem.

3. Specify the following queries on the from above database schema. Also, show the result of each query as it would apply to the database.

• Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
• List the names of all employees who have a dependent with the same first name as themselves.
• Find the names of all employees who are directly supervised by 'Franklin Wong'.
• For each project, list the project name and the total hours per week (by all employees) spent on the project.
• Retrieve the names of all employees who work on every project.
• Retrieve the names of all employees who do not work on any project.
• For each department, retrieve the department name and the average salary of all employees working in that department.
• Retrieve the average salary of all female employees.
• 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.
• List the last names of all department managers who have no dependents.
• 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.
• 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.

Starter SQL:

DROP TABLE employee CASCADE CONSTRAINTS;
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)
);

DROP TABLE department CASCADE CONSTRAINTS;
CREATE TABLE department (
dname varchar2(15) not null,
dnumber number(4),
mgrssn char(9) not null,
mgrstartdate date,
primary key (dnumber),
unique (dname),
foreign key (mgrssn) references employee(ssn)
);

DROP TABLE dept_locations CASCADE CONSTRAINTS;
CREATE TABLE dept_locations (
dnumber number(4),
dlocation varchar2(15),
primary key (dnumber,dlocation),
foreign key (dnumber) references department(dnumber)
);

DROP TABLE project CASCADE CONSTRAINTS;
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)
);

DROP TABLE works_on CASCADE CONSTRAINTS;
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)
);

DROP TABLE dependent CASCADE CONSTRAINTS;
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)
);

DELETE FROM employee;

INSERT INTO employee(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)

VALUES ('John', 'B', 'Smith',

'123456789', '09-JAN-1955', '731 Fondren, Houston,TX', 'M', 30000,

'333445555', 5);

INSERT INTO employee VALUES ('Franklin', 'T', 'Wong',

'333445555', TO_DATE('12-08-1945', 'MM-DD-YYYY'), '638 Voss, Houston,TX', 'M', 40000,

'888665555', 5);

INSERT INTO employee VALUES ('Alicia', 'J', 'Zelaya',

'999887777', TO_DATE('07-19-1958', 'MM-DD-YYYY'), '3321 Castle, Spring,TX', 'F', 25000,

'987654321', 4);

INSERT INTO employee VALUES ('Jennifer', 'S', 'Wallace',

'987654321', TO_DATE('06-20-1931', 'MM-DD-YYYY'), '291 Berry, Bellaire,TX', 'F', 43000,

'888665555', 4);

INSERT INTO employee VALUES ('Ramesh', 'K', 'Narayan',

'666884444', TO_DATE('09-15-1952', 'MM-DD-YYYY'), '975 Fire Oak, Humble,TX', 'M', 38000,

'333445555', 5);

INSERT INTO employee VALUES ('Joyce', 'A', 'English',

'453453453', TO_DATE('07-31-1962', 'MM-DD-YYYY'), '5631 Rice, Houston, TX', 'F', 25000,

'333445555', 5);

INSERT INTO employee VALUES ('Ahmad', 'V', 'Jabbar',

'987987987', TO_DATE('03-29-1959', 'MM-DD-YYYY'), '980 Dallas, Houston,TX', 'M', 25000,

'987654321', 4);

INSERT INTO employee VALUES ('James', 'E', 'Borg',

'888665555', TO_DATE('11-10-1927', 'MM-DD-YYYY'), '450 Stone, Houston,TX', 'M', 55000,

null, 1);

INSERT INTO employee VALUES ('New', 'V', 'Lost',

'777977977', TO_DATE('03-23-1977', 'MM-DD-YYYY'), 'NY,NY', 'M', 20000, '987654321', 4);


DELETE FROM department;

INSERT INTO department (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE)

VALUES ('Research', 5, '333445555', TO_DATE('05-22-1978', 'MM-DD-YYYY'));

INSERT INTO department VALUES ('Administration', 4, '987654321', TO_DATE('01-01-1985', 'MM-DD-YYYY'));

INSERT INTO department VALUES ('Headquarters', 1, '888665555', TO_DATE('06-19-1971', 'MM-DD-YYYY'));

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',TO_DATE('04-05-1976', 'MM-DD-YYYY'),'Daughter');

INSERT INTO dependent VALUES ('333445555','Theodore','M',TO_DATE('10-25-1973', 'MM-DD-YYYY'),'Son');

INSERT INTO dependent VALUES ('333445555','Joy','F',TO_DATE('05-03-1948', 'MM-DD-YYYY'),'Spouse');

INSERT INTO dependent VALUES ('987654321','Abner','M',TO_DATE('02-29-1932', 'MM-DD-YYYY'),'Spouse');

INSERT INTO dependent VALUES ('123456789','Michael','M',TO_DATE('01-01-1978', 'MM-DD-YYYY'),'Son');

INSERT INTO dependent VALUES ('123456789','Alice','F', TO_DATE('12-31-1978', 'MM-DD-YYYY'),'Daughter');

INSERT INTO dependent VALUES ('123456789','Elizabeth','F',TO_DATE('05-05-1957', 'MM-DD-YYYY'),'Spouse');

INSERT INTO dependent VALUES ('123456789','John','M',TO_DATE('01-01-1980', 'MM-DD-YYYY'),'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, 2.0);

INSERT INTO works_on VALUES ('333445555', 30, 2.0);