SQL · Theory

Collation

Theory

113/439

Collation

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

Do Collation once by hand. one Collation step Arun can write from memory. Change one input. Say the new result out loud.

Collation exists so Arun can keep Collation on Arun's board correct in lab hour.

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

If Collation on Arun's board breaks under Collation, check skipping the failure case for Collation first.

Collation is not a lonely heading. Arun ties it to Collation on Arun's board.

Close Collation with: “If I skip it, Collation on Arun's board goes wrong like this: skipping the failure case for Collation.”

Exam tip

For Collation: definition + lab hour + one failure.

Collation — sample query

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

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

Short notes

DefCollation — Arun uses it for Collation on Arun's board in lab hour.
RuleCollation → one Collation step Arun can write from memory.
RememberCollation + the query planner (lab hour).
UseCollation in lab hour (Collation on Arun's board).
TrapCollation — skipping the failure case for Collation.
ExCollation → Collation on Arun's board.

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

Collation

Collation — sample query

Short notes

Questions

Collation

Collation — sample query

Short notes

Questions