Beginner

# One-time setup: Create PostgreSQL container
docker run --name postgres-tutorial \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 \
  -d postgres:16
# => Container created with PostgreSQL 16, default user 'postgres', password 'password'
# => Port 5432 exposed for external connections

# Connect to PostgreSQL using psql
docker exec -it postgres-tutorial psql -U postgres
# => Connects as postgres user (superuser with all privileges)

-- Simple query to verify connection
SELECT version();
-- => Calls built-in function version() (no parameters needed)
-- => Returns single row with single column (text)
-- => Example output: "PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit"
-- => Confirms PostgreSQL server is running and accessible

-- Check current date and time
SELECT NOW();
-- => Calls built-in function NOW() (returns current timestamp)
-- => Returns single row: type TIMESTAMPTZ (timestamp with timezone)
-- => Example output: "2025-12-29 08:08:50.123456+00"
-- => Microsecond precision (6 decimal places)
-- => Timezone offset shown as +00 (UTC)

-- Simple arithmetic
SELECT 2 + 2 AS result;
-- => AS result creates column alias (displayed as column name)
-- => Returns single row with single column named 'result'
-- => Output: 4 (type INTEGER)

-- Create a new database
CREATE DATABASE first_db;
-- => Creates database 'first_db' with default UTF8 encoding
-- => Owner is current user (postgres)

-- List all databases
\l
-- => Shows all databases: postgres, template0, template1, first_db
-- => Displays: name, owner, encoding, collation, privileges

-- Connect to the new database
\c first_db;
-- => Switches connection from postgres to first_db
-- => Output: "You are now connected to database 'first_db' as user 'postgres'"

-- Verify current database
SELECT current_database();
-- => Returns current database name: 'first_db'

-- Drop database (must disconnect first)
\c postgres;
-- => Switch back to postgres (cannot drop database you're connected to)
DROP DATABASE first_db;
-- => Deletes database 'first_db' and all contents
-- => WARNING: Irreversible operation!

-- Create database for this example
CREATE DATABASE example_3;
-- => Creates new database named 'example_3'
-- => Encoding defaults to UTF8 (supports international characters)
\c example_3;
-- => Switches current connection to example_3 database
-- => All subsequent commands execute in this database

-- Create a simple table
CREATE TABLE users (
    id INTEGER,           -- => Integer column for user ID
    name VARCHAR(100),    -- => Variable-length string up to 100 characters
    email VARCHAR(100),   -- => Variable-length string for email addresses
    age INTEGER           -- => Integer column for age (no upper limit enforced yet)
);
-- => Creates table 'users' with 4 columns
-- => Table initially empty (0 rows)

-- Insert sample data
INSERT INTO users (id, name, email, age)
VALUES
    (1, 'Alice', 'alice@example.com', 30),
    (2, 'Bob', 'bob@example.com', 25),
    (3, 'Charlie', 'charlie@example.com', 35);
-- => Inserts 3 rows in single statement (more efficient than 3 separate INSERTs)
-- => Row 1: id=1, name='Alice', email='alice@example.com', age=30
-- => Row 2: id=2, name='Bob', email='bob@example.com', age=25
-- => Row 3: id=3, name='Charlie', email='charlie@example.com', age=35

-- Select all columns, all rows
SELECT * FROM users;
-- => Scans entire users table (3 rows)
-- => Returns all 3 rows with columns: id | name | email | age
-- => Output format: tabular display with column headers

-- Select specific columns
SELECT name, age FROM users;
-- => Scans all 3 rows but returns only 'name' and 'age' columns
-- => Reduces data transfer (2 columns instead of 4)
-- => Output: Alice 30, Bob 25, Charlie 35

-- Select with expressions
SELECT name, age, age + 10 AS age_in_10_years FROM users;
-- => Computes age + 10 for each row (calculation happens per row)
-- => Creates new column 'age_in_10_years' (not stored, computed on-the-fly)
-- => Alice: 30 + 10 = 40, Bob: 25 + 10 = 35, Charlie: 35 + 10 = 45
-- => Original age column unchanged in table

CREATE DATABASE example_4;
-- => Creates database 'example_4'
\c example_4;
-- => Switches to example_4 database
CREATE TABLE products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10, 2),  -- => Decimal with 10 total digits, 2 after decimal point
    in_stock BOOLEAN       -- => TRUE/FALSE/NULL allowed
);
-- => Creates table 'products' with 4 columns
-- => No constraints yet (NULLs allowed, no uniqueness enforced)

-- Insert single row with all columns
INSERT INTO products (id, name, price, in_stock)
VALUES (1, 'Laptop', 999.99, true);
-- => Inserts 1 row: id=1, name='Laptop', price=999.99, in_stock=TRUE
-- => Output: "INSERT 0 1" (0 is OID, 1 is row count)
-- => Table now contains 1 row

-- Insert multiple rows at once (more efficient)
INSERT INTO products (id, name, price, in_stock)
VALUES
    (2, 'Mouse', 29.99, true),
    (3, 'Keyboard', 79.99, false),
    (4, 'Monitor', 299.99, true);
-- => Inserts 3 rows in single transaction (atomic operation)
-- => Row 2: id=2, name='Mouse', price=29.99, in_stock=TRUE
-- => Row 3: id=3, name='Keyboard', price=79.99, in_stock=FALSE
-- => Row 4: id=4, name='Monitor', price=299.99, in_stock=TRUE
-- => Output: "INSERT 0 3" (3 rows inserted)
-- => More efficient than 3 separate INSERT statements (single network round-trip)

-- Insert partial columns (others become NULL)
INSERT INTO products (id, name)
VALUES (5, 'Webcam');
-- => Only id and name specified (price and in_stock not provided)
-- => Row data inserted: id=5, name='Webcam', price=NULL, in_stock=NULL
-- => NULL indicates missing/unknown value (not zero or false)
-- => Table now contains 5 rows total

-- Verify data
SELECT * FROM products;
-- => Retrieves all 5 rows from products table
-- => Shows all columns: id | name | price | in_stock
-- => Rows 1-4 have complete data, row 5 has NULLs for price and in_stock

CREATE DATABASE example_5;
-- => Creates database 'example_5'
\c example_5;
-- => Switches to example_5 database
CREATE TABLE inventory (
    id INTEGER,
    item VARCHAR(100),
    quantity INTEGER,
    price DECIMAL(10, 2)
);
-- => Creates table with 4 columns (no constraints)
INSERT INTO inventory (id, item, quantity, price)
VALUES
    (1, 'Apples', 100, 1.50),
    (2, 'Bananas', 150, 0.75),
    (3, 'Oranges', 80, 2.00);
