Home/Browse database/Browse database

oracle code handout Speech

Any topic at any level
Original essays from scratch
Free revision within 2 days after the order delivery (on demand)
Vip services

Get a price quote

Activate my 15% discount on 1st order

Total price:

Buy custom oracle code handout

Essay's paper info

Topic:	Miscellaneous
Number of pages / Number of words:	3 / 775

Essay's paper body

Student Handouts

SQL to hand to Student to Reproduce Database and Queries on their own.

CREATE TABLE department

(

dname VARCHAR(15) NOT NULL,

dnumber INT NOT NULL,

PRIMARY KEY (dnumber),

UNIQUE (dname)

);

CREATE TABLE employee

(

fname VARCHAR(15) NOT NULL,

lname VARCHAR(15) NOT NULL,

ssn INT NOT NULL,

salary DECIMAL(10,2),

superssn INT,

dno INT NOT NULL,

PRIMARY KEY(ssn),

FOREIGN KEY(superssn) REFERENCES employee(ssn),

FOREIGN KEY(dno) REFERENCES department(dnumber)

);

CREATE TABLE project

(

pname VARCHAR(15) NOT NULL,

pnumber INT NOT NULL,

dnum INT NOT NULL,

PRIMARY KEY (pnumber),

UNIQUE (pname),

FOREIGN KEY (dnum) REFERENCES department(dnumber)

);

CREATE TABLE works_on

(

ESSN INT NOT NULL,

PNO INT NOT NULL,

PRIMARY KEY(essn, pno),

FOREIGN KEY(essn) REFERENCES employee(ssn),

FOREIGN KEY(pno) REFERENCES project(pnumber)

);

dname, dnumber

INSERT INTO department VALUES('Research', '1001');

INSERT INTO department VALUES('Accounting', '1002');

INSERT INTO department VALUES('Manufacturing', '1003');

INSERT INTO department VALUES('Human Resources', '1004');

fname lname ssn salary superssn dno

INSERT INTO employee VALUES('Eileen', 'MacAdoo', '12345987', '65000', '12345987', '1004');

INSERT INTO employee VALUES('Nora', 'Watkins', '45123987', '35500', '12345987', '1001');

INSERT INTO employee VALUES('Mary Anne', 'Lazarro', '32145878', '60000', '12345987', '1003');

INSERT INTO employee VALUES('Clara', 'Thompson', '03412344', '53000', '12345987', '1003');

INSERT INTO employee VALUES('Raymond', 'Thompson', '02932455', '22200', '12345987', '1002');

INSERT INTO employee VALUES('Ziggy', 'Gravellese', '45698755', '35000', '12345987', '1002');

INSERT INTO employee VALUES('Frankie', 'Thompson', '32425444', '17500', '12345987', '1001');

INSERT INTO employee VALUES('Jeanne', 'Dyer', '52455666', '43000', '12345987', '1001');

INSERT INTO employee VALUES('Tony', 'Aero', '52432455', '20000', '12345987', '1003');

INSERT INTO employee VALUES('Jonathon', 'Gravellese', '98765422', '52300', '12345987', '1003');

pname pnumber dnum

INSERT INTO project VALUES('projectA', '222', '1001');

INSERT INTO project VALUES('projectB', '333', '1003');

INSERT INTO project VALUES('projectC', '122', '1003');

INSERT INTO project VALUES('projectD', '232', '1002');

INSERT INTO project VALUES('projectE', '244', '1004');

INSERT INTO project VALUES('projectF', '400', '1002');

ESSN PNO

INSERT INTO works_on VALUES('45123987', '222');

INSERT INTO works_on VALUES('45123987', '333');

INSERT INTO works_on VALUES('45123987', '244');

INSERT INTO works_on VALUES('03412344', '222');

INSERT INTO works_on VALUES('03412344', '122');

INSERT INTO works_on VALUES('12345987', '400');

INSERT INTO works_on VALUES('12345987', '244');

INSERT INTO works_on VALUES('02932455', '222');

INSERT INTO works_on VALUES('02932455', '400');

INSERT INTO works_on VALUES('32425444', '400');

INSERT INTO works_on VALUES('32425444', '333');

INSERT INTO works_on VALUES('32425444', '244');

INSERT INTO works_on VALUES('98765422', '122');

