7tFqKaSQ

1DROP DATABASE IF exists `company_20171016`;
2SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
3SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
4SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
5-- -----------------------------------------------------
6-- Schema Company
7-- -----------------------------------------------------
8CREATE SCHEMA IF NOT EXISTS `company_20171016` DEFAULT CHARACTER SET utf8
9COLLATE utf8_general_ci ;
10USE `company_20171016`;
11
12CREATE TABLE IF NOT EXISTS `e_emp` (
13 `e_empno` DECIMAL(10, 0) NOT NULL,
14 `e_name` VARCHAR(20),
15 `e_job` VARCHAR(20),
16 `e_mgr` DECIMAL(10,0),
17 `e_hiredate` DATE,
18 `e_sal` DECIMAL(10,0),
19 `e_comm` DECIMAL(5, 0),
20 `e_d_deptno` DECIMAL(10,0),
21 PRIMARY KEY (`e_empno`),
22 UNIQUE INDEX `e_empno_UNIQUE` (`e_empno` ASC),
23CONSTRAINT `fk_e_emp_d_dept`
24FOREIGN KEY (`e_d_deptno`)
25REFERENCES `d_dept` (`d_deptno`))
26    
27ENGINE = InnoDB;
28
29CREATE TABLE IF NOT EXISTS `d_dept` (
30 `d_deptno` DECIMAL(10, 0) NOT NULL,
31 `d_name` VARCHAR(20),
32 `d_loc` VARCHAR(20),
33 UNIQUE INDEX `d_deptno_UNIQUE` (`d_deptno` ASC))
34ENGINE = InnoDB; 
35
36CREATE TABLE IF NOT EXISTS `s_salgrade` (
37 `s_grade` DECIMAL(2, 0) NOT NULL,
38 `s_losal` DECIMAL(10,0),
39 `s_hisal` DECIMAL(10,0),
40 UNIQUE INDEX `s_grade_UNIQUE` (`s_grade` ASC))
41ENGINE = InnoDB; 
42
43INSERT INTO d_DEPT (d_deptno, d_name, d_loc) VALUES
44  (10,'ACCOUNTING','NEW YORK'),
45  (20,'RESEARCH','DALLAS'),
46  (30,'SALES','CHICAGO'  ),
47  (40,'OPERATIONS','BOSTON');
48
49INSERT INTO s_SALGRADE (s_grade, s_losal, s_hisal) VALUES
50  (1,700,1200),
51  (2,1201,1400),
52  (3,1401,2000),
53  (4,2001,3000),
54  (5,3001,9000);
55
56INSERT INTO e_emp (e_empno, e_name, e_job, e_mgr, e_hiredate, e_sal, e_comm, e_d_deptno) VALUES
57  (7369,'SMITH','CLERK',7901,'1980-12-17',800,NULL,20),
58  (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
59  (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
60  (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
61  (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
62  (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
63  (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
64  (7788,'SCOTT','ANALYST',7566,'1982-12-09',3000,NULL,20),
65  (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
66  (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
67  (7876,'ADAMS','CLERK',7788,'1983-01-12',1100,NULL,20),
68  (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
69  (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
70  (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);