-- => Inserts 3 rows (initial inventory state)
-- => Row 1: id=1, item='Apples', quantity=100, price=1.50
-- => Row 2: id=2, item='Bananas', quantity=150, price=0.75
-- => Row 3: id=3, item='Oranges', quantity=80, price=2.00

-- Update single row
UPDATE inventory
SET quantity = 120
WHERE id = 1;
-- => WHERE clause filters to row with id=1 (Apples row)
-- => Sets quantity column to 120 (was 100)
-- => Other columns unchanged (item, price remain same)
-- => Affected rows: 1 (output: "UPDATE 1")
-- => Apples row now: id=1, item='Apples', quantity=120, price=1.50

-- Update multiple columns
UPDATE inventory
SET quantity = 200, price = 0.80
WHERE item = 'Bananas';
-- => WHERE filters by item name (string comparison)
-- => Updates TWO columns: quantity and price
-- => quantity: 150 -> 200, price: 0.75 -> 0.80
-- => Affected rows: 1 (output: "UPDATE 1")
-- => Bananas row now: id=2, item='Bananas', quantity=200, price=0.80

-- Update with calculation
UPDATE inventory
SET price = price * 1.10
WHERE price < 2.00;
-- => WHERE finds rows with price less than 2.00 (Apples and Bananas)
-- => price * 1.10 calculates new price (10% increase)
-- => Apples: 1.50 * 1.10 = 1.65
-- => Bananas: 0.80 * 1.10 = 0.88
-- => Affected rows: 2 (output: "UPDATE 2")
-- => Oranges unchanged (price 2.00 not < 2.00)

-- Verify updates
SELECT * FROM inventory;
-- => Shows current state after all updates
-- => Apples: quantity=120, price=1.65
-- => Bananas: quantity=200, price=0.88
-- => Oranges: quantity=80, price=2.00 (unchanged)

-- Delete specific row
DELETE FROM inventory
WHERE id = 3;
-- => WHERE clause targets single row (id=3, Oranges)
-- => Removes row from table permanently
-- => Affected rows: 1 (output: "DELETE 1")
-- => Table now contains 2 rows (Apples, Bananas)

-- DANGEROUS: Update without WHERE affects ALL rows
UPDATE inventory SET quantity = 0;
-- => No WHERE clause! Affects ALL rows in table
-- => Sets quantity to 0 for Apples AND Bananas
-- => Affected rows: 2 (output: "UPDATE 2")
-- => WARNING: Common mistake causing data loss!

-- DANGEROUS: Delete without WHERE removes ALL rows
DELETE FROM inventory;
-- => No WHERE clause! Deletes ALL rows in table
-- => Affected rows: 2 (output: "DELETE 2")
-- => Table structure remains (columns defined)
-- => Table now empty (0 rows)
-- => Use TRUNCATE TABLE for faster empty operation

CREATE DATABASE example_6;
\c example_6;
CREATE TABLE numeric_types (
    id SERIAL,                    -- => Auto-incrementing integer (shorthand for INTEGER with sequence)
    small_int SMALLINT,           -- => -32,768 to 32,767
    regular_int INTEGER,          -- => -2,147,483,648 to 2,147,483,647
    big_int BIGINT,               -- => Very large range (19 digits)
    exact_decimal DECIMAL(10, 2), -- => Exact precision: 10 total digits, 2 after decimal
    exact_numeric NUMERIC(8, 3),  -- => Same as DECIMAL: 8 digits total, 3 after decimal
    float_num REAL,               -- => 4-byte floating point (approximate)
    double_num DOUBLE PRECISION   -- => 8-byte floating point (approximate)
);
INSERT INTO numeric_types
    (small_int, regular_int, big_int, exact_decimal, exact_numeric, float_num, double_num)
VALUES
    (100, 50000, 9223372036854775807, 12345.67, 12345.678, 3.14159, 3.141592653589793);
-- => Inserts single row demonstrating each type's precision and range
-- => small_int: 100 (within -32,768 to 32,767 range)
-- => regular_int: 50000 (within -2 billion to +2 billion range)
-- => big_int: 9223372036854775807 (maximum BIGINT value, 2^63 - 1)
-- => exact_decimal: 12345.67 (10 total digits, 2 after decimal point - exact)
-- => exact_numeric: 12345.678 (8 total digits, 3 after decimal - exact)
-- => float_num: 3.14159 (approximate binary floating-point)
-- => double_num: 3.141592653589793 (approximate, double precision)
SELECT * FROM numeric_types;
-- => Returns 1 row with all 8 columns
-- => id=1 (SERIAL auto-generated from sequence)
-- => All values stored as specified
-- => REAL and DOUBLE PRECISION may display slight approximations in output

-- Arithmetic with exact types
SELECT
    exact_decimal + 100.50 AS decimal_sum,
    exact_numeric * 2 AS numeric_doubled,
    float_num * 2 AS float_doubled
FROM numeric_types;
-- => exact_decimal + 100.50: 12345.67 + 100.50 = 12446.17 (exact arithmetic)
-- => exact_numeric * 2: 12345.678 * 2 = 24691.356 (exact multiplication)
-- => float_num * 2: 3.14159 * 2 ≈ 6.28318 (may have tiny rounding error)
-- => DECIMAL/NUMERIC use base-10 storage (exact for financial calculations)
-- => REAL/DOUBLE use binary floating-point (faster but approximate)

CREATE DATABASE example_7;
-- => Creates new database named 'example_7' with UTF8 encoding
\c example_7;
-- => Connects to example_7 database (disconnects from previous database)
CREATE TABLE text_types (
    fixed_char CHAR(5),
    -- => Exactly 5 characters, space-padded if shorter (e.g., 'AB' becomes 'AB   ')
    -- => Rarely used in modern applications due to padding behavior
    var_char VARCHAR(50),
    -- => Variable-length string up to 50 characters (no padding)
    -- => Storage size = actual string length + 1 byte for length tracking
    unlimited_text TEXT
    -- => Unlimited length text (practical limit: 1 GB per field)
    -- => Same performance as VARCHAR, no length restriction needed
);
-- => Creates text_types table with 3 columns demonstrating text type differences
INSERT INTO text_types (fixed_char, var_char, unlimited_text)
-- => Inserts rows into text_types table with text values
VALUES
    ('AB', 'Hello', 'This is a very long text that can be as long as needed without worrying about length limits'),
    -- => Row 1: fixed_char is 'AB' (will be padded to 'AB   ')
    -- => var_char is 'Hello' (5 chars, no padding)
    -- => unlimited_text is 109 characters long
    ('ABCDE', 'World', 'Another long text example');
    -- => Row 2: fixed_char is 'ABCDE' (exactly 5 chars, no padding needed)
    -- => var_char is 'World' (5 chars)
    -- => unlimited_text is 27 characters long
