Author:  Graeme Birchall ©
Email:   Graeme_Birchall@verizon.net
Web:     http://mysite.verizon.net/Graeme_Birchall/
Title:   DB2 9.5 SQL Cookbook ©
Date:    20-Nov-2007





EMP_NM        EMP_JB       SELECT   nm.id             ANSWER
+----------+  +--------+           ,nm.name           ================
|ID|NAME   |  |ID|JOB  |           ,jb.job            ID NAME    JOB
|--|-------|  |--|-----|   FROM     emp_nm nm         -- ------- -----
|10|Sanders|  |10|Sales|           ,emp_jb jb         10 Sanders Sales
|20|Pernal |  |20|Clerk|   WHERE    nm.id = jb.id     20 Pernal  Clerk
|50|Hanes  |  +--------+   ORDER BY 1;
+----------+
Figure 1, Join example





EMP_NM        EMP_JB       SELECT   nm.id             ANSWER
+----------+  +--------+           ,nm.name           ================
|ID|NAME   |  |ID|JOB  |           ,jb.job            ID NAME    JOB
|--|-------|  |--|-----|   FROM     emp_nm nm         -- ------- -----
|10|Sanders|  |10|Sales|   LEFT OUTER JOIN            10 Sanders Sales
|20|Pernal |  |20|Clerk|            emp_jb jb         20 Pernal  Clerk
|50|Hanes  |  +--------+   ON       nm.id = jb.id     50 Hanes   -
+----------+               ORDER BY nm.id;
Figure 2,Left-outer-join example





EMP_NM        EMP_JB       SELECT   *                         ANSWER
+----------+  +--------+   FROM     emp_nm nm                 ========
|ID|NAME   |  |ID|JOB  |   WHERE NOT EXISTS                   ID NAME
|--|-------|  |--|-----|           (SELECT *                  == =====
|10|Sanders|  |10|Sales|            FROM   emp_jb jb          50 Hanes
|20|Pernal |  |20|Clerk|            WHERE  nm.id = jb.id)
|50|Hanes  |  +--------+   ORDER BY id;
+----------+
Figure 3, Sub-query example





EMP_NM        EMP_JB       SELECT   *                        ANSWER
+----------+  +--------+   FROM     emp_nm                   =========
|ID|NAME   |  |ID|JOB  |   WHERE    name < 'S'               ID 2
|--|-------|  |--|-----|   UNION                             -- ------
|10|Sanders|  |10|Sales|   SELECT   *                        10 Sales
|20|Pernal |  |20|Clerk|   FROM     emp_jb                   20 Clerk
|50|Hanes  |  +--------+   ORDER BY 1,2;                     20 Pernal
+----------+                                                 50 Hanes
Figure 4, Union example





EMP_JB       SELECT   id
+--------+           ,job                                   ANSWER
|ID|JOB  |           ,ROW_NUMBER() OVER(ORDER BY job) AS R  ==========
|--|-----|   FROM     emp_jb                                ID JOB   R
|10|Sales|   ORDER BY job;                                  -- ----- -
|20|Clerk|                                                  20 Clerk 1
+--------+                                                  10 Sales 2
Figure 5, Assign row-numbers example





EMP_JB       SELECT   id                               ANSWER
+--------+           ,job                              ===============
|ID|JOB  |           ,CASE                             ID JOB   STATUS
|--|-----|               WHEN job = 'Sales'            -- ----- ------
|10|Sales|               THEN 'Fire'                   10 Sales Fire
|20|Clerk|               ELSE 'Demote'                 20 Clerk Demote
+--------+            END AS STATUS
             FROM     emp_jb;
Figure 6, Case stmt example





FAMILY          WITH temp (persn, lvl) AS                    ANSWER
+-----------+     (SELECT  parnt, 1                          =========
|PARNT|CHILD|      FROM    family                            PERSN LVL
|-----|-----|      WHERE   parnt = 'Dad'                     ----- ---
|GrDad|Dad  |      UNION ALL                                 Dad     1
|Dad  |Dghtr|      SELECT  child, Lvl + 1                    Dghtr   2
|Dghtr|GrSon|      FROM    temp,                             GrSon   3
|Dghtr|GrDtr|              family                            GrDtr   3
+-----------+      WHERE   persn = parnt)
                SELECT *
                FROM   temp;
Figure 7, Recursion example





INPUT DATA                 Recursive SQL                   ANSWER
=================          ============>                   ===========
"Some silly text"                                          TEXT  LINE#
                                                           ----- -----
                                                           Some      1
                                                           silly     2
                                                           text      3
Figure 8, Convert string to rows





INPUT DATA                 Recursive SQL             ANSWER
===========                ============>             =================
TEXT  LINE#                                          "Some silly text"
----- -----
Some      1
silly     2
text      3
Figure 9, Convert rows to string





EMP_NM         SELECT   *                                    ANSWER
+----------+   FROM     emp_nm                               =========
|ID|NAME   |   ORDER BY id DESC                              ID NAME
|--|-------|   FETCH FIRST 2 ROWS ONLY;                      -- ------
|10|Sanders|                                                 50 Hanes
|20|Pernal |                                                 20 Pernal
|50|Hanes  |
+----------+
Figure 10, Fetch first "n" rows example





EMP_NM         SELECT   *                                   ANSWER
+----------+   FROM     emp_nm                              ==========
|ID|NAME   |   WHERE    name like 'S%'                      ID NAME
|--|-------|   WITH UR;                                     -- -------
|10|Sanders|                                                10 Sanders
|20|Pernal |
|50|Hanes  |
+----------+
Figure 11, Fetch WITH UR example





EMP_NM         SELECT   AVG(id)   AS avg             ANSWER
+----------+           ,MAX(name) AS maxn            =================
|ID|NAME   |           ,COUNT(*)  AS #rows           AVG MAXN    #ROWS
|--|-------|   FROM     emp_nm;                      --- ------- -----
|10|Sanders|                                          26 Sanders     3
|20|Pernal |
|50|Hanes  |
+----------+
Figure 12, Column Functions example





SELECT   job                                                    ANSWER
        ,dept                               ==========================
        ,SUM(salary) AS sum_sal             JOB   DEPT SUM_SAL   #EMPS
        ,COUNT(*)    AS #emps               ----- ---- --------- -----
FROM     staff                              Clerk   15  84766.70     2
WHERE    dept   < 30                        Clerk   20  77757.35     2
  AND    salary < 90000                     Clerk    - 162524.05     4
  AND    job    < 'S'                       Mgr     10 243453.45     3
GROUP BY ROLLUP(job, dept)                  Mgr     15  80659.80     1
ORDER BY job                                Mgr      - 324113.25     4
        ,dept;                              -        - 486637.30     8
Figure 13, Subtotal and Grand-total example





Figure 14, Syntax Diagram Conventions





SELECT   name        -- this is a comment.
FROM     staff       -- this is another comment.
ORDER BY id;
Figure 15, SQL Comment example





--#SET DELIMITER !
SELECT name FROM staff WHERE id = 10!
--#SET DELIMITER ;
SELECT name FROM staff WHERE id = 20;
Figure 16, Set Delimiter example





CREATE TABLE employee
(empno     CHARACTER (00006)    NOT NULL
,firstnme  VARCHAR   (00012)    NOT NULL
,midinit   CHARACTER (00001)    NOT NULL
,lastname  VARCHAR   (00015)    NOT NULL
,workdept  CHARACTER (00003)
,phoneno   CHARACTER (00004)
,hiredate  DATE
,job       CHARACTER (00008)
,edlevel   SMALLINT             NOT NULL
,SEX       CHARACTER (00001)
,birthdate DATE
,salary    DECIMAL   (00009,02)
,bonus     DECIMAL   (00009,02)
,comm      DECIMAL   (00009,02)
 )     
 DATA CAPTURE NONE;
Figure 17, DB2 sample table - EMPLOYEE





CREATE VIEW employee_view AS
SELECT   a.empno, a.firstnme, a.salary, a.workdept
FROM     employee a
WHERE    a.salary >=
        (SELECT AVG(b.salary)
         FROM   employee b
         WHERE  a.workdept = b.workdept);
Figure 18, DB2 sample view - EMPLOYEE_VIEW





CREATE VIEW silly (c1, c2, c3)
AS VALUES (11, 'AAA', SMALLINT(22))
         ,(12, 'BBB', SMALLINT(33))
         ,(13, 'CCC', NULL);
Figure 19, Define a view using a VALUES clause





SELECT   c1, c2, c3                                        ANSWER
FROM     silly                                             ===========
ORDER BY c1 aSC;                                           C1  C2   C3
                                                           --  ---  --
                                                           11  AAA  22
                                                           12  BBB  33
                                                           13  CCC   -
Figure 20, SELECT from a view that has its own data





CREATE VIEW test_data AS
WITH temp1 (num1) AS
(VALUES  (1)
 UNION ALL
 SELECT  num1 + 1
 FROM    temp1
 WHERE   num1 < 10000)
SELECT *
FROM   temp1;
Figure 21, Define a view that creates data on the fly





CREATE ALIAS  employee_al1 FOR employee;
COMMIT;
       
CREATE ALIAS  employee_al2 fOR employee_al1;
COMMIT;
       
CREATE ALIAS  employee_al3 FOR employee_al2;
COMMIT;
Figure 22, Define three aliases, the latter on the earlier





CREATE NICKNAME emp FOR unixserver.production.employee;
Figure 23, Define a nickname





SELECT   *
FROM     staff TABLESAMPLE BERNOULLI(10);
Figure 24, TABLESAMPLE example





