Tables, Rows, Columns & Data Types — SQL Course

What you'll learn

Develop a precise mental model for relations, rows, attributes, domains, and the data types that protect meaning. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Relations and tuples to a realistic data question
Apply Column domains to a realistic data question
Apply Text and numbers to a realistic data question
Apply Dates and timestamps to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Row grain	The real-world fact represented by one row	Write the grain in plain language before choosing columns
Domain	The allowed kind and range of a column value	Choose the narrowest type that preserves meaning
Timestamp with time zone	An instant normalized by PostgreSQL	Use timestamptz for real instants and convert for display

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the relational type model question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Inspect types and values together

Catalog metadata explains declared types while sample rows reveal actual domain shape.

inspect_books.sql

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'course' AND table_name = 'books'
ORDER BY ordinal_position;

SELECT id, title, price, published_at
FROM course.books
LIMIT 5;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Storing dates, money, or identifiers in generic text postpones validation and produces ambiguous sorting, arithmetic, and comparison behavior.

Independent workshop

Build a review-ready relational type model lab against the course commerce dataset.

Your finished workshop must include:

Relations and tuples
Column domains
Text and numbers
Dates and timestamps
Boolean values
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Row grain: Write the grain in plain language before choosing columns
Domain: Choose the narrowest type that preserves meaning
Timestamp with time zone: Use timestamptz for real instants and convert for display

Quick check

1. Which rule best applies to Row grain?

2. Which rule best applies to Domain?

3. Which rule best applies to Timestamp with time zone?

Next: SELECT Lists, Aliases & Expressions