-- => Two rows inserted successfully with different text values

SELECT
    fixed_char,
    -- => Returns fixed_char values: 'AB   ' and 'ABCDE' (note padding on first row)
    LENGTH(fixed_char) AS char_len,
    -- => Returns length including padding: 5 for both rows
    -- => CHAR always returns full declared length
    var_char,
    -- => Returns var_char values: 'Hello' and 'World' (no padding)
    LENGTH(var_char) AS varchar_len,
    -- => Returns actual string length: 5 for both rows
    -- => VARCHAR returns only used characters
    unlimited_text
    -- => Returns full text content without truncation
FROM text_types;
-- => Retrieves all columns to demonstrate text type behaviors

-- String concatenation using || operator
SELECT
    var_char || ' ' || 'PostgreSQL' AS concatenated
    -- => Concatenates var_char value + space + 'PostgreSQL'
    -- => Row 1 result: 'Hello PostgreSQL'
    -- => Row 2 result: 'World PostgreSQL'
FROM text_types;
-- => Returns 2 rows with concatenated strings

-- CHAR padding comparison demonstration
SELECT
    fixed_char = 'AB' AS equals_without_padding,
    -- => Compares 'AB   ' with 'AB' (includes padding in comparison)
    -- => Returns false because 'AB   ' ≠ 'AB'
    TRIM(fixed_char) = 'AB' AS equals_with_trim
    -- => TRIM removes trailing spaces: TRIM('AB   ') = 'AB'
    -- => Returns true because trimmed value matches
FROM text_types
WHERE fixed_char = 'AB   ';
-- => Filters for rows where fixed_char is 'AB   ' (with padding)
-- => Returns 1 row demonstrating padding comparison issue

CREATE DATABASE example_8;
-- => Creates new database named 'example_8' with UTF8 encoding
\c example_8;
-- => Connects to example_8 database (disconnects from previous database)
CREATE TABLE events (
    id SERIAL,
    -- => Auto-incrementing integer column (starts at 1)
    event_date DATE,
    -- => Date only (YYYY-MM-DD format, no time component)
    event_time TIME,
    -- => Time only (HH:MM:SS format, no date component)
    created_at TIMESTAMP,
    -- => Date and time without timezone (local time)
    updated_at TIMESTAMPTZ,
    -- => Date and time with timezone (stores UTC, displays in client timezone)
    duration INTERVAL
    -- => Time span (supports days, hours, minutes, seconds, microseconds)
);
-- => Creates events table with 6 columns for different temporal types
INSERT INTO events (event_date, event_time, created_at, updated_at, duration)
-- => Inserts rows into events table with specific columns
VALUES
    ('2025-12-29', '14:30:00', '2025-12-29 14:30:00', '2025-12-29 14:30:00+07', '2 hours 30 minutes'),
    -- => Row 1: event_date is 2025-12-29, event_time is 14:30:00
    -- => created_at is 2025-12-29 14:30:00 (no timezone)
    -- => updated_at is 2025-12-29 14:30:00+07 (timezone +07:00 specified)
    -- => duration is 2 hours 30 minutes interval
    ('2025-12-30', '09:00:00', NOW(), NOW(), '1 day 3 hours');
    -- => Row 2: event_date is 2025-12-30, event_time is 09:00:00
    -- => NOW() returns current timestamp with timezone (called twice)
    -- => duration is 1 day 3 hours interval
-- => Two rows inserted successfully
SELECT
    event_date,
    event_time,
    created_at,
    updated_at,
    duration
FROM events;
-- => Retrieves all columns from events table (2 rows returned)
-- => Shows date/time values in their respective formats

-- Date arithmetic operations
SELECT
    event_date + INTERVAL '7 days' AS one_week_later,
    -- => Adds 7 days to event_date using INTERVAL
    -- => 2025-12-29 becomes 2026-01-05, 2025-12-30 becomes 2026-01-06
    updated_at - INTERVAL '1 hour' AS one_hour_ago,
    -- => Subtracts 1 hour from updated_at timestamp
    -- => Timezone preserved in result
    event_date + 7 AS seven_days_later_shorthand
    -- => Shorthand: adding integer adds days to DATE type
    -- => Same as INTERVAL '7 days' but more concise
FROM events;
-- => Returns 3 computed columns showing date arithmetic results

-- Extract date/time components
SELECT
    EXTRACT(YEAR FROM event_date) AS year,
    -- => Extracts year component from event_date (returns 2025)
    EXTRACT(MONTH FROM event_date) AS month,
    -- => Extracts month component (returns 12 for December)
    EXTRACT(DAY FROM event_date) AS day,
    -- => Extracts day of month (returns 29, 30)
    EXTRACT(HOUR FROM event_time) AS hour
    -- => Extracts hour component from event_time (returns 14, 9)
FROM events;
-- => Returns 4 integer columns with extracted date/time parts
-- => Useful for grouping by year/month or filtering by hour

CREATE DATABASE example_9;
-- => Creates new database named 'example_9' with UTF8 encoding
\c example_9;
-- => Connects to example_9 database (disconnects from previous database)
CREATE TABLE users (
    username VARCHAR(50),
    -- => Variable-length string up to 50 characters for username
    is_active BOOLEAN,
    -- => Boolean column: accepts true, false, or NULL values
    -- => Storage: 1 byte per value (efficient for flag columns)
    is_verified BOOLEAN DEFAULT false
    -- => Boolean with default value of false
    -- => If is_verified not specified in INSERT, automatically set to false
);
-- => Creates users table with 3 columns including boolean flags
INSERT INTO users (username, is_active)
-- => Inserts rows specifying only username and is_active columns
-- => is_verified will use default value (false)
VALUES
    ('alice', true),
    -- => Row 1: username='alice', is_active=true, is_verified=false (default)
    ('bob', false),
    -- => Row 2: username='bob', is_active=false, is_verified=false (default)
    ('charlie', true);
    -- => Row 3: username='charlie', is_active=true, is_verified=false (default)
-- => Three rows inserted successfully with mixed boolean values
SELECT * FROM users;
-- => Retrieves all columns from users table (3 rows returned)
-- => Shows username, is_active, and is_verified values
-- => All is_verified values are false (from DEFAULT)

-- Boolean comparisons: explicit true comparison
SELECT username, is_active
FROM users
WHERE is_active = true;
-- => Filters rows where is_active column explicitly equals true
-- => Returns 2 rows: alice and charlie
-- => bob is excluded (is_active=false)

