Useful Queries in SQL

Fetch alternate(even number) records from emp table.
SELECT * FROM emp WHERE rowid IN (SELECT DECODE(MOD(rownum,2),0,rowid,NULL) FROM emp); 
Fetch alternate(odd numbered) records from emp table.
SELECT * FROM emp WHERE rowid IN (SELECT DECODE(MOD(rownum,2),0,NULL ,rowid) FROM emp); 
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); 
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); 
Select first n records from emp table.
SELECT * FROM emp WHERE rownum <= &n; 
Select last n records from emp table
SELECT * FROM emp MINUS SELECT * FROM emp WHERE rownum <= (SELECT COUNT(*) – &n FROM emp); 
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;

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; 
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); 
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); 
Select distinct records from emp table.
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno); 
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); 
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; 
If annual salary information provided by emp table. Fetch monthly salary of each and every employee?
SELECT ename, sal / 12 AS monthlysal FROM emp;

Select all records from emp table where deptno either 30 or 50.
SELECT * FROM emp WHERE deptno=30 OR deptno=50;

Select all records from emp table where deptno is 10 and salary is above 5000.
SELECT * FROM emp WHERE deptno=10 AND sal>5000;

Select all records from emp table where job not in SALESMAN or CLERK.
SELECT * FROM emp WHERE job NOT IN (‘SALESMAN’,’CLERK’);

Select all records from emp where ename in ‘BLAKE’, ‘SCOTT’, ‘KING’ and ‘FORD’.
SELECT * FROM emp WHERE ename IN(‘JONES’, ‘BLAKE’, ‘SCOTT’, ‘KING’, ‘FORD’);

Select all records where ename starts with ‘S’ and its lenth is 6 char.
SELECT * FROM emp WHERE ename LIKE’S____’;

Select all records where ename may be any no of character but it should end with ‘R’.
SELECT * FROM emp WHERE ename LIKE’%R’;

Count MGR and their salary in emp table.
SELECT COUNT(MGR), COUNT(sal) FROM emp;

In emp table add comm+sal as total salary .
SELECT ename,(sal+nvl(comm,0)) AS totalsal FROM emp;

Select any salary <2000 FROM emp table.
SELECT * FROM emp WHERE sal> ANY(SELECT sal FROM emp WHERE sal<2000);

Select all salary <5000 FROM emp table.
SELECT * FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE sal<5000);

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

Create an empty table emp1 with same structure as emp?
CREATE TABLE emp1 AS SELECT * FROM emp WHERE 1=2;

How to retrive record where sal between 3000 to 5000?
SELECT * FROM emp WHERE sal>=3000 AND sal<5000 
Select all records where both emp and dept table matches deptno.
SELECT * FROM emp WHERE EXISTS(SELECT * FROM dept WHERE emp.deptno=dept.deptno) 
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) 
Fetch only common records from two tables emp and emp1?
(SELECT * FROM emp) INTERSECT (SELECT * FROM emp1) 
How to retrive all records of emp1 those should not present in emp2?
(SELECT * FROM emp) MINUS (SELECT * FROM emp1) 
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