-----------------------------------------------------------------------------
-- (c) Copyright IBM Corp. 2007 All rights reserved.
-- 
-- The following sample of source code ("Sample") is owned by International 
-- Business Machines Corporation or one of its subsidiaries ("IBM") and is 
-- copyrighted and licensed, not sold. You may use, copy, modify, and 
-- distribute the Sample in any form without payment to IBM, for the purpose of 
-- assisting you in the development of your applications.
-- 
-- The Sample code is provided to you on an "AS IS" basis, without warranty of 
-- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
-- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
-- not allow for the exclusion or limitation of implied warranties, so the above 
-- limitations or exclusions may not apply to you. IBM shall not be liable for 
-- any damages you suffer as a result of using, copying, modifying or 
-- distributing the Sample, even if IBM has been advised of the possibility of 
-- such damages.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: tbread.db2
--
-- SAMPLE: How to read tables
--
-- SQL STATEMENTS USED:
--         SELECT
--         TERMINATE
--
--                           
-----------------------------------------------------------------------------
--
-- For more information about the command line processor (CLP) scripts,
-- see the README file.
--
-- For information on using SQL statements, see the SQL Reference.
--
-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
-----------------------------------------------------------------------------

-- a simple SUBSELECT

-- display the contents of the 'org' table
SELECT * FROM org;

-- display only two columns from the 'org' table
SELECT deptnumb, deptname FROM org;

-- a basic SUBSELECT

-- display the contents of the 'org' table
SELECT * FROM org;

-- use a WHERE clause to display only a few rows
SELECT deptnumb, deptname FROM org WHERE deptnumb < 30;

-- a 'GROUP BY' SUBSELECT

-- display the contents of the 'org' table
SELECT * FROM org;

-- use a GROUP BY clause 
SELECT division, MAX(deptnumb) AS MAX_deptnumb FROM org GROUP BY division;

-- a SUBSELECT with WHERE and GROUP BY clauses

-- display the contents of the 'org' table
SELECT * FROM org;

SELECT division, MAX(deptnumb) AS MAX_deptnumb 
  FROM org
  WHERE location NOT IN 'New York'
  GROUP BY division HAVING division LIKE '%ern';

-- a 'ROW' SUBSELECT

-- display the contents of the 'org' table
SELECT * FROM org;

SELECT deptnumb, deptname
  FROM org
  WHERE location = 'New York';

-- a FULLSELECT with UNION

-- display the contents of the 'org' table
SELECT * FROM org;

SELECT deptnumb, deptname 
  FROM org
  WHERE deptnumb < 20 UNION VALUES(7, 'New Deptname');

-- a SELECT with 'WITH' clause 

-- display the contents of the 'org' table
SELECT * FROM org;

WITH new_org(new_deptnumb, new_deptname)
  AS(SELECT deptnumb, deptname
       FROM org
       UNION VALUES(7, 'New Dept 1'),
                   (77, 'New Dept 2'),
                   (777, 'New Dept 3'))
  SELECT new_deptnumb, new_deptname
    FROM new_org
    WHERE new_deptnumb > 70
    ORDER BY new_deptname;

-- SUBSELECT from multiple tables

-- display the contents of the 'org' table
SELECT * FROM org;

-- display the contents of the 'department' table
SELECT * FROM department;

SELECT deptnumb, o.deptname, deptno, d.deptname
  FROM org o, department d
  WHERE deptnumb <= 15 AND deptno LIKE '%11';

-- SUBSELECT from a joined table

-- display the contents of the 'org' table

SELECT * FROM org;

-- display the contents of the 'department' table
SELECT * FROM department;

SELECT deptnumb, manager, deptno, mgrno
  FROM org INNER JOIN department
  ON manager = INTEGER(mgrno)
  WHERE deptnumb BETWEEN 20 AND 100;

-- SUBSELECT using a SUBQUERY

-- display the contents of the 'org' table
SELECT * FROM org;

SELECT deptnumb, deptname
  FROM org
  WHERE deptnumb < (SELECT AVG(deptnumb) FROM org);

-- SUBSELECT using a CORRELATED SUBQUERY

-- display the contents of the 'org' table
SELECT * FROM org;

SELECT deptnumb, deptname
  FROM org o1
  WHERE deptnumb > (SELECT AVG(deptnumb)
                      FROM org o2
                      WHERE o2.division = o1.division);

-- SUBSELECT using GROUPING SETS

-- display a partial content of the 'employee' table
SELECT job, edlevel, comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP');

SELECT job, edlevel, SUM(comm) AS SUM_comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP')
  GROUP BY GROUPING SETS((job, edlevel), (job));

-- SUBSELECT using ROLLUP

-- display a partial content of the 'employee' table
SELECT job, edlevel, comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP');

SELECT job, edlevel, SUM(comm) AS SUM_comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP')
  GROUP BY ROLLUP(job, edlevel);

-- SUBSELECT using CUBE

-- display a partial content of the 'employee' table
SELECT job, edlevel, comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP');

SELECT job, edlevel, SUM(comm) AS SUM_comm
  FROM employee
  WHERE job IN('DESIGNER', 'FIELDREP')
  GROUP BY CUBE(job, edlevel);

-- SELECT using RECURSIVE COMMON TABLE EXPRESSION

-- display the content of the 'department' table
SELECT * FROM department;

WITH rcte_department(deptno, deptname, admrdept)
  AS(SELECT root.deptno, root.deptname, root.admrdept
       FROM department root
       WHERE root.deptname = 'SUPPORT SERVICES'
       UNION ALL SELECT child.deptno, child.deptname, child.admrdept
                   FROM department child, rcte_department parent
                   WHERE child.admrdept = parent.deptno)
  SELECT * FROM rcte_department;

-- SELECT using QUERY SAMPLING

-- computing AVG(salary) without sampling
SELECT AVG(salary) AS AVG_salary FROM employee;

-- computing AVG(SALARY) with query sampling
--                        - ROW LEVEL SAMPLING   
--                        - BLOCK LEVEL SAMPLING 
-- ROW LEVEL SAMPLING : use the keyword 'BERNOULLI'
-- for a sampling percentage of P, each row of the table is
-- selected for the inclusion in the result with a probability
-- of P/100, independently of the other rows in T

SELECT AVG(salary) AS AVG_salary FROM employee
  TABLESAMPLE BERNOULLI(25) REPEATABLE(5);

-- BLOCK LEVEL SAMPLING : use the keyword 'SYSTEM'
-- for a sampling percentage of P, each row of the table is
-- selected for inclusion in the result with a probability
-- of P/100, not necessarily independently of the other rows
-- in T, based upon an implementation-dependent algorithm

SELECT AVG(salary) AS AVG_salary FROM employee
  TABLESAMPLE SYSTEM(50) REPEATABLE(1234);

-- REPEATABLE clause ensures that repeated executions of that
-- table reference will return identical results for the same
-- value of the repeat argument (in parenthesis)

-- disconnect from the database

CONNECT RESET;

TERMINATE;