SELECT username, is_active
FROM users
WHERE is_active;
-- => Filters rows where is_active is truthy
-- => Shorthand syntax: no '= true' comparison needed
-- => Returns same 2 rows: alice and charlie
-- => More idiomatic than 'WHERE is_active = true'

SELECT username, is_verified
FROM users
WHERE NOT is_verified;
-- => Filters rows where is_verified is NOT true (false or NULL)
-- => Returns all 3 users (all have is_verified = false)
-- => NOT operator negates boolean value

-- Generate UUID manually using built-in function
SELECT gen_random_uuid() AS random_id;
-- => Calls gen_random_uuid() function to generate random UUID
-- => Returns single row with UUID like 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
-- => Each call generates different UUID (cryptographically random)
-- => UUID format: 32 hex digits separated by hyphens (8-4-4-4-12)

CREATE DATABASE example_10;
\c example_10;
CREATE TABLE contacts (
    id SERIAL,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);
INSERT INTO contacts (name, email, phone)
VALUES
    ('Alice', 'alice@example.com', '555-1234'),
    ('Bob', NULL, '555-5678'),              -- => Email is NULL (unknown)
    ('Charlie', 'charlie@example.com', NULL); -- => Phone is NULL
-- => Inserts 3 rows with different NULL patterns
-- => Row 1: Alice has both email and phone
-- => Row 2: Bob missing email (NULL), has phone
-- => Row 3: Charlie has email, missing phone (NULL)

-- Check for NULL values
SELECT name, email
FROM contacts
WHERE email IS NULL;
-- => WHERE clause checks for NULL using IS NULL operator
-- => Evaluates email column for each row
-- => Alice: email='alice@example.com' (NOT NULL, excluded)
-- => Bob: email=NULL (matches IS NULL, included)
-- => Charlie: email='charlie@example.com' (NOT NULL, excluded)
-- => Returns 1 row: Bob with email=NULL

-- This DOES NOT work correctly
SELECT name, email
FROM contacts
WHERE email = NULL;
-- => Uses equality operator = with NULL (WRONG approach!)
-- => NULL = NULL evaluates to NULL (not TRUE or FALSE)
-- => WHERE requires TRUE to include row
-- => All 3 rows: email = NULL evaluates to NULL → excluded
-- => Returns 0 rows (empty result set)
-- => Common mistake causing "missing data" bugs!

-- Check for NOT NULL
SELECT name, phone
FROM contacts
WHERE phone IS NOT NULL;
-- => IS NOT NULL checks for presence of value (opposite of IS NULL)
-- => Alice: phone='555-1234' (NOT NULL, included)
-- => Bob: phone='555-5678' (NOT NULL, included)
-- => Charlie: phone=NULL (excluded)
-- => Returns 2 rows: Alice and Bob

-- COALESCE provides default for NULL values
SELECT
    name,
    COALESCE(email, 'no-email@example.com') AS email,
    COALESCE(phone, 'No phone') AS phone
FROM contacts;
-- => COALESCE(value, default) returns first non-NULL argument
-- => Alice: email='alice@example.com' (not NULL, returns original), phone='555-1234'
-- => Bob: email=NULL → COALESCE returns 'no-email@example.com', phone='555-5678'
-- => Charlie: email='charlie@example.com', phone=NULL → COALESCE returns 'No phone'
-- => All 3 rows returned with NULLs replaced by defaults

-- NULLIF returns NULL if two values are equal
SELECT
    name,
    NULLIF(email, '') AS email_or_null
FROM contacts;
-- => NULLIF(value1, value2) returns NULL if value1 = value2, otherwise returns value1
-- => Useful for converting empty strings to NULL
-- => Alice: NULLIF('alice@example.com', '') → 'alice@example.com' (not equal to '')
-- => Bob: NULLIF(NULL, '') → NULL (already NULL)
-- => Charlie: NULLIF('charlie@example.com', '') → 'charlie@example.com'
-- => If Bob had email='' instead of NULL: NULLIF('', '') → NULL (converts empty to NULL)

CREATE DATABASE example_11;
-- => Creates database for WHERE clause examples
\c example_11;
-- => Switches to example_11 database

CREATE TABLE products (
    id SERIAL,
    -- => id: auto-incrementing product identifier starting at 1
    -- => Type: SERIAL generates sequence automatically
    name VARCHAR(100),
    -- => name: product name (up to 100 characters)
    -- => Example values: 'Laptop', 'Mouse', 'Desk Chair'
    category VARCHAR(50),
    -- => category: product category (Electronics, Furniture, Kitchen)
    -- => Used for filtering products by type
    price DECIMAL(10, 2),
    -- => price: product price (8 digits before decimal, 2 after)
    -- => Exact precision for monetary values
    stock INTEGER
    -- => stock: available inventory quantity (whole numbers only)
    -- => Zero means out of stock
);
-- => Creates products table with 5 columns for product catalog

INSERT INTO products (name, category, price, stock)
VALUES
    ('Laptop', 'Electronics', 999.99, 15),
    -- => Row 1: Electronics product, price $999.99, 15 in stock
    ('Mouse', 'Electronics', 29.99, 50),
    -- => Row 2: Electronics product, lower price, high stock
    ('Desk Chair', 'Furniture', 199.99, 8),
    -- => Row 3: Furniture category, low stock
    ('Monitor', 'Electronics', 299.99, 0),
    -- => Row 4: Electronics, OUT OF STOCK (stock=0)
    ('Coffee Mug', 'Kitchen', 12.99, 100);
    -- => Row 5: Kitchen category, highest stock
-- => Inserts 5 products across 3 categories
-- Single condition
SELECT name, price
-- => Selects only name and price columns (not all columns)
FROM products
-- => Scans all 5 rows in products table
WHERE category = 'Electronics';
-- => WHERE clause filters rows before returning results
-- => Checks each row: category column equals 'Electronics' (string comparison, case-sensitive)
-- => Matching rows: Laptop, Mouse, Monitor (3 out of 5 rows pass filter)
-- => Returns 3 rows: Laptop (999.99), Mouse (29.99), Monitor (299.99)
-- => Excludes: Desk Chair (Furniture), Coffee Mug (Kitchen)
-- => Filtered rows never reach result set (discarded before SELECT)

-- Multiple conditions with AND
SELECT name, price, stock
FROM products
WHERE category = 'Electronics' AND price < 500;
-- => AND requires BOTH conditions true
-- => First condition: category = 'Electronics' (3 rows match)
-- => Second condition: price < 500 (filters the 3 rows)
-- => Returns 2 rows: Mouse (29.99, stock 50), Monitor (299.99, stock 0)
-- => Excludes Laptop (999.99 > 500)

