/**A RELATION database CONTAINS TWO tables employees & DEPT**/

-- create a table
CREATE TABLE EMP (
  EMPNO NUMERIC(4) PRIMARY KEY,
  ENAME VARCHAR(20) NOT NULL,
  JOB CHAR(10),
  MGR NUMERIC(4),
  HIREDATE DATETIME,
  SAL NUMERIC(9,2),
  COMM NUMERIC(7,2),
  DEPT NUMERIC(2)
);
-- insert data
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

-- create a table
CREATE TABLE DEPT (
  DEPTMO NUMERIC(2) PRIMARY KEY,
  DNAME VARCHAR(20) NOT NULL,
  LOC VARCHAR(10) NOT NULL
);
-- insert some values
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', 'BOSTAN');


-- FETCHING THE VALUE
SELECT * FROM EMP;
-- fetch some values
SELECT * FROM DEPT;
SELECT ENAME , SAL FROM EMP where DEPT = 20;
SELECT ENAME, JOB , SAL FROM EMP where JOB != 'MANAGER';
SELECT EMPNO FROM EMP where HIREDATE > '1981-03-01' AND HIREDATE < '1983-06-01';
SELECT ENAME FROM EMP where HIREDATE LIKE '1981%';
SELECT ENAME FROM EMP where ENAME LIKE 'S%';
SELECT ENAME FROM EMP where ENAME LIKE '%S';
SELECT ENAME , SAL FROM EMP where DEPT = 20 AND 40;
SELECT ENAME, JOB , DEPT FROM EMP where JOB IN ('CLERK' , 'SALESMAN');
SELECT ENAME FROM EMP where JOB = 'MANAGER' AND SAL > 2000;
SELECT ENAME FROM EMP where DEPT = 30 ORDER BY SAL DESC;
SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20;
SELECT MAX(HIREDATE) FROM EMP;
SELECT COUNT(*) FROM EMP WHERE DEPTNO=10;
SELECT DEPTNO, SUM(SAL) as "Total Salary" FROM EMP WHERE JOB != 'SALESMAN' GROUP BY DEPTNO HAVING SUM(SAL) > 8500;
SELECT ENAME FROM EMP WHERE HIREDATE=(SELECT MIN(HIREDATE) FROM EMP);
SELECT SUM(SAL) FROM EMP WHERE JOB != 'MANAGER';
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING COUNT(*) > 2;
SELECT ENAME, SAL, DEPTNO FROM EMP E WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO);
SELECT ENAME, SQRT(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE JOB='CLERK';
SELECT SUM(SAL) FROM EMP WHERE HIREDATE BETWEEN '1981-01-01' AND '1981-02-31';
UPDATE EMP SET JOB='SALESMAN', DEPTNO=30, SAL=1600 WHERE EMPNO=7788;
CREATE TABLE NEW AS SELECT * FROM EMP;
UPDATE NEW SET JOB='SALES';
SELECT * FROM NEW;
ALTER TABLE NEW ADD ADDRESS VARCHAR(10);
UPDATE NEW SET ADDRESS='ABC' WHERE EMPNO=7369;
SELECT * FROM NEW;