Wednesday, 23 October 2013

Play with SQL

Overview
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:
  1. 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.
  2. 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.
  3. 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
  4. 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?
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?
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?
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? 
  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?
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 dynamically generate a list of comma-separated items?
I often find myself needing to dynamically generate a list of comma-separated items (i.e. a list of database files separated by commas). The output format, for example, would be: 
...  Datafile1, Datafile2, Datafile3 ...
Instead of thinking of the list as a linear set of items, re-write the set of items each on a separate line as follows: 
...
    Datafile1
  , Datafile2
  , Datafile3
...
Notice that a comma is used on each line except for the first item. We can now take advantage of a popular Analytic Function (RANK) to distinguish the first line. Consider the following CREATE CONTROLFILE example: 
SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 9000
SET LINESIZE 135
SET TRIMSPOOL ON
SET TRIMOUT ON
SET ECHO OFF
SET SQLBLANKLINES ON

SPOOL cr_control_file_example.sql

prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
prompt   MAXLOGFILES 16
prompt   MAXLOGMEMBERS 3
prompt   MAXDATAFILES 100
prompt   MAXINSTANCES 8
prompt   MAXLOGHISTORY 3182
prompt LOGFILE
prompt   GROUP 1 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
prompt   ) SIZE 50M,
prompt   GROUP 2 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
prompt   ) SIZE 50M,
prompt   GROUP 3 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
prompt   ) SIZE 50M
prompt DATAFILE

SELECT
    CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1
      THEN
        '    ''' || name || ''''
      ELSE
        '  , ''' || name || ''''
      END AS file_name
FROM v$datafile;

prompt CHARACTER SET WE8ISO8859P1

SPOOL OFF

------------------------------------------------------------

CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 3182
LOGFILE
GROUP 1 (
'/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
) SIZE 50M,
GROUP 2 (
'/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
) SIZE 50M,
GROUP 3 (
'/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
) SIZE 50M
DATAFILE

    '+ORCL_DATA1/orcl/datafile/example.263.623550121'
  , '+ORCL_DATA1/orcl/datafile/soe.269.623559923'
  , '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217'
  , '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085'
  , '+ORCL_DATA1/orcl/datafile/system.259.623549989'
  , '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053'
  , '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147'
  , '+ORCL_DATA1/orcl/datafile/users.265.623550181'
CHARACTER SET WE8ISO8859P1


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


Can I retrieve only rows X to Y 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
      );


 Can I 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 < 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? 
  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


Type Code Oracle Datatype
1 VARCHAR2
2 NUMBER
12 DATE
96 CHAR
Oracle Type Codes for Column Dumps


Can I code a 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


 Tree-Structured (Hierarchical) Queries 
  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 how 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.


Insert Large Number of Sequence Values using UNION
Here is an example SQL script that will insert 1,000,000 sequence values into a table using a series of UNION's and a cartesian product. 
DROP TABLE sequence_values
/

CREATE TABLE sequence_values (id NUMBER)
/

INSERT INTO sequence_values
    SELECT
        millions.x           +
        hundred_thousands.x  +
        ten_thousands.x      +
        thousands.x          +
        hundreds.x           +
        tens.x               +
        ones.x
    FROM
        (SELECT 0 x FROM dual
         UNION SELECT 1 FROM dual
         UNION SELECT 2 FROM dual
         UNION SELECT 3 FROM dual
         UNION SELECT 4 FROM dual
         UNION SELECT 5 FROM dual
         UNION SELECT 6 FROM dual
         UNION SELECT 7 FROM dual
         UNION SELECT 8 FROM dual
         UNION SELECT 9 FROM dual) ones,
        (SELECT 0 x FROM dual
         UNION SELECT 10 FROM dual
         UNION SELECT 20 FROM dual
         UNION SELECT 30 FROM dual
         UNION SELECT 40 FROM dual
         UNION SELECT 50 FROM dual
         UNION SELECT 60 FROM dual
         UNION SELECT 70 FROM dual
         UNION SELECT 80 FROM dual
         UNION SELECT 90 FROM dual) tens,
        (SELECT 0 x FROM dual
         UNION SELECT 100 FROM dual
         UNION SELECT 200 FROM dual
         UNION SELECT 300 FROM dual
         UNION SELECT 400 FROM dual
         UNION SELECT 500 FROM dual
         UNION SELECT 600 FROM dual
         UNION SELECT 700 FROM dual
         UNION SELECT 800 FROM dual
         UNION SELECT 900 FROM dual) hundreds,
        (SELECT 0 x FROM dual
         UNION SELECT 1000 FROM dual
         UNION SELECT 2000 FROM dual
         UNION SELECT 3000 FROM dual
         UNION SELECT 4000 FROM dual
         UNION SELECT 5000 FROM dual
         UNION SELECT 6000 FROM dual
         UNION SELECT 7000 FROM dual
         UNION SELECT 8000 FROM dual
         UNION SELECT 9000 FROM dual) thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 10000 FROM dual
         UNION SELECT 20000 FROM dual
         UNION SELECT 30000 FROM dual
         UNION SELECT 40000 FROM dual
         UNION SELECT 50000 FROM dual
         UNION SELECT 60000 FROM dual
         UNION SELECT 70000 FROM dual
         UNION SELECT 80000 FROM dual
         UNION SELECT 90000 FROM dual) ten_thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 100000 FROM dual
         UNION SELECT 200000 FROM dual
         UNION SELECT 300000 FROM dual
         UNION SELECT 400000 FROM dual
         UNION SELECT 500000 FROM dual
         UNION SELECT 600000 FROM dual
         UNION SELECT 700000 FROM dual
         UNION SELECT 800000 FROM dual
         UNION SELECT 900000 FROM dual) hundred_thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 1000000 FROM dual
         UNION SELECT 2000000 FROM dual
         UNION SELECT 3000000 FROM dual
         UNION SELECT 4000000 FROM dual
         UNION SELECT 5000000 FROM dual
         UNION SELECT 6000000 FROM dual
         UNION SELECT 7000000 FROM dual
         UNION SELECT 8000000 FROM dual
         UNION SELECT 9000000 FROM dual) millions
/


Table dropped.


Table created.


10000000 rows created.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)