Writing SQL queries

Transform a subquery involving the IN clause to a Join?

Original Statement: 
  SELECT  e.empno, e.ename
  FROM    emp e
  WHERE   e.deptno IN (
      SELECT  d.deptno
      FROM    dept d
      WHERE   d.loc = 'CHICAGO'
  );

New Statement: 
  SELECT e.empno, e.ename
  FROM   emp e, dept d
  WHERE  d.loc = 'CHICAGO'
    AND  d.deptno = e.deptno;

Transform a statement involving an OR condition to a UNION ALL?

Original Statement: 
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
     OR   loc = 'NEW YORK';


New Statement: 
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
  UNION ALL
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'NEW YORK';

Eliminating duplicate values in a table?

Provided below are four methods for identifying or removing duplicate rows from a table: 

Method 1: 
---------
  DELETE FROM emp a
    WHERE rowid > (
      SELECT min(rowid)
      FROM   emp b
      WHERE  a.emp_id = b.emp_id
    );

Method 2: 
---------
  CREATE TABLE emp2 AS
    SELECT distinct *
    FROM   emp;

  DROP TABLE emp;

  RENAME emp2 TO emp;

Method 3: 
---------
  DELETE FROM emp
    WHERE rowid NOT IN (
      SELECT    MIN(rowid)
      FROM      emp
      GROUP BY  emp_id
    );

Method 4: 
---------
  DELETE FROM emp a
    WHERE EXISTS (
      SELECT 'true'
      FROM   emp b
      WHERE  b.emp_id = a.emp_id
        AND  b.rowid < a.rowid
    );NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record. 

Getting a count of the different data values in a column?

  SELECT dname
    , sum(decode(job, 'CLERK',     1, 0)) Clerk
    , sum(decode(job, 'SALESMAN',  1, 0)) Salesman
    , sum(decode(job, 'MANAGER',   1, 0)) Manager
    , sum(decode(job, 'ANALYST',   1, 0)) Analyst
    , sum(decode(job, 'PRESIDENT', 1, 0)) President
  FROM
      emp e
    , dept d
  WHERE
      e.deptno (+) = d.deptno
  GROUP BY
      dname;

  DNAME               CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
  -------------- ---------- ---------- ---------- ---------- ----------
  ACCOUNTING              1          0          1          0          1
  OPERATIONS              0          0          0          0          0
  RESEARCH                2          0          1          2          0
  SALES                   1          4          1          0          0

Getting count/sum RANGES of data values in a column?

A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z). 
  SELECT
      job
    , sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
    , sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
    , sum(decode(greatest(sal,0),    least(sal,999), 1, 0))  "Range 0-1000"
  FROM      emp
  GROUP BY  job;


  JOB       Range 3000-6000 Range 1000-3000 Range 0-1000
  --------- --------------- --------------- ------------
  ANALYST                 2               0            0
  CLERK                   0               2            2
  MANAGER                 0               3            0
  PRESIDENT               1               0            0
  SALESMAN                0               4            0

Retrieve only the Nth row from a table?

Method 1: 
---------

SELECT ename, job, hiredate FROM emp WHERE rowid=
        (SELECT rowid FROM emp WHERE rownum <= 3
         MINUS
        SELECT rowid FROM emp WHERE rownum < 3
        );

Method 2: 
---------
SELECT ename, job, hiredate FROM emp WHERE rownum = 1
    AND rowid NOT IN (SELECT rowid FROM emp WHERE  rownum < 3);
   
NOTE: Always remember that there is no explicit order in a relational database. 

Retrieve rows between N and M from a table?

  SELECT ename, job, hiredate FROM emp WHERE rowid in
      (
        SELECT rowid FROM emp WHERE rownum <= 7
        MINUS
        SELECT rowid FROM emp WHERE rownum < 3
      );

Retrieve EVERY Nth row from a table?

Method 1: 
---------
SELECT ename, job, hiredate FROM emp WHERE (rowid,0) in
      (
        SELECT rowid, mod(rownum,4) FROM emp
      );

Method 2: 
---------
Using Dynamic Views: (available in Oracle7.2 and higher) 
SELECT ename, job, hiredate FROM (
             SELECT rownum rn, empno, ename, job, hiredate FROM   emp) d_table
             WHERE mod(d_table.rn,4) = 0;

Retrieve the TOP N Rows from a table?

Method 1: 
---------
Starting with Oracle8i, you can have an inner-query with an ORDER BY clause 
  SELECT
      ename
    , job
    , hiredate
    , sal
  FROM (  SELECT empno, ename, job, hiredate, sal
          FROM   emp
          ORDER BY sal DESC
       )
  WHERE rownum = ( SELECT count(distinct b.sal)
               FROM   emp b
               WHERE  b.sal >= a.sal
             )
  ORDER BY a.sal DESC

MATRIX Report using SQL?

  SELECT *
  FROM ( SELECT    job
                 , sum(decode(deptno, 10, sal)) DEPT10
                 , sum(decode(deptno, 20, sal)) DEPT20
                 , sum(decode(deptno, 30, sal)) DEPT30
                 , sum(decode(deptno, 40, sal)) DEPT40 
         FROM      emp e
         GROUP BY  job
  ) 
  ORDER BY 1;


  JOB           DEPT10     DEPT20     DEPT30     DEPT40
  --------- ---------- ---------- ---------- ----------
  ANALYST                    6000
  CLERK           1300       1900        950
  MANAGER         2450       2975       2850
  PRESIDENT       5000
  SALESMAN                              5600