-- creates some test-tables and data
-- DROP TABLE EMPLOYEE;
-- DROP TABLE DEPARTMENT;
-- DROP TABLE SALARYGRADE;
-- DROP TABLE BONUS;
-- DROP TABLE PROJECT;
-- DROP TABLE PROJECT_PARTICIPATION;
-- DROP TABLE ROLE;
CREATE TABLE EMPLOYEE(
empno INTEGER NOT NULL,
name VARCHAR(10),
job VARCHAR(9),
boss INTEGER,
hiredate VARCHAR(12),
salary DECIMAL(7, 2),
comm DECIMAL(7, 2),
deptno INTEGER
);
CREATE TABLE DEPARTMENT(
deptno INTEGER NOT NULL,
name VARCHAR(14),
location VARCHAR(13)
);
CREATE TABLE SALARYGRADE(
grade INTEGER NOT NULL,
losal INTEGER NOT NULL,
hisal INTEGER NOT NULL
);
CREATE TABLE BONUS (
ename VARCHAR(10) NOT NULL,
job VARCHAR(9) NOT NULL,
sal DECIMAL(7, 2),
comm DECIMAL(7, 2)
);
CREATE TABLE PROJECT(
projectno INTEGER NOT NULL,
description VARCHAR(100),
start_date VARCHAR(12),
end_date VARCHAR(12)
);
CREATE TABLE PROJECT_PARTICIPATION(
projectno INTEGER NOT NULL,
empno INTEGER NOT NULL,
start_date VARCHAR(12) NOT NULL,
end_date VARCHAR(12),
role_id INTEGER
);
CREATE TABLE ROLE(
role_id INTEGER NOT NULL,
description VARCHAR(100)
);
-- Primary Keys
ALTER TABLE EMPLOYEE
ADD CONSTRAINT emp_pk
PRIMARY KEY (empno);
ALTER TABLE DEPARTMENT
ADD CONSTRAINT dept_pk
PRIMARY KEY (deptno);
ALTER TABLE SALARYGRADE
ADD CONSTRAINT salgrade_pk
PRIMARY KEY (grade);
ALTER TABLE BONUS
ADD CONSTRAINT bonus_pk
PRIMARY KEY (ename, job);
ALTER TABLE PROJECT
ADD CONSTRAINT project_pk
PRIMARY KEY (projectno);
ALTER TABLE PROJECT_PARTICIPATION
ADD CONSTRAINT participation_pk
PRIMARY KEY (projectno, empno, start_date);
ALTER TABLE ROLE
ADD CONSTRAINT role_pk
PRIMARY KEY (role_id);
-- EMPLOYEE to DEPARTMENT
ALTER TABLE EMPLOYEE
ADD CONSTRAINT department
FOREIGN KEY (deptno)
REFERENCES DEPARTMENT (deptno);
-- EMPLOYEE to EMPLOYEE
ALTER TABLE EMPLOYEE
ADD CONSTRAINT boss
FOREIGN KEY (boss)
REFERENCES EMPLOYEE (empno);
-- EMPLOYEE to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
ADD CONSTRAINT employee
FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno);
-- PROJECT to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
ADD CONSTRAINT project
FOREIGN KEY (projectno)
REFERENCES PROJECT (projectno);
-- ROLE to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
ADD CONSTRAINT role
FOREIGN KEY (role_id)
REFERENCES ROLE (role_id);
-- data
INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPARTMENT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPARTMENT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPARTMENT VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO EMPLOYEE VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMPLOYEE VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMPLOYEE VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMPLOYEE VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMPLOYEE VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
INSERT INTO SALARYGRADE VALUES (1, 700, 1200);
INSERT INTO SALARYGRADE VALUES (2, 1201, 1400);
INSERT INTO SALARYGRADE VALUES (3, 1401, 2000);
INSERT INTO SALARYGRADE VALUES (4, 2001, 3000);
INSERT INTO SALARYGRADE VALUES (5, 3001, 9999);
INSERT INTO ROLE VALUES (100, 'Developer');
INSERT INTO ROLE VALUES (101, 'Researcher');
INSERT INTO ROLE VALUES (102, 'Project manager');
INSERT INTO PROJECT VALUES (1001, 'Development of Novel Magnetic Suspension System', '2006-01-01', '2007-08-13');
INSERT INTO PROJECT VALUES (1002, 'Research on thermofluid dynamics in Microdroplets', '2006-08-22', '2007-03-20');
INSERT INTO PROJECT VALUES (1003, 'Foundation of Quantum Technology', '2007-02-24', '2008-07-31');
INSERT INTO PROJECT VALUES (1004, 'High capacity optical network', '2008-01-01', null);
INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7902, '2006-01-01', '2006-12-30', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7369, '2006-01-01', '2007-08-13', 100);
INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7788, '2006-05-15', '2006-11-01', 100);
INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7876, '2006-08-22', '2007-03-20', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7782, '2006-08-22', '2007-03-20', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7934, '2007-01-01', '2007-03-20', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7566, '2007-02-24', '2008-07-31', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7900, '2007-02-24', '2007-01-31', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7499, '2008-01-01', null, 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7521, '2008-05-01', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7654, '2008-04-15', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7844, '2008-02-01', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-03-01', '2008-04-01', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-05-20', null, 101);