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);
How SQL CTE Works
Apply expression SUM(SALARY+COMM), and select the rows.
You will get desired Result.
DB2 Tough Interview Questions.
Useful for interviews and Projects.
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.
References for Recursive