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' displayed 300 characters
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 $10.99/page
Your research paper is written by certified writers
Your requirements and targets are always met
You are able to control the progress of your writing assignment
You get a chance to become an excellent student!
Get a price guote
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' displayed 300 characters
