# Learn PSG — Postgres Zero-Threshold Tutorial (Full Content)

> This file contains the complete course content including all 20 lessons with full explanations and SQL examples.
> For a concise overview, see /llm.txt

---

## Project Overview

An interactive SQL tutorial that runs entirely in the browser. No installation, no backend, no login required. Uses pglite (PostgreSQL compiled to WebAssembly) to execute real SQL queries client-side. All data and progress persist in IndexedDB.

**URL**: https://learnpsg.com
**Tech Stack**: Next.js (App Router) + React + TypeScript + Tailwind CSS + shadcn/ui + @electric-sql/pglite

---

## Database Schema

### users
```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
```

### posts
```sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

### comments
```sql
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    post_id INTEGER NOT NULL REFERENCES posts(id),
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);
```

### tags
```sql
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE
);
```

### post_tags
```sql
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id),
    tag_id INTEGER NOT NULL REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);
```

## Sample Data Summary

- **users**: 8 users (小明, 小红, 老王, 阿强, Lily, Tom, 教程小助手, 匿名用户)
- **posts**: 12 blog posts about SQL topics
- **comments**: 15 comments across multiple posts
- **tags**: 6 tags (SQL基础, PostgreSQL, 查询技巧, 数据统计, 入门教程, 进阶)
- **post_tags**: 21 associations

---

## Complete 20-Lesson Curriculum

---

### Lesson 1: What is a Database (3 min)

**Learning Objective**: Understand databases, tables, rows, columns, and what SQL is used for.

**Knowledge Points**:
- A database is like a spreadsheet file with multiple "sheets" (tables)
- Each table has rows (data records) and columns (fields)
- PostgreSQL is a DBMS (Database Management System)
- SQL is the language used to communicate with databases

**Practice Task**:
```sql
-- View all tables in the current database
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
```

**Expected Result**: Initially may show system views or be empty before table creation.

**Exercise**: Think about how an Excel spreadsheet maps to database concepts — Excel column headers = database columns, Excel rows = database rows.

---

### Lesson 2: Creating Your First Table (5 min)

**Learning Objective**: Master CREATE TABLE syntax and create tables with different column types.

**Knowledge Points**:
- SERIAL: auto-incrementing integer (useful for IDs)
- VARCHAR(n): variable-length text up to n characters
- TEXT: unlimited length text
- INTEGER: whole numbers
- TIMESTAMP: date and time values
- PRIMARY KEY: unique identifier for each row
- NOT NULL: column must have a value
- UNIQUE: column value must be unique across all rows
- DEFAULT: default value when none is provided

**Practice Task**:
```sql
-- Create the users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
```

**Expected Result**: `CREATE TABLE` (success message, no row count).

**Exercise**: Create a `products` table with id, name, price, and created_at columns:
```sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
```

---

### Lesson 3: Inserting Data (4 min)

**Learning Objective**: Master INSERT INTO to add data rows to tables.

**Knowledge Points**:
- INSERT INTO specifies the target table and columns
- VALUES provides the actual data values
- Multiple rows can be inserted in a single statement
- SERIAL columns are auto-generated (don't need to be inserted)

**Practice Task**:
```sql
-- Insert a single user
INSERT INTO users (username, email) VALUES ('小明', 'xiaoming@example.com');

-- Batch insert multiple users
INSERT INTO users (username, email) VALUES
    ('小红', 'xiaohong@example.com'),
    ('老王', 'laowang@example.com'),
    ('阿强', 'aqiang@example.com');

-- Verify insertion
SELECT * FROM users;
```

**Expected Results**: `INSERT 0 1` for single insert, `INSERT 0 3` for batch. SELECT shows 4 rows with auto-generated IDs.

**Exercise**: Insert 3 products into the products table:
```sql
INSERT INTO products (name, price) VALUES
    ('矿泉水', 1.50),
    ('笔记本', 12.00),
    ('马克杯', 45.00);
```

---

### Lesson 4: Querying Data (SELECT Basics) (3 min)

**Learning Objective**: Master the basic SELECT statement for reading data.

**Knowledge Points**:
- SELECT * returns all columns
- SELECT col1, col2 returns only specified columns
- FROM specifies the table to query
- The result is a virtual table displayed in the result area

**Practice Task**:
```sql
-- Query all columns from users
SELECT * FROM users;

