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