Oracle Code Handout

POS 410

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');