Discount applied successfully

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.

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

General points of the essay

Human Values and Social Structures Ethical Values In Accounting Student Values & Uop Tbs Values Values Based Decision-Making Values Used in Business Decision Making Values of Accounting values No more Values Art Values Student Values insert text Values & God Social Values In Mary Shelley's Frankenstein Values and Decision Making

Essays related to the topic
Title Pages / Words Select
Class President Speech essay 1 / 254
Class President Speech essay 1 / 246
oracle code handout essay 3 / 775
Speech essay 1 / 201
baseball essay 3 / 562
comparission essay 2 / 371
Keyboard Shortcuts for Microsoft Excel essay 3 / 694
Services