create database emps;
use emps;
create table `dept`(
`DEPTNO` int primary key auto_increment,
`DNAME` varchar(255) not null,
`LOC` varchar(255) not null
);
insert into `dept`(`DEPTNO`,`DNAME`,`LOC`)
valueS (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
DESC `dept`;
create table `emp`(
`empno` int primary key auto_increment,
`ename` varchar(10) not null,
`job` varchar(20) not null,
`mgr` int not null,
`hiredate` date not null,
`sal` decimal(7,2) not null,
`comm` decimal(7,2) ,
`deptno` int
);
insert into `emp` (`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`)
values
(7916,'GREEN','SALASMAN',7782,'2018-01-01',2000,NULL,10),
(7369,'SMITM','CLERK',0,'1980-12-17',2800,NULL,20),
(7499,'ALLEN','SALESMAN',7598,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-04-02',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-01-05',2850,NULL,20),
(7777,'JACK','SALEMAN',7654,'2020-03-19',2500,0,30),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
create table `emp_jobhistory`(
`ID` int primary key auto_increment,
`EMPNO` INT NOT NULL,
`JOB` varchar(9) not null,
`BEGINDATE` DATE NOT NULL,
`SAL` decimal(7,2) NOT NULL
);
desc `emp_jobhistory`;
INSERT into `emp_jobhistory` (`EMPNO`,`JOB`,`BEGINDATE`,`SAL`)
values
(7839,'TRAINEE','1981-11-17',500),
(7839,'SALESMAN','1982-02-17',1800),
(7839,'CLERK','1983-02-17',2000),
(7839,'SALESMAN','1985-02-17',1800),
(7839,'MANAGER','1987-02-17',3000);
create table `salgrade`(
`GRADE` INT primary key auto_increment,
`LOSAL` INT ,
`HISAL` INT
);
insert INTO `salgrade`(`GRADE`,`LOSAL`,`HISAL`)
values (1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
DESC `salgrade`;