5 Tricky DB2 SQL Queries for Practice

Here are the tricky DB2 SQL queries for practice. You May Also Like: DB2 Developer’s Guide

Tricky DB2 SQL Queries

1. The SELECT SQL statement used to query data.

SELECT * FROM employee;

2. The INSERT, UPDATE, and DELETE SQL statements used to modify table data.

INSERT INTO employee
VALUES ( 
'000998', 'SMITH', 'A', 'JOHN',  
NULL, NULL, NULL, NULL, 18,
'M', NULL, NULL, NULL, NULL 
);

UPDATE employee
SET (hiredate, workdept) = 
(SELECT CURRENT DATE, deptno
FROM department
WHERE deptname='PLANNING')
WHERE empno='000999';

DELETE from employee 
where empno='000999' 
and deptname='PLANNING';

3. Recursive SQL statements.

WITH temptab (person_id, name, parent_id) AS (1)
(SELECT person_id, name, parent_id           (2)
FROM children
WHERE name = 'Jenny'
UNION ALL                                    (3)
SELECT c.person_id, c.name, c.parent_id      (4)
FROM children c, temptab super
WHERE c.person_id = super.parent_id
) 
SELECT * FROM temptab;                        (5)

4. How to query data that just got inserted, updated, or deleted in the same SQL statement.

SELECT * FROM sales
WHERE sales_date IN ('03/29/1996', '04/01/2006');

5. The MERGE SQL statement used to combine insert, update, and/or delete operations in one statement.

MERGE INTO T2 as target
    USING (SELECT ... FROM T1) AS source
       ON target.id=source.id
    WHEN NOT MATCHED THEN
        INSERT INTO T2 ...
    WHEN MATCHED THEN
        UPDATE T2 SET ...

Keep Reading