Chapter 5: SQL Basics — SELECT, INSERT, UPDATE, DELETE

SQL (Structured Query Language) is how you talk to relational databases. It's declarative — you describe WHAT you want, and the database figures out HOW to get it efficiently.

SQL Categories

Creating Tables (DDL)

-- Create a table
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    email    VARCHAR(255) UNIQUE NOT NULL,
    age      INTEGER,
    created  TIMESTAMP DEFAULT NOW()
);

-- SERIAL = auto-incrementing integer (PostgreSQL)
-- VARCHAR(100) = variable-length string, max 100 chars
-- NOT NULL = this column must always have a value
-- UNIQUE = no two rows can have the same value
-- DEFAULT = value used if not specified on INSERT

INSERT — Adding Data

-- Insert one row
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- Insert multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Bob', 'bob@example.com', 25),
    ('Charlie', 'charlie@example.com', 35),
    ('Dave', 'dave@example.com', 28);

-- Insert and return the generated ID
INSERT INTO users (name, email, age)
VALUES ('Eve', 'eve@example.com', 22)
RETURNING id;

SELECT — Reading Data

-- Select all columns, all rows
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with a condition
SELECT name, age FROM users WHERE age > 25;

-- Select one row by primary key
SELECT * FROM users WHERE id = 1;

-- Count rows
SELECT COUNT(*) FROM users;

-- Aliasing columns
SELECT name AS user_name, age AS user_age FROM users;

UPDATE — Modifying Data

-- Update one row
UPDATE users SET age = 31 WHERE id = 1;

-- Update multiple columns
UPDATE users SET name = 'Alice Smith', age = 31 WHERE id = 1;

-- Update multiple rows
UPDATE users SET age = age + 1;  -- everyone gets a year older

-- Update with RETURNING (see what changed)
UPDATE users SET age = 31 WHERE id = 1 RETURNING *;

DELETE — Removing Data

-- Delete one row
DELETE FROM users WHERE id = 3;

-- Delete with a condition
DELETE FROM users WHERE age < 18;

-- Delete ALL rows (dangerous!)
DELETE FROM users;

-- TRUNCATE: faster way to delete all rows (resets table)
TRUNCATE TABLE users;

The SELECT Execution Order

SQL is written in one order but executed in another:

This is why you can't use a column alias in WHERE (it hasn't been defined yet at execution step 2).

Practical Example: Building a Simple App Schema

-- A blog application
CREATE TABLE authors (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id),
    title      VARCHAR(200) NOT NULL,
    body       TEXT,
    published  BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO authors (name, email) VALUES
    ('Alice', 'alice@blog.com'),
    ('Bob', 'bob@blog.com');

INSERT INTO posts (author_id, title, body, published) VALUES
    (1, 'Hello World', 'My first post!', true),
    (1, 'Draft Post', 'Work in progress...', false),
    (2, 'Bob''s Post', 'Content here', true);

-- Query: all published posts
SELECT title, created_at FROM posts WHERE published = true;

-- Query: count posts per author
SELECT author_id, COUNT(*) AS post_count
FROM posts
GROUP BY author_id;