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)

);

--Default date type

select sysdate from dual;

--SYSDATE

--06-FEB-20

--Employee Schema Date type

SELECT TO_DATE('12-08-1945', 'MM-DD-YYYY')

FROM dual;

-- Total 9; 1 done default date; 8 with to_date

--TO_DATE('12-08-1945', 'MM-DD-YYYY')

--TO_DATE('07-19-1958', 'MM-DD-YYYY')

--TO_DATE('06-20-1931', 'MM-DD-YYYY')

--TO_DATE('09-15-1952', 'MM-DD-YYYY')

--TO_DATE('07-31-1962', 'MM-DD-YYYY')

--TO_DATE('03-29-1959', 'MM-DD-YYYY')

--TO_DATE('11-10-1927', 'MM-DD-YYYY')

--TO_DATE('03-23-1977', 'MM-DD-YYYY')

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);

SELECT FNAME,BDATE from Employee;

DELETE FROM department;

--TO_DATE('12-08-1945', 'MM-DD-YYYY')

--TO_DATE('05-22-1978', 'MM-DD-YYYY')

--TO_DATE('01-01-1985', 'MM-DD-YYYY')

--TO_DATE('06-19-1971', 'MM-DD-YYYY')

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');

--TO_DATE('04-05-1976', 'MM-DD-YYYY')

--TO_DATE('10-25-1973', 'MM-DD-YYYY')

--TO_DATE('05-03-1948', 'MM-DD-YYYY')

--TO_DATE('02-29-1932', 'MM-DD-YYYY')

--TO_DATE('01-01-1978', 'MM-DD-YYYY')

--TO_DATE('12-31-1978', 'MM-DD-YYYY')

--TO_DATE('05-05-1957', 'MM-DD-YYYY')

--TO_DATE('01-01-1980', 'MM-DD-YYYY')

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');

SELECT DEPENDENT_NAME, BDATE from dependent;

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);

SELECT * FROM employee;

SELECT * FROM department;

SELECT * FROM project;

SELECT * FROM dept_locations;

SELECT * FROM dependent;

SELECT * FROM works_on;

SELECT owner, table_name FROM all_tables

where owner like 'SQL%'

order by owner;

1. Using SQL, specify the following queries on the from above database schema. Also, show the result of each query as it would apply to the database.

1. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
2. List the names of all employees who have a dependent with the same first name as themselves.
3. Find the names of all employees who are directly supervised by Franklin Wong.
4. For each project, list the project name and the total hours per week (by all employees) spent on the project.