Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Writing SQL queries

</p> <p>Writing SQL queries<br />



Also see
How to debug PL/SQL code


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


This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

One Comment

  1. Pleiadian says:

    Don't try the suggestions in "Eliminating duplicate values in a table"
    <code>
    Method 1:
    ———
      DELETE FROM emp a
        WHERE rowid > (
          SELECT min(rowid)
          FROM   emp b
          WHERE  a.emp_id = b.emp_id
        );
    </code>
    This will delete a lot more…
    And I would not reccomend:
    <code>
    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';
    </code>
    But rather:
    <code>
      SELECT  dname, loc
      FROM    dept
      WHERE   loc in ('CHICAGO','NEW YORK');
    </code>
     

Post a Comment

You must be logged in to post a comment.