Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
43 lines (38 sloc) 2.25 KB
CREATE DATABASE dept_emp;
USE dept_emp;
CREATE TABLE dept (
deptno INTEGER,
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);
CREATE TABLE emp (
empno INTEGER,
ename VARCHAR(10),
job VARCHAR(9),
mgr INTEGER,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INTEGER,
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH' , 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES' , 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp VALUES (7839, 'KING' , 'PRESIDENT', NULL, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000, NULL, 10);
INSERT INTO emp VALUES (7698, 'BLAKE' , 'MANAGER' , 7839, STR_TO_DATE('1-5-1981' , '%d-%m-%Y'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK' , 'MANAGER' , 7839, STR_TO_DATE('9-6-1981' , '%d-%m-%Y'), 2450, NULL, 10);
INSERT INTO emp VALUES (7566, 'JONES' , 'MANAGER' , 7839, STR_TO_DATE('2-4-1981' , '%d-%m-%Y'), 2975, NULL, 20);
INSERT INTO emp VALUES (7788, 'SCOTT' , 'ANALYST' , 7566, STR_TO_DATE('13-6-1987' , '%d-%m-%Y'), 3000, NULL, 20);
INSERT INTO emp VALUES (7902, 'FORD' , 'ANALYST' , 7566, STR_TO_DATE('3-12-1981' , '%d-%m-%Y'), 3000, NULL, 20);
INSERT INTO emp VALUES (7369, 'SMITH' , 'CLERK' , 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN' , 'SALESMAN' , 7698, STR_TO_DATE('20-2-1981' , '%d-%m-%Y'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD' , 'SALESMAN' , 7698, STR_TO_DATE('22-2-1981' , '%d-%m-%Y'), 1250, 500, 30);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN' , 7698, STR_TO_DATE('28-9-1981' , '%d-%m-%Y'), 1250, 1400, 30);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN' , 7698, STR_TO_DATE('8-9-1981' , '%d-%m-%Y'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS' , 'CLERK' , 7788, STR_TO_DATE('13-6-1987' , '%d-%m-%Y'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES' , 'CLERK' , 7698, STR_TO_DATE('3-12-1981' , '%d-%m-%Y'), 950, NULL, 30);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK' , 7782, STR_TO_DATE('23-1-1982' , '%d-%m-%Y'), 1300, NULL, 10);
You can’t perform that action at this time.