-- Multiple conditions with OR
SELECT name, category
FROM products
WHERE category = 'Furniture' OR price > 500;
-- => OR requires AT LEAST ONE condition true
-- => First condition: category = 'Furniture' (1 row: Desk Chair)
-- => Second condition: price > 500 (1 row: Laptop)
-- => Returns 2 rows: Laptop (Electronics), Desk Chair (Furniture)
-- => Both conditions evaluated for each row

-- Comparison operators
SELECT name, price
FROM products
WHERE price >= 100 AND price <= 300;
-- => >= means greater than or equal to (100 included)
-- => <= means less than or equal to (300 included)
-- => Checks: 100 <= price <= 300 (inclusive range)
-- => Evaluates both conditions: price >= 100 AND price <= 300
-- => Desk Chair: 199.99 >= 100 (true) AND 199.99 <= 300 (true) → included
-- => Monitor: 299.99 >= 100 (true) AND 299.99 <= 300 (true) → included
-- => Laptop: 999.99 <= 300 (false) → excluded despite >= 100 being true
-- => Returns 2 rows: Desk Chair (199.99), Monitor (299.99)
-- => Excludes: Laptop (999.99 too high), Mouse (29.99 too low), Coffee Mug (12.99 too low)

-- BETWEEN for ranges
SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 300;
-- => BETWEEN is shorthand for >= AND <=
-- => Equivalent to: price >= 100 AND price <= 300
-- => BETWEEN is INCLUSIVE (includes 100 and 300)
-- => Returns same 2 rows as above: Desk Chair (199.99), Monitor (299.99)

-- IN for multiple values
SELECT name, category
FROM products
WHERE category IN ('Electronics', 'Kitchen');
-- => IN checks if value matches ANY in list
-- => Equivalent to: category = 'Electronics' OR category = 'Kitchen'
-- => More readable than multiple OR conditions
-- => Returns 4 rows: Laptop, Mouse, Monitor (Electronics), Coffee Mug (Kitchen)
-- => Excludes: Desk Chair (Furniture not in list)

-- NOT to negate conditions
SELECT name, stock
FROM products
WHERE NOT stock = 0;
-- => NOT inverts boolean result (true becomes false, false becomes true)
-- => Equivalent to: stock != 0 or stock <> 0
-- => Returns rows where stock is NOT zero
-- => Returns 4 rows: Laptop (15), Mouse (50), Desk Chair (8), Coffee Mug (100)
-- => Excludes: Monitor (stock = 0)

CREATE DATABASE example_12;
-- => Creates database for ORDER BY examples
\c example_12;
-- => Switches to example_12 database

CREATE TABLE employees (
    id SERIAL,
    -- => id: auto-incrementing employee identifier
    -- => Type: SERIAL for unique identifiers
    name VARCHAR(100),
    -- => name: employee name (up to 100 characters)
    -- => Used for display in result sets
    department VARCHAR(50),
    -- => department: Engineering or Sales (two departments)
    -- => Used for grouping and sorting employees
    salary DECIMAL(10, 2),
    -- => salary: annual salary (exact precision)
    -- => Range in dataset: $75,000 to $105,000
    hire_date DATE
    -- => hire_date: date employee was hired (YYYY-MM-DD format)
    -- => Used for seniority calculations and sorting
);
-- => Creates employees table with 5 columns for employee records

INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('Alice', 'Engineering', 95000, '2020-03-15'),
    -- => Alice: Engineering, $95k, hired 2020
    ('Bob', 'Sales', 75000, '2019-06-01'),
    -- => Bob: Sales, $75k (lowest salary)
    ('Charlie', 'Engineering', 105000, '2021-01-10'),
    -- => Charlie: Engineering, $105k (highest salary)
    ('Diana', 'Sales', 80000, '2018-11-20'),
    -- => Diana: Sales, $80k, earliest hire date
    ('Eve', 'Engineering', 95000, '2022-02-28');
    -- => Eve: Engineering, $95k (same as Alice), latest hire date
-- => Inserts 5 employees with varying salaries and hire dates
-- Sort by salary ascending (default)
SELECT name, salary
FROM employees
ORDER BY salary;
-- => ORDER BY salary sorts rows by salary column (low to high)
-- => ASC is default (ascending order, can be omitted)
-- => Compares: 75000 < 80000 < 95000 < 95000 < 105000
-- => Alice and Eve both have 95000 (tie - order between them undefined)
-- => Returns 5 rows: Bob (75000), Diana (80000), Alice (95000), Eve (95000), Charlie (105000)

-- Sort by salary descending
SELECT name, salary
FROM employees
ORDER BY salary DESC;
-- => DESC reverses sort order (high to low)
-- => Compares: 105000 > 95000 > 95000 > 80000 > 75000
-- => Alice and Eve still tied at 95000 (order between them undefined)
-- => Returns: Charlie (105000), Alice (95000), Eve (95000), Diana (80000), Bob (75000)

-- Sort by multiple columns (department, then salary)
SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC;
-- => ORDER BY with multiple columns creates hierarchical sorting
-- => First sort key: department (ascending by default, alphabetical)
-- => Grouping: Engineering comes before Sales (E < S alphabetically)
-- => Second sort key: salary DESC (descending, within each department)
-- => Engineering group sorted internally: 105000 > 95000 > 95000
-- => Charlie (105000) first, then Alice (95000), then Eve (95000)
-- => Sales group sorted internally: 80000 > 75000
-- => Diana (80000) first, then Bob (75000)
-- => Engineering group (3 rows): Charlie (105000), Alice (95000), Eve (95000)
-- => Sales group (2 rows): Diana (80000), Bob (75000)
-- => Returns 5 rows ordered by department first, salary second
-- => Second sort only matters when first sort values are identical

-- Sort by date
SELECT name, hire_date
FROM employees
ORDER BY hire_date;
-- => Sorts by hire_date column (oldest to newest)
-- => Compares dates: 2018-11-20 < 2019-06-01 < 2020-03-15 < 2021-01-10 < 2022-02-28
-- => Returns chronological order: Diana (2018), Bob (2019), Alice (2020), Charlie (2021), Eve (2022)

