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