-- Query only specific columns
SELECT username, email FROM users;
```

**Expected Results**: First query shows all columns (id, username, email, created_at). Second query shows only username and email columns.

**Exercise**: Query all posts' titles and content:
```sql
SELECT title, content FROM posts;
```

---

### Lesson 5: Conditional Queries (WHERE) (5 min)

**Learning Objective**: Use WHERE clause to filter rows that meet specific conditions.

**Knowledge Points**:
- WHERE filters rows before they are returned
- Comparison operators: =, >, <, >=, <=, <>
- LIKE for pattern matching (% is a wildcard matching any characters)
- AND/OR combine multiple conditions

**Practice Task**:
```sql
-- Find user with id = 1
SELECT * FROM users WHERE id = 1;

-- Find posts with "SQL" in the title
SELECT * FROM posts WHERE title LIKE '%SQL%';

-- Find posts with id > 5
SELECT * FROM posts WHERE id > 5;
```

**Expected Results**: Returns only rows matching the WHERE conditions.

**Exercise**: Find users whose email ends with '.com':
```sql
SELECT * FROM users WHERE email LIKE '%.com';
```

---

### Lesson 6: Sorting & Limiting (ORDER BY / LIMIT) (5 min)

**Learning Objective**: Master ORDER BY for sorting and LIMIT for pagination.

**Knowledge Points**:
- ORDER BY column ASC sorts ascending (small to large, A to Z)
- ORDER BY column DESC sorts descending (large to small, Z to A)
- LIMIT n restricts the result to n rows
- ORDER BY is evaluated before LIMIT

**Practice Task**:
```sql
-- Sort users by id ascending
SELECT * FROM users ORDER BY id ASC;

-- Get the 3 most recently published posts
SELECT * FROM posts ORDER BY created_at DESC LIMIT 3;
```

**Expected Results**: First query returns users sorted by ID. Second query returns the 3 newest posts.

**Exercise**: Get the 5 most recent comments:
```sql
SELECT * FROM comments ORDER BY created_at DESC LIMIT 5;
```

---

### Lesson 7: Deduplication & Column Calculations (DISTINCT / AS) (4 min)

**Learning Objective**: Use DISTINCT for deduplication and AS for column aliases.

**Knowledge Points**:
- DISTINCT removes duplicate rows from results
- AS renames a column in the output (alias)
- Aliases make results more readable

**Practice Task**:
```sql
-- Get unique user IDs from comments
SELECT DISTINCT user_id FROM comments;

-- Rename columns with aliases
SELECT username AS '用户名', email AS '邮箱' FROM users;
```

**Expected Results**: First query lists unique user_ids who have commented. Second query shows columns with Chinese aliases.

**Exercise**: Count distinct posts that have been commented on:
```sql
SELECT COUNT(DISTINCT post_id) AS '被评论文章数' FROM comments;
```

---

### Lesson 8: Aggregate Functions (COUNT / SUM / AVG / MAX / MIN) (5 min)

**Learning Objective**: Master five common aggregate functions for data summarization.

**Knowledge Points**:
- COUNT(*): count all rows
- COUNT(column): count non-NULL values in a column
- SUM(column): sum all values
- AVG(column): average of all values
- MAX(column): maximum value
- MIN(column): minimum value
- Aggregate functions summarize multiple rows into a single value

**Practice Task**:
```sql
-- Count total users
SELECT COUNT(*) AS '用户总数' FROM users;

-- Count total posts
SELECT COUNT(*) AS '文章总数' FROM posts;

-- Find max and min post IDs
SELECT MAX(id) AS '最新文章ID', MIN(id) AS '最早文章ID' FROM posts;
```

**Expected Results**: Returns summarized single-row results.

**Exercise**: Count total comments:
```sql
SELECT COUNT(*) AS '评论总数' FROM comments;
```

---

### Lesson 9: Grouping (GROUP BY) (5 min)

**Learning Objective**: Use GROUP BY with aggregate functions for categorized statistics.

**Knowledge Points**:
- GROUP BY divides rows into groups based on column values
- Aggregate functions are applied to each group independently
- The GROUP BY column(s) appear in SELECT along with aggregates

**Practice Task**:
```sql
-- Count comments per post
SELECT post_id, COUNT(*) AS '评论数' FROM comments GROUP BY post_id;

