SQL Common Table Expression Top Interview Questions

Here’re top advantages of Common Table Expression(CTE) – Simplifies SQL Query, Easy to get expressions, Reference is on same table. Also, I’ve listed SQL CTE interview questions.

You can identify CTE by looking at ‘WITH’ clause. More on full-select. In simple terms, it looks like a Sub-query.

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

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

How SQL CTE Works

Step-1

Get MAXSALARY.

Step-2

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

Step-3

You will get desired Result.

References