Week 1: SQL Fundamentals — SELECT, WHERE, ORDER BY & the Relational Model

1. The Relational Model: Tables, Rows & Keys

A relational database stores data in tables — a fixed set of named, typed columns, with each row holding one record. Every table should have a primary key, a column (or set of columns) that uniquely identifies each row.

users
+----+----------+---------------------+
| id | name     | email               |
+----+----------+---------------------+
| 1  | Asha     | asha@example.com    |
| 2  | Ravi     | ravi@example.com    |
+----+----------+---------------------+

"Relational" refers to how tables relate to each other — an orders table doesn't repeat a customer's name and email on every order; it stores a user_id that references the users table's primary key. This — storing each fact exactly once, and referencing it elsewhere — is the core idea Week 5's normalization builds a full framework around.

2. Installing PostgreSQL & Connecting with psql

brew install postgresql@16
brew services start postgresql@16

# Windows: download the installer from postgresql.org/download
# Ubuntu/Debian:
sudo apt install postgresql postgresql-contrib

psql -U postgres

-- once connected:
CREATE DATABASE learn_sql;
\c learn_sql

psql commands starting with a backslash (\c, \dt, \d tablename) are client-side meta-commands, not SQL — \dt lists tables, \d orders describes a specific table's columns, both genuinely useful while learning. A GUI client (TablePlus, DBeaver, or pgAdmin) is a fine alternative once the command line gets tedious, but this course uses psql throughout since it's universal and scriptable.

3. SELECT, WHERE & ORDER BY

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL,
    age   INT
);

INSERT INTO users (name, email, age) VALUES
    ('Asha', 'asha@example.com', 30),
    ('Ravi', 'ravi@example.com', 25),
    ('Priya', 'priya@example.com', 28);

SELECT name, email FROM users;              -- specific columns
SELECT * FROM users;                        -- every column

SELECT * FROM users WHERE age > 26;         -- filtering rows
SELECT * FROM users WHERE name = 'Asha';

SELECT * FROM users ORDER BY age DESC;      -- sorting, newest/oldest first
SELECT * FROM users ORDER BY age ASC LIMIT 1; -- the single youngest user

SQL clauses have a fixed order (SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT) but a different execution order — FROM and WHERE effectively run first to determine which rows are in play, SELECT picks which columns to return from those rows, and ORDER BY/LIMIT apply last. This distinction matters more once GROUP BY and aggregates enter next week.

4. Data Types & NULL

NULL means "unknown" or "not applicable" — it is not zero, not an empty string, and not equal to itself in a comparison.

SELECT * FROM users WHERE age = NULL;   -- returns nothing, always — wrong
SELECT * FROM users WHERE age IS NULL;  -- the correct way to check for NULL

age = NULL never evaluates to true for any row, because comparing anything to "unknown" is itself unknown — this is the single most common beginner mistake in SQL, and IS NULL/IS NOT NULL are the only correct way to test for it.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.