Skip to content

Instantly share code, notes, and snippets.

@lopezm1

lopezm1/ddl.sql

Last active August 29, 2015 14:11
Show Gist options
  • Save lopezm1/45938e1aaa333640508d to your computer and use it in GitHub Desktop.
Save lopezm1/45938e1aaa333640508d to your computer and use it in GitHub Desktop.
Download ZIP
SQL Database -- Practice using function calls and query calls
Raw
ddl.sql
/*
** ----------------------------------------------------------------------------
** script to create the employee table
** --------------------------------------------------------------------------*/
CREATE TABLE employee (
fName VARCHAR2(20)NOT NULL,
mInit CHAR(1),
lName VARCHAR2(20) NOT NULL,
ssn VARCHAR2(9)PRIMARY KEY,
bDate DATE NOT NULL,
address VARCHAR(50) NOT NULL,
sex CHAR(1) NOT NULL CHECK (sex = 'F' OR sex = 'M'),
salary FLOAT NOT NULL CHECK (salary > 0),
superSSN VARCHAR2(9),
dNo INTEGER,
hireDate DATE NOT NULL,
terminateDate DATE
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** add foreign key for the employee table
** --------------------------------------------------------------------------*/
ALTER TABLE employee
ADD (FOREIGN KEY (superSSN) REFERENCES employee(ssn) ON DELETE SET NULL);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the department table
** --------------------------------------------------------------------------*/
CREATE TABLE department (
dName VARCHAR2(20) NOT NULL,
dNumber INTEGER PRIMARY KEY,
mgrSSN VARCHAR2(9),
mgrStartDate Date NOT NULL,
FOREIGN KEY (mgrSSN) REFERENCES employee(ssn) ON DELETE SET NULL
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the dept_location table
** --------------------------------------------------------------------------*/
CREATE TABLE deptLocation (
dNumber INTEGER,
dLocation VARCHAR2(20),
PRIMARY KEY (dNumber,dLocation),
FOREIGN KEY(dNumber) REFERENCES department(dNumber)
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** add foreign key for the employee table
** --------------------------------------------------------------------------*/
ALTER TABLE employee
ADD (FOREIGN KEY (dNo) REFERENCES department(dNumber) ON DELETE SET NULL);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the project table
** --------------------------------------------------------------------------*/
CREATE TABLE project (
pName VARCHAR(20) NOT NULL,
pNumber INTEGER PRIMARY KEY,
pLocation VARCHAR(50) NOT NULL,
dNum INTEGER ,
budgetedHours FLOAT NOT NULL CHECK(budgetedHours > 0),
FOREIGN KEY(dNum) REFERENCES department(dNumber) ON DELETE SET NULL
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the worksOn table
** --------------------------------------------------------------------------*/
CREATE TABLE worksOn (
eSSN VARCHAR2(9),
pNo INTEGER ,
hoursSpent FLOAT NOT NULL CHECK(hoursSpent >= 0),
PRIMARY KEY (eSSN,pNo),
FOREIGN KEY(eSSN) REFERENCES employee(ssn) ON DELETE CASCADE,
FOREIGN KEY(pNo) REFERENCES project(pNumber) ON DELETE CASCADE
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the dependent table
** --------------------------------------------------------------------------*/
CREATE TABLE dependent (
eSSN VARCHAR2(9),
dependentName VARCHAR2(20),
sex CHAR(1) NOT NULL CHECK (sex = 'F' OR sex = 'M'),
bDate DATE NOT NULL,
relationship VARCHAR2(20) NOT NULL,
PRIMARY KEY(eSSN,dependentName),
FOREIGN KEY(eSSN) REFERENCES employee(ssn) ON DELETE CASCADE
);
SHOW ERRORS;
/*
** ----------------------------------------------------------------------------
** script to create the consultant table
** --------------------------------------------------------------------------*/
CREATE TABLE consultant (
eSSN VARCHAR2(9),
consultantCompan VARCHAR2(20),
contractCost FLOAT NOT NULL CHECK(contractCost >=0),
PRIMARY KEY(eSSN, consultantCompany),
FOREIGN KEY(eSSN) REFERENCES employee(ssn) ON DELETE CASCADE
);
SHOW ERRORS;
Raw
drop.sql
/*
** ----------------------------------------------------------------------------
** script to drop all objects in the database
** --------------------------------------------------------------------------*/
-- Tables
/**/
DROP TABLE worksOn purge;
DROP TABLE dependent purge;
DROP TABLE deptLocation purge;
DROP TABLE project purge;
DROP TABLE employee CASCADE CONSTRAINTS purge;
DROP TABLE department purge;
DROP TABLE consultant purge;
-- Views for query
DROP VIEW projectHours;
DROP VIEW projectExEmp;
DROP VIEW empDependentCount;
Raw
insert.sql
/*
** ----------------------------------------------------------------------------
** script to insert data into the employee table
** --------------------------------------------------------------------------*/
INSERT INTO employee VALUES('John','B','Smith','123456789','09-JAN-75','731 Fondren, New York City, NY','M',30000,NULL,NULL, '20-JAN-10',NULL);
INSERT INTO employee VALUES('Ben','T','Franklin','333445555','08-DEC-85','638 Voss, Houston, TX','M',40000,NULL,NULL, '21-May-10',NULL);
INSERT INTO employee VALUES('Alicia','J','Olivas','999887777','19-JUN-85','3321 Castle, Fontana, CA','F',25000,NULL,NULL, '20-JAN-10',NULL);
INSERT INTO employee VALUES('Brittany','S','Wallace','987654321','20-JUN-81','291 Berry, Dallas, TX','F',43000,NULL,NULL, '20-JAN-11',NULL);
INSERT INTO employee VALUES('Ramesh','K','Long','666884444','15-SEP-92','975 Fire Oak, San Antonio, TX','M',38000,NULL,NULL, '20-JAN-11',NULL);
INSERT INTO employee VALUES('Sergio','J','Lopez','112233445','25-FEB-89','102347 E Pantera, Mesa, AZ','M',65000, NULL,NULL, '05-MAR-14',NULL);
INSERT INTO employee VALUES('Joyce','A','Orozco','453453453','31-JUL-82','5631 Rice, Newark, NJ','F',25000,NULL,NULL, '20-JAN-11',NULL);
INSERT INTO employee VALUES('Ahmad','V','Akkad','987987987','29-MAR-89','980 Dallas, LA, CA','M',25000,NULL,NULL, '20-JAN-12',NULL);
INSERT INTO employee VALUES('James','E','Adams','888665555','10-NOV-87','450 Stone, Hollywood, CA','M',55000, NULL,NULL, '20-JAN-12',NULL);
INSERT INTO employee VALUES('Miguel','A','Lopez','000079288','05-FEB-94','102347 E Pantera, Mesa, AZ','M',75000, NULL,NULL, '05-JAN-14',NULL);
/*
** ----------------------------------------------------------------------------
** update superSSN in the employee table
** --------------------------------------------------------------------------*/
UPDATE employee SET superSSN = '333445555' WHERE ssn='123456789';
UPDATE employee SET superSSN = '888665555' WHERE ssn='333445555';
UPDATE employee SET superSSN = '987654321' WHERE ssn='999887777';
UPDATE employee SET superSSN = '888665555' WHERE ssn='987654321';
UPDATE employee SET superSSN = '333445555' WHERE ssn='666884444';
UPDATE employee SET superSSN = '000079288' WHERE ssn='112233445';
UPDATE employee SET superSSN = '333445555' WHERE ssn='453453453';
UPDATE employee SET superSSN = '987654321' WHERE ssn='987987987';
/*
** ----------------------------------------------------------------------------
** script to insert data into the department table
** --------------------------------------------------------------------------*/
INSERT INTO department VALUES('Research',5,'333445555','22-MAY-78');
INSERT INTO department VALUES('Administration',4,'987654321','01-JAN-85');
INSERT INTO department VALUES('Headquarters',1,'888665555','19-JUN-71');
INSERT INTO department VALUES('Software Development',2,'000079288','05-JAN-14');
/*
** ----------------------------------------------------------------------------
** update dNo in the employee table
** --------------------------------------------------------------------------*/
UPDATE employee SET dNo = 5 WHERE ssn='123456789';
UPDATE employee SET dNo = 5 WHERE ssn='333445555';
UPDATE employee SET dNo = 4 WHERE ssn='999887777';
UPDATE employee SET dNo = 4 WHERE ssn='987654321';
UPDATE employee SET dNo = 5 WHERE ssn='666884444';
UPDATE employee SET dNo = 2 WHERE ssn='112233445';
UPDATE employee SET dNo = 5 WHERE ssn='453453453';
UPDATE employee SET dNo = 4 WHERE ssn='987987987';
UPDATE employee SET dNo = 1 WHERE ssn='888665555';
UPDATE employee SET dNo = 2 WHERE ssn='000079288';
/*
** ----------------------------------------------------------------------------
** script to insert data into the dept_location table
** --------------------------------------------------------------------------*/
INSERT INTO deptLocation VALUES(1,'Houston');
INSERT INTO deptLocation VALUES(4,'Scottsdale');
INSERT INTO deptLocation VALUES(5,'New York');
INSERT INTO deptLocation VALUES(5,'San Francisco');
INSERT INTO deptLocation VALUES(5,'LA');
INSERT INTO deptLocation VALUES(2,'Phoenix');
/*
** ----------------------------------------------------------------------------
** script to insert data into the project table
** --------------------------------------------------------------------------*/
INSERT INTO project VALUES('Automation',1,'LA',5,20);
INSERT INTO project VALUES('Search Engine',2,'San Francisco',5,30);
INSERT INTO project VALUES('Framework Update',3,'Houston',5,40);
INSERT INTO project VALUES('Computerization',10,'Scottsdale',4,120);
INSERT INTO project VALUES('Resturcture',20,'Houston',1,130);
INSERT INTO project VALUES('Global Views',30,'New York',4,140);
INSERT INTO project VALUES('Mobile Enhancement',15,'Phoenix',2,500);
/*
** ----------------------------------------------------------------------------
** script to insert data into the worksOn table
** --------------------------------------------------------------------------*/
INSERT INTO worksOn VALUES('123456789',1,32.50);
INSERT INTO worksOn VALUES('123456789',2,7.50);
INSERT INTO worksOn VALUES('666884444',3,40.0);
INSERT INTO worksOn VALUES('112233445',15,300.0);
INSERT INTO worksOn VALUES('453453453',1,20.0);
INSERT INTO worksOn VALUES('453453453',2,20.0);
INSERT INTO worksOn VALUES('333445555',2,10.0);
INSERT INTO worksOn VALUES('333445555',3,10.0);
INSERT INTO worksOn VALUES('333445555',10,10.0);
INSERT INTO worksOn VALUES('333445555',20,10.0);
INSERT INTO worksOn VALUES('999887777',30,30.0);
INSERT INTO worksOn VALUES('999887777',10,10.0);
INSERT INTO worksOn VALUES('987987987',10,35.0);
INSERT INTO worksOn VALUES('987987987',30,5.0);
INSERT INTO worksOn VALUES('987654321',30,20.0);
INSERT INTO worksOn VALUES('987654321',20,15.0);
INSERT INTO worksOn VALUES('888665555',20,12.0);
INSERT INTO worksOn VALUES('123456789',3,0);
INSERT INTO worksOn VALUES('000079288',15,150);
/*
** ----------------------------------------------------------------------------
** script to insert data into the dependent table
** --------------------------------------------------------------------------*/
INSERT INTO dependent VALUES('333445555','Caitlin','F','05-APR-86','DAUGHTER');
INSERT INTO dependent VALUES('333445555','Zoe','M','25-OCT-99','SON');
INSERT INTO dependent VALUES('333445555','Joy','F','03-MAY-88','SPOUSE');
INSERT INTO dependent VALUES('112233445','Alicia','F','21-AUG-94','SPOUSE');
INSERT INTO dependent VALUES('987654321','Rob','M','21-AUG-84','SPOUSE');
INSERT INTO dependent VALUES('123456789','Michael','M','01-JAN-99','SON');
INSERT INTO dependent VALUES('123456789','Katherine','F','31-DEC-98','DAUGHTER');
INSERT INTO dependent VALUES('123456789','Elizabeth','F','05-MAY-87','SPOUSE');
INSERT INTO dependent VALUES('000079288','Victoria','F','13-DEC-12','DAUGHTER');
INSERT INTO dependent VALUES('000079288','Josie','F','05-MAY-95','SPOUSE');
/*
** ----------------------------------------------------------------------------
** script to insert data into the consultant table
** --------------------------------------------------------------------------*/
INSERT INTO consultant VALUES('123456789','Sogeti','30000.00');
INSERT INTO consultant VALUES('112233445','Sogeti','50000.00');
INSERT INTO consultant VALUES('666884444','Consultants R Us','15000.00');
INSERT INTO consultant VALUES('888665555','IT Partners','5000.00');
Raw
query.sql
/*
** ----------------------------------------------------------------------------
** Over-Budget
** Retrieve information for each project that consumes more work-hours
** than budgeted hours.
** --------------------------------------------------------------------------*/
CREATE OR REPLACE VIEW projectHours AS
SELECT pNo, sum(hoursSpent) AS totalHours
FROM worksOn
GROUP BY pNO
HAVING sum(hoursSpent) > (SELECT budgetedHours FROM project WHERE pNumber = pNo);
SELECT PH.pNo, P.pName, P.pLocation, D.dName, P.budgetedHours, PH.totalHours
FROM project P, department D, projectHours PH
WHERE PH.pNo = P.pNumber AND P.dNum = D.dNumber;
/*
** ----------------------------------------------------------------------------
** Projects with External Employees
** Retrieve information on each project that has at least on employee
** assigned to it who does not work in the project’s controlling department.
** --------------------------------------------------------------------------*/
CREATE OR REPLACE VIEW projectExEmp AS
SELECT W.pNo, count(W.eSSN) AS empCount
FROM worksOn W, employee E
WHERE W.eSSN = E.ssn AND E.dNo <> (SELECT dNum FROM project WHERE pNumber = W.pNo)
GROUP BY W.pNO;
SELECT PE.pNo, P.pName, P.pLocation, D.dName, PE.empCount
FROM projectExEmp PE, project P, department D
WHERE PE.pNo = P.pNumber AND P.dNum = D.dNumber;
/*
** ----------------------------------------------------------------------------
** Immediate Supervisors
** Retrieve information on each employee’s immediate supervisor.
** --------------------------------------------------------------------------*/
SELECT E.fName, E.lName, ECopy.fName, ECopy.lName
FROM employee E, employee ECopy
WHERE E.superSSN = ECopy.ssn;
/*
** ----------------------------------------------------------------------------
** Unsupervised Employees
** Retrieve information for each employee who does not have a supervisor.
** --------------------------------------------------------------------------*/
SELECT E.fName, E.lName, E.ssn, D.dName
FROM employee E, department D
WHERE E.superSSN IS NULL AND E.dNo = D.dNumber;
/*
** ----------------------------------------------------------------------------
** Controlling Projects List
** Retrieve information for each project controlled by a department.
** List is sorted in ascending order of project number.
** --------------------------------------------------------------------------*/
SELECT P.pName, P.pNumber, D.dLocation
FROM project P, deptLocation D
WHERE p.dNum = d.dNumber
ORDER BY pNumber;
/*
** ----------------------------------------------------------------------------
** Research Department Employees
** Retrieve information on employees working in the 'Research' department.
** --------------------------------------------------------------------------*/
SELECT E.fName, E.lName, E.mInit, E.address
FROM employee E, department D
WHERE E.dNo = d.dNumber AND D.dName = 'Research';
/*
** ----------------------------------------------------------------------------
** Projects at Scottsdale
** Retrieve information for each project located at 'Scottsdale'.
** --------------------------------------------------------------------------*/
SELECT P.pNumber, D.dNumber, E.lName, E.address, E.bDate
FROM project P, department D, employee E, worksOn W
WHERE P.pLocation = 'Scottsdale' AND P.dNum = D.dNumber AND P.pNumber = W.pNo AND W.eSSN = e.ssn
ORDER BY pNumber,lName;
/*
** ----------------------------------------------------------------------------
** All Projects Done by Smith
** Retrieve all the projects that 'Smith'
** either as a worker or manager, has worked on.
** --------------------------------------------------------------------------*/
SELECT DISTINCT W.pNo
FROM worksOn W, employee E
WHERE (W.eSSN = E.ssn AND E.lName = 'Smith') OR
EXISTS (SELECT * FROM project P, department D
WHERE W.pNo = P.pNumber AND P.dNum = D.dNumber AND D.mgrSSN = E.ssn AND E.lName = 'Smith');
/*
** ----------------------------------------------------------------------------
** Employee with >=2 Dependents
** List names of all employees with two or more dependents
** --------------------------------------------------------------------------*/
CREATE OR REPLACE VIEW empDependentCount AS
SELECT eSSN, COUNT(dependentName) AS dCount
FROM dependent
GROUP BY eSSN
HAVING COUNT(dependentName) >=2;
SELECT fName, mInit, lName
FROM empDependentCount EC, employee E
WHERE EC.eSSN = E.ssn;
/*
** ----------------------------------------------------------------------------
** Employee with no Dependents
** Retrieve the names of employees who have no dependents.
** --------------------------------------------------------------------------*/
SELECT fName, mInit, lName
FROM employee E
WHERE E.ssn NOT IN (SELECT eSSN FROM dependent);
/*
** ----------------------------------------------------------------------------
** Manager with at least one dependent
** List name of all managers with at least one dependent
** --------------------------------------------------------------------------*/
SELECT fName, mInit, lName
FROM department D, employee E
WHERE D.mgrSSN = E.ssn AND D.mgrSSN IN (SELECT eSSN FROM dependent);
/*
** ----------------------------------------------------------------------------
** Employees on all projects controlled by department number 5
** Retrieve information on all employees working for department number 5.
** --------------------------------------------------------------------------*/
SELECT DISTINCT fName, mInit, lName
FROM employee E, worksOn W, project P
WHERE W.pNo = P.pNumber AND P.dNum = 5 AND W.eSSN = E.ssn;
/*
** ----------------------------------------------------------------------------
** Sums the total cost that the company is spending on contracts
** --------------------------------------------------------------------------*/
SELECT sum(contractCost)
FROM consultant C, employee E
WHERE E.ssn = C.eSSN;
/*
** ----------------------------------------------------------------------------
** Employees contracted from the company Sogeti
** Retrieve information on all employees working for the company Sogeti
** --------------------------------------------------------------------------*/
SELECT E.fName, E.mInit, E.lName
FROM consultant C, employee E
WHERE E.ssn = C.eSSN AND C.consultantCompany = 'Sogeti';
/*
** ----------------------------------------------------------------------------
** Find the supervisor of the employee 'Sergio J Lopez'
** Retrieve all information on supervisor
** --------------------------------------------------------------------------*/
SELECT *
FROM employee
WHERE ssn = (SELECT superSSN FROM employee WHERE ssn = '112233445' AND
fName = 'Sergio' AND lName = 'Lopez' AND mInit = 'J');
/*
** ----------------------------------------------------------------------------
** Find the employees who have dependents
** Retrieve the number of dependents that each employee has
** --------------------------------------------------------------------------*/
SELECT E.fName, E.lName, count(D.eSSN)
FROM employee E
INNER JOIN dependent D
ON E.ssn = D.eSSN
GROUP BY E.fName, E.lName
ORDER BY E.lName;
Raw
results.txt
CREATE TABLE succeeded.
No Errors.
ALTER TABLE employee succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
ALTER TABLE employee succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
CREATE TABLE succeeded.
No Errors.
DESC employee
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FNAME NOT NULL VARCHAR2(20)
MINIT CHAR(1)
LNAME NOT NULL VARCHAR2(20)
SSN NOT NULL VARCHAR2(9)
BDATE NOT NULL DATE
ADDRESS NOT NULL VARCHAR2(50)
SEX NOT NULL CHAR(1)
SALARY NOT NULL FLOAT(126)
SUPERSSN VARCHAR2(9)
DNO NUMBER
HIREDATE NOT NULL DATE
TERMINATEDATE DATE
DESC department
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DNAME NOT NULL VARCHAR2(20)
DNUMBER NOT NULL NUMBER
MGRSSN VARCHAR2(9)
MGRSTARTDATE NOT NULL DATE
DESC deptLocation
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DNUMBER NOT NULL NUMBER
DLOCATION NOT NULL VARCHAR2(20)
DESC project
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PNAME NOT NULL VARCHAR2(20)
PNUMBER NOT NULL NUMBER
PLOCATION NOT NULL VARCHAR2(50)
DNUM NUMBER
BUDGETEDHOURS NOT NULL FLOAT(126)
DESC worksOn
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ESSN NOT NULL VARCHAR2(9)
PNO NOT NULL NUMBER
HOURSSPENT NOT NULL FLOAT(126)
DESC dependent
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ESSN NOT NULL VARCHAR2(9)
DEPENDENTNAME NOT NULL VARCHAR2(20)
SEX NOT NULL CHAR(1)
BDATE NOT NULL DATE
RELATIONSHIP NOT NULL VARCHAR2(20)
DESC consultant
Name Null Type
-------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ESSN NOT NULL VARCHAR2(9)
CONSULTANTCOMPANY NOT NULL VARCHAR2(20)
CONTRACTCOST NOT NULL FLOAT(126)
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO HIREDATE TERMINATEDATE
-------------------- ----- -------------------- --------- ------------------------- -------------------------------------------------- --- ---------------------- --------- ---------------------- ------------------------- -------------------------
John B Smith 123456789 09-JAN-75 731 Fondren, New York City, NY M 30000 333445555 5 20-JAN-10
Ben T Franklin 333445555 08-DEC-85 638 Voss, Houston, TX M 40000 888665555 5 21-MAY-10
Alicia J Olivas 999887777 19-JUN-85 3321 Castle, Fontana, CA F 25000 987654321 4 20-JAN-10
Brittany S Wallace 987654321 20-JUN-81 291 Berry, Dallas, TX F 43000 888665555 4 20-JAN-11
Ramesh K Long 666884444 15-SEP-92 975 Fire Oak, San Antonio, TX M 38000 333445555 5 20-JAN-11
Sergio J Lopez 112233445 25-FEB-89 102347 E Pantera, Mesa, AZ M 65000 000079288 2 05-MAR-14
Joyce A Orozco 453453453 31-JUL-82 5631 Rice, Newark, NJ F 25000 333445555 5 20-JAN-11
Ahmad V Akkad 987987987 29-MAR-89 980 Dallas, LA, CA M 25000 987654321 4 20-JAN-12
James E Adams 888665555 10-NOV-87 450 Stone, Hollywood, CA M 55000 1 20-JAN-12
Miguel A Lopez 000079288 05-FEB-94 102347 E Pantera, Mesa, AZ M 75000 2 05-JAN-14
10 rows selected
DNAME DNUMBER MGRSSN MGRSTARTDATE
-------------------- ---------------------- --------- -------------------------
Research 5 333445555 22-MAY-78
Administration 4 987654321 01-JAN-85
Headquarters 1 888665555 19-JUN-71
Software Development 2 000079288 05-JAN-14
DNUMBER DLOCATION
---------------------- --------------------
1 Houston
2 Phoenix
4 Scottsdale
5 LA
5 New York
5 San Francisco
6 rows selected
PNAME PNUMBER PLOCATION DNUM BUDGETEDHOURS
-------------------- ---------------------- -------------------------------------------------- ---------------------- ----------------------
Automation 1 LA 5 20
Search Engine 2 San Francisco 5 30
Framework Update 3 Houston 5 40
Computerization 10 Scottsdale 4 120
Resturcture 20 Houston 1 130
Global Views 30 New York 4 140
Mobile Enhancement 15 Phoenix 2 500
7 rows selected
ESSN PNO HOURSSPENT
--------- ---------------------- ----------------------
123456789 1 32.5
123456789 2 7.5
666884444 3 40
112233445 15 300
453453453 1 20
453453453 2 20
333445555 2 10
333445555 3 10
333445555 10 10
333445555 20 10
999887777 30 30
999887777 10 10
987987987 10 35
987987987 30 5
987654321 30 20
987654321 20 15
888665555 20 12
123456789 3 0
000079288 15 150
19 rows selected
ESSN DEPENDENTNAME SEX BDATE RELATIONSHIP
--------- -------------------- --- ------------------------- --------------------
333445555 Caitlin F 05-APR-86 DAUGHTER
333445555 Zoe M 25-OCT-99 SON
333445555 Joy F 03-MAY-88 SPOUSE
112233445 Alicia F 21-AUG-94 SPOUSE
987654321 Rob M 21-AUG-84 SPOUSE
123456789 Michael M 01-JAN-99 SON
123456789 Katherine F 31-DEC-98 DAUGHTER
123456789 Elizabeth F 05-MAY-87 SPOUSE
000079288 Victoria F 13-DEC-12 DAUGHTER
000079288 Josie F 05-MAY-95 SPOUSE
10 rows selected
ESSN CONSULTANTCOMPANY CONTRACTCOST
--------- -------------------- ----------------------
123456789 Sogeti 30000
112233445 Sogeti 50000
666884444 Consultants R Us 15000
888665555 IT Partners 5000
CREATE OR REPLACE VIEW succeeded.
PNO PNAME PLOCATION DNAME BUDGETEDHOURS TOTALHOURS
---------------------- -------------------- -------------------------------------------------- -------------------- ---------------------- ----------------------
1 Automation LA Research 20 52.5
2 Search Engine San Francisco Research 30 37.5
3 Framework Update Houston Research 40 50
CREATE OR REPLACE VIEW succeeded.
PNO PNAME PLOCATION DNAME EMPCOUNT
---------------------- -------------------- -------------------------------------------------- -------------------- ----------------------
20 Resturcture Houston Headquarters 2
10 Computerization Scottsdale Administration 1
FNAME LNAME FNAME LNAME
-------------------- -------------------- -------------------- --------------------
Joyce Orozco Ben Franklin
Ramesh Long Ben Franklin
John Smith Ben Franklin
Ahmad Akkad Brittany Wallace
Alicia Olivas Brittany Wallace
Brittany Wallace James Adams
Ben Franklin James Adams
Sergio Lopez Miguel Lopez
8 rows selected
FNAME LNAME SSN DNAME
-------------------- -------------------- --------- --------------------
James Adams 888665555 Headquarters
Miguel Lopez 000079288 Software Development
PNAME PNUMBER DLOCATION
-------------------- ---------------------- --------------------
Automation 1 San Francisco
Automation 1 New York
Automation 1 LA
Search Engine 2 San Francisco
Search Engine 2 New York
Search Engine 2 LA
Framework Update 3 San Francisco
Framework Update 3 LA
Framework Update 3 New York
Computerization 10 Scottsdale
Mobile Enhancement 15 Phoenix
Resturcture 20 Houston
Global Views 30 Scottsdale
13 rows selected
FNAME LNAME MINIT ADDRESS
-------------------- -------------------- ----- --------------------------------------------------
John Smith B 731 Fondren, New York City, NY
Ben Franklin T 638 Voss, Houston, TX
Ramesh Long K 975 Fire Oak, San Antonio, TX
Joyce Orozco A 5631 Rice, Newark, NJ
PNUMBER DNUMBER LNAME ADDRESS BDATE
---------------------- ---------------------- -------------------- -------------------------------------------------- -------------------------
10 4 Akkad 980 Dallas, LA, CA 29-MAR-89
10 4 Franklin 638 Voss, Houston, TX 08-DEC-85
10 4 Olivas 3321 Castle, Fontana, CA 19-JUN-85
PNO
----------------------
1
2
3
CREATE OR REPLACE VIEW succeeded.
FNAME MINIT LNAME
-------------------- ----- --------------------
Ben T Franklin
John B Smith
Miguel A Lopez
FNAME MINIT LNAME
-------------------- ----- --------------------
Ahmad V Akkad
Alicia J Olivas
James E Adams
Joyce A Orozco
Ramesh K Long
FNAME MINIT LNAME
-------------------- ----- --------------------
Miguel A Lopez
Ben T Franklin
Brittany S Wallace
FNAME MINIT LNAME
-------------------- ----- --------------------
John B Smith
Ben T Franklin
Ramesh K Long
Joyce A Orozco
SUM(CONTRACTCOST)
----------------------
100000
FNAME MINIT LNAME
-------------------- ----- --------------------
John B Smith
Sergio J Lopez
FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO HIREDATE TERMINATEDATE
-------------------- ----- -------------------- --------- ------------------------- -------------------------------------------------- --- ---------------------- --------- ---------------------- ------------------------- -------------------------
Miguel A Lopez 000079288 05-FEB-94 102347 E Pantera, Mesa, AZ M 75000 2 05-JAN-14
FNAME LNAME COUNT(D.ESSN)
-------------------- -------------------- ----------------------
Ben Franklin 3
Miguel Lopez 2
Sergio Lopez 1
John Smith 3
Brittany Wallace 1
DROP TABLE worksOn succeeded.
DROP TABLE dependent succeeded.
DROP TABLE deptLocation succeeded.
DROP TABLE project succeeded.
DROP TABLE employee succeeded.
DROP TABLE department succeeded.
DROP TABLE consultant succeeded.
DROP VIEW projectHours succeeded.
DROP VIEW projectExEmp succeeded.
DROP VIEW empDependentCount succeeded.
Raw
run.sql
-- create tables
@ddl
-- show tables
DESC employee;
DESC department;
DESC deptLocation;
DESC project;
DESC worksOn;
DESC dependent;
DESC consultant;
-- insert data
@insert
-- show table content
/*
** ----------------------------------------------------------------------------
** employee table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM employee;
/*
** ----------------------------------------------------------------------------
** department table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM department;
/*
** ----------------------------------------------------------------------------
** deptLocation table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM deptLocation;
/*
** ----------------------------------------------------------------------------
** project table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM project;
/*
** ----------------------------------------------------------------------------
** worksOn table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM worksOn;
/*
** ----------------------------------------------------------------------------
** dependent table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM dependent;
/*
** ----------------------------------------------------------------------------
** consultant table contenct
** --------------------------------------------------------------------------*/
SELECT * FROM consultant;
-- run query
/*
** ----------------------------------------------------------------------------
** Query results
** --------------------------------------------------------------------------*/
@query
-- drop tables and views
@drop
Raw
util.sql
-- Before create schema objects, run util.sql to see if there is any extra schema objects
-- After drop schema objects, run util.sql to make sure all the schema objects are correctly droped.
COLUMN object_name FORMAT A20;
COLUMN object_type FORMAT A15;
SELECT object_name, object_type from user_objects
where object_name not like 'BIN%'
order by object_type;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment