The SQL query is the only language that you can use to interact with DB2. Below are tricky DB2 SQL queries for practice.
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';
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)
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
WHEN NOT MATCHED THEN
INSERT INTO T2 ...
WHEN MATCHED THEN
UPDATE T2 SET ...