Oracle SQL FAQ

• 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

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

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;

Finally, create a unique index on this column.

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

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 than B', 'B is greater than A')...

With previous releases one can use Joseph S. Testa's DROP COLUMN package that can be downloaded from http://www.oracle-dba.com/ora_scr.htm.

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. rename t1 to t1_base;
   create view t1 <column list with new name> as select * from t1_base;

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

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;