INSERT INTO works_on VALUES('98765422', '244');

queries

for each department retrieve the department number, the number of employees in the department and their

average salary

select dno, count(*), avg(salary)

from employee

group by DNO

for each project retrieve the project number, project name and number of employees

SELECT pnumber, pname, COUNT(*)

FROM project, works_on

WHERE pnumber = pno

GROUP BY pnumber, pname

for each project on which more than two employees retrieve the project number, the project name and

the number of employees who work on the project

SELECT pnumber, pname, count(*)

FROM project, works_on

WHERE pnumber=pno

GROUP BY pnumber, pname

HAVING count(*) > 2

for each project, retrieve the project number, project name, number of employees from department 1003

who work on the project

SELECT pnumber, pname, count(*)

FROM project, works_on, employee

WHERE pnumber = pno AND ssn=essn AND DNO='1003'

GROUP BY pnumber, pname

for each department havingmore than 5 employees, retireve the department number and number of employees making

over $40,000

SELECT dname, count(*)

FROM department, employee

WHERE dnumber=dno AND salary> 40000 AND

dno IN (SELECT dno

FROM employee

GROUP BY dno

HAVING COUNT(*) > 5)

GROUP BY dname

select all employees currently assigned to projects, present in alphabetical order on last name

SELECT fname, lname

FROM employee

WHERE EXISTS (SELECT *

FROM works_on

where ssn=essn)

ORDER BY lname

List in last name alphabetical order all employees not currently assigned to projects

SELECT fname, lname

FROM employee

WHERE NOT EXISTS (SELECT *

FROM works_on

where ssn=essn)

ORDER BY lname

List in last name alphabetical order, all employees currently assigned to

more than one project

SELECT fname, lname

FROM employee

WHERE NOT EXISTS (SELECT count(*)

FROM works_on

where ssn=essn

GROUP BY essn

HAVING count(*) > 1)

ORDER BY lname

Initial Table Set to Hand Out in Class

Employee

fname lname ssn salary superssn dno

'Eileen' 'MacAdoo' '12345987' '65000' '12345987' '1004'

'Nora' 'Watkins' '45123987' '35500' '12345987' '1001'

'Mary Anne' 'Lazarro' '32145878' '60000' '12345987' '1003'

'Clara' 'Thompson' '03412344' '53000' '12345987' '1003'

'Raymond' 'Thompson' '02932455' '22200' '12345987' '1002'

'Ziggy' 'Gravellese' '45698755' '35000' '12345987' '1002'

'Frankie' 'Thompson' '32425444' '17500' '12345987' '1001'

'Jeanne' 'Dyer' '52455666' '43000' '12345987' '1001'

'Tony' 'Aero' '52432455' '20000' '12345987' '1003'

'Jonathon' 'Gravellese' '98765422' '52300' '12345987' '1003'

Department

dname dnumber

'Research' '1001'

'Accounting' '1002'

'Manufacturing' '1003'

'Human Resources' '1004'

Works_On

essn pno

'45123987' '222'

'45123987' '333'

'45123987' '244'

'03412344' '222'

'03412344' '122'

'12345987' '400'

'12345987' '244'

'02932455' '222'

'02932455' '400'

'32425444' '400'

'32425444' '333'

'32425444' '244'

'98765422' '122'

'98765422' '244'

Project

pname pnumber dnum

'projectA' '222' '1001'

'projectB' '333' '1003'

'projectC' '122' '1003'

'projectD' '232' '1002'

'projectE' '244' '1004'

'projectF' '400' '1002'

Essay fragment Read more

Need an Essay?

Choose one of the options below

Custom Written Essays

Order plagiarism free custom written essay
All essays are written from scratch by professional writers according to your instructions and delivered to your email on time. Prices start from $11.99 /page

Order Custom Paper

Full Access to Essays Database

This option gives you the immediate access to all 184 988 essays
You get access to all the essays and can view as many of them as you like for as little as $28.95/month

Buy Database Access

If at our website you can not find any essay you need for your study, you can order a paper on any topic with us.
Our company employs only qualified writers that are Master's and PhD holders.

Order custom written essay

Benefits

Research papers are written by professional writers
Requirements are always met
Posibility to control the working process of your paper
A chance of becoming the best student in your class.