-- Sort by expression
SELECT name, salary, salary * 1.10 AS salary_with_raise
FROM employees
ORDER BY salary * 1.10 DESC;
-- => ORDER BY can use expressions (computed on-the-fly)
-- => Calculates salary * 1.10 for each row
-- => Charlie: 105000 * 1.10 = 115500
-- => Alice: 95000 * 1.10 = 104500
-- => Eve: 95000 * 1.10 = 104500
-- => Diana: 80000 * 1.10 = 88000
-- => Bob: 75000 * 1.10 = 82500
-- => Sorts by computed values descending
-- => Returns: Charlie (115500), Alice (104500), Eve (104500), Diana (88000), Bob (82500)

CREATE DATABASE example_13;
-- => Creates new database named 'example_13' with UTF8 encoding
\c example_13;
-- => Connects to example_13 database (disconnects from previous database)
CREATE TABLE articles (
    id SERIAL,
    -- => Auto-incrementing integer primary key column
    title VARCHAR(200),
    -- => Article title (variable-length string, max 200 characters)
    views INTEGER,
    -- => View count (32-bit signed integer)
    published_date DATE
    -- => Publication date (DATE type, format YYYY-MM-DD)
);
-- => Creates articles table with 4 columns for article metadata
INSERT INTO articles (title, views, published_date)
-- => Inserts 8 sample article rows (id auto-generated by SERIAL)
VALUES
    ('Introduction to SQL', 1500, '2025-01-01'),
    -- => Row 1: title, views=1500, published on Jan 1, 2025
    ('Advanced Queries', 2300, '2025-01-05'),
    -- => Row 2: views=2300, published 4 days after row 1
    ('Database Design', 1800, '2025-01-10'),
    -- => Row 3: views=1800, published 5 days after row 2
    ('Performance Tuning', 3200, '2025-01-15'),
    -- => Row 4: views=3200 (highest), published 5 days after row 3
    ('Indexing Strategies', 2700, '2025-01-20'),
    -- => Row 5: views=2700, published 5 days after row 4
    ('Backup and Recovery', 1200, '2025-01-25'),
    -- => Row 6: views=1200 (lowest), published 5 days after row 5
    ('Security Best Practices', 2900, '2025-02-01'),
    -- => Row 7: views=2900, published 7 days after row 6 (new month)
    ('Replication Setup', 1600, '2025-02-05');
    -- => Row 8: views=1600, published 4 days after row 7
-- => Eight rows inserted successfully with varying views and dates

-- Get top 3 most viewed articles
SELECT title, views
FROM articles
ORDER BY views DESC
-- => Sorts all 8 rows by views descending (3200, 2900, 2700, 2300, 1800, 1600, 1500, 1200)
LIMIT 3;
-- => Returns only first 3 rows from sorted result
-- => If index on views exists, PostgreSQL can stop after reading 3 rows
-- => Returns: Performance Tuning (3200), Security Best Practices (2900), Indexing Strategies (2700)

-- Pagination: Page 1 (first 3 articles by date)
SELECT title, published_date
FROM articles
ORDER BY published_date
-- => Sorts 8 rows by published_date ascending (default, oldest first)
LIMIT 3 OFFSET 0;
-- => OFFSET 0 skips 0 rows (starts at row 1)
-- => LIMIT 3 returns first 3 rows after offset
-- => Returns rows 1-3: Introduction to SQL (2025-01-01), Advanced Queries (2025-01-05), Database Design (2025-01-10)

-- Pagination: Page 2 (next 3 articles)
SELECT title, published_date
FROM articles
ORDER BY published_date
-- => Same sort order as page 1 (published_date ascending)
LIMIT 3 OFFSET 3;
-- => OFFSET 3 skips first 3 rows (Introduction, Advanced, Database Design)
-- => LIMIT 3 returns next 3 rows (rows 4-6)
-- => Returns rows 4-6: Performance Tuning (2025-01-15), Indexing Strategies (2025-01-20), Backup and Recovery (2025-01-25)

-- Pagination: Page 3 (final 2 articles - partial page)
SELECT title, published_date
FROM articles
ORDER BY published_date
-- => Same sort order (published_date ascending)
LIMIT 3 OFFSET 6;
-- => OFFSET 6 skips first 6 rows (rows 1-6)
-- => LIMIT 3 requests 3 rows but only 2 remain (rows 7-8)
-- => Returns rows 7-8: Security Best Practices (2025-02-01), Replication Setup (2025-02-05)
-- => Partial page demonstrates handling of last page with fewer rows

-- Get oldest 5 articles (explicit ASC)
SELECT title, published_date
FROM articles
ORDER BY published_date ASC
-- => Explicit ASC (ascending) sort - same as default ORDER BY behavior
-- => Oldest articles appear first
LIMIT 5;
-- => Returns first 5 rows from sorted result
-- => No OFFSET clause means OFFSET 0 (start at row 1)
-- => Returns rows 1-5: Introduction (2025-01-01), Advanced (2025-01-05), Database Design (2025-01-10), Performance (2025-01-15), Indexing (2025-01-20)

CREATE DATABASE example_14;
-- => Creates database for aggregate function examples
\c example_14;
-- => Switches to example_14 database
CREATE TABLE sales (
    id SERIAL,
    -- => id: auto-incrementing sale identifier
    product VARCHAR(100),
    -- => product: product name (can have duplicates for multiple sales)
    quantity INTEGER,
    -- => quantity: number of items sold in this transaction
    price DECIMAL(10, 2),
    -- => price: unit price per item (exact precision for money)
    sale_date DATE
    -- => sale_date: date of sale transaction
);
-- => Creates sales table for tracking individual sales transactions
INSERT INTO sales (product, quantity, price, sale_date)
-- => Inserts 5 sales records
VALUES
    ('Laptop', 5, 999.99, '2025-12-20'),
    -- => Sale 1: 5 Laptops at $999.99 each
    ('Mouse', 20, 29.99, '2025-12-21'),
    -- => Sale 2: 20 Mice at $29.99 each (highest quantity, lowest price)
    ('Keyboard', 15, 79.99, '2025-12-22'),
    -- => Sale 3: 15 Keyboards at $79.99 each
    ('Monitor', 8, 299.99, '2025-12-23'),
    -- => Sale 4: 8 Monitors at $299.99 each
    ('Laptop', 3, 999.99, '2025-12-24');
    -- => Sale 5: 3 Laptops (duplicate product, different quantity/date)
-- Count total sales
SELECT COUNT(*) AS total_sales
FROM sales;
-- => COUNT(*) counts all rows (includes NULLs)
-- => Scans all 5 rows in sales table
-- => Returns single row with single column: 5
-- => Output: total_sales = 5

-- Count non-NULL values in specific column
SELECT COUNT(product) AS product_count
FROM sales;
-- => COUNT(column) counts non-NULL values only
-- => Checks product column in each row (all 5 are non-NULL)
-- => Returns: product_count = 5
-- => If any product value was NULL, count would be less than 5

