pbPassingBI
/
sql/flashcards/sql-cards-ddl

Creating & managing tables — flashcards

One card per lesson — 4 in this module. Recall the key points, then check. 4 cards. Click a card to flip, or use the arrow keys and space bar.

  1. SQL data types — what are the key points?

    Use DECIMAL for money, never FLOAT · CHAR pads with spaces, which can break comparisons · Dates stored as text sort alphabetically and break arithmetic Read the lesson

  2. CREATE TABLE and constraints — what are the key points?

    PRIMARY KEY implies unique and not null · UNIQUE allows nulls; PRIMARY KEY does not · Constraints hold whatever wrote the data, unlike application checks Read the lesson

  3. INSERT, UPDATE and DELETE — what are the key points?

    UPDATE or DELETE without WHERE affects every row · Write the SELECT first, or wrap the change in a transaction · TRUNCATE is fast and unfiltered; DELETE is logged and reversible Read the lesson

  4. Views — what are the key points?

    A view stores the query, not the data · Views give one agreed definition of a metric and can restrict access · Materialised views store results — fast to read, stale until refreshed Read the lesson