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 Top Interview Questions

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

DB2 Tough Interview Questions.

Useful for interviews and Projects.

$1.00

SQL CTE 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.