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

A collection is an ordered group of elements, all of the same type.

PL/SQL offers these collection types:

• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings
  for subscript values. (They are similar to hash tables in other programming languages.)
• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
  You can define equivalent SQL types, allowing nested tables to be stored in database tables
  and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements
  (although you can change the number of elements at runtime).
  They use sequential numbers as subscripts.

You can define collection types in a procedure, function, or package and pass collection variables as parameters,
to move data between client-side applications and stored subprograms.
To look up data that is more complex than single values,
you can store PL/SQL records or SQL object types in collections.
Nested tables and varrays can also be attributes of object types.

Nested Tables, Varrays and Associative arrays

Within the database, nested tables can be considered one-column database tables.
The size of a nested table can be increased dynamically.
You can delete elements from a nested table.

A varray stores elements in an order and allows to reference individual elements easily
using standard subscripting syntax.
A varray has a maximum size, which you must specify in its type definition.
Its index has a fixed lower bound of 1 and an extensible upper bound.

Associative arrays are sets of key-value pairs, where each key is unique
and is used to locate a corresponding value in the array.
The key can be an integer or a string.
Assigning a value using a key for the first time adds that key to the associative array.

Manipulating Collections

1) Defining Collection Types
To create collections, you define a collection type, then declare variables of that type.
You can define TABLE and VARRAY types in the declarative part of any PL/SQL block,
subprogram, or package. Collection type can be created as object in the database also.

declare
TYPE number_table is TABLE of number(10);
TYPE emptab is TABLE of emp%rowtype;
TYPE empsaltype is table of number index by varchar2(50);
TYPE int_array_type IS VARRAY(50) OF INTEGER;

As database object

SQL> create or replace type emptab as object(empno number, ename varchar2(50));

2) Initializing and Referencing Collections

To initialize a nested table or varray, you use a constructor, a system-defined function
with the same name as the collection type.
This function "constructs" collections from the elements passed to it.

declare
TYPE number_table is TABLE of number(10);
TYPE int_array_type IS VARRAY(50) OF INTEGER;
nt numbet_table := number_table();
int_arr int_array_type := int_array_type(null, null, null, null);

3) Using Collection Methods

The following collection methods help generalize code,
make collections easier to use, and make your applications easier to maintain:

        EXISTS
        COUNT
        LIMIT
        FIRST and LAST
        PRIOR and NEXT
        EXTEND
        TRIM
        DELETE
A collection method is a built-in function or procedure that operates
on collections and is called using dot notation. The syntax follows:
collection_name.method_name[(parameters)]

i)   EXISTS(n) returns TRUE if the nth element in a collection exists.
ii)  COUNT returns the number of elements that a collection currently contains
iii) For varrays, LIMIT returns the maximum number of elements that a varray can contain
iv)   FIRST and LAST return the first and last (smallest and largest) index numbers in a collection.
v)   To increase the size of a nested table or varray, use EXTEND. You cannot use EXTEND with index-by tables.
          This procedure has three forms:
                     EXTEND appends one null element to a collection.
                     EXTEND(n) appends n null elements to a collection.
                     EXTEND(n,i) appends n copies of the ith element to a collection. 

vi) TRIM decreasing the Size of a Collection (TRIM Method)
          This procedure has two forms:
                     TRIM removes one element from the end of a collection.
                     TRIM(n) removes n elements from the end of a collection. 

vii) Deleting Collection Elements (DELETE Method)
          This procedure has various forms:
                     DELETE removes all elements from a collection.
                     DELETE(n) removes the nth element from an associative array with a numeric key or a nested table.
                                        If the associative array has a string key, the element corresponding to the key value is deleted.
                                        If n is null, DELETE(n) does nothing.
                     DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
                                        If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Avoiding Collection Exceptions

In most cases, if you reference a nonexistent collection element, PL/SQL raises a predefined exception. Consider the following example:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList;  -- atomically null
BEGIN
   /* Assume execution continues despite the raised exceptions. */
   nums(1) := 1;            -- raises COLLECTION_IS_NULL       (1)
   nums := NumList(1,2);  -- initialize table
   nums(NULL) := 3          -- raises VALUE_ERROR              (2)
   nums(0) := 3;            -- raises SUBSCRIPT_OUTSIDE_LIMIT  (3)
   nums(3) := 3;            -- raises SUBSCRIPT_BEYOND_COUNT   (4)
   nums.DELETE(1);  -- delete element 1
   IF nums(1) = 1 THEN ...  -- raises NO_DATA_FOUND            (5)