CREATE TABLE sales_record
(sales#              INTEGER             NOT NULL
                     GENERATED ALWAYS AS IDENTITY
                     (START   WITH 1
                     ,INCREMENT BY 1
                     ,NO MAXVALUE
                     ,NO CYCLE)
,sale_ts             TIMESTAMP           NOT NULL
,num_items           SMALLINT            NOT NULL
,payment_type        CHAR(2)             NOT NULL
,sale_value          DECIMAL(12,2)       NOT NULL
,sales_tax           DECIMAL(12,2)
,employee#           INTEGER             NOT NULL
,CONSTRAINT sales1   CHECK(payment_type IN ('CS','CR'))
,CONSTRAINT sales2   CHECK(sale_value    > 0)
,CONSTRAINT sales3   CHECK(num_items     > 0)
,CONSTRAINT sales4   FOREIGN KEY(employee#)
                     REFERENCES staff(id) ON DELETE RESTRICT
,PRIMARY KEY(sales#));
Figure 25, Sample table definition





CREATE TABLE default_values
(c1       CHAR        NOT NULL
,d1       DECIMAL     NOT NULL);
Figure 26, Table with default column lengths





SELECT   DECFLOAT(+1.23)      +  NaN                    AS "      NaN"
        ,DECFLOAT(-1.23)      +  NaN                    AS "      NaN"
        ,DECFLOAT(-1.23)      + -NaN                    AS "     -NaN"
        ,DECFLOAT(+infinity)  +  NaN                    AS "      NaN"
        ,DECFLOAT(+sNaN)      +  NaN                    AS "      NaN"
        ,DECFLOAT(-sNaN)      +  NaN                    AS "     -NaN"
        ,DECFLOAT(+NaN)       +  NaN                    AS "      NaN"
        ,DECFLOAT(-NaN)       +  NaN                    AS "     -NaN"
FROM     sysibm.sysdummy1;
Figure 27, NaN arithmetic usage





SELECT   DECFLOAT(1) / +infinity                        AS "  0E-6176"
        ,DECFLOAT(1) * +infinity                        AS " Infinity"
        ,DECFLOAT(1) + +infinity                        AS " Infinity"
        ,DECFLOAT(1) - +infinity                        AS "-Infinity"
        ,DECFLOAT(1) / -infinity                        AS " -0E-6176"
        ,DECFLOAT(1) * -infinity                        AS "-Infinity"
        ,DECFLOAT(1) + -infinity                        AS "-Infinity"
        ,DECFLOAT(1) - -infinity                        AS " Infinity"
FROM     sysibm.sysdummy1;
Figure 28, Infinity arithmetic usage





SELECT   DECFLOAT(+1.23)      /  0                      AS " Infinity"
        ,DECFLOAT(-1.23)      /  0                      AS "-Infinity"
        ,DECFLOAT(+1.23)      +  infinity               AS " Infinity"
        ,DECFLOAT(0)          /  0                      AS "      NaN"
        ,DECFLOAT(infinity)   +  -infinity              AS "      NaN"
        ,LOG(DECFLOAT(0))                               AS "-Infinity"
        ,LOG(DECFLOAT(-123))                            AS "      NaN"
        ,SQRT(DECFLOAT(-123))                           AS "      NaN"
FROM     sysibm.sysdummy1;
Figure 29, DECFLOAT arithmetic results





-NaN  -sNan  -infinity  -1.2  -1.20  0  1.20  1.2  infinity  sNaN  NaN
Figure 30, DECFLOAT value order





                                                                ANSWER
WITH temp1 (d1, d2) AS                                          ======
  (VALUES (DECFLOAT(+1.0), DECFLOAT(+1.00))                          1
         ,(DECFLOAT(-1.0), DECFLOAT(-1.00))                         -1
         ,(DECFLOAT(+0.0), DECFLOAT(+0.00))                          1
         ,(DECFLOAT(-0.0), DECFLOAT(-0.00))                          1
         ,(DECFLOAT(+0),   DECFLOAT(-0))                             0
  )    
SELECT   TOTALORDER(d1,d2)
FROM     temp1;
Figure 31, Equal values that may have different orders





WITH temp1 (d1) AS
  (VALUES (DECFLOAT(+0     ,16))
         ,(DECFLOAT(+0.0   ,16))
         ,(DECFLOAT(+0.00  ,16))
         ,(DECFLOAT(+0.000 ,16))
  )    
SELECT   d1
        ,HEX(d1)                      AS hex_d1
        ,NORMALIZE_DECFLOAT(d1)       AS d2
        ,HEX(NORMALIZE_DECFLOAT(d1))  AS hex_d2
FROM     temp1;
                                                                ANSWER
                            ==========================================
                            D1    HEX_D1           D2 HEX_D2
                            ----- ---------------- -- ----------------
                                0 0000000000003822  0 0000000000003822
                              0.0 0000000000003422  0 0000000000003822
                             0.00 0000000000003022  0 0000000000003822
                            0.000 0000000000002C22  0 0000000000003822
Figure 32, Remove trailing zeros





    LABELED DURATIONS          ITEM       WORKS WITH DATE/TIME
<------------------------>     FIXED     <--------------------->
SINGULAR      PLURAL           SIZE      DATE   TIME   TIMESTAMP
===========   ============     =====     ====   ====   =========
YEAR          YEARS            N         Y      -      Y
MONTH         MONTHS           N         Y      -      Y
DAY           DAYS             Y         Y      -      Y
HOUR          HOURS            Y         -      Y      Y
MINUTE        MINUTES          Y         -      Y      Y
SECOND        SECONDS          Y         -      Y      Y
MICROSECOND   MICROSECONDS     Y         -      Y      Y
Figure 33, Labeled Durations and Date/Time Types





                                                            ANSWER
                                                            ==========
SELECT   sales_date                                     <=  1995-12-31
        ,sales_date -  10   DAY    AS d1                <=  1995-12-21
        ,sales_date +  -1   MONTH  AS d2                <=  1995-11-30
        ,sales_date +  99   YEARS  AS d3                <=  2094-12-31
        ,sales_date +  55   DAYS
                    -  22   MONTHS AS d4                <=  1994-04-24
        ,sales_date + (4+6) DAYS   AS d5                <=  1996-01-10
FROM     sales
WHERE    sales_person = 'GOUNOT'
  AND    sales_date   = '1995-12-31'
Figure 34, Example, Labeled Duration usage





                                                            ANSWER
                                                            ==========
SELECT   sales_date                                     <=  1995-12-31
        ,sales_date +    2  MONTH  AS d1                <=  1996-02-29
        ,sales_date +    3  MONTHS AS d2                <=  1996-03-31
        ,sales_date +    2  MONTH
                    +    1  MONTH  AS d3                <=  1996-03-29
        ,sales_date + (2+1) MONTHS AS d4                <=  1996-03-31
FROM     sales
WHERE    sales_person = 'GOUNOT'
  AND    sales_date   = '1995-12-31';
Figure 35, Adding Months - Varying Results





DURATION-TYPE  FORMAT         NUMBER-REPRESENTS        USE-WITH-D-TYPE
=============  =============  =====================    ===============
DATE           DECIMAL(8,0)   yyyymmdd                 TIMESTAMP, DATE
TIME           DECIMAL(6,0)   hhmmss                   TIMESTAMP, TIME
TIMESTAMP      DECIMAL(20,6)  yyyymmddhhmmss.zzzzzz    TIMESTAMP
Figure 36, Date/Time Durations





SELECT   empno                    ANSWER
        ,hiredate                 ====================================
        ,birthdate                EMPNO  HIREDATE   BIRTHDATE
        ,hiredate - birthdate     ------ ---------- ---------- -------
FROM     employee                 000150 1972-02-12 1947-05-17 240826.
WHERE    workdept = 'D11'         000200 1966-03-03 1941-05-29 240905.
  AND    lastname < 'L'           000210 1979-04-11 1953-02-23 260116.
ORDER BY empno;
Figure 37, Date Duration Generation





                                                            ANSWER
                                                            ==========
SELECT   hiredate                                       <=  1972-02-12
        ,hiredate - 12345678.                           <=  0733-03-26
        ,hiredate - 1234 years
                  -   56 months
                  -   78 days                           <=  0733-03-26
FROM     employee
WHERE    empno = '000150';
Figure 38, Subtracting a Date Duration





SPECIAL REGISTER                                 UPDATE  DATA-TYPE
===============================================  ======  =============
CURRENT CLIENT_ACCTNG                            no      VARCHAR(255)
CURRENT CLIENT_APPLNAME                          no      VARCHAR(255)
CURRENT CLIENT_USERID                            no      VARCHAR(255)
CURRENT CLIENT_WRKSTNNAME                        no      VARCHAR(255)
CURRENT DATE                                     no      DATE
CURRENT DBPARTITIONNUM                           no      INTEGER
CURRENT DECFLOAT ROUNDING MODE                   no      VARCHAR(128)
CURRENT DEFAULT TRANSFORM GROUP                  yes     VARCHAR(18)
CURRENT DEGREE                                   yes     CHAR(5)
CURRENT EXPLAIN MODE                             yes     VARCHAR(254)
CURRENT EXPLAIN SNAPSHOT                         yes     CHAR(8)
CURRENT FEDERATED ASYNCHRONY                     yes     INTEGER
CURRENT IMPLICIT XMLPARSE OPTION                 yes     VARCHAR(19)
CURRENT ISOLATION                                yes     CHAR(2)
CURRENT LOCK TIMEOUT                             yes     INTEGER
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION  yes     VARCHAR(254)
CURRENT MDC ROLLOUT MODE                         yes     VARCHAR(9)
CURRENT OPTIMIZATION PROFILE                     yes     VARCHAR(261)
CURRENT PACKAGE PATH                             yes     VARCHAR(4096)
CURRENT PATH                                     yes     VARCHAR(2048)
CURRENT QUERY OPTIMIZATION                       yes     INTEGER
CURRENT REFRESH AGE                              yes     DECIMAL(20,6)
CURRENT SCHEMA                                   yes     VARCHAR(128)
CURRENT SERVER                                   no      VARCHAR(128)
CURRENT TIME                                     no      TIME
CURRENT TIMESTAMP                                no      TIMESTAMP
CURRENT TIMEZONE                                 no      DECIMAL(6,0)
CURRENT USER                                     no      VARCHAR(128)
SESSION_USER                                     yes     VARCHAR(128)
SYSTEM_USER                                      no      VARCHAR(128)
USER                                             yes     VARCHAR(128)
Figure 39, DB2 Special Registers





SET CURRENT ISOLATION = RR;
SET CURRENT SCHEMA    = 'ABC';                 ANSWER
                                               =======================
SELECT  CURRENT TIME       AS cur_TIME         CUR_TIME CUR_ISO CUR_ID
       ,CURRENT ISOLATION  AS cur_ISO          -------- ------- ------
       ,CURRENT SCHEMA     AS cur_ID           12:15:16 RR      ABC
FROM    sysibm.sysdummy1;
Figure 40, Using Special Registers





Figure 41, Create Distinct Type Syntax





CREATE DISTINCT TYPE JAP_YEN AS DECIMAL(15,2) WITH COMPARISONS;
DROP   DISTINCT TYPE JAP_YEN;
Figure 42, Create and drop distinct type





CREATE TABLE customer
(id                 INTEGER               NOT NULL
,fname              VARCHAR(00010)        NOT NULL WITH DEFAULT ''
,lname              VARCHAR(00015)        NOT NULL WITH DEFAULT ''
,date_of_birth      DATE
,citizenship        CHAR(03)
,usa_sales          DECIMAL(9,2)
,eur_sales          DECIMAL(9,2)
,sales_office#      SMALLINT
,last_updated       TIMESTAMP
,PRIMARY KEY(id));
Figure 43, Sample table, without distinct types





SELECT   id
        ,usa_sales + eur_sales AS tot_sales
FROM     customer;
Figure 44, Silly query, but works





CREATE DISTINCT TYPE USA_DOLLARS AS DECIMAL(9,2) WITH COMPARISONS;
CREATE DISTINCT TYPE EUR_DOLLARS AS DECIMAL(9,2) WITH COMPARISONS;
Figure 45, Create Distinct Type examples





CREATE TABLE customer
(id                 INTEGER               NOT NULL
,fname              VARCHAR(00010)        NOT NULL WITH DEFAULT ''
,lname              VARCHAR(00015)        NOT NULL WITH DEFAULT ''
,date_of_birth      DATE
,citizenship        CHAR(03)
,usa_sales          USA_DOLLARS
,eur_sales          EUR_DOLLARS
,sales_office#      SMALLINT
,last_updated       TIMESTAMP
,PRIMARY KEY(id));
Figure 46, Sample table, with distinct types





SELECT   id
        ,usa_sales + eur_sales AS tot_sales
FROM     customer;
Figure 47, Silly query, now fails





SELECT   id
        ,DECIMAL(usa_sales) +
         DECIMAL(eur_sales) AS tot_sales
FROM     customer;
Figure 48, Silly query, works again





WITH   
   get_matching_rows AS
    (  
       SELECT   id
               ,name
               ,salary          SUBSELECT
       FROM     staff
       WHERE    id  <  50
     UNION ALL                                  FULLSELECT
       SELECT   id
               ,name
               ,salary          SUBSELECT
       FROM     staff
       WHERE    id  =  100
    )  
       
SELECT   *
FROM     get_matching_rows                       COMMON TABLE
ORDER BY id                                      EXPRESSION
FETCH FIRST 10 ROWS ONLY        SUBSELECT
FOR FETCH ONLY
WITH UR;
Figure 49, Query components





Figure 50, SELECT Statement Syntax (general)





Figure 51, SELECT Statement Syntax





SELECT   deptno                                    ANSWER
        ,admrdept                                  ===================
        ,'ABC' AS abc                              DEPTNO ADMRDEPT ABC
FROM     department                                ------ -------- ---
WHERE    deptname LIKE '%ING%'                     B01    A00      ABC
ORDER BY 1;                                        D11    D01      ABC
Figure 52, Sample SELECT statement





SELECT   *                                            ANSWER (part of)
FROM     department                                   ================
WHERE    deptname LIKE '%ING%'                        DEPTNO etc...
ORDER BY 1;                                           ------ ------>>>
                                                      B01    PLANNING
                                                      D11    MANUFACTU
Figure 53, Use "*" to select all columns in table





SELECT   deptno                                ANSWER (part of)
        ,department.*                          =======================
FROM     department                            DEPTNO DEPTNO etc...
WHERE    deptname LIKE '%ING%'                 ------ ------ ------>>>
ORDER BY 1;                                    B01    B01    PLANNING
                                               D11    D11    MANUFACTU
Figure 54, Select an individual column, and all columns





SELECT   department.*                                 ANSWER (part of)
        ,department.*                                 ================
FROM     department                                   DEPTNO etc...
WHERE    deptname LIKE '%NING%'                       ------ ------>>>
ORDER BY 1;                                           B01    PLANNING
Figure 55, Select all columns twice





Figure 56, Fetch First clause Syntax





SELECT   years                                   ANSWER
        ,name                                    =====================
        ,id                                      YEARS  NAME      ID
FROM     staff                                   ------ --------- ----
FETCH FIRST 3 ROWS ONLY;                              7 Sanders     10
                                                      8 Pernal      20
                                                      5 Marenghi    30
Figure 57, FETCH FIRST without ORDER BY, gets random rows





SELECT   years                                   ANSWER
        ,name                                    =====================
        ,id                                      YEARS  NAME      ID
FROM     staff                                   ------ --------- ----
WHERE    years IS NOT NULL                           13 Graham     310
ORDER BY years DESC                                  12 Jones      260
FETCH FIRST 3 ROWS ONLY;                             10 Hanes       50
Figure 58, FETCH FIRST with ORDER BY, gets wrong answer





SELECT   years                                   ANSWER
        ,name                                    =====================
        ,id                                      YEARS  NAME      ID
FROM     staff                                   ------ --------- ----
WHERE    years IS NOT NULL                           13 Graham     310
ORDER BY years DESC                                  12 Jones      260
        ,id    DESC                                  10 Quill      290
FETCH FIRST 3 ROWS ONLY;
Figure 59, FETCH FIRST with ORDER BY, gets right answer





SELECT   a.empno                                     ANSWER
        ,a.lastname                                  =================
FROM     employee  a                                 EMPNO  LASTNAME
        ,(SELECT MAX(empno)AS empno                  ------ ----------
          FROM   employee) AS b                      000340 GOUNOT
WHERE    a.empno = b.empno;
Figure 60, Correlation Name usage example





SELECT   a.empno                                ANSWER
        ,a.lastname                             ======================
        ,b.deptno AS dept                       EMPNO  LASTNAME   DEPT
FROM     employee   a                           ------ ---------- ----
        ,department b                           000090 HENDERSON  E11
WHERE    a.workdept  = b.deptno                 000280 SCHNEIDER  E11
  AND    a.job      <> 'SALESREP'               000290 PARKER     E11
  AND    b.deptname  = 'OPERATIONS'             000300 SMITH      E11
  AND    a.sex      IN ('M','F')                000310 SETRIGHT   E11
  AND    b.location IS NULL
ORDER BY 1;
Figure 61, Correlation name usage example





SELECT   empno    AS  e_num                        ANSWER
        ,midinit  AS "m int"                       ===================
        ,phoneno  AS "..."                         E_NUM   M INT  ...
FROM     employee                                  ------  -----  ----
WHERE    empno < '000030'                          000010  I      3978
ORDER BY 1;                                        000020  L      3476
Figure 62, Renaming fields using AS





CREATE view emp2 AS
SELECT empno    AS  e_num
      ,midinit  AS "m int"
      ,phoneno  AS "..."
FROM   employee;                                   ANSWER
                                                   ===================
SELECT *                                           E_NUM   M INT  ...
FROM   emp2                                        ------  -----  ----
WHERE "..." = '3978';                              000010  I      3978
Figure 63, View field names defined using AS





SELECT   AVG(comm)            AS a1                    ANSWER
        ,SUM(comm) / COUNT(*) AS a2                    ===============
FROM     staff                                         A1       A2
WHERE    id < 100;                                     -------  ------
                                                       796.025  530.68
Figure 64, AVG of data containing null values





SELECT   COUNT(*)      AS num                                 ANSWER
        ,MAX(lastname) AS max                                 ========
FROM     employee                                             NUM  MAX
WHERE    firstnme = 'FRED';                                   ---  ---
                                                                0  -
Figure 65, Getting a NULL value from a field defined NOT NULL





SELECT   AVG(comm)            AS a1                    ANSWER
        ,SUM(comm) / COUNT(*) AS a2                    ===============
FROM     staff                                         A1       A2
WHERE    id < 100                                      -------  ------
  AND    comm IS NOT NULL;                             796.025  796.02
Figure 66, AVG of those rows that are not null





SELECT   'JOHN'          AS J1
        ,'JOHN''S'       AS J2           ANSWER
        ,'''JOHN''S'''   AS J3           =============================
        ,'"JOHN''S"'     AS J4           J1   J2     J3       J4
FROM     staff                           ---- ------ -------- --------
WHERE    id = 10;                        JOHN JOHN'S 'JOHN'S' "JOHN'S"
Figure 67, Quote usage





SELECT   id      AS "USER ID"          ANSWER
        ,dept    AS "D#"               ===============================
        ,years   AS "#Y"               USER ID D# #Y 'TXT' "quote" fld
        ,'ABC'   AS "'TXT'"            ------- -- -- ----- -----------
        ,'"'     AS """quote"" fld"         10 20  7 ABC   "
FROM     staff s                            20 20  8 ABC   "
WHERE    id < 40                            30 38  5 ABC   "
ORDER BY "USER ID";
Figure 68, Double-quote usage





Figure 69, Basic Predicate syntax, 1 of 2





SELECT    id, job, dept                                ANSWER
FROM      staff                                        ===============
WHERE     job  =  'Mgr'                                ID   JOB   DEPT
  AND NOT job  <> 'Mgr'                                ---  ----  ----
  AND NOT job  =  'Sales'                               10  Mgr     20
  AND     id   <>  100                                  30  Mgr     38
  AND     id   >=    0                                  50  Mgr     15
  AND     id   <=  150                                 140  Mgr     51
  AND NOT dept =    50
ORDER BY  id;
Figure 70, Basic Predicate examples





Figure 71, Basic Predicate syntax, 2 of 2





SELECT   id, dept, job                                     ANSWER
FROM     staff                                             ===========
WHERE    (id,dept)  = (30,28)                              ID DEPT JOB
   OR    (id,years) = (90, 7)                              -- ---- ---
   OR    (dept,job) = (38,'Mgr')                           30   38 Mgr
ORDER BY 1;
Figure 72, Basic Predicate example, multi-value check





SELECT   id, dept, job                                     ANSWER
FROM     staff                                             ===========
WHERE    (id   = 30  AND  dept  =    28)                   ID DEPT JOB
   OR    (id   = 90  AND  years =     7)                   -- ---- ---
   OR    (dept = 38  AND  job   = 'Mgr')                   30   38 Mgr
ORDER BY 1;
Figure 73, Same query as prior, using individual predicates





Figure 74, Quantified Predicate syntax





SELECT   id, job                                              ANSWER
FROM     staff                                                ========
WHERE    job  = ANY (SELECT job FROM staff)                   ID  JOB
  AND    id  <= ALL (SELECT id  FROM staff)                   --- ----
ORDER BY id;                                                   10 Mgr
Figure 75, Quantified Predicate example, two single-value sub-queries





SELECT   id, dept, job                                  ANSWER
FROM     staff                                          ==============
WHERE    (id,dept) = ANY                                ID  DEPT JOB
         (SELECT dept, id                               --- ---- -----
          FROM   staff)                                  20   20 Sales
ORDER BY 1;
Figure 76, Quantified Predicate example, multi-value sub-query





Figure 77, BETWEEN Predicate syntax





SELECT id, job                                               ANSWER
FROM   staff                                                 =========
WHERE     id     BETWEEN 10 AND 30                           ID  JOB
  AND     id NOT BETWEEN 30 AND 10                           --- -----
  AND NOT id NOT BETWEEN 10 AND 30                            10 Mgr
ORDER BY  id;                                                 20 Sales
                                                              30 Mgr
Figure 78, BETWEEN Predicate examples





Figure 79, EXISTS Predicate syntax





SELECT id, job                                               ANSWER
FROM   staff a                                               =========
WHERE  EXISTS                                                ID  JOB
      (SELECT *                                              --- -----
       FROM   staff b                                         10 Mgr
       WHERE  b.id = a.id                                     20 Sales
         AND  b.id < 50)                                      30 Mgr
ORDER BY id;                                                  40 Sales
Figure 80, EXISTS Predicate example





Figure 81, IN Predicate syntax





SELECT id, job                                               ANSWER
FROM   staff a                                               =========
WHERE  id IN (10,20,30)                                      ID  JOB
  AND  id IN (SELECT id                                      --- -----
              FROM   staff)                                   10 Mgr
  AND  id NOT IN 99                                           20 Sales
ORDER BY id;                                                  30 Mgr
Figure 82, IN Predicate examples, single values





SELECT   empno, lastname                               ANSWER
FROM     employee                                      ===============
WHERE    (empno, 'AD3113') IN                          EMPNO  LASTNAME
         (SELECT empno, projno                         ------ -------
          FROM   emp_act                               000260 JOHNSON
          WHERE  emptime > 0.5)                        000270 PEREZ
ORDER BY 1;
Figure 83, IN Predicate example, multi-value





Figure 84, LIKE Predicate syntax





SELECT id, name                                         ANSWER
FROM   staff                                            ==============
WHERE  name LIKE 'S%n'                                  ID   NAME
   OR  name LIKE '_a_a%'                                ---  ---------
   OR  name LIKE '%r_%a'                                130  Yamaguchi
ORDER BY id;                                            200  Scoutten
Figure 85, LIKE Predicate examples





LIKE STATEMENT TEXT                             WHAT VALUES MATCH
===========================                     ======================
LIKE 'AB%'                                      Finds AB, any string
LIKE 'AB%'       ESCAPE '+'                     Finds AB, any string
LIKE 'AB+%'      ESCAPE '+'                     Finds AB%
LIKE 'AB++'      ESCAPE '+'                     Finds AB+
LIKE 'AB+%%'     ESCAPE '+'                     Finds AB%, any string
LIKE 'AB++%'     ESCAPE '+'                     Finds AB+, any string
LIKE 'AB+++%'    ESCAPE '+'                     Finds AB+%
LIKE 'AB+++%%'   ESCAPE '+'                     Finds AB+%, any string
LIKE 'AB+%+%%'   ESCAPE '+'                     Finds AB%%, any string
LIKE 'AB++++'    ESCAPE '+'                     Finds AB++
LIKE 'AB+++++%'  ESCAPE '+'                     Finds AB++%
LIKE 'AB++++%'   ESCAPE '+'                     Finds AB++, any string
LIKE 'AB+%++%'   ESCAPE '+'                     Finds AB%+, any string
Figure 86, LIKE and ESCAPE examples





SELECT id                                                       ANSWER
FROM   staff                                                    ======
WHERE  id = 10                                                     ID
  AND  'ABC' LIKE 'AB%'                                            ---
  AND  'A%C' LIKE 'A/%C'  ESCAPE '/'                                10
  AND  'A_C' LIKE 'A\_C'  ESCAPE '\'
  AND  'A_$' LIKE 'A$_$$' ESCAPE '$';
Figure 87, LIKE and ESCAPE examples





Figure 88, NULL Predicate syntax





SELECT    id, comm                                           ANSWER
FROM      staff                                              =========
WHERE     id    < 100                                        ID   COMM
  AND     id   IS NOT NULL                                   ---  ----
  AND     comm IS     NULL                                    10  -
  AND NOT comm IS NOT NULL                                    30  -
ORDER BY id;                                                  50  -
Figure 89, NULL predicate examples





SELECT   id
        ,name
FROM     staff
WHERE    name LIKE '%a' || X'3B' || '%'
ORDER BY id;
Figure 90, Refer to semi-colon in SQL text





Example:       555 +    -22  /  (12 - 3) * 66                   ANSWER
                                                                ======
                   ^    ^    ^      ^    ^                         423
                  5th  2nd  3rd    1st  4th
Figure 91, Precedence rules example





SELECT               (12   - 3)       AS int1
        ,      -22 / (12   - 3)       AS int2
        ,      -22 / (12   - 3) * 66  AS int3
        ,555 + -22 / (12   - 3) * 66  AS int4
FROM     sysibm.sysdummy1;                                      ANSWER
                                                   ===================
                                                   INT1 INT2 INT3 INT4
                                                   ---- ---- ---- ----
                                                      9   -2 -132  423
Figure 92, Precedence rules, integer example





SELECT               (12.0 - 3)       AS dec1
        ,      -22 / (12.0 - 3)       AS dec2
        ,      -22 / (12.0 - 3) * 66  AS dec3
        ,555 + -22 / (12.0 - 3) * 66  AS dec4
FROM     sysibm.sysdummy1;                                      ANSWER
                                           ===========================
                                            DEC1   DEC2   DEC3   DEC4
                                           ------ ------ ------ ------
                                              9.0   -2.4 -161.3  393.6
Figure 93, Precedence rules, decimal example





SELECT   *                          ANSWER>>   COL1 COL2   TABLE1
FROM     table1                                ---- ----   +---------+
WHERE    col1  = 'C'                           A   AA      |COL1|COL2|
  AND    col1 >= 'A'                           B   BB      |----|----|
   OR    col2 >= 'AA'                          C   CC      |A   |AA  |
ORDER BY col1;                                             |B   |BB  |
                                                           |C   |CC  |
SELECT   *                          ANSWER>>   COL1 COL2   +---------+
FROM     table1                                ---- ----
WHERE   (col1  = 'C'                           A    AA
  AND    col1 >= 'A')                          B    BB
   OR    col2 >= 'AA'                          C    CC
ORDER BY col1;
       
SELECT   *                          ANSWER>>   COL1 COL2
FROM     table1                                ---- ----
WHERE    col1  = 'C'                           C    CC
  AND   (col1 >= 'A'
   OR    col2 >= 'AA')
ORDER BY col1;
Figure 94, Use of OR and parenthesis





FROM     clause
JOIN ON  clause
WHERE    clause
GROUP BY and aggregate
HAVING   clause
SELECT   list
ORDER BY clause
FETCH FIRST
Figure 95, Query Processing Sequence





Figure 96, CAST expression syntax





SELECT   id                                          ANSWER
        ,salary                                      =================
        ,CAST(salary AS INTEGER) AS sal2             ID SALARY   SAL2
FROM     staff                                       -- -------- -----
WHERE    id < 30                                     10 98357.50 98357
ORDER BY id;                                         20 78171.25 78171
Figure 97, Use CAST expression to convert Decimal to Integer





SELECT   id                                              ANSWER
        ,job                                             =============
        ,CAST(job AS CHAR(3)) AS job2                    ID JOB   JOB2
FROM     staff                                           -- ----- ----
WHERE    id < 30                                         10 Mgr   Mgr
ORDER BY id;                                             20 Sales Sal
Figure 98, Use CAST expression to truncate Char field





SELECT   id                                                    ANSWER
        ,CAST(NULL AS SMALLINT) AS junk                        =======
FROM     staff                                                 ID JUNK
WHERE    id < 30                                               -- ----
ORDER BY id;                                                   10    -
                                                               20    -
Figure 99, Use CAST expression to define SMALLINT field with null values





SELECT   stf.id                                              ANSWER
        ,emp.empno                                           =========
FROM     staff    stf                                        ID EMPNO
LEFT OUTER JOIN                                              -- ------
         employee emp                                        10 -
ON       stf.id   =  CAST(emp.empno AS INTEGER)              20 000020
AND      emp.job  = 'MANAGER'                                30 000030
WHERE    stf.id   <  60                                      40 -
ORDER BY stf.id;                                             50 000050
Figure 100, CAST expression in join





SELECT   stf.id                                              ANSWER
        ,emp.empno                                           =========
FROM     staff    stf                                        ID EMPNO
LEFT OUTER JOIN                                              -- ------
         employee emp                                        10 -
ON       stf.id   =  INTEGER(emp.empno)                      20 000020
AND      emp.job  = 'MANAGER'                                30 000030
WHERE    stf.id   <  60                                      40 -
ORDER BY stf.id;                                             50 000050
Figure 101, Function usage in join





Figure 102, VALUES expression syntax





VALUES   6                                        <= 1 row,  1 column
VALUES  (6)                                       <= 1 row,  1 column
VALUES   6, 7, 8                                  <= 1 row,  3 columns
VALUES  (6), (7), (8)                             <= 3 rows, 1 column
VALUES  (6,66), (7,77), (8,NULL)                  <= 3 rows, 2 columns
Figure 103, VALUES usage examples





PLAIN VALUES       VALUES + WITH          VALUES + SELECT       ANSWER
============       ====================   ==================    ======
                   WITH temp (c1,c2) AS   SELECT *                1  2
VALUES  (1,2)      (VALUES  (1,2)         FROM  (VALUES (1,2)    -- --
       ,(2,3)              ,(2,3)                      ,(2,3)     3  4
       ,(3,4)              ,(3,4))                     ,(3,4)     2  3
ORDER BY 2 DESC;   SELECT   *                   )temp (c1,c2)     1  2
                   FROM     temp          ORDER BY 2 DESC;
                   ORDER BY 2 DESC;
Figure 104, Logically equivalent VALUES statements





VALUES ((SELECT COUNT(*)     FROM employee)                   ANSWER
       ,(SELECT AVG(salary)  FROM staff)              ================
       ,(SELECT MAX(deptno)  FROM department))         1         2   3
FOR FETCH ONLY                                        -- --------- ---
WITH UR;                                              42  67932.78 J22
Figure 105, VALUES running selects





WITH temp1 (col1, col2) AS                                   ANSWER
(VALUES    (   0, 'AA')                                      =========
          ,(   1, 'BB')                                      COL1 COL2
          ,(   2, NULL)                                      ---- ----
)                                                               0 AA
SELECT *                                                        1 BB
FROM   temp1;                                                   2 -
Figure 106, Use VALUES to define a temporary table (1 of 4)





WITH temp1 (col1, col2) AS                                   ANSWER
(VALUES    (DECIMAL(0 ,3,1), 'AA')                           =========
          ,(DECIMAL(1 ,3,1), 'BB')                           COL1 COL2
          ,(DECIMAL(2 ,3,1), NULL)                           ---- ----
)                                                             0.0 AA
SELECT *                                                      1.0 BB
FROM   temp1;                                                 2.0 -
Figure 107, Use VALUES to define a temporary table (2 of 4)





WITH temp1 (col1, col2) AS                                   ANSWER
(VALUES    (   0, CAST('AA' AS CHAR(1)))                     =========
          ,(   1, CAST('BB' AS CHAR(1)))                     COL1 COL2
          ,(   2, CAST(NULL AS CHAR(1)))                     ---- ----
)                                                               0 A
SELECT *                                                        1 B
FROM   temp1;                                                   2 -
Figure 108, Use VALUES to define a temporary table (3 of 4)





WITH temp1 (col1, col2) AS                                   ANSWER
(VALUES    (   0, CHAR('AA',1))                              =========
          ,(   1, CHAR('BB',1))                              COL1 COL2
          ,(   2, NULL)                                      ---- ----
)                                                               0 A
SELECT *                                                        1 B
FROM   temp1;                                                   2 -
Figure 109, Use VALUES to define a temporary table (4 of 4)





WITH temp1 (col1, col2, col3) AS                            ANSWER
(VALUES    (   0, 'AA', 0.00)                               ==========
          ,(   1, 'BB', 1.11)                               COL1B COLX
          ,(   2, 'CC', 2.22)                               ----- ----
)                                                               0 0.00
,temp2 (col1b, colx) AS                                         1 2.11
(SELECT  col1                                                   2 4.22
        ,col1 + col3
 FROM    temp1
)      
SELECT *
FROM   temp2;
Figure 110, Derive one temporary table from another





CREATE VIEW silly (c1, c2, c3)
AS VALUES (11, 'AAA', SMALLINT(22))
         ,(12, 'BBB', SMALLINT(33))
         ,(13, 'CCC', NULL);
COMMIT;
Figure 111, Define a view using a VALUES clause





WITH temp1 (col1) AS                                            ANSWER
(VALUES     0                                                   ======
 UNION ALL                                                      COL1
 SELECT col1 + 1                                                ----
 FROM   temp1                                                      0
 WHERE  col1 + 1 < 100                                             1
)                                                                  2
SELECT *                                                           3
FROM   temp1;                                                    etc
Figure 112, Use VALUES defined data to seed a recursive SQL statement





SELECT   *                                                     ANSWER
FROM    (VALUES (123,'ABC')                                    ======
               ,(234,'DEF')                                    --- ---
        )AS ttt                                                234 DEF
ORDER BY 1 DESC;                                               123 ABC
Figure 113, Generate table with unnamed columns





SELECT  id                                                      ANSWER
       ,salary   AS sal                ===============================
       ,comm     AS com                ID      SAL    COM    COMBO TYP
       ,combo                          -- -------- ------ -------- ---
       ,typ                            10 98357.50      -        - COM
FROM    staff                          10 98357.50      - 98357.50 SAL
       ,TABLE(VALUES(salary,'SAL')     20 78171.25 612.45   612.45 COM
                   ,(comm,  'COM')     20 78171.25 612.45 78171.25 SAL
       )AS tab(combo,typ)              30 77506.75      -        - COM
WHERE   id < 40                        30 77506.75      - 77506.75 SAL
ORDER BY id
        ,typ;
Figure 114, Combine columns example





Figure 115, CASE expression syntax - 1st type





SELECT   Lastname                                 ANSWER
        ,sex   AS sx                              ====================
        ,CASE  sex                                LASTNAME   SX SEXX
           WHEN 'F' THEN 'FEMALE'                 ---------- -- ------
           WHEN 'M' THEN 'MALE'                   JEFFERSON  M  MALE
           ELSE NULL                              JOHN       F  FEMALE
         END AS sexx                              JOHNSON    F  FEMALE
FROM     employee                                 JONES      M  MALE
WHERE    lastname LIKE 'J%'
ORDER BY 1;
Figure 116, Use CASE (1st type) to expand a value





Figure 117, CASE expression syntax - 2nd type





SELECT   lastname                                 ANSWER
        ,sex   AS sx                              ====================
        ,CASE                                     LASTNAME   SX SEXX
           WHEN sex = 'F' THEN 'FEMALE'           ---------- -- ------
           WHEN sex = 'M' THEN 'MALE'             JEFFERSON  M  MALE
           ELSE NULL                              JOHN       F  FEMALE
         END AS sexx                              JOHNSON    F  FEMALE
FROM     employee                                 JONES      M  MALE
WHERE    lastname LIKE 'J%'
ORDER BY 1;
Figure 118, Use CASE (1st type) to expand a value





SELECT   lastname                                  ANSWER
        ,midinit AS mi                             ===================
        ,sex     AS sx                             LASTNAME   MI SX MX
        ,CASE                                      ---------- -- -- --
           WHEN midinit > SEX                      JEFFERSON  J  M  M
           THEN midinit                            JOHN       K  K  K
           ELSE sex                                JOHNSON    P  F  P
         END AS mx                                 JONES      T  M  T
FROM     employee
WHERE    lastname LIKE 'J%'
ORDER BY 1;
Figure 119, Use CASE to display the higher of two values





SELECT   COUNT(*)                                  AS tot    ANSWER
        ,SUM(CASE sex WHEN 'F' THEN 1 ELSE 0  END) AS #f     =========
        ,SUM(CASE sex WHEN 'M' THEN 1 ELSE 0  END) AS #m     TOT #F #M
FROM     employee                                            --- -- --
WHERE    lastname LIKE 'J%';                                   4  2  2
Figure 120, Use CASE to get multiple counts in one pass





SELECT   lastname                                ANSWER
        ,LENGTH(RTRIM(lastname)) AS len          =====================
        ,SUBSTR(lastname,1,                      LASTNAME   LEN LASTNM
           CASE                                  ---------- --- ------
            WHEN LENGTH(RTRIM(lastname))         JEFFERSON    9 JEFFER
                 > 6 THEN 6                      JOHN         4 JOHN
            ELSE LENGTH(RTRIM(lastname))         JOHNSON      7 JOHNSO
           END  ) AS lastnm                      JONES        5 JONES
FROM     employee
WHERE    lastname LIKE 'J%'
ORDER BY 1;
Figure 121, Use CASE inside a function





UPDATE staff
SET    comm = CASE dept
                 WHEN 15 THEN comm * 1.1
                 WHEN 20 THEN comm * 1.2
                 WHEN 38 THEN
                    CASE
                       WHEN years  < 5 THEN comm * 1.3
                       WHEN years >= 5 THEN comm * 1.4
                       ELSE NULL
                    END
                 ELSE comm
              END
WHERE  comm IS NOT NULL
  AND  dept  < 50;
Figure 122, UPDATE statement with nested CASE expressions





WITH temp1 (c1,c2) AS                                         ANSWER
(VALUES    (88,9),(44,3),(22,0),(0,1))                        ========
SELECT c1                                                     C1 C2 C3
      ,c2                                                     -- -- --
      ,CASE c2                                                88  9  9
         WHEN 0 THEN NULL                                     44  3 14
         ELSE c1/c2                                           22  0  -
       END AS c3                                               0  1  0
FROM   temp1;
Figure 123, Use CASE to avoid divide by zero





SELECT   lastname                                    ANSWER
        ,sex                                         =================
        ,CASE                                        LASTNAME   SX SXX
           WHEN sex >= 'M' THEN 'MAL'                ---------- -- ---
           WHEN sex >= 'F' THEN 'FEM'                JEFFERSON  M  MAL
         END AS sxx                                  JOHN       F  FEM
FROM     employee                                    JOHNSON    F  FEM
WHERE    lastname LIKE 'J%'                          JONES      M  MAL
ORDER BY 1;
Figure 124, Use CASE to derive a value (correct)





SELECT   lastname                                    ANSWER
        ,sex                                         =================
        ,CASE                                        LASTNAME   SX SXX
           WHEN sex >= 'F' THEN 'FEM'                ---------- -- ---
           WHEN sex >= 'M' THEN 'MAL'                JEFFERSON  M  FEM
         END AS sxx                                  JOHN       F  FEM
FROM     employee                                    JOHNSON    F  FEM
WHERE    lastname LIKE 'J%'                          JONES      M  FEM
ORDER BY 1;
Figure 125, Use CASE to derive a value (incorrect)





SELECT   id                                    ANSWER
        ,dept                                  =======================
        ,salary                                ID  DEPT SALARY   COMM
        ,comm                                  --- ---- -------- -----
FROM     staff                                 130   42 10505.90 75.60
WHERE    CASE                                  270   66 18555.50     -
            WHEN comm      <    70  THEN 'A'   330   66 10988.00 55.50
            WHEN name   LIKE 'W%'   THEN 'B'
            WHEN salary    < 11000  THEN 'C'
            WHEN salary    < 18500
             AND dept     <>    33  THEN 'D'
            WHEN salary    < 19000  THEN 'E'
         END IN ('A','C','E')
ORDER BY id;
Figure 126, Use CASE in a predicate





                                               ANSWER
                                               =======================
                                               ID  DEPT SALARY   COMM
SELECT   id                                    --- ---- -------- -----
        ,name                                  130   42 10505.90 75.60
        ,salary                                270   66 18555.50     -
        ,comm                                  330   66 10988.00 55.50
FROM     staff
WHERE   (comm   < 70)
   OR   (salary < 11000  AND NOT  name LIKE 'W%')
   OR   (salary < 19000  AND NOT (name LIKE 'W%'
                              OR (salary < 18500 AND dept <> 33)))
ORDER BY id;
Figure 127, Same stmt as prior, without CASE predicate





Figure 128, DECLARE CURSOR statement syntax





DECLARE fred CURSOR FOR
WITH RETURN TO CALLER
SELECT   id
        ,name
        ,salary
        ,comm
FROM     staff
WHERE    id       <  :id-var
  AND    salary   >  1000
ORDER BY id ASC
FETCH FIRST  10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
FOR FETCH ONLY
WITH UR
Figure 129, Sample cursor





DECLARE fred CURSOR WITH HOLD FOR
SELECT   name
        ,salary
FROM     staff
WHERE    id > :id-var
FOR UPDDATE OF salary, comm
OPEN fred
DO UNTIL SQLCODE = 100
   FETCH   fred
   INTO    :name-var
          ,:salary-var
   IF salary < 1000 THEN DO
      UPDATE  staff
      SET     salary = :new-salary-var
      WHERE CURRENT OF fred
   END-IF
END-DO 
CLOSE fred
Figure 130, Use cursor in program





SELECT  name
       ,salary
INTO    :name-var
       ,:salary-var
FROM    staff
WHERE   id = :id-var
Figure 131, Singleton select





Figure 132, PREPARE statement syntax





STATEMENT CAN BE USED BY      STATEMENT TYPE
========================      ==============
DESCRIBE                      Any statement
DECLARE CURSOR                Must be SELECT
EXECUTE                       Must not be SELECT
Figure 133, What statements can use prepared statement





Figure 134, DESCRIBE statement syntax





DESCRIBE OUTPUT SELECT * FROM staff
       
SQLDA Information
sqldaid : SQLDA     sqldabc: 896  sqln: 20  sqld: 7
Column Information
       
sqltype             sqllen  sqlname.data                sqlname.length
------------------  ------  --------------------------  --------------
500   SMALLINT           2  ID                                       2
449   VARCHAR            9  NAME                                     4
501   SMALLINT           2  DEPT                                     4
453   CHARACTER          5  JOB                                      3
501   SMALLINT           2  YEARS                                    5
485   DECIMAL         7, 2  SALARY                                   6
485   DECIMAL         7, 2  COMM                                     4
Figure 135, DESCRIBE the output columns in a select statement





DESCRIBE TABLE staff
       
Column                   Type      Type
name                     schema    name          Length   Scale  Nulls
-----------------------  --------  ------------  -------  -----  -----
ID                       SYSIBM    SMALLINT            2      0  No
NAME                     SYSIBM    VARCHAR             9      0  Yes
DEPT                     SYSIBM    SMALLINT            2      0  Yes
JOB                      SYSIBM    CHARACTER           5      0  Yes
YEARS                    SYSIBM    SMALLINT            2      0  Yes
SALARY                   SYSIBM    DECIMAL             7      2  Yes
COMM                     SYSIBM    DECIMAL             7      2  Yes
Figure 136, DESCRIBE the columns in a table





SET :host-var = CURRENT TIMESTAMP
Figure 137, SET single host-variable





SET :host-v1 = CURRENT TIME
   ,:host-v2 = CURRENT DEGREE
   ,:host-v3 = NULL
Figure 138, SET multiple host-variables





SET    (:hv1
       ,:hv2
       ,:hv3) =
(SELECT  id
        ,name
        ,salary
 FROM    staff
 WHERE   id = :id-var)
Figure 139, SET using row-fullselect





SET CONNECTION
SET CURRENT DEFAULT TRANSFORM GROUP
SET CURRENT DEGREE
SET CURRENT EXPLAIN MODE
SET CURRENT EXPLAIN SNAPSHOT
SET CURRENT ISOLATION
SET CURRENT LOCK TIMEOUT
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
SET CURRENT PACKAGE PATH
SET CURRENT PACKAGESET
SET CURRENT QUERY OPTIMIZATION
SET CURRENT REFRESH AGE
SET ENCRYPTION PASSWORD
SET EVENT MONITOR STATE
SET INTEGRITY
SET PASSTHRU
SET PATH
SET SCHEMA
SET SERVER OPTION
SET SESSION AUTHORIZATION
Figure 140, Other SET statements





Figure 141, SAVEPOINT statement syntax





Figure 142, Example of savepoint usage





Figure 143, RELEASE SAVEPOINT statement syntax





Figure 144, ROLLBACK statement syntax





CREATE TABLE emp_act_copy
(empno              CHARACTER  (00006)    NOT NULL
,projno             CHARACTER  (00006)    NOT NULL
,actno              SMALLINT              NOT NULL
,emptime            DECIMAL    (05,02)
,emstdate           DATE
,emendate           DATE);
Figure 145, EMP_ACT_COPY sample table - DDL





Figure 146, INSERT statement syntax





INSERT INTO emp_act_copy VALUES
   ('100000' ,'ABC' ,10 ,1.4 ,'2003-10-22', '2003-11-24');
Figure 147, Single row insert





INSERT INTO emp_act_copy VALUES
   ('200000' ,'ABC' ,10 ,1.4 ,'2003-10-22', '2003-11-24')
  ,('200000' ,'DEF' ,10 ,1.4 ,'2003-10-22', '2003-11-24')
  ,('200000' ,'IJK' ,10 ,1.4 ,'2003-10-22', '2003-11-24');
Figure 148, Multi row insert





INSERT INTO emp_act_copy VALUES
   ('400000' ,'ABC' ,10 ,NULL ,DEFAULT, CURRENT DATE);
Figure 149,Using null and default values





INSERT INTO emp_act_copy (projno, emendate, actno, empno) VALUES
   ('ABC' ,DATE(CURRENT TIMESTAMP) ,123 ,'500000');
Figure 150, Explicitly listing columns being populated during insert





INSERT INTO
   (SELECT *
    FROM   emp_act_copy
    WHERE  empno < '1'
   )   
VALUES ('510000' ,'ABC' ,10 ,1.4 ,'2003-10-22', '2003-11-24');
Figure 151, Insert into a fullselect





INSERT INTO emp_act_copy
SELECT LTRIM(CHAR(id + 600000))
      ,SUBSTR(UCASE(name),1,6)
      ,salary / 229
      ,123
      ,CURRENT DATE
      ,'2003-11-11'
FROM   staff
WHERE  id < 50;
Figure 152,Insert result of select statement





INSERT INTO emp_act_copy (empno, actno, projno)
SELECT LTRIM(CHAR(id + 700000))
      ,MINUTE(CURRENT TIME)
      ,'DEF'
FROM   staff
WHERE  id < 40;
Figure 153, Insert result of select - specified columns only





INSERT   INTO emp_act_copy
SELECT * FROM emp_act_copy;
Figure 154, Stupid - insert - doubles rows





INSERT INTO emp_act_copy (empno, actno, projno)
SELECT LTRIM(CHAR(id + 800000))
      ,77
      ,'XYZ'
FROM   staff
WHERE  id < 40
UNION  
SELECT LTRIM(CHAR(id + 900000))
      ,SALARY / 100
      ,'DEF'
FROM   staff
WHERE  id < 50;
Figure 155, Inserting result of union





INSERT INTO emp_act_copy (empno, actno, projno, emptime)
WITH temp1 (col1) AS
(VALUES (1),(2),(3),(4),(5),(6))
SELECT LTRIM(CHAR(col1 + 910000))
      ,col1
      ,CHAR(col1)
      ,col1 / 2
FROM   temp1;
Figure 156, Insert from common table expression





INSERT INTO emp_act_copy (empno, actno, projno)
SELECT LTRIM(CHAR(id + 920000))
      ,id
      ,'ABC'
FROM   staff
WHERE  id < 40
  AND  NOT EXISTS
      (SELECT *
       FROM   emp_act_copy
       WHERE  empno LIKE '92%');
Figure 157, Insert with irrelevant sub-query





CREATE TABLE us_customer                  CREATE TABLE intl_customer
(cust#    INTEGER   NOT NULL              (cust#    INTEGER   NOT NULL
,cname    CHAR(10)  NOT NULL              ,cname    CHAR(10)  NOT NULL
,country  CHAR(03)  NOT NULL              ,country  CHAR(03)  NOT NULL
,CHECK    (country = 'USA')               ,CHECK    (country <> 'USA')
,PRIMARY KEY (cust#));                    ,PRIMARY KEY (cust#));
Figure 158, Customer tables - for insert usage





INSERT INTO
  (SELECT   *
   FROM     us_customer
   UNION ALL
   SELECT   *
   FROM     intl_customer)
VALUES (111,'Fred','USA')
      ,(222,'Dave','USA')
      ,(333,'Juan','MEX');
Figure 159, Insert into multiple tables





UPDATE  emp_act_copy
SET     emptime  =  NULL
       ,emendate =  DEFAULT
       ,emstdate =  CURRENT DATE + 2 DAYS
       ,actno    =  ACTNO / 2
       ,projno   =  'ABC'
WHERE   empno    = '100000';
Figure 160, Single row update





Figure 161, UPDATE statement syntax





UPDATE  emp_act_copy
SET     actno = actno / 2;
Figure 162, Mass update





UPDATE  emp_act_copy ac1
SET     actno     =  actno * 2
       ,emptime   =  actno * 2
WHERE   empno LIKE '910%';
Figure 163, Two columns get same value





UPDATE  emp_act_copy
SET     actno    = (SELECT MAX(salary) / 10
                    FROM   staff)
WHERE   empno    = '200000';
Figure 164, Update using select





UPDATE  emp_act_copy
SET    (actno
       ,emstdate
       ,projno)  = (SELECT MAX(salary)  / 10
                          ,CURRENT DATE + 2 DAYS
                          ,MIN(CHAR(id))
                    FROM   staff
                    WHERE  id <> 33)
WHERE   empno LIKE '600%';
Figure 165, Multi-row update using select





UPDATE  emp_act_copy ac1
SET    (actno
       ,emptime)  = (SELECT ac2.actno   + 1
                           ,ac1.emptime / 2
                     FROM   emp_act_copy ac2
                     WHERE  ac2.empno        LIKE '60%'
                       AND  SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE   EMPNO LIKE '700%';
Figure 166, Multi-row update using correlated select





UPDATE  emp_act_copy
SET     emptime =  10
WHERE   empno   = '000010'
  AND   projno  = 'MA2100';
Figure 167, Direct update of table





UPDATE 
  (SELECT  *
   FROM    emp_act_copy
   WHERE   empno   = '000010'
     AND   projno  = 'MA2100'
  )AS ea
SET emptime = 20;
Figure 168, Update of fullselect





UPDATE 
  (SELECT   *
   FROM     staff
   ORDER BY salary DESC
   FETCH FIRST 5 ROWS ONLY
  )AS xxx
SET comm = 10000;
Figure 169, Update first "n" rows





UPDATE  emp_act_copy ea1
SET     emptime = (SELECT MAX(emptime)
                   FROM   emp_act_copy ea2
                   WHERE  ea1.empno = ea2.empno)
WHERE   empno   = '000010'
  AND   projno  = 'MA2100';
Figure 170, Set employee-time in row to MAX - for given employee





UPDATE 
  (SELECT  ea1.*
          ,MAX(emptime) OVER(PARTITION BY empno) AS maxtime
   FROM    emp_act_copy ea1
  )AS ea2
SET     emptime = maxtime
WHERE   empno   = '000010'
  AND   projno  = 'MA2100';
Figure 171, Use OLAP function to get max-time, then apply (correct)





UPDATE  emp_act_copy
SET     emptime =  MAX(emptime) OVER(PARTITION BY empno)
WHERE   empno   = '000010'
  AND   projno  = 'MA2100';
Figure 172, Use OLAP function to get max-time, then apply (wrong)





UPDATE  emp_act_copy ac1
SET    (actno
       ,emptime)  = (SELECT ROW_NUMBER() OVER()
                           ,ac1.emptime / 2
                     FROM   emp_act_copy ac2
                     WHERE  ac2.empno        LIKE '60%'
                       AND  SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE   EMPNO LIKE '800%';
Figure 173, Update with correlated query





UPDATE  emp_act_copy ac1
SET    (actno
       ,emptime)  = (SELECT c1
                           ,c2
                     FROM  (SELECT ROW_NUMBER() OVER() AS c1
                                  ,actno / 100         AS c2
                                  ,empno
                            FROM   emp_act_copy
                            WHERE  empno LIKE '60%'
                    )AS ac2
                     WHERE  SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE   empno LIKE '900%';
Figure 174, Update with uncorrelated query





DELETE 
FROM    emp_act_copy
WHERE   empno    = '000010'
  AND   projno   = 'MA2100'
  AND   actno    =  10;
Figure 175, Single-row delete





Figure 176, DELETE statement syntax





DELETE 
FROM    emp_act_copy;
Figure 177, Mass delete





DELETE 
FROM    emp_act_copy
WHERE   empno   LIKE '00%'
  AND   projno    >= 'MA';
Figure 178, Selective delete





DELETE 
FROM    staff s1
WHERE   id NOT IN
       (SELECT MAX(id)
        FROM   staff s2
        WHERE  s1.dept = s2.dept);
Figure 179, Correlated delete (1 of 2)





DELETE 
FROM    staff s1
WHERE   EXISTS
       (SELECT *
        FROM   staff s2
        WHERE  s2.dept = s1.dept
          AND  s2.id   > s1.id);
Figure 180, Correlated delete (2 of 2)





DELETE FROM
  (SELECT  id
          ,MAX(id) OVER(PARTITION BY dept) AS max_id
   FROM    staff
  )AS ss
WHERE id <> max_id;
Figure 181, Delete using fullselect and OLAP function





DELETE 
FROM    (SELECT   *
         FROM     staff
         ORDER BY salary DESC
         FETCH FIRST 5 ROWS ONLY
        )AS xxx;
Figure 182, Delete first "n" rows





Figure 183, Select DML statement syntax





                                                        ANSWER
                                                        ==============
SELECT   empno                                          EMPNO  PRJ ACT
        ,projno AS prj                                  ------ --- ---
        ,actno  AS act                                  200000 ABC  10
FROM     FINAL TABLE                                    200000 DEF  10
   (INSERT INTO emp_act_copy
    VALUES ('200000','ABC',10 ,1,'2003-10-22','2003-11-24')
          ,('200000','DEF',10 ,1,'2003-10-22','2003-11-24'))
ORDER BY 1,2,3;
Figure 184, Select rows inserted





SELECT   empno                                       ANSWER
        ,projno AS prj                               =================
        ,actno  AS act                               EMPNO  PRJ ACT R#
        ,row#   AS r#                                ------ --- --- --
FROM     FINAL TABLE                                 300000 ZZZ 999  1
   (INSERT INTO emp_act_copy (empno, projno, actno)  300000 VVV 111  2
    INCLUDE (row# SMALLINT)
    VALUES ('300000','ZZZ',999,1)
          ,('300000','VVV',111,2))
ORDER BY row#;
Figure 185, Include column to get insert sequence





SELECT   empno                                       ANSWER
        ,projno AS prj                               =================
        ,actno  AS act                               EMPNO  PRJ ACT R#
        ,ROW_NUMBER() OVER() AS r#                   ------ --- --- --
FROM     FINAL TABLE                                 400000 ZZZ 999  1
   (INSERT INTO emp_act_copy (empno, projno, actno)  400000 VVV 111  2
    VALUES ('400000','ZZZ',999)
          ,('400000','VVV',111))
ORDER BY INPUT SEQUENCE;
Figure 186, Select rows in insert order





SELECT   empno                                       ANSWER
        ,projno AS prj                               =================
        ,actno  AS act                               EMPNO  PRJ ACT R#
        ,ROW_NUMBER() OVER() AS r#                   ------ ---  -- --
FROM     NEW TABLE                                   600010 1    59  1
   (INSERT INTO emp_act_copy (empno, actno, projno)  600020 563  59  2
    SELECT  LTRIM(CHAR(id + 600000))                 600030 193  59  3
           ,SECOND(CURRENT TIME)
           ,CHAR(SMALLINT(RAND(1) * 1000))
    FROM    staff
    WHERE   id < 40)
ORDER BY INPUT SEQUENCE;
Figure 187, Select from an insert that has unknown values





SELECT   empno                                        ANSWER
        ,projno  AS prj                               ================
        ,emptime AS etime                             EMPNO  PRJ ETIME
FROM     OLD TABLE                                    ------ --- -----
   (UPDATE emp_act_copy                               200000 ABC  1.00
    SET    emptime = emptime * 2                      200000 DEF  1.00
    WHERE  empno   = '200000')
ORDER BY projno;
Figure 188, Select values - from before update





SELECT   projno  AS prj                                ANSWER
        ,old_t   AS old_t                              ===============
        ,emptime AS new_t                              PRJ OLD_T NEW_T
FROM     NEW TABLE                                     --- ----- -----
   (UPDATE  emp_act_copy                               ABC  2.00  0.02
    INCLUDE (old_t DECIMAL(5,2))                       DEF  2.00 11.27
    SET     emptime = emptime * RAND(1) * 10
           ,old_t   = emptime
    WHERE   empno   = '200000')
ORDER BY 1;
Figure 189, Select values - before and after update





SELECT   projno AS prj                                         ANSWER
        ,actno  AS act                                         =======
FROM     OLD TABLE                                             PRJ ACT
   (DELETE                                                     --- ---
    FROM   emp_act_copy                                        VVV 111
    WHERE  empno = '300000')                                   ZZZ 999
ORDER BY 1,2;
Figure 190, List deleted rows





SELECT   empno                                    ANSWER
        ,projno                                   ====================
        ,actno  AS act                            EMPNO  PROJNO ACT R#
        ,row#   AS r#                             ------ ------ --- --
FROM     OLD TABLE                                000260 AD3113  70  2
   (DELETE                                        000260 AD3113  80  4
    FROM    emp_act_copy                          000260 AD3113 180  6
    INCLUDE (row# SMALLINT)
    SET     row#  = ROW_NUMBER() OVER()
    WHERE   empno = '000260')
WHERE    row# = row# / 2 * 2
ORDER BY 1,2,3;
Figure 191, Assign row numbers to deleted rows





SELECT   empno                              ANSWER
        ,(SELECT  lastname                  ==========================
          FROM   (SELECT  empno AS e#       EMPNO  LASTNAME PROJNO ACT
                         ,lastname          ------ -------- ------ ---
                  FROM    employee          000010 HAAS     AD3100  10
                 )AS xxx                    000010 HAAS     MA2100  10
          WHERE   empno = e#)               000010 HAAS     MA2110  10
        ,projno AS projno                   000020 THOMPSON PL2100  30
        ,actno  AS act                      000030 KWAN     IF1000  10
FROM     OLD TABLE
   (DELETE
    FROM    emp_act_copy
    WHERE   empno < '0001')
ORDER BY 1,2,3
FETCH FIRST 5 ROWS ONLY;
Figure 192, Join result to another table





Figure 193, MERGE statement syntax





CREATE TABLE old_staff AS            OLD_STAFF            NEW_STAFF
   (SELECT id, job, salary           +-----------------+  +----------+
    FROM   staff)                    |ID|JOB  |SALARY  |  |ID|SALARY |
WITH NO DATA;                        |--|-----|--------|  |--|-------|
                                     |20|Sales|78171.25|  |30|7750.67|
CREATE TABLE new_staff AS            |30|Mgr  |77506.75|  |40|7800.60|
   (SELECT id, salary                |40|Sales|78006.00|  |50|8065.98|
    FROM   staff)                    +-----------------+  +----------+
WITH NO DATA;
       
INSERT INTO old_staff                INSERT INTO new_staff
SELECT id, job, salary               SELECT id, salary / 10
FROM   staff                         FROM   staff
WHERE  id BETWEEN 20 and 40;         WHERE  id BETWEEN 30 and 50;
Figure 194, Sample tables for merge





MERGE INTO old_staff oo              OLD_STAFF            NEW_STAFF
USING new_staff nn                   +-----------------+  +----------+
ON    oo.id = nn.id                  |ID|JOB  |SALARY  |  |ID|SALARY |
WHEN MATCHED THEN                    |--|-----|--------|  |--|-------|
   UPDATE                            |20|Sales|78171.25|  |30|7750.67|
   SET oo.salary = nn.salary         |30|Mgr  |77506.75|  |40|7800.60|
WHEN NOT MATCHED THEN                |40|Sales|78006.00|  |50|8065.98|
   INSERT                            +-----------------+  +----------+
   VALUES (nn.id,'?',nn.salary);
                                                     AFTER-MERGE
                                                     =================
                                                     ID JOB   SALARY
                                                     -- ----- --------
                                                     20 Sales 78171.25
                                                     30 Mgr    7750.67
                                                     40 Sales  7800.60
                                                     50 ?      8065.98
Figure 195, Merge - do update or insert





MERGE INTO old_staff oo                              AFTER-MERGE
USING new_staff nn                                   =================
ON    oo.id = nn.id                                  ID JOB   SALARY
WHEN MATCHED THEN                                    -- ----- --------
   DELETE;                                           20 Sales 78171.25
Figure 196, Merge - delete if match





MERGE INTO old_staff oo              OLD_STAFF            NEW_STAFF
USING new_staff nn                   +-----------------+  +----------+
ON    oo.id = nn.id                  |ID|JOB  |SALARY  |  |ID|SALARY |
WHEN MATCHED                         |--|-----|--------|  |--|-------|
AND  oo.salary < 78000 THEN          |20|Sales|78171.25|  |30|7750.67|
   UPDATE                            |30|Mgr  |77506.75|  |40|7800.60|
   SET oo.salary = nn.salary         |40|Sales|78006.00|  |50|8065.98|
WHEN MATCHED                         +-----------------+  +----------+
AND  oo.salary > 78000 THEN
   DELETE                                            AFTER-MERGE
WHEN NOT MATCHED                                     =================
AND  nn.id > 10 THEN                                 ID JOB   SALARY
   INSERT                                            -- ----- --------
   VALUES (nn.id,'?',nn.salary)                      20 Sales 78171.25
WHEN NOT MATCHED THEN                                30 Mgr    7750.67
   SIGNAL SQLSTATE '70001'                           50 ?      8065.98
   SET MESSAGE_TEXT = 'New ID <= 10';
Figure 197, Merge with multiple options





MERGE INTO old_staff                                 AFTER-MERGE
USING                                                =================
  (SELECT MAX(id) + 1 AS max_id                      ID JOB   SALARY
         ,MAX(job)    AS max_job                     -- ----- --------
         ,MAX(salary) AS max_sal                     20 Sales 78171.25
   FROM   old_staff                                  30 Mgr   77506.75
  )AS mx                                             40 Sales 78006.00
ON    id = max_id                                    41 Sales 78171.25
WHEN NOT MATCHED THEN
   INSERT
   VALUES (max_id, max_job, max_sal);
Figure 198, Merge MAX row into table





INSERT INTO old_staff
SELECT MAX(id) + 1 AS max_id
      ,MAX(job)    AS max_job
      ,MAX(salary) AS max_sal
FROM   old_staff;
Figure 199, Merge logic - done using insert





MERGE INTO                           OLD_STAFF            NEW_STAFF
  (SELECT *                          +-----------------+  +----------+
   FROM   old_staff                  |ID|JOB  |SALARY  |  |ID|SALARY |
   WHERE  id < 40                    |--|-----|--------|  |--|-------|
  )AS oo                             |20|Sales|78171.25|  |30|7750.67|
USING                                |30|Mgr  |77506.75|  |40|7800.60|
  (SELECT *                          |40|Sales|78006.00|  |50|8065.98|
   FROM   new_staff                  +-----------------+  +----------+
   WHERE  id < 50
  )AS nn                                             AFTER-MERGE
ON    oo.id = nn.id                                  =================
WHEN MATCHED THEN                                    ID JOB   SALARY
   DELETE                                            -- ----- --------
WHEN NOT MATCHED THEN                                20 Sales 78171.25
   INSERT                                            40 ?      7800.60
   VALUES (nn.id,'?',nn.salary);                     40 Sales 78006.00
Figure 200, Merge using two fullselects





MERGE INTO old_staff oo                              AFTER-MERGE
USING new_staff nn                                   =================
ON    oo.id = nn.id                                  ID JOB   SALARY
WHEN MATCHED THEN                                    -- ----- --------
   UPDATE                                            20 Sales 78171.25
   SET (salary,job) = (1234,'?')                     30 ?      1234.00
WHEN NOT MATCHED THEN                                40 ?      1234.00
   INSERT (id,salary,job)                            50 ?      5678.90
   VALUES (id,5678.9,'?');
Figure 201, Listing columns and values in insert





Figure 202, Compound SQL Statement syntax





BEGIN ATOMIC
   DECLARE cntr SMALLINT DEFAULT 1;
   FOR V1 AS
      SELECT   id as idval
      FROM     staff
      WHERE    id < 80
      ORDER BY id
   DO  
      UPDATE   staff
      SET      comm = cntr
      WHERE    id   = idval;
      SET cntr = cntr + 1;
   END FOR;
END    
Figure 203, Sample Compound SQL statement





--#SET DELIMITER !
       
SELECT NAME FROM STAFF WHERE id = 10!
       
--#SET DELIMITER ;
       
SELECT NAME FROM STAFF WHERE id = 20;
Figure 204, Set Delimiter example





BEGIN ATOMIC
   DECLARE aaa, bbb, ccc SMALLINT DEFAULT 1;
   DECLARE ddd           CHAR(10) DEFAULT NULL;
   DECLARE eee           INTEGER;
   SET eee = aaa + 1;
   UPDATE   staff
   SET      comm   = aaa
           ,salary = bbb
           ,years  = eee
   WHERE    id     = 10;
END    
Figure 205, DECLARE examples





Figure 206, FOR statement syntax





BEGIN ATOMIC                                                    BEFORE
   FOR V1 AS                                      ====================
      SELECT   years    AS yr_num                 ID     SALARY   COMM
              ,max(id)  AS max_id                 --- --------- ------
      FROM     staff                              180  37009.75 236.50
      WHERE    years < 4                          230  83369.80 189.65
      GROUP BY years                              330  49988.00  55.50
      ORDER BY years
   DO  
      UPDATE staff                                               AFTER
      SET    salary = salary / 10                 ====================
      WHERE  id     = max_id;                     ID     SALARY   COMM
      UPDATE staff                                --- --------- ------
      set    comm   = 0                           180  37009.75   0.00
      WHERE  years  = yr_num;                     230   8336.98   0.00
   END FOR;                                       330   4998.80   0.00
END    
Figure 207, FOR statement example





Figure 208, GET DIAGNOSTICS statement syntax





BEGIN ATOMIC
   DECLARE numrows INT DEFAULT 0;
   UPDATE staff
   SET    salary = 12345
   WHERE  id < 100;
   GET DIAGNOSTICS numrows = ROW_COUNT;
   UPDATE staff
   SET    salary = numrows
   WHERE  id = 10;
END    
Figure 209, GET DIAGNOSTICS statement example





Figure 210, IF statement syntax





BEGIN ATOMIC
   DECLARE cur INT;
   SET cur = MICROSECOND(CURRENT TIMESTAMP);
   IF cur > 600000 THEN
      UPDATE staff
      SET    name = CHAR(cur)
      WHERE  id   = 10;
   ELSEIF cur > 300000 THEN
      UPDATE staff
      SET    name = CHAR(cur)
      WHERE  id   = 20;
   ELSE
      UPDATE staff
      SET    name = CHAR(cur)
      WHERE  id   = 30;
   END IF;
END    
Figure 211, IF statement example





Figure 212, ITERATE statement syntax





BEGIN ATOMIC
   DECLARE cntr INT DEFAULT 0;
   whileloop:
   WHILE cntr < 60  DO
      SET cntr = cntr + 10;
      UPDATE staff
      SET    salary = cntr
      WHERE  id     = cntr;
      ITERATE whileloop;
      UPDATE staff
      SET    comm   = cntr + 1
      WHERE  id     = cntr;
   END WHILE;
END    
Figure 213, ITERATE statement example





Figure 214, LEAVE statement syntax





BEGIN ATOMIC
   DECLARE cntr INT DEFAULT 1;
   whileloop:
   WHILE 1 <> 2 DO
      SET cntr = cntr + 1;
      IF RAND() > 0.99 THEN
         LEAVE whileloop;
      END IF;
   END WHILE;
   UPDATE staff
   SET    salary = cntr
   WHERE  id = 10;
END    
Figure 215, LEAVE statement example





Figure 216, SIGNAL statement syntax





BEGIN ATOMIC
   DECLARE cntr INT DEFAULT 1;
   DECLARE emsg CHAR(20);
   whileloop:
   WHILE RAND() < .99 DO
      SET cntr = cntr + 1;
   END WHILE;
   SET emsg = '#loops: ' || CHAR(cntr);
   SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = emsg;
END    
Figure 217, SIGNAL statement example





Figure 218, WHILE statement syntax





BEGIN ATOMIC
   DECLARE c1, C2 INT DEFAULT 1;
   WHILE c1 < 10 DO
      WHILE c2 < 20 DO
         SET c2 = c2 + 1;
      END WHILE;
      SET c1 = c1 + 1;
   END WHILE;
   UPDATE staff
   SET    salary = c1
         ,comm   = c2
   WHERE  id     = 10;
END    
Figure 219, WHILE statement example





SELECT   dept                                               ANSWER
        ,count(*) as #rows                                  ==========
FROM     staff                                              DEPT #ROWS
GROUP BY dept                                               ---- -----
ORDER BY dept;                                                10     4
                                                              15     4
                                                              20     4
                                                              38     5
                                                              42     4
                                                              51     5
                                                              66     5
                                                              84     4
Figure 220, List departments in STAFF table





--#SET DELIMITER !                                        IMPORTANT
                                                          ============
CREATE TABLE dpt                                          This example
(dept    SMALLINT     NOT NULL                            uses an "!"
,#names  SMALLINT                                         as the stmt
,PRIMARY KEY(dept))!                                      delimiter.
COMMIT!
       
CREATE TRIGGER dpt1 AFTER INSERT ON dpt
REFERENCING NEW AS NNN
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
   DECLARE namecnt SMALLINT DEFAULT 0;
   FOR getnames AS
      SELECT   COUNT(*) AS #n
      FROM     staff
      WHERE    dept = nnn.dept
   DO  
      SET namecnt = #n;
   END FOR;
   UPDATE dpt
   SET    #names = namecnt
   WHERE  dept   = nnn.dept;                               ANSWER
END!                                                       ===========
COMMIT!                                                    DEPT #NAMES
                                                           ---- ------
INSERT INTO dpt (dept)                                       10      4
SELECT DISTINCT dept                                         15      4
FROM   staff!                                                20      4
COMMIT!                                                      38      5
                                                             42      4
SELECT   *                                                   51      5
FROM     dpt                                                 66      5
ORDER BY dept!                                               84      4
Figure 221, Trigger with compound SQL





--#SET DELIMITER !                                        IMPORTANT
                                                          ============
CREATE FUNCTION dpt1 (deptin SMALLINT)                    This example
RETURNS SMALLINT                                          uses an "!"
BEGIN ATOMIC                                              as the stmt
   DECLARE num_names SMALLINT;                            delimiter.
   FOR getnames AS
      SELECT   COUNT(*) AS #n
      FROM     staff
      WHERE    dept = deptin
   DO  
      SET num_names = #n;
   END FOR;                                                ANSWER
   RETURN num_names;                                       ===========
END!                                                       DEPT #NAMES
COMMIT!                                                    ---- ------
                                                             10      4
SELECT   XXX.*                                               15      4
        ,dpt1(dept) as #names                                20      4
FROM    (SELECT   dept                                       38      5
         FROM     staff                                      42      4
         GROUP BY dept                                       51      5
        )AS XXX                                              66      5
ORDER BY dept!                                               84      4
Figure 222, Scalar Function with compound SQL





--#SET DELIMITER !                                        IMPORTANT
                                                          ============
CREATE FUNCTION dpt1 (deptin SMALLINT)                    This example
RETURNS SMALLINT                                          uses an "!"
BEGIN ATOMIC                                              as the stmt
   RETURN                                                 delimiter.
   SELECT COUNT(*)
   FROM   staff
   WHERE  dept = deptin;
END!   
COMMIT!
       
SELECT   XXX.*
        ,dpt1(dept) as #names
FROM    (SELECT   dept
         FROM     staff
         GROUP BY dept
        )AS XXX
ORDER BY dept!
Figure 223, Scalar Function with compound SQL





--#SET DELIMITER !                                        IMPORTANT
                                                          ============
CREATE FUNCTION dpt2 ()                                   This example
RETURNS TABLE (dept    SMALLINT                           uses an "!"
              ,#names  SMALLINT)                          as the stmt
BEGIN ATOMIC                                              delimiter.
   RETURN
   SELECT   dept
           ,count(*)                                       ANSWER
   FROM     staff                                          ===========
   GROUP BY dept                                           DEPT #NAMES
   ORDER BY dept;                                          ---- ------
END!                                                         10      4
COMMIT!                                                      15      4
                                                             20      4
--#SET DELIMITER ;                                           38      5
                                                             42      4
SELECT   *                                                   51      5
FROM     TABLE(dpt2()) T1                                    66      5
ORDER BY dept;                                               84      4
Figure 224, Table Function with compound SQL





Figure 225, ARRAY_AGG function syntax





Figure 226, AVG function syntax





SELECT   AVG(dept)          AS a1                       ANSWER
        ,AVG(ALL dept)      AS a2                       ==============
        ,AVG(DISTINCT dept) AS a3                       A1 A2 A3 A4 A5
        ,AVG(dept/10)       AS a4                       -- -- -- -- --
        ,AVG(dept)/10       AS a5                       41 41 40  3  4
FROM     staff
HAVING   AVG(dept) > 40;
Figure 227, AVG function examples





UPDATE staff
SET    comm = 0
WHERE  comm IS NULL;
      
SELECT AVG(salary) AS salary                   ANSWER
      ,AVG(comm)   AS comm1                    ======================
      ,AVG(CASE comm                            SALARY   COMM1  COMM2
             WHEN 0 THEN NULL                  -------- ------ ------
             ELSE comm                         67932.78 351.98 513.31
           END) AS comm2
FROM   staff;
      
UPDATE staff
SET    comm = NULL
WHERE  comm = 0;
Figure 228, Convert zero to null before doing AVG





SELECT   COUNT(*) AS c1                                    ANSWER
        ,AVG(salary) AS a1                                 ===========
        ,COALESCE(AVG(salary),0) AS a2                     C1 A1 A2 A3
        ,CASE                                              -- -- -- --
           WHEN AVG(salary) IS NULL THEN 0                  0  -  0  0
           ELSE AVG(salary)
         END AS a3
FROM     staff
WHERE    id < 10;
Figure 229, Convert null output (from AVG) to zero





SELECT  AVG(DAYS(birthdate))                         ANSWER
       ,DATE(AVG(DAYS(birthdate)))                   =================
FROM    employee;                                    1      2
                                                     ------ ----------
                                                     721092 1975-04-14
Figure 230, AVG of date column





SELECT  AVG(avg_sal) AS avg_avg                       ANSWER
FROM   (SELECT   dept                                 ================
                ,AVG(salary) AS avg_sal               
        FROM     staff
        GROUP BY dept
       )AS xxx;
Figure 231, Select average of average





Figure 232, CORRELATION function syntax





WITH temp1(col1, col2, col3, col4) AS      ANSWER
(VALUES   (0   , 0   , 0   , RAND(1))      ===========================
 UNION ALL                                 COR11  COR12  COR23  COR34
 SELECT col1 + 1                           ------ ------ ------ ------
       ,col2 - 1                            1.000 -1.000 -0.017 -0.005
       ,RAND()
       ,RAND()
 FROM   temp1
 WHERE  col1 <= 1000
)      
SELECT DEC(CORRELATION(col1,col1),5,3)  AS cor11
      ,DEC(CORRELATION(col1,col2),5,3)  AS cor12
      ,DEC(CORRELATION(col2,col3),5,3)  AS cor23
      ,DEC(CORRELATION(col3,col4),5,3)  AS cor34
FROM   temp1;
Figure 233, CORRELATION function examples





Figure 234, COUNT function syntax





SELECT COUNT(*)                     AS c1            ANSWER
      ,COUNT(INT(comm/10))          AS c2            =================
      ,COUNT(ALL INT(comm/10))      AS c3            C1 C2 C3 C4 C5 C6
      ,COUNT(DISTINCT INT(comm/10)) AS c4            -- -- -- -- -- --
      ,COUNT(DISTINCT INT(comm))    AS c5            35 24 24 19 24  2
      ,COUNT(DISTINCT INT(comm))/10 AS c6
FROM   staff;
Figure 235, COUNT function examples





SELECT   'NO GP-BY'  AS c1                                ANSWER
        ,COUNT(*)    AS c2                                ============
FROM     staff                                            C1        C2
WHERE    id = -1                                          --------  --
UNION                                                     NO GP-BY   0
SELECT   'GROUP-BY'  AS c1
        ,COUNT(*)    AS c2
FROM     staff
WHERE    id = -1
GROUP BY dept;
Figure 236, COUNT function with and without GROUP BY





Figure 237, COUNT_BIG function syntax





SELECT   COUNT_BIG(*)                AS c1         ANSWER
        ,COUNT_BIG(dept)             AS c2         ===================
        ,COUNT_BIG(DISTINCT dept)    AS c3         C1  C2  C3  C4  C5
        ,COUNT_BIG(DISTINCT dept/10) AS c4         --- --- --- --- ---
        ,COUNT_BIG(DISTINCT dept)/10 AS c5         35. 35.  8.  7.  0.
FROM     STAFF;
Figure 238, COUNT_BIG function examples





Figure 239, COVARIANCE function syntax





WITH temp1(c1, c2, c3, c4) AS          ANSWER
(VALUES   (0 , 0 , 0 , RAND(1))        ===============================
 UNION ALL                             COV11   COV12   COV23   COV34
 SELECT c1 + 1                         ------- ------- ------- -------
       ,c2 - 1                          83666. -83666. -1.4689 -0.0004
       ,RAND()
       ,RAND()
 FROM   temp1
 WHERE  c1 <= 1000
)      
SELECT DEC(COVARIANCE(c1,c1),6,0)  AS cov11
      ,DEC(COVARIANCE(c1,c2),6,0)  AS cov12
      ,DEC(COVARIANCE(c2,c3),6,4)  AS cov23
      ,DEC(COVARIANCE(c3,c4),6,4)  AS cov34
FROM   temp1;
Figure 240, COVARIANCE function examples





Figure 241, GROUPING function syntax





SELECT   dept                                         ANSWER
        ,AVG(salary)    AS salary                     ================
        ,GROUPING(dept) AS df                         DEPT SALARY   DF
FROM     staff                                        ---- -------- --
GROUP BY ROLLUP(dept)                                   10 83365.86  0
ORDER BY dept;                                          15 60482.33  0
                                                        20 63571.52  0
                                                        38 60457.11  0
                                                        42 49592.26  0
                                                        51 83218.16  0
                                                        66 73015.24  0
                                                        84 66536.75  0
                                                         - 67932.78  1
Figure 242, GROUPING function example





Figure 243, MAX function syntax





SELECT   MAX(dept)                                     ANSWER
        ,MAX(ALL dept)                                 ===============
        ,MAX(DISTINCT dept)                             1   2   3   4
        ,MAX(DISTINCT dept/10)                         --- --- --- ---
FROM     staff;                                         84  84  84   8
Figure 244, MAX function examples





SELECT MAX(hiredate)                  ANSWER
      ,CHAR(MAX(hiredate),USA)        ================================
      ,MAX(CHAR(hiredate,USA))        1          2          3
FROM   employee;                      ---------- ---------- ----------
                                      2006-12-15 12/15/2006 12/15/2006
Figure 245, MAX function with dates





SELECT MAX(id)         AS id                       ANSWER
      ,MAX(CHAR(id))   AS chr                      ===================
      ,MAX(DIGITS(id)) AS dig                      ID     CHR    DIG
FROM   staff;                                      ------ ------ -----
                                                      350 90     00350
Figure 246, MAX function with numbers, 1 of 2





SELECT MAX(id - 250)         AS id               ANSWER
      ,MAX(CHAR(id - 250))   AS chr              =====================
      ,MAX(DIGITS(id - 250)) AS dig              ID    CHR  DIG
FROM   staff;                                    ----- ---- ----------
                                                   100 90   0000000240
Figure 247, MAX function with numbers, 2 of 2





Figure 248, MIN function syntax





SELECT   MIN(dept)                                     ANSWER
        ,MIN(ALL dept)                                 ===============
        ,MIN(DISTINCT dept)                             1   2   3   4
        ,MIN(DISTINCT dept/10)                         --- --- --- ---
FROM     staff;                                         10  10  10   1
Figure 249, MIN function examples





Figure 250, REGRESSION functions syntax





                                                            ANSWERS
                                                            ==========
SELECT  DEC(REGR_SLOPE(bonus,salary)    ,7,5)  AS r_slope      0.00247
       ,DEC(REGR_INTERCEPT(bonus,salary),7,3)  AS r_icpt       644.862
       ,INT(REGR_COUNT(bonus,salary)        )  AS r_count            5
       ,INT(REGR_AVGX(bonus,salary)         )  AS r_avgx         70850
       ,INT(REGR_AVGY(bonus,salary)         )  AS r_avgy           820
       ,DEC(REGR_SXX(bonus,salary)       ,10)  AS r_sxx     8784575000
       ,INT(REGR_SXY(bonus,salary)          )  AS r_sxy       21715000
       ,INT(REGR_SYY(bonus,salary)          )  AS r_syy         168000
FROM    employee
WHERE   workdept = 'A00';
Figure 251, REGRESSION functions examples





Figure 252, STDDEV function syntax





                                       ANSWER
                                       ===============================
                                       A1 S1            S2   S3   S4
                                       -- ------------- ---- ---- ----
SELECT AVG(dept) AS a1                 41 +2.3522355E+1 23.5 23.5 24.1
      ,STDDEV(dept) AS s1
      ,DEC(STDDEV(dept),3,1) AS s2
      ,DEC(STDDEV(ALL dept),3,1) AS s3
      ,DEC(STDDEV(DISTINCT dept),3,1) AS s4
FROM   staff;
Figure 253, STDDEV function examples





Figure 254, SUM function syntax





SELECT   SUM(dept)          AS s1             ANSWER
        ,SUM(ALL dept)      AS s2             ========================
        ,SUM(DISTINCT dept) AS s3              S1   S2   S3   S4   S5
        ,SUM(dept/10)       AS s4             ---- ---- ---- ---- ----
        ,SUM(dept)/10       AS s5             1459 1459  326  134  145
FROM     staff;
Figure 255, SUM function examples





Figure 256, VARIANCE function syntax





                                        ANSWER
                                        ==============================
                                        A1 V1              V2  V3  V4
                                        -- --------------- --- --- ---
SELECT AVG(dept) AS a1                  41 +5.533012244E+2 553 553 582
      ,VARIANCE(dept) AS s1
      ,DEC(VARIANCE(dept),4,1) AS s2
      ,DEC(VARIANCE(ALL dept),4,1) AS s3
      ,DEC(VARIANCE(DISTINCT dept),4,1) AS s4
FROM   staff;
Figure 257, VARIANCE function examples





SELECT   s1.job, s1.id, s1.salary                    ANSWER
FROM     staff s1                                    =================
WHERE    s1.name LIKE '%s%'                          JOB   ID SALARY
  AND    s1.id      <  90                            ----- -- --------
ORDER BY s1.job                                      Clerk 80 43504.60
        ,s1.id;                                      Mgr   10 98357.50
                                                     Mgr   50 80659.80
Figure 258, Select rows from STAFF table





SELECT   s1.job, s1.id, s1.salary
        ,SUM(salary)  OVER(ORDER BY job, id) AS sumsal
        ,ROW_NUMBER() OVER(ORDER BY job, id) AS r               ANSWER
FROM     staff s1                                               ======
WHERE    s1.name LIKE '%s%'              JOB   ID SALARY   SUMSAL    R
  AND    s1.id      <  90                ----- -- -------- --------- -
ORDER BY s1.job                          Clerk 80 43504.60  43504.60 1
        ,s1.id;                          Mgr   10 98357.50 141862.10 2
                                         Mgr   50 80659.80 222521.90 3
Figure 259, Using OLAP functions to get additional fields





WITH temp1 AS                                                   ANSWER
   (SELECT   *                           =============================
    FROM     staff s1                    JOB   ID SALARY   SUMSAL    R
    WHERE    s1.name LIKE '%s%'          ----- -- -------- --------- -
      AND    s1.id      <  90            Clerk 80 43504.60  43504.60 1
   )                                     Mgr   10 98357.50 141862.10 2
SELECT   s1.job, s1.id, s1.salary        Mgr   50 80659.80 222521.90 3
        ,(SELECT   SUM(s2.salary)
          FROM     temp1 s2
          WHERE   (s2.job < s1.job)
             OR   (s2.job = s1.job AND s2.id <= s1.id)) AS sumsal
        ,(SELECT   COUNT(*)
          FROM     temp1 s2
          WHERE   (s2.job < s1.job)
             OR   (s2.job = s1.job AND s2.id <= s1.id)) AS r
FROM     temp1 s1
ORDER BY s1.job
        ,s1.id;
Figure 260, Running counts without OLAP functions





Figure 261, Sample OLAP query





SELECT   dept ,id
        ,salary
        ,DEC(AVG(salary) OVER()                          ,8,2) AS avg1
        ,DEC(AVG(salary) OVER(PARTITION BY dept)         ,8,2) AS avg2
        ,DEC(AVG(salary) OVER(PARTITION BY dept
                              ORDER BY id
                              ROWS UNBOUNDED PRECEDING)  ,8,2) AS avg3
        ,DEC(AVG(salary) OVER(PARTITION BY dept
                              ORDER BY id
                              ROWS BETWEEN 1 PRECEDING
                                       AND 2 FOLLOWING)  ,8,2) AS avg4
FROM     staff
WHERE    dept  IN  (15,20)
  AND    id     >   20
ORDER BY dept ,id;
                                                                ANSWER
                 =====================================================
                 DEPT  ID   SALARY     AVG1     AVG2     AVG3     AVG4
                 ---- --- -------- -------- -------- -------- --------
                   15  50 80659.80 53281.11 60482.33 80659.80 66556.94
                   15  70 76502.83 53281.11 60482.33 78581.31 60482.33
                   15 110 42508.20 53281.11 60482.33 66556.94 53756.51
                   15 170 42258.50 53281.11 60482.33 60482.33 42383.35
                   20  80 43504.60 53281.11 38878.67 43504.60 38878.67
                   20 190 34252.75 53281.11 38878.67 38878.67 38878.67
Figure 262, Sample OLAP query





Figure 263, PARTITION BY syntax





SELECT   id  ,dept ,job  ,years  ,salary
        ,DEC(AVG(salary) OVER(PARTITION BY dept)      ,7,2) AS dpt_avg
        ,DEC(AVG(salary) OVER(PARTITION BY job)       ,7,2) AS job_avg
        ,DEC(AVG(salary) OVER(PARTITION BY years/2)   ,7,2) AS yr2_avg
        ,DEC(AVG(salary) OVER(PARTITION BY dept, job) ,7,2) AS d_j_avg
FROM     staff
WHERE    dept  IN  (15,20)
  AND    id     >   20
ORDER BY id;
                                                                ANSWER
     =================================================================
     ID  DEPT JOB   YEARS   SALARY  DPT_AVG  JOB_AVG  YR2_AVG  D_J_AVG
     --- ---- ----- ----- -------- -------- -------- -------- --------
      50   15 Mgr      10 80659.80 60482.33 80659.80 80659.80 80659.80
      70   15 Sales     7 76502.83 60482.33 76502.83 76502.83 76502.83
      80   20 Clerk     - 43504.60 38878.67 40631.01 43504.60 38878.67
     110   15 Clerk     5 42508.20 60482.33 40631.01 42383.35 42383.35
     170   15 Clerk     4 42258.50 60482.33 40631.01 42383.35 42383.35
     190   20 Clerk     8 34252.75 38878.67 40631.01 34252.75 38878.67
Figure 264, PARTITION BY examples





SELECT   dept                                         ANSWER
        ,SUM(years) AS sum                            ================
        ,AVG(years) AS avg                            DEPT SUM AVG ROW
        ,COUNT(*)   AS row                            ---- --- --- ---
FROM     staff                                          15  22   7   3
WHERE    id BETWEEN 40 AND 120                          38   6   6   1
  AND    years IS NOT NULL                              42  13   6   2
GROUP BY dept;
Figure 265, Sample query using GROUP BY





SELECT   dept                                        ANSWER
        ,SUM(years) OVER(PARTITION BY dept) AS sum   =================
        ,AVG(years) OVER(PARTITION BY dept) AS avg   DEPT  SUM AVG ROW
        ,COUNT(*)   OVER(PARTITION BY dept) AS row   ----- --- --- ---
FROM     staff                                          15  22   7   3
WHERE    id BETWEEN 40 AND 120                          15  22   7   3
  AND    years IS NOT NULL                              15  22   7   3
ORDER BY dept;                                          38   6   6   1
                                                        42  13   6   2
                                                        42  13   6   2
Figure 266, Sample query using PARTITION





SELECT   DISTINCT                                               ANSWER
         dept                                        =================
        ,SUM(years) OVER(PARTITION BY dept) AS sum   DEPT  SUM AVG ROW
        ,AVG(years) OVER(PARTITION BY dept) AS avg   ----- --- --- ---
        ,COUNT(*)   OVER(PARTITION BY dept) AS row      15  22   7   3
FROM     staff                                          38   6   6   1
WHERE    id BETWEEN 40 AND 120                          42  13   6   2
  AND    years IS NOT NULL
ORDER BY dept;
Figure 267, Sample query using PARTITION and DISTINCT





Figure 268, Moving window definition syntax





SELECT   id  ,salary
        ,DEC(AVG(salary) OVER()                       ,7,2) AS avg_all
        ,DEC(AVG(salary) OVER(ORDER BY id)            ,7,2) AS avg_odr
        ,DEC(AVG(salary) OVER(ORDER BY id
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND UNBOUNDED FOLLOWING)    ,7,2) AS avg_p_f
        ,DEC(AVG(salary) OVER(ORDER BY id
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW)            ,7,2) AS avg_p_c
        ,DEC(AVG(salary) OVER(ORDER BY id
                 ROWS BETWEEN CURRENT ROW
                          AND UNBOUNDED FOLLOWING)    ,7,2) AS avg_c_f
        ,DEC(AVG(salary) OVER(ORDER BY id
                 ROWS BETWEEN 2 PRECEDING
                          AND 1 FOLLOWING)            ,7,2) AS avg_2_1
FROM     staff
WHERE    dept  IN  (15,20)
  AND    id     >   20
ORDER BY id;
                                                                ANSWER
    ==================================================================
    ID    SALARY  AVG_ALL  AVG_ODR  AVG_P_F  AVG_P_C  AVG_C_F  AVG_2_1
    --- -------- -------- -------- -------- -------- -------- --------
     50 80659.80 53281.11 80659.80 53281.11 80659.80 53281.11 78581.31
     70 76502.83 53281.11 78581.31 53281.11 78581.31 47805.37 66889.07
     80 43504.60 53281.11 66889.07 53281.11 66889.07 40631.01 60793.85
    110 42508.20 53281.11 60793.85 53281.11 60793.85 39673.15 51193.53
    170 42258.50 53281.11 57086.78 53281.11 57086.78 38255.62 40631.01
    190 34252.75 53281.11 53281.11 53281.11 53281.11 34252.75 39673.15
Figure 269, Different window sizes





SELECT   id
        ,SUM(id) OVER(ORDER BY id)                             AS sum1
        ,SUM(id) OVER(ORDER BY id  ROWS         1 PRECEDING)   AS sum2
        ,SUM(id) OVER(ORDER BY id  ROWS UNBOUNDED PRECEDING)   AS sum3
        ,SUM(id) OVER(ORDER BY id  ROWS CURRENT ROW)           AS sum4
        ,SUM(id) OVER(ORDER BY id  ROWS         2 FOLLOWING)   AS sum5
        ,SUM(id) OVER(ORDER BY id  ROWS UNBOUNDED FOLLOWING)   AS sum6
FROM     staff
WHERE    id < 40                                                ANSWER
ORDER BY id;                          ================================
                                      ID SUM1 SUM2 SUM3 SUM4 SUM5 SUM6
                                      -- ---- ---- ---- ---- ---- ----
                                      10   10   10   10   10   60   60
                                      20   30   30   30   20   50   50
                                      30   60   50   60   30   30   30
Figure 270, Different window sizes





SELECT   id
        ,SMALLINT(SUM(id)  OVER(ORDER BY id
             RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING))     AS rng1
        ,SMALLINT(SUM(id)  OVER(ORDER BY id
             ROWS  BETWEEN  1 PRECEDING AND  1 FOLLOWING))     AS row1
        ,SMALLINT(SUM(id)  OVER(ORDER BY id
             RANGE BETWEEN 10 PRECEDING AND CURRENT ROW))      AS rng2
        ,SMALLINT(SUM(id)  OVER(ORDER BY id
             ROWS  BETWEEN  3 PRECEDING AND 1 PRECEDING))      AS row2
        ,SMALLINT(SUM(id)  OVER(ORDER BY id DESC
             ROWS  BETWEEN  3 PRECEDING AND 1 PRECEDING))      AS row3
        ,SMALLINT(SUM(id)  OVER(ORDER BY id
             RANGE  BETWEEN   UNBOUNDED PRECEDING
                        AND   20 FOLLOWING))                   AS rng3
FROM     staff
WHERE    id < 60
ORDER BY id;                                                    ANSWER
                                      ================================
                                      ID RNG1 ROW1 RNG2 ROW2 ROW3 RNG3
                                      -- ---- ---- ---- ---- ---- ----
                                      10   30   30   10    -   90   60
                                      20   60   60   30   10  120  100
                                      30   90   90   50   30   90  150
                                      40  120  120   70   60   50  150
                                      50   90   90   90   90    -  150
Figure 271, ROW vs. RANGE example





Figure 272, ORDER BY syntax





SELECT   dept ,name  ,salary
        ,DEC(SUM(salary)  OVER(ORDER BY dept)            ,8,2) AS sum1
        ,DEC(SUM(salary)  OVER(ORDER BY dept DESC)       ,8,2) AS sum2
        ,DEC(SUM(salary)  OVER(ORDER BY ORDER OF s1)     ,8,2) AS sum3
        ,SMALLINT(RANK()  OVER(ORDER BY salary, name, dept)  ) AS r1
        ,SMALLINT(RANK()  OVER(ORDER BY ORDER OF s1)         ) AS r2
        ,ROW_NUMBER()     OVER(ORDER BY salary)                AS w1
        ,COUNT(*)         OVER(ORDER BY salary)                AS w2
FROM    (SELECT   *
         FROM     staff
         WHERE    id < 60
         ORDER BY dept
                 ,name
        )AS s1
ORDER BY 1, 2;
       
                                                                ANSWER
      ================================================================
      DEPT NAME       SALARY      SUM1      SUM2      SUM3 R1 R2 W1 W2
      ---- -------- -------- --------- --------- --------- -- -- -- --
        15 Hanes    80659.80  80659.80 412701.30  80659.80  4  1  4  4
        20 Pernal   78171.25 257188.55 332041.50 158831.05  3  2  3  3
        20 Sanders  98357.50 257188.55 332041.50 257188.55  5  3  5  5
        38 Marenghi 77506.75 412701.30 155512.75 334695.30  1  4  1  1
        38 O'Brien  78006.00 412701.30 155512.75 412701.30  2  5  2  2
Figure 273, ORDER BY example





SELECT   id
        ,years                                               AS yr
        ,salary
        ,DENSE_RANK()  OVER(ORDER BY years ASC)              AS a
        ,DENSE_RANK()  OVER(ORDER BY years ASC  NULLS FIRST) AS af
        ,DENSE_RANK()  OVER(ORDER BY years ASC  NULLS LAST ) AS al
        ,DENSE_RANK()  OVER(ORDER BY years DESC)             AS d
        ,DENSE_RANK()  OVER(ORDER BY years DESC NULLS FIRST) AS df
        ,DENSE_RANK()  OVER(ORDER BY years DESC NULLS LAST ) AS dl
FROM     staff
WHERE    id     < 100
ORDER BY years
        ,salary;                                                ANSWER
                                    ==================================
                                    ID YR SALARY    A  AF AL  D  DF DL
                                    -- -- --------  -- -- --  -- -- --
                                    30  5 77506.75   1  2  1   6  6  5
                                    90  6 38001.75   2  3  2   5  5  4
                                    40  6 78006.00   2  3  2   5  5  4
                                    70  7 76502.83   3  4  3   4  4  3
                                    10  7 98357.50   3  4  3   4  4  3
                                    20  8 78171.25   4  5  4   3  3  2
                                    50 10 80659.80   5  6  5   2  2  1
                                    80  - 43504.60   6  1  6   1  1  6
                                    60  - 66808.30   6  1  6   1  1  6
Figure 274, Overriding the default null ordering sequence





SELECT   COUNT(DISTINCT years) AS y#1
        ,MAX(y#)               AS y#2
FROM    (SELECT   years
                 ,DENSE_RANK()  OVER(ORDER BY years) AS y#
         FROM     staff
         WHERE    id     < 100
        )AS xxx                                                ANSWER
ORDER BY 1;                                                    =======
                                                               Y#1 Y#2
                                                               --- ---
                                                                 5   6
Figure 275, Counting distinct values - comparison





Figure 276, Ranking functions syntax





SELECT   id
        ,years
        ,salary
        ,RANK()       OVER(ORDER BY years) AS rank#
        ,DENSE_RANK() OVER(ORDER BY years) AS dense#
        ,ROW_NUMBER() OVER(ORDER BY years) AS row#
FROM     staff
WHERE    id     < 100                                           ANSWER
  AND    years  < 10               ===================================
ORDER BY years;                    ID YEARS SALARY   RANK# DENSE# ROW#
                                   -- ----- -------- ----- ------ ----
                                   30     5 77506.75     1      1    1
                                   40     6 78006.00     2      2    2
                                   90     6 38001.75     2      2    3
                                   10     7 98357.50     4      3    4
                                   70     7 76502.83     4      3    5
                                   20     8 78171.25     6      4    6
Figure 277, Ranking functions example





SELECT   job                                                 AS job
        ,years                                               AS yr
        ,id                                                  AS id
        ,name                                                AS name
        ,RANK() OVER(ORDER BY job ASC                      ) AS a1
        ,RANK() OVER(ORDER BY job ASC,  years ASC          ) AS a2
        ,RANK() OVER(ORDER BY job ASC,  years ASC  ,id ASC ) AS a3
        ,RANK() OVER(ORDER BY job DESC                     ) AS d1
        ,RANK() OVER(ORDER BY job DESC, years DESC         ) AS d2
        ,RANK() OVER(ORDER BY job DESC, years DESC, id DESC) AS d3
        ,RANK() OVER(ORDER BY job ASC,  years DESC, id ASC ) AS m1
        ,RANK() OVER(ORDER BY job DESC, years ASC,  id DESC) AS m2
FROM     staff
WHERE    id      <  150
  AND    years  IN (6,7)                                        ANSWER
  AND    job     >  'L'    ===========================================
ORDER BY job               JOB   YR ID  NAME    A1 A2 A3 D1 2 D3 M1 M2
        ,years             ----- -- --- ------- -- -- -- -- - -- -- --
        ,id;               Mgr    6 140 Fraye    1  1  1  4 6  6  3  4
                           Mgr    7  10 Sanders  1  2  2  4 4  5  1  6
                           Mgr    7 100 Plotz    1  2  3  4 4  4  2  5
                           Sales  6  40 O'Brien  4  4  4  1 2  3  5  2
                           Sales  6  90 Koonitz  4  4  5  1 2  2  6  1
                           Sales  7  70 Rothman  4  6  6  1 1  1  4  3
Figure 278, ORDER BY usage





SELECT   id                                          ANSWER
        ,years  AS yr                                =================
        ,salary                                      ID YR SALARY   R1
        ,RANK() OVER(PARTITION BY years              -- -- -------- --
                     ORDER     BY salary) AS r1      30  5 77506.75  1
FROM     staff                                       40  6 78006.00  1
WHERE    id     < 80                                 70  7 76502.83  1
  AND    years IS NOT NULL                           10  7 98357.50  2
ORDER BY years                                       20  8 78171.25  1
        ,salary;                                     50  0 80659.80  1
Figure 279, Values ranked by subset of rows





SELECT   id
        ,years
        ,salary
        ,SMALLINT(RANK() OVER(ORDER BY years ASC))  AS rank_a
        ,SMALLINT(RANK() OVER(ORDER BY years DESC)) AS rank_d
        ,SMALLINT(RANK() OVER(ORDER BY id, years))  AS rank_iy
FROM     STAFF
WHERE    id     < 100
  AND    years IS NOT NULL
ORDER BY years;
Figure 280, Multiple rankings in same query





SELECT   id
        ,years
        ,name
        ,salary
        ,SMALLINT(RANK() OVER(ORDER BY SUBSTR(name,3,2))) AS dumb1
        ,SMALLINT(RANK() OVER(ORDER BY salary / 1000))    AS dumb2
        ,SMALLINT(RANK() OVER(ORDER BY years * ID))       AS dumb3
        ,SMALLINT(RANK() OVER(ORDER BY 1))                AS dumb4
FROM     staff
WHERE    id     < 40
  AND    years IS NOT NULL
ORDER BY 1;
Figure 281, Dumb rankings, SQL





ID  YEARS  NAME      SALARY    DUMB1  DUMB2  DUMB3  DUMB4
--  -----  --------  --------  -----  -----  -----  -----
10      7  Sanders   98357.50      1      3      1      1
20      8  Pernal    78171.25      3      2      3      1
30      5  Marenghi  77506.75      2      1      2      1
Figure 282, Dumb ranking, Answer





SELECT   xxx.*                                        ANSWER
        ,RANK()OVER(ORDER BY id) AS r2                ================
FROM    (SELECT   id                                  ID NAME    R1 R2
                 ,name                                -- ------- -- --
                 ,RANK() OVER(ORDER BY id) AS r1      40 O'Brien  4  1
         FROM     staff                               50 Hanes    5  2
         WHERE    id     < 100                        70 Rothman  6  3
           AND    years IS NOT NULL                   90 Koonitz  7  4
        )AS xxx
WHERE    id > 30
ORDER BY id;
Figure 283, Subsequent processing of ranked data





SELECT   id                                          ANSWER
        ,RANK() OVER(PARTITION BY dept               =================
                     ORDER BY salary DESC) AS r1     ID R1 SALARY   DP
        ,salary                                      -- -- -------- --
        ,dept AS dp                                  10  1 98357.50 20
FROM     staff                                       50  1 80659.80 15
WHERE    id     < 80                                 40  1 78006.00 38
  AND    years IS NOT NULL                           20  2 78171.25 20
ORDER BY r1     ASC                                  30  2 77506.75 38
        ,salary DESC;                                70  2 76502.83 15
Figure 284, Ordering rows by rank, using RANK function





SELECT   id                                          ANSWER
        ,(SELECT COUNT(*)                            =================
          FROM   staff s2                            ID R1 SALARY   DP
          WHERE  s2.id        < 80                   -- -- -------- --
            AND  S2.YEARS IS NOT NULL                10  1 98357.50 20
            AND  s2.dept      = s1.dept              50  1 80659.80 15
            AND  s2.salary   >= s1.salary) AS R1     40  1 78006.00 38
        ,SALARY                                      20  2 78171.25 20
        ,dept AS dp                                  30  2 77506.75 38
FROM     staff s1                                    70  2 76502.83 15
WHERE    id     < 80
  AND    years IS NOT NULL
ORDER BY r1     ASC
        ,salary DESC;
Figure 285, Ordering rows by rank, using sub-query





SELECT   id                                             ANSWER
        ,salary                                         ==============
        ,dept AS dp                                     ID SALARY   DP
FROM    (SELECT   s1.*                                  -- -------- --
                 ,RANK() OVER(PARTITION BY dept         50 80659.80 15
                         ORDER BY salary DESC) AS r1    10 98357.50 20
         FROM     staff s1                              40 78006.00 38
         WHERE    id     < 80
           AND    years IS NOT NULL
        )AS xxx
WHERE    r1 = 1
ORDER BY dp;
Figure 286, Get highest salary in each department, use RANK function





SELECT   id                                             ANSWER
        ,salary                                         ==============
        ,dept AS dp                                     ID SALARY   DP
FROM     staff s1                                       -- -------- --
WHERE    id     < 80                                    50 80659.80 15
  AND    years IS NOT NULL                              10 98357.50 20
  AND    NOT EXISTS                                     40 78006.00 38
        (SELECT *
         FROM   staff s2
         WHERE  s2.id         < 80
           AND  s2.years IS NOT NULL
           AND  s2.dept      = s1.dept
           AND  s2.salary    > s1.salary)
ORDER BY DP;
Figure 287, Get highest salary in each department, use correlated sub-query





SELECT   id                                             ANSWER
        ,salary                                         ==============
        ,dept AS dp                                     ID SALARY   DP
FROM     staff                                          -- -------- --
WHERE    id     < 80                                    50 80659.80 15
  AND    years IS NOT NULL                              10 98357.50 20
  AND   (dept, salary) IN                               40 78006.00 38
        (SELECT   dept, MAX(salary)
         FROM     staff
         WHERE    id         < 80
           AND    years IS NOT NULL
         GROUP BY dept)
ORDER BY dp;
Figure 288, Get highest salary in each department, use uncorrelated sub-query





Figure 289, Numbering function syntax





SELECT   id                                          ANSWER
        ,name                                        =================
        ,ROW_NUMBER() OVER()            AS r1        ID NAME     R1 R2
        ,ROW_NUMBER() OVER(ORDER BY id) AS r2        -- -------- -- --
FROM     staff                                       10 Sanders   1  1
WHERE    id     < 50                                 20 Pernal    2  2
  AND    years IS NOT NULL                           30 Marenghi  3  3
ORDER BY id;                                         40 O'Brien   4  4
Figure 290, ORDER BY example, 1 of 3





SELECT   id                                          ANSWER
        ,name                                        =================
        ,ROW_NUMBER() OVER()              AS r1      ID NAME     R1 R2
        ,ROW_NUMBER() OVER(ORDER BY name) AS r2      -- -------- -- --
FROM     staff                                       10 Sanders   4  4
WHERE    id     < 50                                 20 Pernal    3  3
  AND    years IS NOT NULL                           30 Marenghi  1  1
ORDER BY id;                                         40 O'Brien   2  2
Figure 291, ORDER BY example, 2 of 3





SELECT   id                                       ANSWER
        ,name                                     ====================
        ,ROW_NUMBER() OVER()              AS r1   ID NAME     R1 R2 R3
        ,ROW_NUMBER() OVER(ORDER BY ID)   AS r2   -- -------- -- -- --
        ,ROW_NUMBER() OVER(ORDER BY NAME) AS r3   10 Sanders   1  1  4
FROM     staff                                    20 Pernal    2  2  3
WHERE    id     < 50                              30 Marenghi  3  3  1
  AND    years IS NOT NULL                        40 O'Brien   4  4  2
ORDER BY id;
Figure 292, ORDER BY example, 3 of 3





SELECT   job
        ,years
        ,id
        ,name
        ,ROW_NUMBER() OVER(PARTITION BY job  ORDER BY years) AS row#
        ,RANK()       OVER(PARTITION BY job  ORDER BY years) AS rn1#
        ,DENSE_RANK() OVER(PARTITION BY job  ORDER BY years) AS rn2#
FROM     staff
WHERE    id      <  150                                         ANSWER
  AND    years  IN (6,7)        ======================================
  AND    job     >  'L'         JOB   YEARS ID  NAME    ROW# RN1# RN2#
ORDER BY job                    ----- ----- --- ------- ---- ---- ----
        ,years;                 Mgr       6 140 Fraye      1    1    1
                                Mgr       7  10 Sanders    2    2    2
                                Mgr       7 100 Plotz      3    2    2
                                Sales     6  40 O'Brien    1    1    1
                                Sales     6  90 Koonitz    2    1    1
                                Sales     7  70 Rothman    3    3    2
Figure 293, Use of PARTITION phrase





SELECT   *                                               ANSWER
FROM    (SELECT   id                                     =============
                 ,name                                   ID NAME     R
                 ,ROW_NUMBER() OVER(ORDER BY id) AS r    -- -------- -
         FROM     staff                                  10 Sanders  1
         WHERE    id     < 100                           20 Pernal   2
           AND    years IS NOT NULL                      30 Marenghi 3
        )AS xxx
WHERE    r <= 3
ORDER BY id;
Figure 294, Select first 3 rows, using ROW_NUMBER function





SELECT   id                                              ANSWER
        ,name                                            =============
        ,ROW_NUMBER() OVER(ORDER BY id) AS r             ID NAME     R
FROM     staff                                           -- -------- -
WHERE    id     < 100                                    10 Sanders  1
  AND    years IS NOT NULL                               20 Pernal   2
ORDER BY id                                              30 Marenghi 3
FETCH FIRST 3 ROWS ONLY;
Figure 295, Select first 3 rows, using FETCH FIRST notation





SELECT   *                                               ANSWER
FROM    (SELECT   id                                     =============
                 ,name                                   ID NAME     R
                 ,ROW_NUMBER() OVER(ORDER BY id) AS r    -- -------- -
         FROM     staff                                  30 Marenghi 3
         WHERE    id         < 200                       40 O'Brien  4
           AND    years IS NOT NULL                      50 Hanes    5
        )AS xxx                                          70 Rothman  6
WHERE    r BETWEEN 3 AND 6
ORDER BY id;
Figure 296, Select 3rd through 6th rows





SELECT   *                                              ANSWER
FROM    (SELECT   id                                    ==============
                 ,name                                  ID  NAME    R
                 ,ROW_NUMBER() OVER(ORDER BY id) AS r   --- ------- --
         FROM     staff                                  10 Sanders  1
         WHERE    id         < 200                       70 Rothman  6
           AND    years IS NOT NULL                     140 Fraye   11
        )AS xxx                                         190 Sneider 16
WHERE    (r - 1) = ((r - 1) / 5) * 5
ORDER BY id;
Figure 297, Select every 5th matching row





SELECT   *
FROM    (SELECT   id
                 ,name
                 ,ROW_NUMBER() OVER(ORDER BY id DESC) AS r
         FROM     staff                                         ANSWER
         WHERE    id         < 200                      ==============
           AND    years IS NOT NULL                     ID  NAME     R
        )AS xxx                                         --- -------- -
WHERE    r <= 2                                         180 Abrahams 2
ORDER BY id;                                            190 Sneider  1
Figure 298, Select last two rows





SELECT   *
FROM    (SELECT  years
                ,id
                ,name
                ,RANK()       OVER(ORDER BY years)     AS rnk
                ,ROW_NUMBER() OVER(ORDER BY years, id) AS row
         FROM    staff
         WHERE   id         < 200                               ANSWER
           AND   years IS NOT NULL          ==========================
        )AS xxx                             YEARS ID  NAME     RNK ROW
WHERE    rnk  <= 3                          ----- --- -------- --- ---
ORDER BY years                                  3 180 Abrahams   1   1
        ,id;                                    4 170 Kermisch   2   2
                                                5  30 Marenghi   3