SQL Queries for Interview

Sql Queries

SELECT * FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'yyyy')<1982;
SELECT * FROM SCOTT.EMP WHERE COMM>SAL;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP,SAL/30 SAL FROM SCOTT.EMP ;--where sal/30>200;
SELECT ENAME,EMPNO,SAL,TO_CHAR(HIREDATE,'day') FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'day')IN( 'monday','saturday');
SELECT * FROM SCOTT.EMP WHERE SAL<=1000;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP FROM  SCOTT.EMP;
SELECT DISTINCT * FROM SCOTT.EMP;
SELECT DISTINCT(DEPTNO) FROM SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE EMPNO IN(SELECT MGR FROM SCOTT.EMP WHERE SAL>2000);
SELECT * FROM SCOTT.EMP WHERE MGR IS NULL;
SELECT (SAL*12),EMPNO FROM SCOTT.EMP;
SELECT JOB,DEPTNO,ENAME FROM SCOTT.EMP;
SELECT ENAME,SAL,JOB FROM SCOTT.EMP WHERE JOB IN(SELECT JOB FROM SCOTT.EMP WHERE ENAME='FORD');
SELECT * FROM SCOTT.EMP WHERE HIREDATE>(SELECT HIREDATE FROM SCOTT.EMP WHERE ENAME='KING');
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP);
SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL=(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='CLERK');
SELECT AVG(SAL),MIN(SAL),MAX(SAL),ENAME,JOB FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='MANAGER' GROUP BY ENAME,JOB;
SELECT DEPTNO FROM SCOTT.EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*)>=2;
SELECT SUM(NVL(COMM,100)+SAL),DEPTNO FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT ENAME FROM SCOTT.EMP WHERE SAL >(SELECT SAL FROM SCOTT.EMP WHERE ENAME='JONES') AND SAL> (SELECT SAL FROM SCOTT.EMP WHERE ENAME='SCOTT');
SELECT * FROM SCOTT.EMP E WHERE SAL=(SELECT MAX(SAL) FROM SCOTT.EMP  WHERE DEPTNO=E.DEPTNO);
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL<(SELECT SAL FROM SCOTT.EMP WHERE ENAME='PRESIDENT');
SELECT ENAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
CREATE VIEW TEMP_VIEW AS SELECT  ENAME,EMPNO,JOB,SAL,MGR,HIREDATE,E.DEPTNO,LOC,DNAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO;
SELECT * FROM TEMP_VIEW;
SELECT COUNT(EMPNO) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='NEW YORK';
SELECT  DNAME,LOC,COUNT(EMPNO),AVG(SAL) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=&DEPTNO GROUP BY DNAME, LOC;
SELECT E.ENAME MGR,F.ENAME  FROM SCOTT.EMP E,SCOTT.EMP F WHERE E.EMPNO=F.MGR;
SELECT JOB FROM SCOTT.EMP  HAVING SUM(SAL) >(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MGR');
SELECT SAL,(CASE WHEN SAL<=1500 THEN 'Low'
WHEN SAL>2000 THEN 'High'
ELSE 'Med'
END ) RANGE,
DECODE(SAL,5000,'King','ASDF') ASD
FROM
SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'yyyy')-TO_CHAR(HIREDATE,'yyyy')>=27;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(HIREDATE,'YYYY')>=30;
SELECT USERNAME FROM ALL_USERS;
SELECT TNAME FROM TAB;
SHOW USER;
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO IN(10,20,40) OR JOB IN('CLERKS','SALESMAN','ANALYST');
SELECT ENAME,SAL*12 FROM SCOTT.EMP ORDER BY SAL DESC;
SELECT DEPTNO,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT JOB,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY JOB;
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL =(SELECT MAX(SAL) FROM SCOTT.EMP);
DISPLAY THE NAMES OF CLERKS WHO EARN A SALARY MORE THAN THE LOWEST SALARY OF ANY SALESMAN.
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL>(SELECT MIN(SAL) FROM SCOTT.EMP WHERE JOB='SALESMAN');
DISPLAY THE NAMES OF THE EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE DEPARTMENTS
SELECT ENAME,DEPTNO,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP GROUP BY DEPTNO);
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO=(SELECT DEPTNO FROM SCOTT.DEPT WHERE DNAME='ACCOUNTING');
OR
SELECT ENAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND DNAME='ACCOUNTING';
DISPLAY THE JOB GROUPS HAVING TOTAL SALARY GREATER THAN THE MAXIMUM SALARY FOR MANAGERS.
SELECT JOB,SUM(SAL) TOTAL_SAL FROM SCOTT.EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MANAGER');
SELECT ENAME,LENGTH(ENAME) FROM SCOTT.EMP;
SELECT EMPNO,ENAME,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') FROM SCOTT.EMP;
SELECT TO_DATE(SYSDATE)-TO_DATE('05-may-90')FROM DUAL;
SELECT TO_DATE(SYSDATE)-TO_DATE('17-dec-88')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'ddth Month day year') FROM DUAL;
SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=20);
DISPLAY THE DETAILS OF THOSE WHO DO NOT HAVE ANY PERSON WORKING UNDER THEM.
SELECT E.ENAME FROM SCOTT.EMP,SCOTT.EMP E WHERE EMP.MGR=E.EMPNO GROUP BY E.ENAME HAVING COUNT(*)=1;
SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR;
SELECT ENAME FROM SCOTT.EMP WHERE ENAME NOT IN(SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR);
SELECT M.ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.EMPNO=M.MGR AND E.ENAME='JONES';
DISPLAY THOSE EMPLOYEES WHOSE SALARY GRETER THAN HIS MANAGER SALARY.
SELECT P.ENAME FROM SCOTT.EMP E,SCOTT.EMP P WHERE E.EMPNO=P.MGR AND P.SAL>E.SAL;
DISPLAY THOSE EMPLOYEE WHOSE SALARY IS EQUAL TO AVERAGE OF MAXIMUM AND MINIMUM?
SELECT ENAME FROM SCOTT.EMP WHERE SAL=(SELECT MAX(SAL)+MIN(SAL)/2 FROM SCOTT.EMP);
SELECT COUNT(*) FROM SCOTT.EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>3
FIND OUT LAST 5(LEAST)EARNERS OF THE COMPANY.?;
SELECT DISTINCT SAL FROM SCOTT.EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM SCOTT.EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;
SELECT C.ENAME FROM SCOTT.EMP C, SCOTT.EMP E WHERE C.SAL=E.DEPTNO;