In the first case, the nested table is atomically null. In the second case,
the subscript is null. In the third case, the subscript is outside the legal range.
In the fourth case, the subscript exceeds the number of elements in the table.
In the fifth case, the subscript designates a deleted element.

The following list shows when a given exception is raised:

In some cases, you can pass invalid subscripts to a method without raising an exception.
For instance, when you pass a null subscript to procedure DELETE, it does nothing.
Also, you can replace deleted elements without raising NO_DATA_FOUND, as the following example shows:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList := NumList(10,20,30);  -- initialize table
BEGIN
   nums.DELETE(-1);  -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
   nums.DELETE(3);   -- delete 3rd element
   dbms_output.put_line(nums.COUNT);  -- prints 2
   nums(3) := 30;    -- allowed; does not raise NO_DATA_FOUND
   dbms_output.put_line(nums.COUNT);  -- prints 3
END;

Reducing Loop Overhead for Collections with Bulk Binds

Collections can be used to bulk bind data values with SQL statement

BULK COLLECT

declare
TYPE emptab is TABLE of emp%rowtype;
et emptab;
n1 number;
BEGIN
select * BULK COLLECT INTO et from emp;
for n1 in et.FIRST..et.LAST LOOP
dbms_output.put_line(et(n1).ename);
END LOOP;
END;
/

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding

BULK DML

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
v_empno numtab;
v_ename chartab;
v_sal numtab;
n1 number;
BEGIN
select empno, ename, sal BULK COLLECT INTO v_empno,v_ename, v_sal from emp;
for n1 in v_sal.FIRST..v_sal.LAST LOOP
v_sal(n1)  := v_sal(n1)*1.1;
END LOOP;
forall n1 in v_empno.first..v_empno.last
update emp set sal=v_sal(n1) where empno=v_empno(n1);
END;
/

Using rowid for BULK update or delete
Accessing using rowid is the fastest method to fetch a record.
using rowid for bulk update is faster than using a primary key.

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
type rowidtab is table of rowid;
v_empno numtab;
v_ename chartab;
v_sal numtab;
v_rid rowidtab;
n1 number;
BEGIN
select rowid, empno, ename, sal BULK COLLECT INTO v_rid, v_empno,v_ename, v_sal from emp;
v_sal(n1)  := v_sal(n1)*1.1;
END LOOP;
forall n1 in v_empno.first..v_empno.last
update emp set sal=v_sal(n1) where rowid=v_rid(n1);
END;
/

Upsert using values from external table

Task
       1) Read external table
       2) Check whether record exists in the database table CUSTOMER for the key value custno
       3) If records is not present then insert else update

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
type rowidtab is table of rowid;
v_custno numtab;
v_custname chartab;
v_custtype numtab;
v_c1 numtab;
v_c2 chartab;
v_rid rowidtab;
n1 number;
nr number;
BEGIN
select rowid, custno, custname, custtype,b.c1, b.c2 BULK COLLECT INTO v_rid, v_custno,v_custname, v_custtype, v_c1, v_c2
from customer a, ext_table b where a.custno(+)=b.c1 order by 1 nulls first,2;
-- The data is ordered by riowid. Nulls values first
-- The first set of rows where rowid is null is uded for insert
nr:=0;
forall n1 in v_custno.first..v_custno.last LOOP
if v_rid(n1) is null then nr:=n1; end if;
END IF;
if nr>0 then
forall n1 in 1..nr
insert into customer(custno,custname) values(v_c1(n1), v_c2(n1));
end if;
if nr < v_rid.count then
forall n1 in nr+1..v_rid.count
update customer set custname=v_c2(n1) where rowid=v_rid(n1);
end if;
END;
/

GLOBAL TEMPORARY TABLES

In Oracle Specify GLOBAL TEMPORARY in the create table statemnt to indicate that the table is temporary.
Temporary tables are useful in applications where a result set is to be buffered,
perhaps because it is constructed by running multiple DML operations.

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

For a global temporary table

• The data in a temporary table is visible only to the session that inserts the data into the table.
  The LOCK statement has no effect on a temporary table, because each session has its own private data.
• Space is allocated for the table segment at the time of the first DML operation on the table.
• A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session.
  It does not truncate the data of other sessions that are using the same table.
• The data you entered is lost when you rollback a transaction
• DML statements on temporary tables do not generate redo logs for the data changes.
  However, undo logs for the data and redo logs for the undo logs are generated.
• It is possible to create indexes, triggers and views based on temporary table
• Oracle utilities can export and import the definition of a temporary table.
  However, no data rows are exported even if you use the ROWS clause.

For small amount of data rows global temporary table is an alternative for collections.
Indexes can be created on global temporary tables.