My-sql-Employee-Database/Schemata.sql at main · Albeteme/My-sql-Employee-Database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
-- departments (dept_no, dept_name)
DROP TABLE IF EXISTS departments CASCADE;
CREATE TABLE departments (
	dept_no VARCHAR(4) PRIMARY KEY,
	dept_name VARCHAR NOT NULL
);

COPY
	departments (dept_no, dept_name)
FROM 'C:\BootCamp\sql-challenge\data\departments.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM departments;

-- employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
DROP TABLE IF EXISTS employees CASCADE;
CREATE TABLE employees (
	emp_no INTEGER PRIMARY KEY,
	birth_date VARCHAR(10) NOT NULL,
	first_name VARCHAR NOT NULL,
	last_name VARCHAR NOT NULL,
	gender VARCHAR(1) NOT NULL,
	hire_date VARCHAR(10) NOT NULL
);

COPY
	employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
FROM 'C:\BootCamp\sql-challenge\data\employees.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM employees;

-- dept_emp (emp_no, dept_no, from_date, to_date)
DROP TABLE IF EXISTS dept_emp CASCADE;
CREATE TABLE dept_emp (
	emp_no INTEGER NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
	dept_no VARCHAR(4) NOT NULL,
	FOREIGN KEY (dept_no) REFERENCES departments(dept_no),
	from_date VARCHAR(10) NOT NULL,
	to_date VARCHAR(10) DEFAULT '9999-01-01',
	PRIMARY KEY (emp_no, dept_no)
);

COPY
	dept_emp (emp_no, dept_no, from_date, to_date)
FROM 'C:\BootCamp\sql-challenge\data\dept_emp.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM dept_emp;

-- dept_manager (dept_no, emp_no, from_date, to_date)
DROP TABLE IF EXISTS dept_manager CASCADE;
CREATE TABLE dept_manager (
	dept_no VARCHAR(4) NOT NULL,
	FOREIGN KEY (dept_no) REFERENCES departments(dept_no),
	emp_no INTEGER NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
	from_date VARCHAR(10) NOT NULL,
	to_date VARCHAR(10) DEFAULT '9999-01-01',
	PRIMARY KEY (dept_no, emp_no)
);

COPY
	dept_manager (dept_no, emp_no, from_date, to_date)
FROM 'C:\BootCamp\sql-challenge\data\dept_manager.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM dept_manager;

-- salaries (emp_no, salary, from_date, to_date)
DROP TABLE IF EXISTS salaries CASCADE;
CREATE TABLE salaries (
	id SERIAL PRIMARY KEY,
	emp_no INTEGER NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
	salary INTEGER NOT NULL,
	from_date VARCHAR(10) NOT NULL,
	to_date VARCHAR(10) DEFAULT '9999-01-01'
);

COPY
	salaries (emp_no, salary, from_date, to_date)
FROM 'C:\BootCamp\sql-challenge\data\salaries.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM salaries;

-- titles (emp_no, title, from_date, to_date)
DROP TABLE IF EXISTS titles CASCADE;
CREATE TABLE titles (
	id SERIAL PRIMARY KEY,
	emp_no INTEGER NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
	title VARCHAR NOT NULL,
	from_date VARCHAR(10) NOT NULL,
	to_date VARCHAR(10) DEFAULT '9999-01-01'
);

COPY
	titles (emp_no, title, from_date, to_date)
FROM 'C:\BootCamp\sql-challenge\data\titles.csv'
DELIMITER ',' CSV HEADER;

SELECT * FROM titles;