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.

Advertisements

Tricky DB2 SQL Queries

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.