Tough SQL Queries - Part 9

These questions asked in most of the interviews:

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

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

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

 4.Find the 3rd MIN 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 a table.
SELECT *
FROM emp
WHERE rownum <= n;

 6.Select LAST n records
FROM a 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:
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;

 8.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;

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

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

 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 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;

 14. 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;

 15.Select ALL record
FROM emp TABLE
WHERE deptno =10
  OR 40.
  SELECT *
  FROM emp WHERE deptno=30
  OR deptno=10;

 16.Select ALL record
FROM emp TABLE
WHERE deptno=30
  AND sal>1500.
  SELECT *
  FROM emp WHERE deptno=30
  AND sal>1500;

 17.Select ALL record
FROM emp
WHERE job NOT IN SALESMAN
  OR CLERK.
  SELECT *
  FROM emp WHERE job NOT IN (‘SALESMAN’,'CLERK’);

18.Select all record 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 sal .
SELECT ename,
       (sal+nvl(comm,0)) AS totalsal
FROM emp;

 23.Select ANY salary <3000
FROM emp TABLE.
SELECT *
FROM emp
WHERE sal> ANY
    (SELECT sal
     FROM emp
     WHERE sal<3000);

 24.Select ALL salary <3000
FROM emp TABLE.
SELECT *
FROM emp
WHERE sal> ALL
    (SELECT sal
     FROM emp
     WHERE sal<3000);

 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.How can I
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 1000 TO 2000?
  SELECT *
  FROM emp WHERE sal>=1000
  AND sal<2000 28.
  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) 29.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) 30.How TO FETCH ONLY common records
FROM two tables emp
AND emp1?
  (SELECT *
   FROM emp) INTERSECT
  (SELECT *
   FROM emp1) 31. How can I retrive ALL records OF emp1 those should NOT present IN emp2?
  (SELECT *
   FROM emp) Minus
  (SELECT *
   FROM emp1) 32.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