-- Count posts per user
SELECT user_id, COUNT(*) AS '文章数' FROM posts GROUP BY user_id;
```

**Expected Results**: Each row shows a group (post_id or user_id) with its aggregate count.

**Exercise**: Count comments per user:
```sql
SELECT user_id, COUNT(*) AS '评论数' FROM comments GROUP BY user_id;
```

---

### Lesson 10: Filtering Groups with HAVING (5 min)

**Learning Objective**: Use HAVING for secondary filtering after GROUP BY.

**Knowledge Points**:
- WHERE filters rows BEFORE grouping
- HAVING filters groups AFTER grouping
- HAVING can reference aggregate functions, WHERE cannot
- Rule of thumb: WHERE before GROUP BY, HAVING after GROUP BY

**Practice Task**:
```sql
-- Find posts with more than 2 comments
SELECT post_id, COUNT(*) AS '评论数' FROM comments
GROUP BY post_id
HAVING COUNT(*) > 2;
```

**Expected Results**: Only groups (post_ids) with count > 2 are shown.

**Exercise**: Find users who have posted more than 1 article:
```sql
SELECT user_id, COUNT(*) AS '发帖数' FROM posts
GROUP BY user_id
HAVING COUNT(*) > 1;
```

---

### Lesson 11: Comprehensive Query Practice (5 min)

**Learning Objective**: Combine multiple SQL clauses for complex queries.

**Knowledge Points**:
- SQL clause execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
- All clauses can be combined in a single query
- Each clause filters or transforms data at a specific stage

**Practice Task**:
```sql
-- Top 3 most commented posts
SELECT post_id, COUNT(*) AS '评论数' FROM comments
GROUP BY post_id
ORDER BY COUNT(*) DESC
LIMIT 3;

-- Posts with at least 2 comments, sorted by comment count
SELECT post_id, COUNT(*) AS '评论数' FROM comments
GROUP BY post_id
HAVING COUNT(*) >= 2
ORDER BY COUNT(*) DESC;
```

**Expected Results**: First query returns the 3 most-commented posts. Second query returns all posts with 2+ comments.

**Exercise**: Top 2 users by post count:
```sql
SELECT user_id, COUNT(*) AS '发帖数' FROM posts
GROUP BY user_id
ORDER BY COUNT(*) DESC
LIMIT 2;
```

---

### Lesson 12: Introduction to JOIN — Merging Tables (6 min)

**Learning Objective**: Understand JOIN principles and INNER JOIN usage.

**Knowledge Points**:
- Data in relational databases is spread across multiple tables
- JOIN connects tables using related columns (foreign keys)
- INNER JOIN returns only rows with matches in BOTH tables
- JOIN syntax: table1 INNER JOIN table2 ON table1.key = table2.key

**Practice Task**:
```sql
-- Show post titles with author usernames
SELECT posts.title, users.username
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- Show comments with their parent post titles
SELECT comments.content, posts.title
FROM comments
INNER JOIN posts ON comments.post_id = posts.id;
```

**Expected Results**: Combined data from two tables into a single result set.

**Exercise**: Three-table JOIN — comments with commenter username and post title:
```sql
SELECT comments.content, users.username, posts.title
FROM comments
INNER JOIN users ON comments.user_id = users.id
INNER JOIN posts ON comments.post_id = posts.id;
```

---

### Lesson 13: LEFT JOIN (5 min)

**Learning Objective**: Understand LEFT JOIN vs INNER JOIN, and NULL handling.

**Knowledge Points**:
- LEFT JOIN keeps ALL rows from the LEFT table
- Right table columns show NULL when no match exists
- NULL means "no value," not an empty string
- LEFT JOIN is essential for finding "orphan" records

**Practice Task**:
```sql
-- All users and their posts (including users with no posts)
SELECT users.username, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

-- All posts and their comments (including posts with no comments)
SELECT posts.title, comments.content
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id;
```

**Expected Results**: All left-table rows appear. Unmatched right-table columns show NULL.

**Exercise**: All users with their comment counts (0 for users with no comments):
```sql
SELECT users.username, COUNT(comments.id) AS '评论数'
FROM users
LEFT JOIN comments ON users.id = comments.user_id
GROUP BY users.id;
```

---

### Lesson 14: Multi-Table JOIN Practice (6 min)

**Learning Objective**: Combine INNER JOIN and LEFT JOIN for complex multi-table queries.

**Knowledge Points**:
- JOINs can be chained together
- INNER JOIN + LEFT JOIN can be mixed in the same query
- Each JOIN is evaluated left to right
- GROUP BY with multiple tables requires specifying which table's column to group by

**Practice Task**:
```sql
-- Post title, author, and comment count (0 for no comments)
SELECT
    posts.title,
    users.username,
    COUNT(comments.id) AS '评论数'
FROM posts
INNER JOIN users ON posts.user_id = users.id
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id, users.username;
```

**Expected Results**: 12 rows (all posts), each with author name and comment count.

**Exercise**: All users with post count and comment count, sorted by total activity:
```sql
SELECT
    users.username,
    COUNT(DISTINCT posts.id) AS '发帖数',
    COUNT(comments.id) AS '评论数'
FROM users
LEFT JOIN posts ON users.id = posts.user_id
LEFT JOIN comments ON users.id = comments.user_id
GROUP BY users.id
ORDER BY (COUNT(DISTINCT posts.id) + COUNT(comments.id)) DESC;
```

---

### Lesson 15: Subqueries (6 min)

**Learning Objective**: Understand and use nested SELECT queries (subqueries).

**Knowledge Points**:
- A subquery is a SELECT inside another query
- Subqueries can be used in WHERE, FROM, or SELECT clauses
- = operator expects a single value from the subquery
- IN operator expects multiple values from the subquery
- Subqueries are evaluated first (inner to outer)

**Practice Task**:
```sql
-- Find the most-commented post's title
SELECT title FROM posts
WHERE id = (
    SELECT post_id FROM comments
    GROUP BY post_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

-- Find posts with above-average comment count
SELECT title FROM posts
WHERE id IN (
    SELECT post_id FROM comments
    GROUP BY post_id
    HAVING COUNT(*) > (
        SELECT AVG(cnt) FROM (
            SELECT COUNT(*) AS cnt FROM comments GROUP BY post_id
        ) AS sub
    )
);
```

**Expected Results**: Returns titles of posts matching the subquery conditions.

**Exercise**: Find posts that have no comments:
```sql
SELECT title FROM posts
WHERE id NOT IN (
    SELECT DISTINCT post_id FROM comments
);
```

---

### Lesson 16: Updating Data (UPDATE) (5 min)

**Learning Objective**: Master UPDATE and understand the critical importance of WHERE.

**Knowledge Points**:
- UPDATE modifies existing data
- SET specifies which columns to change and their new values
- WHERE is CRITICAL — without it, ALL rows are updated
- String concatenation with || operator

**Practice Task**:
```sql
-- Rename user
UPDATE users SET username = '大明' WHERE username = '小明';

-- Add prefix to post titles
UPDATE posts SET title = '【已更新】' || title WHERE id > 5;

-- Verify the update
SELECT * FROM posts WHERE id > 5 LIMIT 3;
```

**Expected Results**: `UPDATE 1` and `UPDATE 7` showing affected row counts.

**Exercise**: Update comment content:
```sql
UPDATE comments SET content = '这个教程真的很棒！' WHERE id = 1;
```

---

### Lesson 17: Deleting Data (DELETE) (4 min)

**Learning Objective**: Master DELETE and understand the difference from DROP TABLE.

**Knowledge Points**:
- DELETE removes rows (data only, table structure remains)
- DROP TABLE removes the entire table (structure + data)
- DELETE without WHERE removes ALL rows but keeps the table
- DELETE returns the number of affected rows (0 if no match)

**Practice Task**:
```sql
-- Delete a specific user
DELETE FROM users WHERE id = 1;

-- Try deleting non-existent user (no error)
DELETE FROM users WHERE id = 999;

-- Delete comments with id > 10
DELETE FROM comments WHERE id > 10;
```

**Expected Results**: `DELETE 1`, `DELETE 0`, `DELETE N` respectively.

**Exercise**: Delete posts with id > 5:
```sql
DELETE FROM posts WHERE id > 5;
```

---

### Lesson 18: One-to-Many Relationships (5 min)

**Learning Objective**: Understand 1:N relationships and foreign key constraints.

**Knowledge Points**:
- One-to-many: one user can have many posts
- Foreign key (posts.user_id) references primary key (users.id)
- LEFT JOIN is essential for analyzing 1:N relationships
- Foreign keys maintain referential integrity

**Practice Task**:
```sql
-- Posts count per user
SELECT users.username, COUNT(posts.id) AS '文章数'
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id;

-- Comments count per post
SELECT posts.title, COUNT(comments.id) AS '评论数'
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id;
```

**Expected Results**: All entities appear with their related counts (0 for none).

**Exercise**: Find users who have posted at least 1 article:
```sql
SELECT users.username
FROM users
INNER JOIN posts ON users.id = posts.user_id
GROUP BY users.id
HAVING COUNT(posts.id) >= 1;
```

---

### Lesson 19: Many-to-Many Relationships (6 min)

**Learning Objective**: Understand N:M relationships and junction table usage.

**Knowledge Points**:
- Many-to-many: a post can have many tags, a tag can belong to many posts
- A junction table (post_tags) connects the two tables
- Junction table has two foreign keys forming a composite primary key
- Three-table JOIN is needed to traverse N:M relationships

**Practice Task**:
```sql
-- Show each post with its tags
SELECT posts.title, tags.name
FROM post_tags
INNER JOIN posts ON post_tags.post_id = posts.id
INNER JOIN tags ON post_tags.tag_id = tags.id;

-- Find all posts tagged "SQL基础"
SELECT posts.title
FROM posts
INNER JOIN post_tags ON posts.id = post_tags.post_id
INNER JOIN tags ON post_tags.tag_id = tags.id
WHERE tags.name = 'SQL基础';

-- Count tags per post
SELECT posts.title, COUNT(post_tags.tag_id) AS '标签数'
FROM posts
LEFT JOIN post_tags ON posts.id = post_tags.post_id
GROUP BY posts.id;
```

**Expected Results**: Cross-referenced data from three tables.

**Exercise**: Find posts that have both "SQL基础" AND "入门教程" tags:
```sql
SELECT posts.title
FROM posts
INNER JOIN post_tags ON posts.id = post_tags.post_id
INNER JOIN tags ON post_tags.tag_id = tags.id
WHERE tags.name IN ('SQL基础', '入门教程')
GROUP BY posts.id
HAVING COUNT(DISTINCT tags.id) = 2;
```

---

### Lesson 20: Indexes & Transactions (6 min)

**Learning Objective**: Understand indexes for performance and transactions for data integrity.

**Knowledge Points**:
- Indexes speed up WHERE queries (like a book's index)
- Indexes slow down INSERT/UPDATE (must be maintained)
- CREATE INDEX adds an index on a column
- BEGIN starts a transaction
- COMMIT permanently saves all changes in the transaction
- ROLLBACK undoes all changes since BEGIN
- ACID: Atomicity, Consistency, Isolation, Durability

**Practice Task**:
```sql
-- Create an index
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- View indexes on the posts table
SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'posts';

-- Transaction rollback (data not saved)
BEGIN;
INSERT INTO users (username, email) VALUES ('事务测试', 'test@test.com');
ROLLBACK;

-- Transaction commit (data saved)
BEGIN;
INSERT INTO users (username, email) VALUES ('事务测试2', 'test2@test.com');
COMMIT;
```

**Expected Results**: Index created successfully. After ROLLBACK, the test user is not in the table. After COMMIT, the test user is permanently saved.

**Exercise**: Create an index on comments.post_id:
```sql
CREATE INDEX idx_comments_post_id ON comments(post_id);
```

---

## FAQ

**Q: How can I learn PostgreSQL without installation?**
A: This tutorial uses pglite (PostgreSQL compiled to WASM), running directly in the browser with no installation required.

**Q: How long does it take to complete?**
A: 20 lessons at approximately 5 minutes each — about 100 minutes total to complete the fundamentals.

**Q: Do I need programming experience?**
A: No. This tutorial is designed for complete beginners, starting from basic database concepts.

**Q: How is data persisted?**
A: This tutorial uses IndexedDB to store data and SQL operation history. Your progress is preserved across page refreshes.

**Q: What is a JOIN?**
A: A JOIN combines data from two or more tables. INNER JOIN returns only matching rows, while LEFT JOIN preserves all records from the left table.

**Q: What is the difference between DELETE and DROP TABLE?**
A: DELETE removes rows but keeps the table structure. DROP TABLE removes the entire table including its structure.

**Q: What is a transaction?**
A: A transaction groups multiple SQL operations into a single atomic unit. BEGIN starts, COMMIT saves, ROLLBACK undoes.

**Q: What does NULL mean?**
A: NULL means "no value" — it is different from an empty string or zero. NULL represents missing or unknown data.

---

*Generated by Learn PSG — https://learnpsg.com*