SQL: Common Table Expression Best Example

The interesting point in DB2 SQL is Common Table Expression. The name Expression confuses many people. I have simplified it for you. You can read the complete story of fullselect.

CTE: Common Table Expression Best Example.

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);

3 Steps to Complete SQL Query.

WITH

first SELECT

second SELECT

DB2 tough interview questions e-book

DB2 Tough Interview Questions.

Useful for interviews and Projects.

$1.00

How this SQL Query Works.

In this example, the DEPTOTAL common table expression is established by calculating the sum of the salaries plus commissions for each department.

The WITH clause identifies the common table expression, followed by the name of the common table expression and the column names.

The fullselect within the parentheses following the WITH clause defines the data for the common table expression. This data can then be used in the remainder of the query.

Related Posts

Author: Srini

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