· 6 years ago · Mar 21, 2019, 03:48 PM
1DROP DATABASE IF EXISTS employees;
2CREATE DATABASE IF NOT EXISTS employees;
3USE employees;
4
5DROP TABLE IF EXISTS employees,
6 departments;
7
8CREATE TABLE employees (
9 emp_no INT NOT NULL,
10 birth_date DATE NOT NULL,
11 first_name VARCHAR(14) NOT NULL,
12 last_name VARCHAR(16) NOT NULL,
13 gender ENUM ('M','F') NOT NULL,
14 hire_date DATE NOT NULL,
15 PRIMARY KEY (emp_no)
16);
17
18CREATE TABLE departments (
19 dept_no CHAR(4) NOT NULL,
20 dept_name VARCHAR(40) NOT NULL,
21 PRIMARY KEY (dept_no),
22 UNIQUE KEY (dept_name)
23);
24
25INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
26(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
27(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
28(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
29(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
30(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
31(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
32(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
33(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
34(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
35(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
36(10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
37(10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
38(10014,'1956-02-12','Berni','Genin','M','1987-03-11'),
39(10015,'1959-08-19','Guoxiang','Nooteboom','M','1987-07-02'),
40(10016,'1961-05-02','Kazuhito','Cappelletti','M','1995-01-27'),
41(10017,'1958-07-06','Cristinel','Bouloucos','F','1993-08-03'),
42(10018,'1954-06-19','Kazuhide','Peha','F','1987-04-03'),
43(10019,'1953-01-23','Lillian','Haddadi','M','1999-04-30'),
44(10020,'1952-12-24','Mayuko','Warwick','M','1991-01-26'),
45(10021,'1960-02-20','Ramzi','Erde','M','1988-02-10');
46
47
48INSERT INTO `departments` VALUES
49('d001','Marketing'),
50('d002','Finance'),
51('d003','Human Resources'),
52('d004','Production'),
53('d005','Development'),
54('d006','Quality Management'),
55('d007','Sales'),
56('d008','Research'),
57('d009','Customer Service');