SQL: Common Table Expression Best Example

In DB2 SQL, Common Table Expression (CTE) simplifies query writing; it derives and use to get needed result. The first word in CTE is WITH. Here is 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.