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.

5 Tricky DB2 SQL Queries for Practice
5 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');

“Technology is best when it brings people together.”

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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.