- Create a database.
- Run CreateTables.sql to create tables.
- Select all tables in mysql employees database and export to newly created database in postgresql.
Last active
August 18, 2024 08:55
-
-
Save rdbhandari/f5896cee8641a9402970d598a0c5f675 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE employees ( | |
| emp_no INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 500000), | |
| birth_date DATE NOT NULL, | |
| first_name VARCHAR(14) NOT NULL, | |
| last_name VARCHAR(16) NOT NULL, | |
| gender CHAR(1) CHECK (gender IN ('M', 'F')) NOT NULL, | |
| hire_date DATE NOT NULL | |
| ); | |
| CREATE TABLE departments ( | |
| dept_no CHAR(4) NOT NULL, | |
| dept_name VARCHAR(40) NOT NULL, | |
| PRIMARY KEY (dept_no), | |
| UNIQUE (dept_name) | |
| ); | |
| CREATE TABLE dept_manager ( | |
| emp_no INT NOT NULL, | |
| dept_no CHAR(4) NOT NULL, | |
| from_date DATE NOT NULL, | |
| to_date DATE NOT NULL, | |
| 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, | |
| 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, | |
| 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, | |
| FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, | |
| PRIMARY KEY (emp_no, from_date) | |
| ); | |
| CREATE OR REPLACE VIEW dept_emp_latest_date AS | |
| SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date | |
| FROM dept_emp | |
| GROUP BY emp_no; | |
| -- Shows only the current department for each employee | |
| CREATE OR REPLACE VIEW current_dept_emp AS | |
| SELECT l.emp_no, d.dept_no, l.from_date, l.to_date | |
| FROM dept_emp d | |
| INNER JOIN dept_emp_latest_date l | |
| ON d.emp_no = l.emp_no AND d.from_date = l.from_date AND l.to_date = d.to_date; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment