CREATE DATABASE EXERCISE;
USE EXERCISE;

create table dept
(DEPTNO     NUMERIC(2) primary key,
DNAME       VARCHAR(14),
LOC         VARCHAR(13));

create table emp
(EMPNO    NUMERIC(4)  primary key,
ENAME    VARCHAR(10),
JOB      VARCHAR(9),
MGR      NUMERIC(4),
HIREDATE DATE,
SAL      NUMERIC(7,2),
COMM     NUMERIC(7,2),
DEPTNO   NUMERIC(2),
foreign key (deptno) references dept(deptno));

create table salgrade
(GRADE     NUMERIC(2),
LOSAL      NUMERIC(7,2),
HISAL      NUMERIC(7,2));

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(7369,'SMITH','CLERK',7902,'2022.12.17',800, NULL,20);
Insert into emp values(7499,'ALLEN','SALESMAN',7698, '2023.02.20',1600,300,30);
Insert into emp values(7521,'WARD','SALESMAN',7698, '2023.02.22',1250,500,30);
Insert into emp values(7566,'JONES','MANAGER',7839, '2023.04.02',2975,NULL,20);
Insert into emp values(7654,'MARTIN','SALESMAN',7698,'2023.09.28',1250,1400,30);
Insert into emp values(7698,'BLAKE','MANAGER',7839, '2023.05.01',2850,NULL,30);
Insert into emp values(7782,'CLARK','MANAGER',7839, '2023.06.09', 2450,NULL,10);
Insert into emp values(7788,'SCOTT','ANALYST',7566, '2025.04.19', 3000,NULL,20);
Insert into emp values(7839,'KING','PRESIDENT',NULL, '2023.11.17', 5000,NULL,10);
Insert into emp values(7844,'TURNER' ,'SALESMAN',7698, '2023.09.08',1500,0, 30);
Insert into emp values(7876,'ADAMS','CLERK',7788, '2025.05.23', 1100,NULL,20);
Insert into emp values(7900,'JAMES','CLERK',7698, '2023.12.03', 950,NULL,30);
Insert into emp values(7902,'FORD','ANALYST',7566, '2023.12.03', 3000,NULL,20);
Insert into emp values(7934,'MILLER','CLERK',7782, '2024.01.23',1300,NULL,10);

Insert into salgrade values (1, 700, 1200);
Insert into salgrade values (2,1201, 1400);
Insert into salgrade values (3,1401, 2000);
Insert into salgrade values (4,2001, 3000);
Insert into salgrade values (5,3001, 9999);