Important DB2 SQL Query Interview Questions & Answers for Experienced Professionals
1. Fetch second highest salary:
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
2. Find duplicate records:
SELECT emp_id, COUNT(*) FROM employee GROUP BY emp_id HAVING COUNT(*) > 1;
3. Delete duplicates keeping one:
WITH cte AS (
SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) AS rn
FROM employee
)
DELETE FROM employee WHERE emp_id IN (SELECT emp_id FROM cte WHERE rn > 1);
4. UNION vs UNION ALL:
UNION removes duplicates, UNION ALL retains all rows. UNION is slower due to sorting.
5. Example of correlated subquery:
SELECT emp_id, salary FROM employee e1
WHERE salary > (SELECT AVG(salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id);
6. Fetch current timestamp:
SELECT CURRENT TIMESTAMP FROM sysibm.sysdummy1;
7. Update salary by 10% for IT employees:
UPDATE employee SET salary = salary * 1.10 WHERE dept_id = 'IT';
8. Fetch top 5 rows:
SELECT * FROM employee ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
9. Join example (INNER JOIN):
SELECT e.emp_id, e.name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id;
10. Employees with no department (LEFT JOIN):
SELECT e.emp_id, e.name FROM employee e LEFT JOIN department d ON e.dept_id = d.dept_id WHERE d.dept_id
IS NULL;
11. Handling NULL values:
SELECT COALESCE(column_name, 'N/A') FROM table_name;
12. Explain Plan usage:
EXPLAIN PLAN FOR SELECT * FROM employee WHERE emp_id = 123;
13. Efficient row count:
SELECT COUNT(*) FROM employee;
Page 1
Important DB2 SQL Query Interview Questions & Answers for Experienced Professionals
14. Employees earning above average salary:
SELECT emp_id, salary FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);
15. Pagination query:
SELECT * FROM employee ORDER BY emp_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
16. Check if table exists:
SELECT COUNT(*) FROM sysibm.systables WHERE name = 'EMPLOYEE' AND creator = 'YOUR_SCHEMA';
17. CHAR vs VARCHAR:
CHAR is fixed-length, VARCHAR is variable-length (more storage efficient).
18. DELETE vs TRUNCATE:
DELETE removes rows one by one (slower), TRUNCATE is faster (minimal logging).
19. Fetch table size:
SELECT TBNAME, CARDF, NPAGES FROM SYSIBM.SYSTABLES WHERE NAME = 'EMPLOYEE';
20. Join more than two tables:
SELECT e.emp_id, e.name, d.dept_name, m.manager_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN manager m ON e.manager_id = m.manager_id;
21. Employees hired in last 30 days:
SELECT * FROM employee WHERE hire_date >= CURRENT DATE - 30 DAYS;
22. Use of RUNSTATS:
Collects table and index statistics to help DB2 optimizer. Example: RUNSTATS TABLESPACE DBNAME.TSNAME
23. Check current isolation level:
SELECT CURRENT ISOLATION FROM SYSIBM.SYSDUMMY1;
24. Performance tips for batch queries:
- Use bulk fetch
- Use proper indexes
- Use parameter markers
- Check EXPLAIN access path
- Consider table partitioning
25. Fetch even-numbered rows:
WITH cte AS (
SELECT emp_id, ROW_NUMBER() OVER (ORDER BY emp_id) AS rn FROM employee
)
SELECT emp_id FROM cte WHERE MOD(rn, 2) = 0;
Page 2
