Author: Graeme Birchall ©
Email: Graeme_Birchall@compuserve.com
Web: http://ourworld.compuserve.com/homepages/Graeme_Birchall
Title: DB2 UDB V8.2 SQL Cookbook ©
Date: 1-Jun-2005
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 #EMP
,COUNT(*) AS #emps ----- ---- -------- ----
FROM staff Clerk 15 24766.70 2
WHERE dept < 30 Clerk 20 27757.35 2
AND salary < 20000 Clerk - 52524.05 4
AND job < 'S' Mgr 10 19260.25 1
GROUP BY ROLLUP(job, dept) Mgr 20 18357.50 1
ORDER BY job Mgr - 37617.75 2
,dept; - - 90141.80 6
Figure 13, Subtotal and Grand-total example
Figure 14, Syntax Diagram Conventions
--#SET DELIMITER !
SELECT name FROM staff WHERE id = 10!
--#SET DELIMITER ;
SELECT name FROM staff WHERE id = 20;
Figure 15, 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 16, 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 17, 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 18, 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 19, 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 20, 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 21, Define three aliases, the latter on the earlier
CREATE NICKNAME emp FOR unixserver.production.employee;
Figure 22, Define a nickname
SELECT *
FROM staff TABLESAMPLE BERNOULLI(10);
Figure 23, 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 24, Sample table definition
CREATE TABLE default_values
(c1 CHAR NOT NULL
,d1 DECIMAL NOT NULL);
Figure 25, Table with default column lengths
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 26, 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 27, 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 28, 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 29, 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 30, 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 31, 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 DEFAULT TRANSFORM GROUP yes VARCHAR(18)
CURRENT DEGREE yes CHAR(5)
CURRENT EXPLAIN MODE yes VARCHAR(254)
CURRENT EXPLAIN SNAPSHOT yes CHAR(8)
CURRENT ISOLATION yes CHAR(2)
CURRENT LOCK TIMEOUT yes INTEGER
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION yes VARCHAR(254)
CURRENT PACKAGE PATH yes VARCHAR(4096)
CURRENT PATH yes VARCHAR(254)
CURRENT QUERY OPTIMIZATION yes INTEGER
CURRENT REFRESH AGE yes DECIMAL(20,6)
CURRENT SCHEMA yes VARCHAR(128)
CURRENT SERVER no VARCHAR(18)
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 32, DB2 Special Registers
Figure 33, Create Distinct Type Syntax
CREATE DISTINCT TYPE JAP_YEN AS DECIMAL(15,2) WITH COMPARISONS;
DROP DISTINCT TYPE JAP_YEN;
Figure 34, 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 35, Sample table, without distinct types
SELECT id
,usa_sales + eur_sales AS tot_sales
FROM customer;
Figure 36, 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 37, 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 38, Sample table, with distinct types
SELECT id
,usa_sales + eur_sales AS tot_sales
FROM customer;
Figure 39, Silly query, now fails
SELECT id
,DECIMAL(usa_sales) +
DECIMAL(eur_sales) AS tot_sales
FROM customer;
Figure 40, Silly query, works again
Figure 41, SELECT Statement Syntax (general)
Figure 42, 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 43, Sample SELECT statement
SELECT * ANSWER (part of)
FROM department ================
WHERE deptname LIKE '%ING%' DEPTNO etc...
ORDER BY 1; ------ ------>>>
B01 PLANNING
D11 MANUFACTU
Figure 44, 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 45, 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 46, Select all columns twice
Figure 47, 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 48, 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 49, 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 50, 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 51, 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 52, 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 53, 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 54, 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 55, AVG of data containing null values
SELECT COUNT(*) AS num ANSWER
,MAX(lastname) AS max ========
FROM employee NUM MAX
WHERE firstnme = 'FRED'; --- ---
0 -
Figure 56, 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 57, 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 58, 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 59, Double-quote usage
Figure 60, 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 61, Basic Predicate examples
Figure 62, 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 63, 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 64, Same query as prior, using individual predicates
Figure 65, 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 66, 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 67, Quantified Predicate example, multi-value sub-query
Figure 68, 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 69, BETWEEN Predicate examples
Figure 70, 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 71, EXISTS Predicate example
Figure 72, 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 73, 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 74, IN Predicate example, multi-value
Figure 75, 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 76, 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 77, 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 78, LIKE and ESCAPE examples
Figure 79, 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 80, NULL predicate examples
SELECT id
,name
FROM staff
WHERE name LIKE '%a' || X'3B' || '%'
ORDER BY id;
Figure 81, Refer to semi-colon in SQL text
Example: 555 + -22 / (12 - 3) * 66 ANSWER
======
^ ^ ^ ^ ^ 423
5th 2nd 3rd 1st 4th
Figure 82, 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 83, 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 84, 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 85, Use of OR and parenthesis
Figure 86, CAST expression syntax
SELECT id ANSWER
,salary =================
,CAST(salary AS INTEGER) AS sal2 ID SALARY SAL2
FROM staff -- -------- -----
WHERE id < 30 10 18357.50 18357
ORDER BY id; 20 18171.25 18171
Figure 87, 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 88, 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 89, 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 SMALLINT) 20 000020
AND emp.job = 'MANAGER' 30 000030
WHERE stf.id < 60 40 -
ORDER BY stf.id; 50 000050
Figure 90, CAST expression in join
SELECT stf.id ANSWER
,emp.empno =========
FROM staff stf ID EMPNO
LEFT OUTER JOIN -- ------
employee emp 10 -
ON stf.id = SMALLINT(emp.empno) 20 000020
AND emp.job = 'MANAGER' 30 000030
WHERE stf.id < 60 40 -
ORDER BY stf.id; 50 000050
Figure 91, Function usage in join
Figure 92, 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 93, VALUES usage examples
WITH temp1 (col1, col2) AS ANSWER
(VALUES ( 0, 'AA') =========
,( 1, 'BB') COL1 COL2
,( 2, NULL) ---- ----
) 0 AA
SELECT * 1 BB
FROM temp1; 2 -
Figure 94, 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 95, 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 96, 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 97, 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 98, 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 99, 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 100, 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 101, Generate table with unnamed columns
Figure 102, CASE expression syntax
SELECT Lastname ANSWER
,sex AS sx ====================
,CASE sex LASTNAME SX SEXX
WHEN 'F' THEN 'FEMALE' ---------- -- ------
WHEN 'M' THEN 'MALE' JEFFERSON M MALE
ELSE NULL JOHNSON F FEMALE
END AS sexx JONES M MALE
FROM employee
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 103, Use CASE (type 1) to expand a value
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 JOHNSON F FEMALE
END AS sexx JONES M MALE
FROM employee
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 104, Use CASE (type 2) 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 JOHNSON P F P
ELSE sex JONES T M T
END AS mx
FROM employee
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 105, 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%'; 3 1 2
Figure 106, Use CASE to get multiple counts in one pass
SELECT lastname ANSWER
,sex ==============
FROM employee LASTNAME SEX
WHERE lastname LIKE 'J%' ---------- ---
AND CASE sex JEFFERSON M
WHEN 'F' THEN '' JOHNSON F
WHEN 'M' THEN '' JONES M
ELSE NULL
END IS NOT NULL
ORDER BY 1;
Figure 107, Use CASE in a predicate
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 JOHNSON 7 JOHNSO
ELSE LENGTH(RTRIM(lastname)) JONES 5 JONES
END ) AS lastnm
FROM employee
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 108, 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 109, 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 110, Use CASE to avoid divide by zero
SELECT name ANSWER
,CASE ============
WHEN name = LCASE(name) THEN NULL NAME DUMB
ELSE CAST(NULL AS CHAR(1)) ------- ----
END AS dumb Sanders -
FROM staff Pernal -
WHERE id < 30;
Figure 111, Silly CASE expression that always returns NULL
SELECT lastname ANSWER
,sex =================
,CASE LASTNAME SX SXX
WHEN sex >= 'M' THEN 'MAL' ---------- -- ---
WHEN sex >= 'F' THEN 'FEM' JEFFERSON M MAL
END AS sxx JOHNSON F FEM
FROM employee JONES M MAL
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 112, 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 JOHNSON F FEM
FROM employee JONES M FEM
WHERE lastname LIKE 'J%'
ORDER BY 1;
Figure 113, Use CASE to derive a value (incorrect)
Figure 114, 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 115, 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 116, Use cursor in program
SELECT name
,salary
INTO :name-var
,:salary-var
FROM staff
WHERE id = :id-var
Figure 117, Singleton select
Figure 118, PREPARE statement syntax
STATEMENT CAN BE USED BY STATEMENT TYPE
======================== ==============
DESCRIBE Any statement
DECLARE CURSOR Must be SELECT
EXECUTE Must not be SELECT
Figure 119, What statements can use prepared statement
SET :host-var = CURRENT TIMESTAMP
Figure 120, SET single host-variable
SET :host-v1 = CURRENT TIME
,:host-v2 = CURRENT DEGREE
,:host-v3 = NULL
Figure 121, SET multiple host-variables
SET (:hv1
,:hv2
,:hv3) =
(SELECT id
,name
,salary
FROM staff
WHERE id = :id-var)
Figure 122, 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 123, Other SET statements
Figure 124, SAVEPOINT statement syntax
Figure 125, Example of savepoint usage
Figure 126, RELEASE SAVEPOINT statement syntax
Figure 127, ROLLBACK statement syntax
CREATE TABLE emp_act
(empno CHARACTER (00006) NOT NULL
,projno CHARACTER (00006) NOT NULL
,actno SMALLINT NOT NULL
,emptime DECIMAL (05,02)
,emstdate DATE
,emendate DATE);
Figure 128, EMP_ACT sample table - DDL
Figure 129, INSERT statement syntax
INSERT INTO emp_act VALUES
('100000' ,'ABC' ,10 ,1.4 ,'2003-10-22', '2003-11-24');
Figure 130, Single row insert
INSERT INTO emp_act 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 131, Multi row insert
INSERT INTO emp_act VALUES
('400000' ,'ABC' ,10 ,NULL ,DEFAULT, CURRENT DATE);
Figure 132,Using null and default values
INSERT INTO emp_act (projno, emendate, actno, empno) VALUES
('ABC' ,DATE(CURRENT TIMESTAMP) ,123 ,'500000');
Figure 133, Explicitly listing columns being populated during insert
INSERT INTO
(SELECT *
FROM emp_act
WHERE empno < '1'
)
VALUES ('510000' ,'ABC' ,10 ,1.4 ,'2003-10-22', '2003-11-24');
Figure 134, Insert into a full-select
INSERT INTO emp_act
SELECT LTRIM(CHAR(id + 600000))
,SUBSTR(UCASE(name),1,6)
,salary / 229
,123
,CURRENT DATE
,'2003-11-11'
FROM staff
WHERE id < 50;
Figure 135,Insert result of select statement
INSERT INTO emp_act (empno, actno, projno)
SELECT LTRIM(CHAR(id + 700000))
,MINUTE(CURRENT TIME)
,'DEF'
FROM staff
WHERE id < 40;
Figure 136, Insert result of select - specified columns only
INSERT INTO emp_act
SELECT *
FROM emp_act;
Figure 137, Stupid - insert - doubles rows
INSERT INTO emp_act (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 138, Inserting result of union
INSERT INTO emp_act (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 139, Insert from common table expression
INSERT INTO emp_act (empno, actno, projno)
SELECT LTRIM(CHAR(id + 920000))
,id
,'ABC'
FROM staff
WHERE id < 40
AND NOT EXISTS
(SELECT *
FROM emp_act
WHERE empno LIKE '92%');
Figure 140, 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 141, 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 142, Insert into multiple tables
UPDATE emp_act
SET emptime = NULL
,emendate = DEFAULT
,emstdate = CURRENT DATE + 2 DAYS
,actno = ACTNO / 2
,projno = 'ABC'
WHERE empno = '100000';
Figure 143, Single row update
Figure 144, UPDATE statement syntax
UPDATE emp_act
SET actno = actno / 2;
Figure 145, Mass update
UPDATE emp_act ac1
SET actno = actno * 2
,emptime = actno * 2
WHERE empno LIKE '910%';
Figure 146, Two columns get same value
UPDATE emp_act
SET actno = (SELECT MAX(salary)
FROM staff)
WHERE empno = '200000';
Figure 147, Update using select
UPDATE emp_act
SET (actno
,emstdate
,projno) = (SELECT MAX(salary)
,CURRENT DATE + 2 DAYS
,MIN(CHAR(id))
FROM staff
WHERE id <> 33)
WHERE empno LIKE '600%';
Figure 148, Multi-row update using select
UPDATE emp_act ac1
SET (actno
,emptime) = (SELECT ac2.actno + 1
,ac1.emptime / 2
FROM emp_act ac2
WHERE ac2.empno LIKE '60%'
AND SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE EMPNO LIKE '700%';
Figure 149, Multi-row update using correlated select
UPDATE emp_act
SET emptime = 10
WHERE empno = '000010'
AND projno = 'MA2100';
Figure 150, Direct update of table
UPDATE
(SELECT *
FROM emp_act
WHERE empno = '000010'
AND projno = 'MA2100'
)AS ea
SET emptime = 20;
Figure 151, Update of full-select
UPDATE emp_act ea1
SET emptime = (SELECT MAX(emptime)
FROM emp_act ea2
WHERE ea1.empno = ea2.empno)
WHERE empno = '000010'
AND projno = 'MA2100';
Figure 152, Set employee-time in row to MAX - for given employee
UPDATE
(SELECT ea1.*
,MAX(emptime) OVER(PARTITION BY empno) AS maxtime
FROM emp_act ea1
)AS ea2
SET emptime = maxtime
WHERE empno = '000010'
AND projno = 'MA2100';
Figure 153, Use OLAP function to get max-time, then apply (correct)
UPDATE emp_act
SET emptime = MAX(emptime) OVER(PARTITION BY empno)
WHERE empno = '000010'
AND projno = 'MA2100';
Figure 154, Use OLAP function to get max-time, then apply (wrong)
UPDATE emp_act ac1
SET (actno
,emptime) = (SELECT ROW_NUMBER() OVER()
,ac1.emptime / 2
FROM emp_act ac2
WHERE ac2.empno LIKE '60%'
AND SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE EMPNO LIKE '800%';
Figure 155, Update with correlated query
UPDATE emp_act ac1
SET (actno
,emptime) = (SELECT c1
,c2
FROM (SELECT ROW_NUMBER() OVER() AS c1
,actno / 100 AS c2
,empno
FROM emp_act
WHERE empno LIKE '60%'
)AS ac2
WHERE SUBSTR(ac2.empno,3) = SUBSTR(ac1.empno,3))
WHERE empno LIKE '900%';
Figure 156, Update with uncorrelated query
DELETE
FROM emp_act
WHERE empno = '000010'
AND projno = 'MA2100'
AND actno = 10;
Figure 157, Single-row delete
Figure 158, DELETE statement syntax
DELETE
FROM emp_act;
Figure 159, Mass delete
DELETE
FROM emp_act
WHERE empno LIKE '00%'
AND projno >= 'MA';
Figure 160, Selective delete
DELETE
FROM staff s1
WHERE id NOT IN
(SELECT MAX(id)
FROM staff s2
WHERE s1.dept = s2.dept);
Figure 161, 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 162, 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 163, Delete using full-select and OLAP function
DELETE
FROM emp_act
WHERE (empno, projno, actno) IN
(SELECT empno
,projno
,actno
FROM (SELECT eee.*
,ROW_NUMBER()
OVER(ORDER BY empno, projno, actno) AS r#
FROM emp_act eee
)AS xxx
WHERE r# <= 10);
Figure 164, Delete first "n" rows
Figure 165, 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
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 166, 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 (empno, projno, actno) 300000 VVV 111 2
INCLUDE (row# SMALLINT)
VALUES ('300000','ZZZ',999,1)
,('300000','VVV',111,2))
ORDER BY row#;
Figure 167, 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 (empno, projno, actno) 400000 VVV 111 2
VALUES ('400000','ZZZ',999)
,('400000','VVV',111))
ORDER BY INPUT SEQUENCE;
Figure 168, 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 (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 169, 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 200000 ABC 1.00
SET emptime = emptime * 2 200000 DEF 1.00
WHERE empno = '200000')
ORDER BY projno;
Figure 170, 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 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 171, Select values - before and after update
SELECT projno AS prj ANSWER
,actno AS act =======
FROM OLD TABLE PRJ ACT
(DELETE --- ---
FROM emp_act VVV 111
WHERE empno = '300000') ZZZ 999
ORDER BY 1,2;
Figure 172, 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 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 173, 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
WHERE empno < '0001')
FETCH FIRST 5 ROWS ONLY;
Figure 174, Join result to another table
Figure 175, 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|18171.25| |30|1750.67|
CREATE TABLE new_staff AS |30|Mgr |17506.75| |40|1800.60|
(SELECT id, salary |40|Sales|18006.00| |50|2065.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 176, 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|18171.25| |30|1750.67|
SET oo.salary = nn.salary |30|Mgr |17506.75| |40|1800.60|
WHEN NOT MATCHED THEN |40|Sales|18006.00| |50|2065.98|
INSERT +-----------------+ +----------+
VALUES (nn.id,'?',nn.salary);
AFTER-MERGE
=================
ID JOB SALARY
-- ----- --------
20 Sales 18171.25
30 Mgr 1750.67
40 Sales 1800.60
50 ? 2065.98
Figure 177, 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 18171.25
Figure 178, 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 < 18000 THEN |20|Sales|18171.25| |30|1750.67|
UPDATE |30|Mgr |17506.75| |40|1800.60|
SET oo.salary = nn.salary |40|Sales|18006.00| |50|2065.98|
WHEN MATCHED +-----------------+ +----------+
AND oo.salary > 18000 THEN
DELETE AFTER-MERGE
WHEN NOT MATCHED =================
AND nn.id > 10 THEN ID JOB SALARY
INSERT -- ----- --------
VALUES (nn.id,'?',nn.salary) 20 Sales 18171.25
WHEN NOT MATCHED THEN 30 Mgr 1750.67
SIGNAL SQLSTATE '70001' 50 ? 2065.98
SET MESSAGE_TEXT = 'New ID <= 10';
Figure 179, 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 18171.25
FROM old_staff 30 Mgr 17506.75
)AS mx 40 Sales 18006.00
ON id = max_id 41 Sales 18171.25
WHEN NOT MATCHED THEN
INSERT
VALUES (max_id, max_job, max_sal);
Figure 180, 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 181, 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|18171.25| |30|1750.67|
USING |30|Mgr |17506.75| |40|1800.60|
(SELECT * |40|Sales|18006.00| |50|2065.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 18171.25
INSERT 40 ? 1800.60
VALUES (nn.id,'?',nn.salary); 40 Sales 18006.00
Figure 182, Merge using two full-selects
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 18171.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 183, Listing columns and values in insert
Figure 184, 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 185, Sample Compound SQL statement
--#SET DELIMITER !
SELECT NAME FROM STAFF WHERE ID = 10!
--#SET DELIMITER ;
SELECT NAME FROM STAFF WHERE ID = 20;
Figure 186, 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 187, DECLARE examples
Figure 188, FOR statement syntax
BEGIN ATOMIC
FOR V1 AS
SELECT dept AS dname
,max(id) AS max_id
FROM staff
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY dept
DO
UPDATE staff
SET id = id * -1
WHERE id = max_id;
UPDATE staff
set dept = dept / 10
WHERE dept = dname
AND dept < 30;
END FOR;
END
Figure 189, FOR statement example
Figure 190, 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 191, GET DIAGNOSTICS statement example
Figure 192, 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 193, IF statement example
Figure 194, 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 195, ITERATE statement example
Figure 196, 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 197, LEAVE statement example
Figure 198, 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 199, SIGNAL statement example
Figure 200, 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 201, 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 202, 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 203, 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 204, 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 205, 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 206, Table Function with compound SQL
Figure 207, 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 208, 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 16675.6 351.9 513.3
END) AS comm2
FROM staff;
UPDATE staff
SET comm = NULL
WHERE comm = 0;
Figure 209, 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 210, Convert null output (from AVG) to zero
SELECT AVG(DAYS(birthdate)) ANSWER
,DATE(AVG(DAYS(birthdate))) =================
FROM employee; 1 2
------ ----------
709113 1942-06-27
Figure 211, 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 212, Select average of average
Figure 213, 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 214, CORRELATION function examples
Figure 215, 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 216, 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 217, COUNT function with and without GROUP BY
Figure 218, 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 219, COUNT_BIG function examples
Figure 220, 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 221, COVARIANCE function examples
Figure 222, GROUPING function syntax
SELECT dept ANSWER
,AVG(salary) AS salary ================
,GROUPING(dept) AS df DEPT SALARY DF
FROM staff ---- -------- --
GROUP BY ROLLUP(dept) 10 20865.86 0
ORDER BY dept; 15 15482.33 0
20 16071.52 0
38 15457.11 0
42 14592.26 0
51 17218.16 0
66 17215.24 0
84 16536.75 0
- 16675.64 1
Figure 223, GROUPING function example
Figure 224, 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 225, MAX function examples
SELECT MAX(hiredate) ANSWER
,CHAR(MAX(hiredate),USA) ================================
,MAX(CHAR(hiredate,USA)) 1 2 3
FROM employee; ---------- ---------- ----------
1980-09-30 09/30/1980 12/15/1976
Figure 226, 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 227, 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 228, MAX function with numbers, 2 of 2
Figure 229, 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 230, MIN function examples
Figure 231, REGRESSION functions syntax
ANSWERS
==========
SELECT DEC(REGR_SLOPE(bonus,salary) ,7,5) AS r_slope 0.01710
,DEC(REGR_INTERCEPT(bonus,salary),7,3) AS r_icpt 100.871
,INT(REGR_COUNT(bonus,salary) ) AS r_count 3
,INT(REGR_AVGX(bonus,salary) ) AS r_avgx 42833
,INT(REGR_AVGY(bonus,salary) ) AS r_avgy 833
,INT(REGR_SXX(bonus,salary) ) AS r_sxx 296291666
,INT(REGR_SXY(bonus,salary) ) AS r_sxy 5066666
,INT(REGR_SYY(bonus,salary) ) AS r_syy 86666
FROM employee
WHERE workdept = 'A00';
Figure 232, REGRESSION functions examples
Figure 233, 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 234, STDDEV function examples
Figure 235, 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 236, SUM function examples
Figure 237, 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 238, 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 13504.60
,s1.id; Mgr 10 18357.50
Mgr 50 20659.80
Figure 239, 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 13504.60 13504.60 1
,s1.id; Mgr 10 18357.50 31862.10 2
Mgr 50 20659.80 52521.90 3
Figure 240, Using OLAP functions to get additional fields
SELECT s1.job, s1.id, s1.salary
,xx.sumsal, xx.r
FROM staff s1
,TABLE
(SELECT SUM(s2.salary) AS sumsal
,COUNT(*) AS r
FROM staff s2
WHERE s2.name LIKE '%s%'
AND s2.id < 90
AND (s2.job < s1.job
OR (s2.job = s1.job ANSWER
AND s2.id <= s1.id)) ============================
)AS xx JOB ID SALARY SUMSAL R
WHERE s1.name LIKE '%s%' ----- -- -------- -------- -
AND s1.id < 90 Clerk 80 13504.60 13504.60 1
ORDER BY s1.job Mgr 10 18357.50 31862.10 2
,s1.id; Mgr 50 20659.80 52521.90 3
Figure 241, Using Nested Table Expression to get additional fields
SELECT s1.job, s1.id, s1.salary ANSWER
,SUM(s2.salary) AS sumsal ============================
,COUNT(*) AS r JOB ID SALARY SUMSAL R
FROM staff s1 ----- -- -------- -------- -
,staff s2 Clerk 80 13504.60 13504.60 1
WHERE s1.name LIKE '%s%' Mgr 10 18357.50 31862.10 2
AND s1.id < 90 Mgr 50 20659.80 52521.90 3
AND s2.name LIKE '%s%'
AND s2.id < 90
AND (s2.job < s1.job
OR (s2.job = s1.job
AND s2.id <= s1.id))
GROUP BY s1.job
,s1.id
,s1.salary
ORDER BY s1.job
,s1.id;
Figure 242, Using Self-Join and Group By to get additional fields
SELECT s1.job, s1.id, s1.salary
,(SELECT SUM(s2.salary)
FROM staff s2
WHERE s2.name LIKE '%s%'
AND s2.id < 90
AND (s2.job < s1.job
OR (s2.job = s1.job
AND s2.id <= s1.id))) AS sumsal
,(SELECT COUNT(*)
FROM staff s3
WHERE s3.name LIKE '%s%'
AND s3.id < 90
AND (s3.job < s1.job
OR (s3.job = s1.job
AND s3.id <= s1.id))) AS r
FROM staff s1
WHERE s1.name LIKE '%s%' ANSWER
AND s1.id < 90 ============================
ORDER BY s1.job JOB ID SALARY SUMSAL R
,s1.id; ----- -- -------- -------- -
Clerk 80 13504.60 13504.60 1
Mgr 10 18357.50 31862.10 2
Mgr 50 20659.80 52521.90 3
Figure 243, Using Nested Table Expressions in Select to get additional fields
SELECT dpt.deptname
,emp.empno
,emp.lastname
,emp.salary
,SUM(salary) OVER(ORDER BY dpt.deptname ASC
,emp.salary DESC
,emp.empno ASC) AS sumsal
,ROW_NUMBER() OVER(ORDER BY dpt.deptname ASC
,emp.salary DESC
,emp.empno ASC) AS row#
FROM employee emp
,department dpt
WHERE emp.firstnme LIKE '%S%'
AND emp.workdept = dpt.deptno
AND dpt.admrdept LIKE 'A%'
AND NOT EXISTS
(SELECT *
FROM emp_act eat
WHERE emp.empno = eat.empno
AND eat.emptime > 10)
ORDER BY dpt.deptname ASC
,emp.salary DESC
,emp.empno ASC;
Figure 244, Complicated query using OLAP functions
Figure 245, 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
AND years IS NOT NULL ANSWER
ORDER BY years; ===================================
ID YEARS SALARY RANK# DENSE# ROW#
-- ----- -------- ----- ------ ----
30 5 17506.75 1 1 1
40 6 18006.00 2 2 2
90 6 18001.75 2 2 3
10 7 18357.50 4 3 4
70 7 16502.83 4 3 5
20 8 18171.25 6 4 6
50 10 20659.80 7 5 7
Figure 246, Ranking functions example
SELECT job
,years
,id
,name
,SMALLINT(RANK() OVER(ORDER BY job ASC)) AS asc1
,SMALLINT(RANK() OVER(ORDER BY job ASC
,years ASC)) AS asc2
,SMALLINT(RANK() OVER(ORDER BY job ASC
,years ASC
,id ASC)) AS asc3
,SMALLINT(RANK() OVER(ORDER BY job DESC)) AS dsc1
,SMALLINT(RANK() OVER(ORDER BY job DESC
,years DESC)) AS dsc2
,SMALLINT(RANK() OVER(ORDER BY job DESC
,years DESC
,id DESC)) AS Dsc3
,SMALLINT(RANK() OVER(ORDER BY job ASC
,years DESC
,id ASC)) AS mix1
,SMALLINT(RANK() OVER(ORDER BY job DESC
,years ASC
,id DESC)) AS mix2
FROM staff
WHERE id < 150
AND years IN (6,7)
AND job > 'L'
ORDER BY job
,years
,id;
ANSWER
================================================================
JOB YEARS ID NAME ASC1 ASC2 ASC3 DSC1 DSC2 DSC3 MIX1 MIX2
----- ----- --- ------- ---- ---- ---- ---- ---- ---- ---- ----
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 247, ORDER BY usage
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 ANSWER
,salary; ==================================
ID YR SALARY A AF AL D DF DL
-- -- -------- -- -- -- -- -- --
30 5 17506.75 1 2 1 6 6 5
90 6 18001.75 2 3 2 5 5 4
40 6 18006.00 2 3 2 5 5 4
70 7 16502.83 3 4 3 4 4 3
10 7 18357.50 3 4 3 4 4 3
20 8 18171.25 4 5 4 3 3 2
50 10 20659.80 5 6 5 2 2 1
80 - 13504.60 6 1 6 1 1 6
60 - 16808.30 6 1 6 1 1 6
Figure 248, 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 249, Counting distinct values - comparison
SELECT id ANSWER
,years AS yr =================
,salary ID YR SALARY R1
,RANK() OVER(PARTITION BY years -- -- -------- --
ORDER BY salary) AS r1 30 5 17506.75 1
FROM staff 40 6 18006.00 1
WHERE id < 80 70 7 16502.83 1
AND years IS NOT NULL 10 7 18357.50 2
ORDER BY years 20 8 18171.25 1
,salary; 50 0 20659.80 1
Figure 250, 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 251, 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 rand())) AS dumb4
,SMALLINT(RANK() OVER(ORDER BY 1)) AS dumb5
FROM staff
WHERE id < 40
AND years IS NOT NULL
ORDER BY 1;
Figure 252, Dumb rankings, SQL
ID YEARS NAME SALARY DUMB1 DUMB2 DUMB3 DUMB4 DUMB5
-- ----- -------- -------- ----- ----- ----- ----- -----
10 7 Sanders 18357.50 1 3 1 1 1
20 8 Pernal 18171.25 3 2 3 3 1
30 5 Marenghi 17506.75 2 1 2 2 1
Figure 253, 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 254, 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 50 1 20659.80 15
FROM staff 10 1 18357.50 20
WHERE id < 80 40 1 18006.00 38
AND years IS NOT NULL 20 2 18171.25 20
ORDER BY r1 ASC 30 2 17506.75 38
,salary DESC; 70 2 16502.83 15
Figure 255, 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 50 1 20659.80 15
AND s2.dept = s1.dept 10 1 18357.50 20
AND s2.salary >= s1.salary) AS R1 40 1 18006.00 38
,SALARY 20 2 18171.25 20
,dept AS dp 30 2 17506.75 38
FROM staff s1 70 2 16502.83 15
WHERE id < 80
AND years IS NOT NULL
ORDER BY r1 ASC
,salary DESC;
Figure 256, 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 20659.80 15
ORDER BY salary DESC) AS r1 10 18357.50 20
FROM staff s1 40 18006.00 38
WHERE id < 80
AND years IS NOT NULL
)AS xxx
WHERE r1 = 1
ORDER BY dp;
Figure 257, 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 20659.80 15
AND years IS NOT NULL 10 18357.50 20
AND NOT EXISTS 40 18006.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 258, 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 20659.80 15
AND years IS NOT NULL 10 18357.50 20
AND (dept, salary) IN 40 18006.00 38
(SELECT dept, MAX(salary)
FROM staff
WHERE id < 80
AND years IS NOT NULL
GROUP BY dept)
ORDER BY dp;
Figure 259, Get highest salary in each department, use uncorrelated sub-query
Figure 260, 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 261, 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 262, 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 263, 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
AND years IN (6,7) ANSWER
AND job > 'L' ======================================
ORDER BY job JOB YEARS ID NAME ROW# RN1# RN2#
,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 264, 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 265, 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 266, 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 267, 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 268, Select every 5th matching row
SELECT *
FROM (SELECT id
,name
,ROW_NUMBER() OVER(ORDER BY id DESC) AS r
FROM staff
WHERE id < 200
AND years IS NOT NULL ANSWER
)AS xxx ==============
WHERE r <= 2 ID NAME R
ORDER BY id; --- -------- -
180 Abrahams 2
190 Sneider 1
Figure 269, Select last two rows
WITH
temp1(years, id, name, rnk, row) AS
(SELECT years
,id
,name
,RANK() OVER(ORDER BY years)
,ROW_NUMBER() OVER(ORDER BY years, id)
FROM staff
WHERE id < 200
AND years IS NOT NULL
),
temp2(rnk) AS
(SELECT rnk
FROM temp1
WHERE row = 3 ANSWER
) ==========================
SELECT temp1.* YEARS ID NAME RNK ROW
FROM temp1 ----- --- -------- --- ---
,temp2 3 180 Abrahams 1 1
WHERE temp1.rnk <= temp2.rnk 4 170 Kermisch 2 2
ORDER BY years 5 30 Marenghi 3 3
,id; 5 110 Ngan 3 4
Figure 270, Select first "n" rows, or more if needed
CREATE TABLE invoice
(inv# INTEGER NOT NULL
,customer# INTEGER NOT NULL
,sale_date DATE NOT NULL
,sale_value DECIMAL(9,2) NOT NULL
,CONSTRAINT ctx1 PRIMARY KEY (inv#)
,CONSTRAINT ctx2 CHECK(inv# >= 0));
Figure 271, Performance test table - definition
INSERT INTO invoice
WITH temp (n,m) AS
(VALUES (INTEGER(0),RAND(1))
UNION ALL
SELECT n+1, RAND()
FROM temp
WHERE n+1 < 500000
)
SELECT n AS inv#
,INT(m * 1000) AS customer#
,DATE('2000-11-01') + (m*40) DAYS AS sale_date
,DECIMAL((m * m * 100),8,2) AS sale_value
FROM temp;
Figure 272, Performance test table - insert 500,000 rows
SELECT s.*
FROM invoice s
ORDER BY inv#
FETCH FIRST 5 ROWS ONLY;
Figure 273, Fetch first 5 rows - 0.313 elapsed seconds
SELECT s.*
FROM invoice s
ORDER BY inv#
FETCH FIRST 5 ROWS ONLY
OPTIMIZE FOR 5 ROWS;
Figure 274, Fetch first 5 rows - 0.281 elapsed seconds
SELECT s.*
,ROW_NUMBER() OVER() AS row#
FROM invoice s
ORDER BY inv#
FETCH FIRST 5 ROWS ONLY;
Figure 275, Fetch first 5 rows+ number rows - 0.672 elapsed seconds
SELECT *
FROM (SELECT s.*
,ROW_NUMBER() OVER() AS row#
FROM invoice s
)xxx
WHERE row# <= 5
ORDER BY inv#;
Figure 276, Process and number 5 rows only - 0.000 elapsed seconds
SELECT *
FROM (SELECT s.*
,ROW_NUMBER() OVER(ORDER BY inv#) AS row#
FROM invoice s
)xxx
WHERE row# <= 5
ORDER BY inv#;
Figure 277, Process and number 5 rows only - 0.281 elapsed seconds
WITH temp (inv#, c#, sd, sv, n) AS
(SELECT inv.*
,1
FROM invoice inv
WHERE inv# =
(SELECT MIN(inv#)
FROM invoice)
UNION ALL
SELECT new.*, n + 1
FROM temp old
,invoice new
WHERE old.inv# < new.inv#
AND old.n < 5
AND new.inv# =
(SELECT MIN(xxx.inv#)
FROM invoice xxx
WHERE xxx.inv# > old.inv#)
)
SELECT *
FROM temp;
Figure 278, Fetch first 5 rows - 0.000 elapsed seconds
Figure 279, Aggregation Function syntax
SELECT id
,name
,salary
,SUM(salary) OVER() AS sum_sal
,AVG(salary) OVER() AS avg_sal
,MIN(salary) OVER() AS min_sal
,MAX(salary) OVER() AS max_sal
,COUNT(*) OVER() AS #rows
FROM staff
WHERE id < 60
ORDER BY id;
Figure 280, Aggregation function, basic usage, SQL
ID NAME SALARY SUM_SAL AVG_SAL MIN_SAL MAX_SAL #ROWS
-- -------- -------- -------- -------- -------- -------- -----
10 Sanders 18357.50 92701.30 18540.26 17506.75 20659.80 5
20 Pernal 18171.25 92701.30 18540.26 17506.75 20659.80 5
30 Marenghi 17506.75 92701.30 18540.26 17506.75 20659.80 5
40 O'Brien 18006.00 92701.30 18540.26 17506.75 20659.80 5
50 Hanes 20659.80 92701.30 18540.26 17506.75 20659.80 5
Figure 281, Aggregation function, basic usage, Answer
WITH
temp1 (id, name, salary) AS
(SELECT id, name, salary
FROM staff
WHERE id < 60
),
temp2 (sum_sal, avg_sal, min_sal, max_sal, #rows) AS
(SELECT SUM(salary)
,AVG(salary)
,MIN(salary)
,MAX(salary)
,COUNT(*)
FROM temp1
)
SELECT *
FROM temp1
,temp2
ORDER BY id;
Figure 282, Select detailed data, plus summary data
SELECT id
,name
,salary
,SUM(salary) OVER() AS sum1
,SUM(salary) OVER(ORDER BY id * 0) AS sum2
,SUM(salary) OVER(ORDER BY 'ABC') AS sum3
,SUM(salary) OVER(ORDER BY 'ABC'
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS sum4
FROM staff
WHERE id < 60
ORDER BY id;
Figure 283, Logically equivalent aggregation functions, SQL
ID NAME SALARY SUM1 SUM2 SUM3 SUM4
-- -------- -------- -------- -------- -------- --------
10 Sanders 18357.50 92701.30 92701.30 92701.30 92701.30
20 Pernal 18171.25 92701.30 92701.30 92701.30 92701.30
30 Marenghi 17506.75 92701.30 92701.30 92701.30 92701.30
40 O'Brien 18006.00 92701.30 92701.30 92701.30 92701.30
50 Hanes 20659.80 92701.30 92701.30 92701.30 92701.30
Figure 284, Logically equivalent aggregation functions, Answer
SELECT dept
,name
,salary
,SUM(salary) OVER(ORDER BY dept) AS sum1
,SUM(salary) OVER(ORDER BY dept DESC) AS sum2
,SUM(salary) OVER(ORDER BY dept, NAME) AS sum3
,SUM(salary) OVER(ORDER BY dept DESC, name DESC) AS sum4
,COUNT(*) OVER(ORDER BY dept) AS row1
,COUNT(*) OVER(ORDER BY dept, NAME) AS row2
FROM staff
WHERE id < 60
ORDER BY dept
,name;
Figure 285, Aggregation function, order by usage, SQL
DEPT NAME SALARY SUM1 SUM2 SUM3 SUM4 ROW1 ROW2
---- -------- -------- -------- -------- -------- -------- ---- ----
15 Hanes 20659.80 20659.80 92701.30 20659.80 92701.30 1 1
20 Pernal 18171.25 57188.55 72041.50 38831.05 72041.50 3 2
20 Sanders 18357.50 57188.55 72041.50 57188.55 53870.25 3 3
38 Marenghi 17506.75 92701.30 35512.75 74695.30 35512.75 5 4
38 O'Brien 18006.00 92701.30 35512.75 92701.30 18006.00 5 5
Figure 286, Aggregation function, order by usage, Answer
SELECT dept
,name
,years
,SMALLINT(SUM(years) OVER(ORDER BY dept)) AS d
,SMALLINT(SUM(years) OVER(ORDER BY dept, name)) AS dn
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS UNBOUNDED PRECEDING))AS dnu
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS 3 PRECEDING)) AS dn3
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS 1 PRECEDING)) AS dn1
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS 0 PRECEDING)) AS dn0
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS CURRENT ROW)) AS dnc
,SMALLINT(SUM(years) OVER(ORDER BY dept DESC, name DESC
ROWS 1 PRECEDING)) AS dnx
FROM staff
WHERE id < 100
AND years IS NOT NULL
ORDER BY dept
,name;
Figure 287, Starting ROWS usage. Implied end is current row, SQL
DEPT NAME YEARS D DN DNU DN3 DN1 DN0 DNC DNX
---- -------- ----- -- -- --- --- --- --- --- ---
15 Hanes 10 17 10 10 10 10 10 10 17
15 Rothman 7 17 17 17 17 17 7 7 15
20 Pernal 8 32 25 25 25 15 8 8 15
20 Sanders 7 32 32 32 32 15 7 7 12
38 Marenghi 5 43 37 37 27 12 5 5 11
38 O'Brien 6 43 43 43 26 11 6 6 12
42 Koonitz 6 49 49 49 24 12 6 6 6
Figure 288, Starting ROWS usage. Implied end is current row, Answer
SELECT dept
,name
,years
,SMALLINT(SUM(years) OVER(ORDER BY dept, name)) AS uc1
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS UNBOUNDED PRECEDING)) AS uc2
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)) AS uc3
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN CURRENT ROW
AND CURRENT ROW)) AS cu1
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING)) AS pf1
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN 2 PRECEDING
AND 2 FOLLOWING)) AS pf2
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN 3 PRECEDING
AND 3 FOLLOWING)) AS pf3
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)) AS cu1
,SMALLINT(SUM(years) OVER(ORDER BY dept, name
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)) AS uu1
FROM staff
WHERE id < 100
AND years IS NOT NULL
ORDER BY dept
,name;
Figure 289, ROWS usage, with BETWEEN phrase, SQL
DEPT NAME YEARS UC1 UC2 UC3 CU1 PF1 PF2 PF3 CU1 UU1
---- -------- ----- --- --- --- --- --- --- --- --- ---
15 Hanes 10 10 10 10 10 17 25 32 49 49
15 Rothman 7 17 17 17 7 25 32 37 39 49
20 Pernal 8 25 25 25 8 22 37 43 32 49
20 Sanders 7 32 32 32 7 20 33 49 24 49
38 Marenghi 5 37 37 37 5 18 32 39 17 49
38 O'Brien 6 43 43 43 6 17 24 32 12 49
42 Koonitz 6 49 49 49 6 12 17 24 6 49
Figure 290, ROWS usage, with BETWEEN phrase, Answer
SELECT id
,name
,SMALLINT(SUM(id) OVER(ORDER BY id ASC
ROWS BETWEEN 1 PRECEDING
AND CURRENT ROW)) AS apc
,SMALLINT(SUM(id) OVER(ORDER BY id ASC
ROWS BETWEEN CURRENT ROW
AND 1 FOLLOWING)) AS acf
,SMALLINT(SUM(id) OVER(ORDER BY id DESC
ROWS BETWEEN 1 PRECEDING
AND CURRENT ROW)) AS dpc
,SMALLINT(SUM(id) OVER(ORDER BY id DESC
ROWS BETWEEN CURRENT ROW
AND 1 FOLLOWING)) AS dcf
FROM staff
WHERE id < 50
AND years IS NOT NULL ANSWER
ORDER BY id; ===========================
ID NAME APC ACF DPC DCF
-- -------- --- --- --- ---
10 Sanders 10 30 30 10
20 Pernal 30 50 50 30
30 Marenghi 50 70 70 50
40 O'Brien 70 40 40 70
Figure 291,BETWEEN and ORDER BY usage
ASC id (10,20,30,40)
READ ROWS, LEFT to RIGHT 1ST-ROW 2ND-ROW 3RD-ROW 4TH-ROW
========================== ======== ======== ======== ========
1 PRECEDING to CURRENT ROW 10=10 10+20=30 20+30=40 30