TestDB.sql

-- Compile Envo "Google Cloud SQL".

-- DROP DATABASE IF EXISTS employees;
-- CREATE DATABASE IF NOT EXISTS employees;
USE employees;


-- DROP TABLE IF EXISTS dept_emp,
--                     dept_manager,
--                     titles,
--                     salaries, 
--                     employees, 
--                     departments;

-- set storage_engine = InnoDB; Google Cloud SQL support and only support InnoDB
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;


CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   dept_no      CHAR(4)         NOT NULL,
   emp_no       INT             NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   KEY         (emp_no),
   KEY         (dept_no),
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
); 

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    KEY         (emp_no),
    KEY         (dept_no),
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
); 

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    KEY         (emp_no),
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
); 

INSERT INTO employees(emp_no, birth_date, first_name, last_name, gender, hire_date)
VALUES (1, '1991-09-23', 'Kunkka', 'microco', 'M', '2018-12-4');

INSERT INTO employees
VALUES (2, '1993-04-25', 'Shadow', 'Field', 'M', '2018-12-5'),
       (3, '1995-01-29', 'Wind', 'Ranger', 'F', '2018-12-5');

INSERT INTO titles
VALUES (1, 'Manager', '2008-9-9', '2009-8-8'),
       (1, 'Associate Manager', '2008-12-9', '2011-9-8'),
       (2, 'IT', '2017-3-3', '2019-12-12');

SELECT employees.birth_date, employees.first_name, titles.title, titles.emp_no
FROM employees
INNER JOIN titles ON titles.emp_no = employees.emp_no;

SELECT employees.birth_date, employees.first_name, titles.title, titles.emp_no
FROM employees
LEFT JOIN titles ON titles.emp_no = employees.emp_no;