Useful Queries in SQL

Useful Queries in SQL

1. Fetch alternate(even number) records from emp table.
   SELECT * FROM emp WHERE rowid IN (SELECT DECODE(MOD(rownum,2),0,rowid,NULL) FROM emp);

2. Fetch alternate(odd numbered) records from emp table.
   SELECT * FROM emp WHERE rowid IN (SELECT DECODE(MOD(rownum,2),0,NULL ,rowid) FROM emp);

3. Find out the third maximum 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);

4. Find out the third minimum 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);

5. Select first n records from emp table.
   SELECT * FROM emp WHERE rownum <= &n;

6. Select last n records from emp table
   SELECT * FROM emp MINUS SELECT * FROM emp WHERE rownum <= (SELECT COUNT(*) – &n FROM emp);

7. 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 for above example:

SELECT * FROM dept a WHERE NOT EXISTS (SELECT * FROM emp b WHERE a.deptno = b.deptno);
Altertnate solution for above example:

SELECT empno, ename, b.deptno, dname FROM emp a, dept b WHERE a.deptno(+) = b.deptno AND empno is NULL;

8. How to get three maximum 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;

9. How to get three minimum salaries?
   SELECT DISTINCT sal FROM emp a WHERE 3 >= (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

10. How to get nth maximum salary?
    SELECT DISTINCT hiredate FROM emp a WHERE &n = (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

11. Select distinct records from emp table.
    SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

12. 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);

13. Count the number of employees, department wise.
    SELECT COUUNT(empno), b.deptno, dname FROM emp a, dept b WHERE a.deptno(+)=b.deptno GROUP BY b.deptno,dname;

14. If annual salary information provided by emp table. Fetch monthly salary of each and every employee?

SELECT ename, sal / 12 AS monthlysal FROM emp;

15. Select all records from emp table where deptno either 30 or 50.

SELECT * FROM emp WHERE deptno=30 OR deptno=50;

16. Select all records from emp table where deptno is 10 and salary is above 5000.

SELECT * FROM emp WHERE deptno=10 AND sal>5000;

17. Select all records from emp table where job not in SALESMAN or CLERK.

SELECT * FROM emp WHERE job NOT IN (‘SALESMAN’,’CLERK’);

18. Select all records from emp where ename in ‘BLAKE’, ‘SCOTT’, ‘KING’ and ‘FORD’.

SELECT * FROM emp WHERE ename IN(‘JONES’, ‘BLAKE’, ‘SCOTT’, ‘KING’, ‘FORD’);

19. Select all records where ename starts with ‘S’ and its lenth is 6 char.

SELECT * FROM emp WHERE ename LIKE’S____’;

20. Select all records where ename may be any no of character but it should end with ‘R’.

SELECT * FROM emp WHERE ename LIKE’%R’;

21. Count MGR and their salary in emp table.

SELECT COUNT(MGR), COUNT(sal) FROM emp;

22. In emp table add comm+sal as total salary .

SELECT ename,(sal+nvl(comm,0)) AS totalsal FROM emp;

23. Select any salary <2000 FROM emp table.

SELECT * FROM emp WHERE sal> ANY(SELECT sal FROM emp WHERE sal<2000);

24. Select all salary <5000 FROM emp table.

SELECT * FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE sal<5000);

25. Select all the employee group by deptno and sal in descending order. SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC;

26. Create an empty table emp1 with same structure as emp?

CREATE TABLE emp1 AS SELECT * FROM emp WHERE 1=2;

27. How to retrive record where sal between 3000 to 5000?
    SELECT * FROM emp WHERE sal>=3000 AND sal<5000

28. Select all records where both emp and dept table matches deptno.
    SELECT * FROM emp WHERE EXISTS(SELECT * FROM dept WHERE emp.deptno=dept.deptno)

29. If there are two tables emp1 and emp2, and both have common record. Fetch all the recods, but common records only once?
    (SELECT * FROM emp) UNION (SELECT * FROM emp1)

30. Fetch only common records from two tables emp and emp1?
    (SELECT * FROM emp) INTERSECT (SELECT * FROM emp1)

31. How to retrive all records of emp1 those should not present in emp2?
    (SELECT * FROM emp) MINUS (SELECT * FROM emp1)

32. Count the total salary deptno. wise where more than two employees exist.
    SELECT deptno, SUM(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2