/*

-- Re-enable the following 4 lines if you are dropping and refreshing the sample-data tables

DROP TABLE employee_skill;

DROP TABLE skill;

DROP TABLE employee;

DROP TABLE department;

*/

 

-- Create tables

CREATE TABLE department (

     department_id        NUMBER(8,0)  PRIMARY KEY,

     department_name      VARCHAR2(25) NOT NULL

     )

;

 

CREATE TABLE employee (

     employee_id          NUMBER(8,0)  PRIMARY KEY,

     last_name            VARCHAR2(20) NOT NULL,

     first_name           VARCHAR2(15) NOT NULL,

     hire_date            DATE         NOT NULL,

     department_id        NUMBER(8,0)  NOT NULL,

     salary               NUMBER(8,2)  NOT NULL

     )

;

 

CREATE TABLE skill (

     skill_code           VARCHAR2(8)  PRIMARY KEY,

     skill_full_name      VARCHAR2(30) NOT NULL

     )

;

 

CREATE TABLE employee_skill (

     employee_id          NUMBER(8,0) NOT NULL,

     skill_code           VARCHAR2(8) NOT NULL,

     skill_level          NUMBER(1,0) NOT NULL

                             CHECK (skill_level BETWEEN 1 AND 5)

     )

;

 

ALTER TABLE employee_skill

ADD PRIMARY KEY (employee_id, skill_code)

;

 

ALTER TABLE employee

ADD FOREIGN KEY (department_id) REFERENCES department

;

 

ALTER TABLE employee_skill

ADD FOREIGN KEY (employee_id) REFERENCES employee

;

 

ALTER TABLE employee_skill

ADD FOREIGN KEY (skill_code) REFERENCES skill

;

 

-- Populate tables

/*

DELETE FROM employee_skill;

DELETE FROM skill;

DELETE FROM employee;

DELETE FROM department;

*/

 

INSERT INTO department VALUES (1, 'Sales');

INSERT INTO department VALUES (2, 'Research and Development');

INSERT INTO department VALUES (3, 'Information Technology');

INSERT INTO department VALUES (4, 'Maintenance');

 

INSERT INTO employee VALUES (1001, 'Haupt', 'Joseph', '30-JUN-1972', 3, 65000);

INSERT INTO employee VALUES (1002, 'Avila', 'Arnold', '29-OCT-1988', 4, 50000);

INSERT INTO employee VALUES (1003, 'Gutwirth', 'David', '30-JUN-2002', 1, 75000);

INSERT INTO employee VALUES (1004, 'Rusch', 'Grace', '28-JUL-1999', 2, 80000);

INSERT INTO employee VALUES (1005, 'Zuckerman', 'John', '29-OCT-2001', 3, 95000);

 

INSERT INTO skill VALUES ('SALES', 'Selling Techniques');

INSERT INTO skill VALUES ('ACCTG', 'Accounting');

INSERT INTO skill VALUES ('C', 'C Programming');

INSERT INTO skill VALUES ('DBA', 'Database Administrator');

INSERT INTO skill VALUES ('DEV', 'Oracle Developer');

INSERT INTO skill VALUES ('DA', 'Data Architecture');

INSERT INTO skill VALUES ('SI', 'Systems Integration');

INSERT INTO skill VALUES ('JAVA', 'Java Programming');

INSERT INTO skill VALUES ('HM', 'Humorous in Meetings');

 

INSERT INTO employee_skill VALUES (1005, 'DEV', 5);

INSERT INTO employee_skill VALUES (1005, 'DBA', 4);

INSERT INTO employee_skill VALUES (1005, 'DA', 5);

INSERT INTO employee_skill VALUES (1004, 'DEV', 4);

INSERT INTO employee_skill VALUES (1004, 'DA', 2);

INSERT INTO employee_skill VALUES (1004, 'HM', 5);

INSERT INTO employee_skill VALUES (1002, 'SI', 5);

INSERT INTO employee_skill VALUES (1002, 'DA', 1);

INSERT INTO employee_skill VALUES (1001, 'SALES', 4);

 

COMMIT;

 

alter table employee

add (profit_sharing_indicator Number (1,0) NULL

constraint between_0_and_1 check (

profit_sharing_indicator between 0 and 1));

 

COMMIT;