CREATE TABLE EMPLOYEE (
   empno      INTEGER AUTO_INCREMENT,
   name       VARCHAR(10),
   job        VARCHAR(9),
   hiredate   VARCHAR(12),
   salary     DECIMAL(7, 2),
   comm       DECIMAL(7, 2),
   deptno     INTEGER,
   PRIMARY KEY (empno),
   UNIQUE (name)
);

CREATE TABLE DEPARTMENT (
   deptno     INTEGER AUTO_INCREMENT,
   name       VARCHAR(14),
   location   VARCHAR(13),
   PRIMARY KEY (deptno),
   UNIQUE (name)
);

CREATE TABLE PERSON (
   id          INTEGER AUTO_INCREMENT,
   name        VARCHAR(20) NOT NULL,
   surname     VARCHAR(20),
   nickname    VARCHAR(20),
   gender      VARCHAR(1) NOT NULL,
   birthdate   DATE,
   email       VARCHAR(50),
   phone       VARCHAR(20),
   mobile      VARCHAR(20),
   address     VARCHAR(200),
   sports      VARCHAR(2000),
   comment     VARCHAR(2000),
   PRIMARY KEY (id)
);

ALTER TABLE EMPLOYEE
   ADD CONSTRAINT department
   FOREIGN KEY (deptno)
   REFERENCES DEPARTMENT (deptno);


-- data
INSERT INTO DEPARTMENT(name, location) VALUES ('ACCOUNTING', 'NEW YORK');
INSERT INTO DEPARTMENT(name, location) VALUES ('RESEARCH',   'DALLAS');
INSERT INTO DEPARTMENT(name, location) VALUES ('SALES',      'CHICAGO');
INSERT INTO DEPARTMENT(name, location) VALUES ('OPERATIONS', 'BOSTON');

INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES ('JONES',  'MANAGER',    '1981-04-02',  2975, NULL, 2);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('SCOTT',  'ANALYST',    '1982-12-09', 3000, NULL, 2);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('ADAMS',  'CLERK',      '1983-01-12', 1100, NULL, 2);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('FORD',   'ANALYST',    '1981-12-03',  3000, NULL, 2);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('SMITH',  'CLERK',      '1980-12-17',  800, NULL, 2);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('BLAKE',  'MANAGER',   '1981-05-01',  2850, NULL, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('ALLEN',  'SALESMAN',   '1981-02-20', 1600,  300, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('WARD',   'SALESMAN',   '1981-02-22', 1250,  500, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('MARTIN', 'SALESMAN',   '1981-09-28', 1250, 1400, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('TURNER', 'SALESMAN',   '1981-09-08',  1500,    0, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('JAMES',  'CLERK',      '1981-12-03',   950, NULL, 3);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('CLARK',  'MANAGER',    '1981-06-09',  2450, NULL, 1);
INSERT INTO EMPLOYEE(name,job,hiredate,salary,comm,deptno) VALUES('MILLER', 'CLERK',     '1982-01-23', 1300, NULL, 1);

INSERT INTO PERSON(name,gender,birthdate) VALUES ('LUIZ',  'M',    '1975-09-22');