oracle code handout

Essay text:

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'