-- Sum total quantity sold
SELECT SUM(quantity) AS total_quantity
FROM sales;
-- => SUM adds all values in quantity column
-- => Calculation: 5 + 20 + 15 + 8 + 3
-- => Returns single row: total_quantity = 51
-- => SUM ignores NULL values (doesn't treat them as 0)

-- Calculate average price
SELECT AVG(price) AS avg_price
FROM sales;
-- => AVG computes arithmetic mean of price column
-- => Calculation: (999.99 + 29.99 + 79.99 + 299.99 + 999.99) / 5
-- => Returns: avg_price = 481.79
-- => AVG ignores NULL values (doesn't count them in divisor)

-- Find minimum and maximum prices
SELECT
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM sales;
-- => MIN finds smallest value in price column (29.99 for Mouse)
-- => MAX finds largest value in price column (999.99 for Laptop)
-- => Single scan through all 5 rows
-- => Returns single row with two columns: lowest_price=29.99, highest_price=999.99

-- Calculate total revenue
SELECT SUM(quantity * price) AS total_revenue
FROM sales;
-- => Calculates quantity * price for EACH row first
-- => Row 1: 5 * 999.99 = 4,999.95
-- => Row 2: 20 * 29.99 = 599.80
-- => Row 3: 15 * 79.99 = 1,199.85
-- => Row 4: 8 * 299.99 = 2,399.92
-- => Row 5: 3 * 999.99 = 2,999.97
-- => Then SUM: 4,999.95 + 599.80 + 1,199.85 + 2,399.92 + 2,999.97 = 12,199.49
-- => Returns: total_revenue = 12,199.49

-- Multiple aggregates in one query
SELECT
    COUNT(*) AS num_sales,
    SUM(quantity) AS total_qty,
    AVG(price) AS avg_price,
    MIN(sale_date) AS first_sale,
    MAX(sale_date) AS last_sale
FROM sales;
-- => Single table scan computes all 5 aggregates (efficient!)
-- => COUNT(*): 5 (total rows)
-- => SUM(quantity): 51 (total items sold)
-- => AVG(price): 481.79 (average price)
-- => MIN(sale_date): '2025-12-20' (earliest sale)
-- => MAX(sale_date): '2025-12-24' (latest sale)
-- => Returns single row with 5 columns

CREATE DATABASE example_15;
-- => Creates database for GROUP BY examples
\c example_15;
-- => Switches to example_15 database
CREATE TABLE orders (
    id SERIAL,
    -- => id: auto-incrementing order identifier
    customer VARCHAR(100),
    -- => customer: customer name (can have duplicates - same customer, multiple orders)
    product VARCHAR(100),
    -- => product: product ordered (can have duplicates - same product, multiple customers)
    quantity INTEGER,
    -- => quantity: number of items in this order
    total_price DECIMAL(10, 2)
    -- => total_price: total cost for this order (quantity * unit_price)
);
-- => Creates orders table for customer purchase records
INSERT INTO orders (customer, product, quantity, total_price)
-- => Inserts 6 orders from 3 customers
VALUES
    ('Alice', 'Laptop', 1, 999.99),
    -- => Order 1: Alice buys 1 Laptop
    ('Bob', 'Mouse', 2, 59.98),
    -- => Order 2: Bob buys 2 Mice
    ('Alice', 'Keyboard', 1, 79.99),
    -- => Order 3: Alice buys 1 Keyboard (second Alice order)
    ('Charlie', 'Laptop', 2, 1999.98),
    -- => Order 4: Charlie buys 2 Laptops (highest single order value)
    ('Bob', 'Monitor', 1, 299.99),
    -- => Order 5: Bob buys 1 Monitor (second Bob order)
    ('Alice', 'Mouse', 3, 89.97);
    -- => Order 6: Alice buys 3 Mice (third Alice order)
-- Count orders per customer
SELECT customer, COUNT(*) AS num_orders
FROM orders
GROUP BY customer;
-- => GROUP BY splits table into groups by customer name
-- => Alice group: 3 rows (orders 1, 3, 6)
-- => Bob group: 2 rows (orders 2, 5)
-- => Charlie group: 1 row (order 4)
-- => COUNT(*) applied to each group separately
-- => Returns 3 rows: Alice (3), Bob (2), Charlie (1)

-- Total spending per customer
SELECT customer, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;
-- => GROUP BY customer creates 3 groups
-- => SUM(total_price) for Alice: 999.99 + 79.99 + 89.97 = 1169.95
-- => SUM(total_price) for Bob: 59.98 + 299.99 = 359.97
-- => SUM(total_price) for Charlie: 1999.98
-- => ORDER BY sorts results by total_spent descending
-- => Returns: Charlie (1999.98), Alice (1169.95), Bob (359.97)

-- Count and total per product
SELECT
    product,
    COUNT(*) AS times_ordered,
    SUM(quantity) AS total_quantity,
    SUM(total_price) AS total_revenue
FROM orders
GROUP BY product
ORDER BY total_revenue DESC;
-- => GROUP BY product creates 4 groups (Laptop, Mouse, Keyboard, Monitor)
-- => Laptop group: 2 rows (Alice, Charlie)
-- => COUNT(*): 2 times, SUM(quantity): 1+2=3, SUM(total_price): 999.99+1999.98=2999.97
-- => Monitor group: 1 row (Bob)
-- => COUNT(*): 1 time, SUM(quantity): 1, SUM(total_price): 299.99
-- => Mouse group: 2 rows (Bob, Alice)
-- => COUNT(*): 2 times, SUM(quantity): 2+3=5, SUM(total_price): 59.98+89.97=149.95
-- => Keyboard group: 1 row (Alice)
-- => COUNT(*): 1 time, SUM(quantity): 1, SUM(total_price): 79.99
-- => ORDER BY revenue descending: Laptop (2999.97), Monitor (299.99), Mouse (149.95), Keyboard (79.99)

-- HAVING filters groups (not individual rows)
SELECT customer, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(total_price) > 500;
-- => First, GROUP BY creates 3 customer groups
-- => Alice total: 1169.95 (> 500, included)
-- => Bob total: 359.97 (< 500, excluded by HAVING)
-- => Charlie total: 1999.98 (> 500, included)
-- => HAVING clause filters groups AFTER aggregation
-- => Returns 2 rows: Alice (1169.95), Charlie (1999.98)
-- => Bob excluded because total < 500

-- WHERE filters rows BEFORE grouping
SELECT customer, COUNT(*) AS num_orders
FROM orders
WHERE product = 'Laptop'
GROUP BY customer;
-- => WHERE filters rows FIRST (before GROUP BY)
-- => Filters to 2 rows: Alice's Laptop order, Charlie's Laptop order
-- => Then GROUP BY customer creates 2 groups
-- => Alice group: 1 Laptop order, COUNT(*) = 1
-- => Charlie group: 1 Laptop order, COUNT(*) = 1
-- => Returns 2 rows: Alice (1), Charlie (1)
-- => Bob has no Laptop orders, so not in result

CREATE DATABASE example_16;
\c example_16;
-- Create table with various constraints
CREATE TABLE employees (
    id SERIAL,                             -- => Auto-incrementing integer
    email VARCHAR(100) NOT NULL,           -- => Email required (cannot be NULL)
    age INTEGER CHECK (age >= 18),         -- => Age must be at least 18
    salary DECIMAL(10, 2) CHECK (salary > 0), -- => Salary must be positive
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending')),
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE -- => Required, defaults to today
);
-- Valid insert
INSERT INTO employees (email, age, salary, status)
VALUES ('alice@example.com', 25, 75000, 'active');
-- => PostgreSQL validates all constraints before inserting
-- => email NOT NULL: 'alice@example.com' provided (✓ valid)
-- => age CHECK (age >= 18): 25 >= 18 (✓ valid)
-- => salary CHECK (salary > 0): 75000 > 0 (✓ valid)
-- => status CHECK (status IN (...)): 'active' in allowed list (✓ valid)
-- => hire_date defaults to CURRENT_DATE (e.g., '2026-01-02')
-- => Row inserted successfully: id=1, email='alice@example.com', age=25, salary=75000, status='active', hire_date='2026-01-02'

-- Invalid: NULL email (violates NOT NULL)
INSERT INTO employees (age, salary, status)
VALUES (30, 80000, 'active');
-- => email column not specified in INSERT (defaults to NULL)
-- => Constraint check: email NOT NULL violated
-- => PostgreSQL rejects INSERT with error message
-- => ERROR: null value in column "email" violates not-null constraint
-- => No row inserted, table unchanged

-- Invalid: age < 18 (violates CHECK)
INSERT INTO employees (email, age, salary, status)
VALUES ('bob@example.com', 16, 50000, 'active');
-- => Constraint check: age CHECK (age >= 18)
-- => Evaluates: 16 >= 18 → FALSE
-- => PostgreSQL rejects INSERT
-- => ERROR: new row violates check constraint "employees_age_check"
-- => No row inserted

-- Invalid: salary <= 0 (violates CHECK)
INSERT INTO employees (email, age, salary, status)
VALUES ('charlie@example.com', 28, -5000, 'active');
-- => Constraint check: salary CHECK (salary > 0)
-- => Evaluates: -5000 > 0 → FALSE
-- => Negative salary not allowed
-- => ERROR: new row violates check constraint "employees_salary_check"
-- => No row inserted

-- Invalid: status not in allowed values
INSERT INTO employees (email, age, salary, status)
VALUES ('diana@example.com', 32, 90000, 'retired');
-- => Constraint check: status IN ('active', 'inactive', 'pending')
-- => Evaluates: 'retired' IN ('active', 'inactive', 'pending') → FALSE
-- => 'retired' not in allowed list
-- => ERROR: new row violates check constraint "employees_status_check"
-- => No row inserted

-- Valid: use default hire_date
INSERT INTO employees (email, age, salary, status)
VALUES ('eve@example.com', 27, 70000, 'pending');
-- => hire_date column not specified (uses DEFAULT)
-- => DEFAULT CURRENT_DATE evaluates to today's date
-- => All constraints pass validation
-- => Row inserted: id=2, email='eve@example.com', age=27, salary=70000, status='pending', hire_date=(today)
-- => Success

CREATE DATABASE example_17;
-- => Creates database 'example_17'
\c example_17;
-- => Switches connection to example_17 database

-- SERIAL creates auto-incrementing integer primary key
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    -- => id: auto-incrementing integer (starts at 1, increments by 1)
    -- => SERIAL is shorthand for INTEGER + sequence creation
    -- => PRIMARY KEY enforces uniqueness and NOT NULL automatically
    -- => Creates index on id column for fast lookups
    username VARCHAR(50) NOT NULL,
    -- => username: user's login name (required, up to 50 chars)
    -- => NOT NULL means this column cannot be empty
    email VARCHAR(100) NOT NULL
    -- => email: user's email address (required, up to 100 chars)
);
-- => Creates users table with auto-incrementing primary key
-- Insert without specifying id
INSERT INTO users (username, email)
-- => INSERT into users table (id column omitted)
-- => PostgreSQL automatically generates id value
VALUES ('alice', 'alice@example.com');
-- => Row data: username='alice', email='alice@example.com'
-- => SERIAL sequence generates next value: 1
-- => Actual row inserted: id=1, username='alice', email='alice@example.com'
INSERT INTO users (username, email)
-- => Second INSERT (id still omitted)
VALUES ('bob', 'bob@example.com');
-- => Row data: username='bob', email='bob@example.com'
-- => SERIAL sequence increments: 2
-- => Actual row inserted: id=2, username='bob', email='bob@example.com'

-- Verify auto-generated IDs
SELECT * FROM users;
-- => Query all columns (*)
-- => Returns 2 rows with auto-generated IDs
-- => Row 1: id=1, username='alice', email='alice@example.com'
-- => Row 2: id=2, username='bob', email='bob@example.com'

-- Cannot insert duplicate primary key
INSERT INTO users (id, username, email)
-- => INSERT into users table begins
VALUES (1, 'charlie', 'charlie@example.com');
-- => Row data values follow
-- => ERROR: duplicate key value violates unique constraint "users_pkey"

-- Cannot insert NULL primary key
INSERT INTO users (id, username, email)
-- => INSERT into users table begins
VALUES (NULL, 'diana', 'diana@example.com');
-- => Row data values follow
-- => ERROR: null value in column "id" violates not-null constraint

-- Composite primary key (multiple columns)
CREATE TABLE order_items (
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id) -- => Uniqueness enforced on combination
);
INSERT INTO order_items (order_id, product_id, quantity)
-- => INSERT into order_items table begins
VALUES (1, 101, 5), (1, 102, 3), (2, 101, 2);
-- => Row data values follow
-- => Success: combinations are unique
INSERT INTO order_items (order_id, product_id, quantity)
-- => INSERT into order_items table begins
VALUES (1, 101, 10);
-- => Row data values follow
-- => ERROR: duplicate key violates unique constraint (order_id=1, product_id=101 already exists)