1.List the employees who are getting less salary than it’s department average salary?
SELECT * FROM EMP E1 WHERE SAL <(SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO);2.Display last word (Means Last Letter of that WORD) for every name?
SELECT SUBSTR(ENAME, -1) FROM EMP;3.Query for Nth highest salary?
SELECT * FROM EMP E1 WHERE 2 = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL)4.Query For max salary – department wise?
SELECT * FROM EMP E1 WHERE N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL AND E1.DEPTNO = E2.DEPTNO)5.Display the duplicate records?
SELECT * FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) >1)6.Display the selected records?
SELECT * FROM (SELECT ROWNUM R,ENAME,EMPNO,SAL,DEPTNO FROM EMP) WHERE R = 4 OR R = 97.Find how many comma’s in a string? For Example – ‘K,R,I,S,H,N,A’?
SELECT LENGTH('K,R,I,S,H,N,A')-LENGTH(REPLACE('K,R,I,S,H,N,A',',',NULL)) FROM DUAL8.How to delete duplicate records?
DELETE FROM EMP WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO, ENAME, SAL, JOB, DEPTNO)9.Select updated records?
SELECT S.ENAME, S.EMPNO, S.SAL FROM EMP S, EMP T WHERE S.ENAME != T.ENAME OR S.EMPNO != T.EMPNO OR S.SAL != T.SAL10.How to find the day in given date?
SELECT TO_CHAR(TO_DATE('15-AUG-1947','DD-MM-YY'),'DAY') FROM DUAL;11.Find experience of employees in Employee table?
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) AS EXPERIENCE FROM EMP12.Find the last record of a Employee table?
SELECT * FROM EMP WHERE ROWID = (SELECT MAX(ROWID) FROM EMP);13.To fetch ALTERNATE records from a table. (EVEN NUMBERED)
SELECT * FROM EMP WHERE ROWID IN (SELECT DECODE(MOD(ROWNUM,2),0,ROWID, NULL) FROM EMP);14.To select ALTERNATE records from a table. (ODD NUMBERED)
SELECT * FROM EMP WHERE ROWID IN (SELECT DECODE(MOD(ROWNUM,2),0,NULL ,ROWID) FROM EMP);15.Find the 3rd MAX salary in the emp table.
SELECT DISTINCT SAL FROM EMP E1 WHERE 3 = (SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E1.SAL <= E2.SAL);16.Find the 3rd MIN salary in the emp table.
SELECT DISTINCT SAL FROM EMP E1 WHERE 3 = (SELECT COUNT(DISTINCT SAL) FROM EMP E2WHERE E1.SAL >= E2.SAL);17.Select FIRST n records from a table.
SELECT * FROM EMP WHERE ROWNUM <= &N;18.Select LAST n records from a table
SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM <= (SELECT COUNT(*) - &N FROM EMP);19.List dept no., Dept name for all the departments in which there are no employees in the department.
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
ALTERNATE SOLUTION: SELECT * FROM DEPT A WHERE NOT EXISTS (SELECT * FROM EMP B WHERE A.DEPTNO = B.DEPTNO);
ALTERTNATE SOLUTION: SELECT EMPNO,ENAME,B.DEPTNO,DNAME FROM EMP A, DEPT B WHERE A.DEPTNO(+) = B.DEPTNO AND EMPNO IS NULL;20.How to get 3 Max salaries?
SELECT DISTINCT SAL FROM EMP A WHERE 3 >= (SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL <= B.SAL) ORDER BY A.SAL DESC;21.Second highest salary department wise without using analytical functions?
SELECT T.DEPTNO, MAX(T.SALARY) AS MAXS FROM TABLE T
WHERE T.SALARY < (SELECT MAX(SALARY)
FROM TABLE T2 WHERE T2.DEPTNO = T.DEPTNO )
GROUP BY T.DEPTNO;22.Second highest salary department wise
SELECT MAX(E1.SAL), E1.DEPTNO FROM S_EMP E1
WHERE SAL < (SELECT MAX(SAL) FROM S_EMP E2
WHERE E2.DEPTNO = E1.DEPTNO)
GROUP BY E1.DEPTNO;23.How to get 3 Min salaries?
SELECT DISTINCT SAL FROM EMP A WHERE 3 >= (SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL >= B.SAL);24.How to get nth max salaries ?
SELECT DISTINCT HIREDATE FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL >= B.SAL);25.Select DISTINCT RECORDS from emp table.
SELECT * FROM EMP A WHERE ROWID = (SELECT MAX(ROWID) FROM EMP B WHERE A.EMPNO=B.EMPNO);26.How to delete duplicate rows in a table?
DELETE FROM EMP A WHERE ROWID != (SELECT MAX(ROWID) FROM EMP B WHERE A.EMPNO=B.EMPNO);27.Count of number of employees in department wise.
SELECT COUNT(EMPNO), B.DEPTNO, DNAME FROM EMP A, DEPT B WHERE A.DEPTNO(+)=B.DEPTNO GROUP BY B.DEPTNO,DNAME;28.Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
SELECT ENAME,SAL/12 AS MONTHLYSAL FROM EMP;29.Select all record from emp table where deptno =10 or 40.
SELECT * FROM EMP WHERE DEPTNO=30 OR DEPTNO=10;30.Select all record from emp table where deptno=30 and sal>1500.
SELECT * FROM EMP WHERE DEPTNO=30 AND SAL>1500;31.Select all record from emp where job not in SALESMAN or CLERK.
SELECT * FROM EMP WHERE JOB NOT IN ('SALESMAN','CLERK');32.Select all record from emp where ename in ‘BLAKE’,’SCOTT’,’KING’and’FORD’.
SELECT * FROM EMP WHERE ENAME IN('JONES','BLAKE','SCOTT','KING','FORD');33.Select all records where ename starts with ‘S’ and its lenth is 6 char.
SELECT * FROM EMP WHERE ENAME LIKE'S____';34.Select all records where ename may be any no of character but it should end with ‘R’.
SELECT * FROM EMP WHERE ENAME LIKE'%R';35.Count MGR and their salary in emp table.
SELECT COUNT(MGR),COUNT(SAL) FROM EMP;36.In emp table add comm+sal as total sal .
SELECT ENAME,(SAL+NVL(COMM,0)) AS TOTALSAL FROM EMP;37.Select any salary <3000 from emp table.
SELECT * FROM EMP WHERE SAL> ANY(SELECT SAL FROM EMP WHERE SAL<3000);38.Select all salary <3000 from emp table.
SELECT * FROM EMP WHERE SAL> ALL(SELECT SAL FROM EMP WHERE SAL<3000);39.Select all the employee group by deptno and sal in descending order.
SELECT ENAME,DEPTNO,SAL FROM EMP ORDER BY DEPTNO,SAL DESC;40.How can I create an empty table emp1 with same structure as emp?
CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE 1=2;41.How to retrive record where sal between 1000 to 2000?
SELECT * FROM EMP WHERE SAL>=1000 AND SAL<200042.Select all records where dept no of both emp and dept table matches.
SELECT * FROM EMP WHERE EXISTS(SELECT * FROM DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO)43.If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(SELECT * FROM EMP) UNION (SELECT * FROM EMP1)44.How to fetch only common records from two tables emp and emp1?
(SELECT * FROM EMP) INTERSECT (SELECT * FROM EMP1)45.How can I retrive all records of emp1 those should not present in emp2?
(SELECT * FROM EMP) MINUS (SELECT * FROM EMP1)46.Count the totalsa deptno wise where more than 2 employees exist.
SELECT DEPTNO, SUM(SAL) AS TOTALSAL
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 2