Scribd
Upload
33 views3 pages

DB2 SQL Interview Questions and Answers

The document contains a list of DB2 SQL interview questions and answers covering various topics such as the differences between SQL clauses, how to fetch rows, handle duplicates, and perform updates. Key concepts include the use of JOINs, subqueries, and functions like COALESCE and NULLIF. It also addresses performance tuning with EXPLAIN plans and pagination techniques.

Uploaded by

manishkrnanand
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views3 pages

DB2 SQL Interview Questions and Answers

The document contains a list of DB2 SQL interview questions and answers covering various topics such as the differences between SQL clauses, how to fetch rows, handle duplicates, and perform updates. Key concepts include the use of JOINs, subqueries, and functions like COALESCE and NULLIF. It also addresses performance tuning with EXPLAIN plans and pagination techniques.

Uploaded by

manishkrnanand
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DB2 SQL Interview Questions and Answers

1. What is the difference between WHERE and HAVING clause in DB2?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Example:

SELECT DEPT, COUNT(*)

FROM EMP

WHERE SALARY > 1000

GROUP BY DEPT

HAVING COUNT(*) > 5;

2. What is the difference between INNER JOIN and LEFT JOIN in DB2?

INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows

from the right.

Example:

SELECT A.EMP_ID, B.DEPT_NAME

FROM EMP A LEFT JOIN DEPT B ON A.DEPT_ID = B.DEPT_ID;

3. How do you fetch the first N rows in DB2?

Use FETCH FIRST N ROWS ONLY.

Example:

SELECT * FROM EMP FETCH FIRST 10 ROWS ONLY;

4. What is a correlated subquery?

A subquery that refers to a column from the outer query.

Example:

SELECT [Link], [Link]

FROM EMP E1

WHERE [Link] > (SELECT AVG(SALARY) FROM EMP E2 WHERE [Link] = [Link]);

5. How to find duplicate records in a DB2 table?

Use GROUP BY and HAVING with COUNT > 1.


Example:

SELECT EMPNO, COUNT(*) FROM EMP GROUP BY EMPNO HAVING COUNT(*) > 1;

6. What is the difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL includes all records.

7. How do you perform UPDATE using JOIN in DB2?

Use EXISTS clause.

Example:

UPDATE EMP E

SET SALARY = SALARY + 1000

WHERE EXISTS (SELECT 1 FROM BONUS B WHERE [Link] = [Link]);

8. How to select rows with maximum salary per department?

Use a correlated subquery.

Example:

SELECT DEPTNO, EMPNO, SALARY FROM EMP E1 WHERE SALARY = (SELECT MAX(SALARY) FROM

EMP E2 WHERE [Link] = [Link]);

9. What is the use of NULLIF and COALESCE in DB2?

NULLIF returns NULL if arguments are equal. COALESCE returns the first non-NULL value.

Example:

SELECT COALESCE(BONUS, 0) FROM EMP;

SELECT NULLIF(100, 100);

10. What is the difference between DELETE and TRUNCATE?

DELETE can have WHERE clause and is logged. TRUNCATE is faster, removes all rows, and is not logged.

11. How to get Nth highest salary in DB2?

Use DENSE_RANK or ROW_NUMBER.

Example:

SELECT SALARY FROM (

SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RANK FROM EMP
) AS TEMP WHERE RANK = 3;

12. What is an EXPLAIN plan in DB2?

It shows how DB2 will execute a query. Useful for performance tuning.

13. What are DB2 isolation levels?

RR (Repeatable Read), RS (Read Stability), CS (Cursor Stability), UR (Uncommitted Read).

Example:

SELECT * FROM EMP WITH UR;

14. How to implement pagination in DB2?

Use OFFSET and FETCH.

Example:

SELECT * FROM EMP ORDER BY EMPNO OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

15. How to find employees working in more than one department?

Use GROUP BY and HAVING COUNT > 1.

Example:

SELECT EMPID FROM EMP_DEPT GROUP BY EMPID HAVING COUNT(DISTINCT DEPTID) > 1;

You might also like