SQL Queries

CREATE TABLE DEPT (DEPTNO NUMERIC(2) PRIMARY KEY, DNAME VARCHAR(20), LOC VARCHAR(10));

CREATE TABLE EMP (EMPNO NUMERIC(4) PRIMARY KEY, ENAME VARCHAR(20) NOT NULL, JOB CHAR(10), MGR NUMERIC(4), HIREDATE SMALLDATETIME, SAL NUMERIC(9,2), COMM NUMERIC(7,2) , DEPTNO NUMERIC(2) FOREIGN KEY REFERENCES DEPT(DEPTNO));

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,20);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,COMM) VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,30,300);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,COMM) VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,30,500);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,20);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,COMM) VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,30,1400);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7698,'BLAKE','MANAGER',7839,'1981-09-28',2850,30);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7782,'CLARK','MANAGER',7839,'1981-05-01',2450,10);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7788,'SCOTT','ANALYST',7566,'1981-06-09',3000,20);

INSERT INTO EMP(EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) VALUES (7839,'KING','PRESIDENT','1981-04-19',5000,10);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,COMM) VALUES (7844,'TURNER','SALESMAN',7698,'1981-11-17',1500,30,0);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7876,'ADAMS','CLERK',7788,'1981-09-08',1100,20);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7900,'JAMES','CLERK',7698,'1981-12-23',950,30);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,20);

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7934,'MILLER','CLERK',7782,'1980-12-17',1300,10);

                                          

SELECT * FROM EMP;

SELECT * FROM DEPT;

SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 20;

SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='MANAGER’;

SELECT ENAME, JOB, SAL FROM EMP WHERE JOB!='MANAGER';

SELECT * FROM EMP WHERE HIREDATE BETWEEN'1981-03-01' AND '1983-06-01';

SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';

SELECT ENAME FROM EMP WHERE ENAME LIKE '%S';

SELECT ENAME FROM EMP WHERE YEAR (HIREDATE) =1981;

SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN(20,40);

SELECT ENAME,JOB,DEPTNO FROM EMP WHERE JOB IN('CLERK','SALESMAN');

SELECT ENAME FROM EMP WHERE JOB='MANAGER' AND SAL>=2000;

SELECT ENAME FROM EMP WHERE DEPTNO =30 ORDER BY SAL DESC;

SELECT SUM(SAL) AS TOTAL_SALARY FROM EMP;

SELECT AVG(SAL) AS AVERAGE_SALARY FROM EMP WHERE DEPTNO =30;

SELECT MIN(SAL) AS MINIMUM_SALARY FROM EMP WHERE DEPTNO=20;

SELECT MAX(SAL) AS MAXIMUM_SALARY FROM EMP WHERE DEPTNO=20;

SELECT MAX(HIREDATE)AS MAXIMUM_HIREDATE FROM EMP;

SELECT COUNT(ENAME) TOTAL_EMPLOYEES FROM EMP WHERE DEPTNO=10;

SELECT SUM(SAL) TOTAL_SALARY FROM EMP WHERE JOB!='MANAGER';

SELECT DEPTNO, SUM(SAL) TOTAL_SALARY FROM EMP WHERE JOB!='SALESMAN' GROUP BY DEPTNO HAVING SUM(SAL)>8500;

SELECT SQRT(SUM(SAL)) AS SQUARE_ROOT FROM EMP;

SELECT JOB,AVG(SAL) AVERGAE_SALARY FROM EMP GROUP BY JOB HAVING COUNT(ENAME)>2;

THANKS TO SHRIYANSH KOTHARI