5 Tricky DB2 SQL Queries for Practice

The SQL query is the only language that you can use to interact with DB2. Below are tricky DB2 SQL queries for practice.

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';

---

You May Also Like: DB2 Developer’s Guide

---

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