SQL · Theory

CTE

← All stacks

Theory

282/439

CTE

Don’t start CTE with jargon. Start with CTE on Riya's board. That is the whole point.

CTE order — meaning → one CTE step Riya can write from memory → one limit. Dumping ten features helps nobody.

CTE exists so Riya can keep CTE on Riya's board correct in lab hour.

CTE shows up in lab hour. Name CTE on Riya's board, not “a real-world scenario”.

CTE trap — only reciting the name of CTE. Riya loses marks for that every viva.

Place CTE next to nearby SQL work — one CTE step Riya can write from memory is the link.

Close CTE with — “If I skip it, CTE on Riya's board goes wrong like this: only reciting the name of CTE.”

Exam tip

Say CTE in one breath, then one CTE step Riya can write from memory, then only reciting the name of 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.

Short notes

DefCTE — Riya uses it for CTE on Riya's board in lab hour.
RuleCTE → one CTE step Riya can write from memory.
RememberCTE + InnoDB (lab hour).
UseCTE in lab hour (CTE on Riya's board).
TrapCTE — only reciting the name of CTE.
ExCTE → CTE on Riya's board.

-- 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

CTE — sample query

Short notes

Questions

CTE

CTE — sample query

Short notes

Questions