CREATE TABLE department (
  name varchar(20) NOT NULL, id int NOT NULL PRIMARY KEY );

CREATE TABLE employee (
  name varchar(20) default NULL,
  payment number default NULL,
  bonus number default NULL,
  birthDate date default NULL,
  id int NOT NULL PRIMARY KEY, 
  depid int,  FOREIGN KEY (depid) REFERENCES department (id) );

INSERT INTO department (name, id) VALUES ('IT', 1);
INSERT INTO department (name, id) VALUES ('HR', 2);
INSERT INTO department (name, id) VALUES ('BA', 3);


INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Ross',  3000.0, 0.3, to_date('1970-12-02','yyyy-mm-dd') , 1, 1 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Chandler',  1500.0, 0.25, to_date('1980-02-15','yyyy-mm-dd') , 1, 2 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Joel',   2300.0, 0.25, to_date('1976-07-20','yyyy-mm-dd') , 1, 3 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Rachel', 2400.0, 0.1, to_date('1974-10-24','yyyy-mm-dd') , 1, 4 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('John',   1800.0, 0.2, to_date('1972-06-05','yyyy-mm-dd') , 1, 5 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Jack',  1400.0, 0.15, to_date('1968-08-22','yyyy-mm-dd') , 2, 6 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Tom',  2100.0, 0.05, to_date('1971-10-16','yyyy-mm-dd') , 2, 7 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Keith',   1800.0, 0.2, to_date('1979-03-21','yyyy-mm-dd') , 2, 8 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Simon',  1900.0, 0.1, to_date('1974-12-05','yyyy-mm-dd') , 2, 9 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Denise',  2400.0, 0.2, to_date('1976-12-02','yyyy-mm-dd') , 3, 10 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Williams',  2200.0, 0.1, to_date('1981-05-25','yyyy-mm-dd') , 3, 11 );
INSERT INTO employee   (name,payment, bonus, birthDate, depid, id) VALUES   ('Kent',  1700.0, 0.15, to_date('1983-06-17','yyyy-mm-dd') , 3, 12 );