How to Use Common Table Expression [CTE] in DB2

Here is an example and how to use Common Table Expression (CTE) in DB2. How to identify it? The ‘WITH’ Clause in SQL query says that it is a CTE.

CTE the best example SQL query

WITH DEPTOTAL (WORKDEPT, MAXSALARY) AS
(SELECT WORKDEPT, SUM(SALARY+COMM)
  FROM DSN8910.EMP
  GROUP BY WORKDEPT)
SELECT WORKDEPT FROM DEPTOTAL
WHERE MAXSALARY =
  (SELECT MAX(MAXSALARY)
          FROM DEPTOTAL);

Here, the derived value is ‘WORKDEPT’ from the WITH table ‘DEPTOTAL‘.

SQL CTE Top Interview Questions

3 Steps How CTE SQL Works

Step-1

Get MAXSALARY.

Step-2

Apply expression SUM(SALARY+COMM), and select the rows.

Step-3

You will get desired Result.

DB2 Tough Interview Questions.

Useful for interviews and Projects.

$1.00

Interview questions

1.What’s CTE recursion?

It’s not true recursion. Here same table is referenced. The WITH values will be used to get data from other table.

2. CTE Vs Sub-query?

It looks like sub-query. Here the table is same.

Related Posts

References for Recursive

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.