Oracle SQL FAQ

$Date: 15-Aug-2002 $
$Revision: 1.88 $
$Author: Frank Naudé $

Topics

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.

The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9.

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like what rollback segment to use

        SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

Use two quotes for every one displayed. Example:

        SELECT 'Franks''s Oracle site' FROM DUAL;
        SELECT 'A ''quoted'' word.' FROM DUAL;
        SELECT 'A ''''double quoted'''' word.' FROM DUAL;				

Method 1:

   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);

   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;

   SQL> Delete from my_table where rowid not in(
   SQL>     select max(rowid) from my_table
   SQL>     group by my_column_name );

   SQL> delete from my_table t1
   SQL> where  exists (select 'x' from my_table t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid      > t1.rowid);

Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

        SQL> UPDATE table_name SET seqno = ROWNUM;

or use a sequences generator:

        SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
        SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

        select floor(((date1-date2)*24*60*60)/3600)
               || ' HOURS ' ||
               floor((((date1-date2)*24*60*60) -
               floor(((date1-date2)*24*60*60)/3600)*3600)/60)
               || ' MINUTES ' ||
               round((((date1-date2)*24*60*60) -
               floor(((date1-date2)*24*60*60)/3600)*3600 -
               (floor((((date1-date2)*24*60*60) -
               floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
               || ' SECS ' time_difference
        from   ...

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

        select to_char(to_date('00:00:00','HH24:MI:SS') +
                    (date1 - date2), 'HH24:MI:SS') time_difference
        from ...

Note that the second query only uses the time-of-day portion of the date fields and thus will never return a value bigger than 23:59:59.

        SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
		
        SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
        -------------------- -------------------- -------------------- --------------------
        03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

       select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

        NOW                  NOW_PLUS_30_SECS
        -------------------- --------------------
        03-JUL-2002 16:47:23 03-JUL-2002 16:47:53

        select dept, sum(  decode(sex,'M',1,0)) MALE,
                     sum(  decode(sex,'F',1,0)) FEMALE,
                     count(decode(sex,'M',1,'F',1)) TOTAL
        from   my_emp_table
        group  by dept;

        select f2,
               sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
               sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
               sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
        from   my_table
        group  by f2;

        select ename "Name", sal "Salary",
               decode( trunc(f2/1000, 0), 0, 0.0,
                                          1, 0.1,
                                          2, 0.2,
                                          3, 0.31) "Tax rate"
        from   my_table;

        SELECT f1 FROM t1
        WHERE  rowid = (
           SELECT rowid FROM t1
           WHERE  rownum <= 10
          MINUS
           SELECT rowid FROM t1
           WHERE  rownum < 10);

        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < 10);

        SELECT *
        FROM   tableX
        WHERE  rowid in (
           SELECT rowid FROM tableX
           WHERE rownum <= 7
          MINUS
           SELECT rowid FROM tableX
           WHERE rownum < 5);

Method 1: Using a subquery

        SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);

        SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;

        SELECT *
        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
        WHERE  ROWNUM < 10;

        SELECT *
          FROM my_table a
         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                        FROM my_table b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;

The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:

        select  LEVEL, EMPNO, ENAME, MGR
          from  EMP
        connect by prior EMPNO = MGR
          start with MGR is NULL;

        select lpad(' ', LEVEL * 2) || ENAME ........

One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

        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 scott.emp
                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

Some examples:

        select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
        from   employees;

        select a, b, decode( abs(a-b), a-b, 'a > b',
                                       0,   'a = b',
                                            'a < b') from  tableX;

        select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...
		
        select decode( GREATEST(A,B), 
                  A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 
                  'A NOT GREATER THAN B')...

From Oracle 8i one can also use CASE statements in SQL. Look at this example:

        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END
        FROM   emp;

        SELECT DUMP(col1)
        FROM tab1
        WHERE cond1 = val1;

        DUMP(COL1)
        -------------------------------------
        Typ=96 Len=4: 65,66,67,32

Other workarounds:

1. SQL> update t1 set column_to_drop = NULL;
   SQL> rename t1 to t1_base;
   SQL> create view t1 as select <specific columns> from t1_base;

2. SQL> create table t2 as select <specific columns> from t1;
   SQL> drop table t1;
   SQL> rename t2 to t1;

1. -- Use a view with correct column names...
   rename t1 to t1_base;
   create view t1 <column list with new name> as select * from t1_base;

2. -- Recreate the table with correct column names...
   create table t2 <column list with new name> as select * from t1;
   drop table t1;
   rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
   alter table t1 add ( newcolame datatype );  
   update t1 set newcolname=oldcolname;
   alter table t1 drop column oldcolname;

From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".

You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.

COPY TO SCOTT/TIGER@REMOTE     -
CREATE IMAGE_TABLE USING       -
       SELECT IMAGE_NO, IMAGE  -
       FROM   IMAGES;

• The SQL FAQ

• Introduction to Structured Query Language

• SQL Tutorial

• Tina London's SQL Guide