-- Oracle tables

CREATE TABLE department(
deptno   NUMBER(6) PRIMARY KEY,
deptname  VARCHAR2(25),
code   VARCHAR(30));


INSERT  INTO department VALUES (1,'IT', 'asa_123456');
INSERT  INTO department VALUES (2,'Economy','s''dfg*234');
INSERT  INTO department VALUES (3,'Research','a_ss*8888');
INSERT  INTO department VALUES (4,'Marketing','a%');


CREATE TABLE employee(
empno CHAR (4) PRIMARY KEY,
fname VARCHAR2 (40),
lname VARCHAR2 (40),
city    VARCHAR2 (40),
education CHAR (8),
salary  DECIMAL(7, 2),
tax_rate  DECIMAL(3,1),
start_date     DATE,
deptno    NUMBER (6));

INSERT INTO employee VALUES (2134,'Peter','Stream'
,'LONDON','Ba',2800    ,22  ,to_date('2020-03-02','yyyy-mm-dd'),3);
INSERT INTO employee VALUES (2234,'Mike','Wood'
,'LONDON','PhD',3100    ,33  ,to_date('2009-10-15','yyyy-mm-dd'),1);
INSERT INTO employee VALUES (2245,'Rachel','Brooke'
,'HELSINKI','MA',3100    ,31  ,to_date('2014-09-24','yyyy-mm-dd'),4);
INSERT INTO employee VALUES (2345,'Leon','Lake'
,'LONDON',NULL,2800    ,24.5,to_date('2018-01-01','yyyy-mm-dd'), 3);
INSERT INTO employee VALUES (2884,'Peter','Taylor'
,'HELSINKI','MA',2960    ,31  ,to_date('2009-05-12','yyyy-mm-dd'),NULL);
INSERT INTO employee VALUES (3546,'Laura','Brown'
,'SYDNEY','Ba',2650    ,22  ,to_date('2017-09-15','yyyy-mm-dd'),1);
INSERT INTO employee VALUES (3547,'Lilian','River'
,'SYDNEY','DIP',2800    ,37  ,to_date('2009-05-12','yyyy-mm-dd'),3);

CREATE TABLE project (
projno   CHAR(4) NOT NULL PRIMARY KEY,
project_name   VARCHAR(50),
priority   SMALLINT,
location VARCHAR(50)
);


INSERT INTO project VALUES ('P1','BOOKKEEPING',2,'LONDON');
INSERT INTO project VALUES ('P2','BILLING',1,'HELSINKI');
INSERT INTO project VALUES('P3','WAREHOUSING',3,'HELSINKI');
INSERT INTO project VALUES('P4','ACCOUNTING',2,'LONDON');
INSERT INTO project VALUES('P5','CUSTOMERS',3,'SINGAPORE');
INSERT INTO project VALUES('P6','STATISTICS', NULL, NULL);

CREATE TABLE proj_emp
(projno    CHAR (4)  NOT NULL,
empno      CHAR (4)  NOT NULL,
hours_act    NUMBER(6),
hours_est  NUMBER(6),
CONSTRAINT proj_emp_pk PRIMARY KEY (projno, empno));


INSERT INTO proj_emp VALUES ('P1','2134',300,300);
INSERT INTO proj_emp VALUES ('P1','2245',200,300);
INSERT INTO proj_emp VALUES ('P1','3546',400,500);
INSERT INTO proj_emp VALUES ('P1','2884',100,200);
INSERT INTO proj_emp VALUES ('P1','2234',200,NULL);
INSERT INTO proj_emp VALUES ('P1','2345',100,100);
INSERT INTO proj_emp VALUES ('P1','3547',300,200);
INSERT INTO proj_emp VALUES ('P2','2134',300,NULL);
INSERT INTO proj_emp VALUES ('P2','2245',400,500);
INSERT INTO proj_emp VALUES ('P3','2245',900,100);
INSERT INTO proj_emp VALUES ('P4','2245',200,200);
INSERT INTO proj_emp VALUES ('P4','2234',300,400);
INSERT INTO proj_emp VALUES ('P4','2884',400,600);