CTE — SQL Technical Interview Questions & Answers

CTE

CTE

  • CTE is WITH name AS (SELECT …) SELECT … FROM name; A named subquery you can read top-down. MySQL 8+. WITH fail AS (SELECT * FROM students WHERE marks < 40) SELECT COUNT(*) FROM fail;
  • WITH RECURSIVE walks a tree (org chart). Don’t start there. Start with one WITH as a readable subquery.
  • Trap: CTE on MySQL 5.7 (not supported). Trap: recursive CTE without a stop condition.
  • On the example next to this theory: CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: WITH fail AS (SELECT … WHERE marks < 40) SELECT * FROM fail;

Code Examples for CTE

CTE — sample query

-- CTE
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain CTE to an Interviewer

What version added CTE?

MySQL 8 WITH … AS