-- PostrgreSQL tables

CREATE TABLE department(
deptno   SMALLINT    NOT NULL PRIMARY KEY,
deptname  VARCHAR(15),
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 VARCHAR  (40),
lname VARCHAR  (40),
city    VARCHAR  (40),
education CHAR (8),
salary  DECIMAL(7, 2),
tax_rate  DECIMAL(3,1),
start_date     DATE,
deptno    SMALLINT);

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