
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';
Advertisements
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
You must be logged in to post a comment.