Problem solve Get help with specific problems with your technologies, process and projects.

Fun with SQL

SearchOracle.com contributor Jeff Hunter provides answers to some of your basic SQL questions.

Jeff Hunter

Jeff Hunter is a SearchOracle.com guest contributor. Jeff is a senior DBA and author. He specializes in advanced...

performance tuning, Java programming, capacity planning, database security and physical/logical database design in UNIX, Linux and Windows NT environments.

You can find more of Jeff's work at www.iDevelopment.info.

TABLE OF CONTENTS
   Overview
   How can I transform a subquery involving the IN clause to a Join?
   How can I transform a statement involving an OR condition to a UNION ALL?
   How do I eliminate duplicate values in a table?
   How can I get a count of the different data values in a column?
   How can I get count/sum RANGES of data values in a column?
   How can I get the time difference between two date columns?
   Can I retrieve only the Nth row from a table?
   Can I retrieve only rows X to Y from a table?
   Can I retrieve every Nth row from a table?
   Retrieve the TOP N Rows from a table?
   Is it possible to dump/examine the exact content of a database column?
   Can I code a MATRIX Report using SQL?
   Tree-structured (hierarchical) queries


Overview
[ Return to Table of Contents ]

Structured Query Language (SQL) is an implementation of a "data sublanguage" used in almost all relational database systems. SQL was developed by IBM in the early 1970s for use in System R, and is "de facto" standard, as well as an ISO and ANSI standard.

In common usage, SQL supports four types of SQL statements:

Queries: This type of SQL statement implements the standard relational operations such as SELECTION, PROJECTION and JOIN. The SELECT statement forms the basis for many other SQL operations. You can use SELECT statements to query tables, create views, in subqueries, or when creating a table defined as the result set of query.

Data Manipulation Language (DML): DML statements are used to allow data in the database to be added, amended or deleted. DML functionality is provided by the use of INSERT, UPDATE and DELETE statements.

Data Definition Language (DDL): This is the language that allows for objects to be created or altered. Provided below are a few example Oracle DDL statements:

  • CREATE TABLE
  • DROP TABLE
  • ALTER TABLE
  • CREATE INDEX
  • DROP INDEX
  • ALTER INDEX
  • CREATE VIEW
  • CREATE TYPE
  • Data Control Language (DCL): This is the language that allows for transaction control. Provided below are a few example Oracle DCL statements:

  • COMMIT
  • SAVEPOINT
  • ROLLBACK
  • SET TRANSACTION
  • The development of SQL is governed by standards. A major revision of the SQL standard was completed in 1992, called SQL2. SQL3 is a newer revision that supports object extensions and will be partially implemented starting with Oracle8.


    How can I transform a subquery involving the IN clause to a Join?
    [ Return to Table of Contents ]

    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;


    How can I transform a statement involving an OR condition to a UNION ALL?
    [ Return to Table of Contents ]

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


    How do I eliminate duplicate values in a table?
    [ Return to Table of Contents ]

    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.


    How can I get a count of the different data values in a column?
    [ Return to Table of Contents ]

      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
    


    How can I get count/sum RANGES of data values in a column?
    [ Return to Table of Contents ]

    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
    


    How can I get the time difference between two date columns?
    [ Return to Table of Contents ]

    Method 1: (Very long-winded. A more efficient version is indicated in Method 2.)

      SELECT floor(((e1.hiredate-e2.hiredate)*24*60*60)/3600)
             || ' HOURS ' ||
             floor((( (e1.hiredate-e2.hiredate)*24*60*60) -
                      floor(((e1.hiredate-e2.hiredate)*24*60*60)/3600)*3600)/60)
             || ' MINUTES ' ||
             round((( (e1.hiredate-e2.hiredate)*24*60*60) -
                      floor(( (e1.hiredate-e2.hiredate)*24*60*60)/3600)*3600 -
                              (floor((((e1.hiredate-e2.hiredate)*24*60*60) -
                                floor(((e1.hiredate-e2.hiredate)*24*60*60)/3600)*3600)/60)*60))
                              )
             || ' SECONDS ' time_difference
      FROM   emp e1, emp e2
      WHERE  e1.ename = 'BLAKE'
        AND  e2.ename = 'CLARK'
    
    
      TIME_DIFFERENCE
      -------------------------------
      162 HOURS 44 MINUTES 25 SECONDS
    

    Method 2: (Thanks to Chris Hunt, http://www.extracon.com/, for providing me with this much easier and efficient SQL query to produce the same results!)

      SELECT floor((e1.hiredate-e2.hiredate)*24)
           || ' HOURS ' ||
           mod(floor((e1.hiredate-e2.hiredate)*24*60),60)
           || ' MINUTES ' ||
           mod(floor((e1.hiredate-e2.hiredate)*24*60*60),60)
             || ' SECONDS ' time_difference
      FROM   emp e1, emp e2
      WHERE  e1.ename = 'BLAKE'
        AND  e2.ename = 'CLARK'
    
    
      TIME_DIFFERENCE
      -------------------------------
      162 HOURS 44 MINUTES 25 SECONDS
    


    Can I retrieve only the Nth row from a table?
    [ Return to Table of Contents ]

    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.


    Can I retrieve only rows X to Y from a table?
    [ Return to Table of Contents ]

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


    Can I retrieve EVERY Nth row from a table?
    [ Return to Table of Contents ]

    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?
    [ Return to Table of Contents ]

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

    Method 2: The following workaround will work with prior releases.

      SELECT
          ename
        , job
        , hiredate
        , sal
      FROM
          emp a
      WHERE 5 >= ( SELECT count(distinct b.sal)
                   FROM   emp b
                   WHERE  b.sal >= a.sal
                 )
      ORDER BY a.sal DESC
    


    Is it possible to dump/examine the exact content of a database column?
    [ Return to Table of Contents ]

      SELECT ename, job, dump(job)
      FROM   emp
      WHERE  ename = 'SMITH';
    
    
    ENAME      JOB        DUMP(JOB)
    ---------- ---------  ---------------------------
    SMITH      CLERK      Typ=1 Len=5: 67,76,69,82,75
    

    1 VARCHAR2 2 NUMBER 12 DATE 96 CHAR
    Type Code Oracle Datatype

    Oracle type codes for column dumps


    Can I code a MATRIX Report using SQL?
    [ Return to Table of Contents ]

      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
    


    Tree-structured (hierarchical) queries
    [ Return to Table of Contents ]

      SELECT  lpad(' ', level*4) || ename
      FROM    emp
      CONNECT BY PRIOR empno = mgr
      START WITH mgr IS NULL;
    
    
      Employee Name
      ------------------------
      KING
          JONES
              JHUNTER
                  ADAMS
              FORD
                  SMITH
          BLAKE
              ALLEN
              WARD
              MARTIN
              TURNER
              JAMES
          CLARK
              MILLER
    

    NOTE: Using the "EMP" table from the SCOTT/TIGER schema makes a great test table since it has a "self-referencing" relation. (The MGR column contains the employee number of the "current" employee's boss.

    The LEVEL pseudo-column is an indication of hwo deep in the tree you are. Oracle can handle queries with a depth up to 255 levels.

    The "START WITH" clause is used to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "CONNECT BY PRIOR" clause is that you cannot perform a JOIN to other tables.



    This was last published in June 2004

    Dig Deeper on Oracle and SQL

    PRO+

    Content

    Find more PRO+ content and other member only offers, here.

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchDataManagement

    SearchBusinessAnalytics

    SearchSAP

    SearchSQLServer

    TheServerSide.com

    SearchDataCenter

    SearchContentManagement

    SearchFinancialApplications

    Close