SQL Fundamentals and CRUD Operations

My journey from data loss nightmares to mastering SQL operations

Introduction: The Week I Lost Everything

I'll never forget the Monday morning I opened my Excel spreadsheet with 127 blog posts, three months of meticulous work, and saw this:

Excel cannot open the file 'blog-posts.xlsx' because the file format is not valid.

My heart sank. I had no backup. No version control. Just hope that maybe, somehow, I could recover the file.

I tried every recovery tool I could find. Nothing worked. A week of blog drafts, carefully organized categories, scheduled publication dates—all gone. That moment of staring at an error message taught me a brutal lesson: if you care about your data, you need a real database.

That's when I committed to learning SQL properly. Not just creating tables, but understanding CRUD operations—the fundamental ways you interact with data. The "CRUD" operations (Create, Read, Update, Delete) became my new religion, and PostgreSQL became my sanctuary.

Let me share what I learned about managing data the right way, so you never have to experience that sinking feeling of lost work.

Understanding CRUD: The Foundation

CRUD is an acronym that represents the four basic operations you can perform on data:

Create: Adding new data (INSERT)
Read: Retrieving data (SELECT)
Update: Modifying existing data (UPDATE)
Delete: Removing data (DELETE)

Every application you've ever used—whether it's social media, email, or banking—relies on these four operations. Master CRUD, and you understand how data flows through systems.

How SQL Queries Flow Through PostgreSQL

Here's what happens when you run a query:

Understanding this flow helped me write better queries. The planner is smart, but giving it good data structures (proper types, indexes) makes it even faster.

CREATE: Building Better Tables

In Part 1, we created a simple posts table. Now let's build it properly with auto-incrementing IDs, constraints, and defaults.

The SERIAL Data Type: Auto-Incrementing IDs

My first tables had manual ID management. I'd insert id=1, then id=2, etc. This is tedious and error-prone. Enter SERIAL:

-- Drop the old table if it exists
DROP TABLE IF EXISTS posts;

-- Create improved posts table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,              -- Auto-incrementing ID
    title VARCHAR(200) NOT NULL,        -- Limited length, required
    slug VARCHAR(200) UNIQUE NOT NULL,  -- URL-friendly, unique
    content TEXT,                        -- Unlimited length
    excerpt VARCHAR(500),                -- Short summary
    published BOOLEAN DEFAULT false,     -- Draft vs published
    view_count INTEGER DEFAULT 0,        -- Track views
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Breaking Down the Improvements

Let's understand what each change accomplishes:

id SERIAL PRIMARY KEY

SERIAL automatically generates sequential numbers (1, 2, 3...)
No more manual ID management!
Internally creates a sequence object

title VARCHAR(200) NOT NULL

Limited to 200 characters (prevents novels as titles)
NOT NULL ensures every post has a title
More efficient storage than TEXT for short strings

slug VARCHAR(200) UNIQUE NOT NULL

URL-friendly identifier (e.g., "my-first-post")
UNIQUE ensures no two posts have the same slug
Perfect for clean URLs: /blog/my-first-post

published BOOLEAN DEFAULT false

Explicitly tracks draft vs published state
Defaults to false (draft) if not specified
Better than checking if a publish_date exists

view_count INTEGER DEFAULT 0

Tracks page views
Starts at 0 automatically
Can be incremented on each visit

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Automatically set to current time on insert
No more manual date entry!
Includes time, not just date

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Will be updated via trigger or application code
Tracks last modification time

Creating the Authors Table

Now let's add an authors table (we'll connect them in Part 3):

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Verification

Always verify your table structure:

-- Describe posts table
\d posts

-- Describe authors table
\d authors

-- List all tables
\dt

INSERT: Adding Data the Right Way

With our improved schema, inserting data becomes much easier.

Basic INSERT with Auto-Incrementing ID

-- Notice: no need to specify id, created_at, or updated_at!
INSERT INTO posts (title, slug, content, excerpt, published) 
VALUES (
    'My First Blog Post',
    'my-first-blog-post',
    'This is the full content of my first blog post about learning databases.',
    'Learning databases with PostgreSQL',
    true
);

INSERT with RETURNING Clause

One of my favorite PostgreSQL features—see what you just inserted:

INSERT INTO posts (title, slug, content, published) 
VALUES (
    'PostgreSQL Tips and Tricks',
    'postgresql-tips-tricks',
    'Here are some PostgreSQL tips I learned the hard way...',
    true
)
RETURNING id, created_at;

-- Output:
--  id |         created_at         
-- ----+----------------------------
--   2 | 2025-12-30 14:23:45.123456

This is incredibly useful when you need the generated ID for your application.

INSERT Multiple Rows Efficiently

INSERT INTO posts (title, slug, excerpt, published) 
VALUES 
    ('Python Tutorial', 'python-tutorial', 'Learn Python basics', true),
    ('Docker Guide', 'docker-guide', 'Container fundamentals', true),
    ('API Design', 'api-design', 'RESTful best practices', false),
    ('Database Series', 'database-series', 'SQL from scratch', false)
RETURNING id, title, published;

INSERT with Conflict Handling (UPSERT)

What if you try to insert a duplicate slug? Here's how to handle it:

-- Try to insert, but update if slug already exists
INSERT INTO posts (title, slug, content, published)
VALUES ('Updated Title', 'my-first-blog-post', 'New content', true)
ON CONFLICT (slug) 
DO UPDATE SET 
    title = EXCLUDED.title,
    content = EXCLUDED.content,
    updated_at = CURRENT_TIMESTAMP;

This is called an "UPSERT" (UPDATE or INSERT). It prevented countless errors in my applications.

Inserting Authors

INSERT INTO authors (username, email, full_name, bio)
VALUES 
    ('john_doe', '[email protected]', 'John Doe', 'Tech blogger and developer'),
    ('jane_smith', '[email protected]', 'Jane Smith', 'Database enthusiast')
RETURNING *;

SELECT: Querying Like a Pro

SELECT is where SQL truly shines. Let's move beyond simple queries.

Basic SELECT Patterns

-- All columns, all rows
SELECT * FROM posts;

-- Specific columns
SELECT title, slug, published FROM posts;

-- With column aliases (renaming)
SELECT 
    title AS post_title,
    created_at AS publication_date,
    view_count AS views
FROM posts;

Filtering with WHERE

-- Published posts only
SELECT title, created_at 
FROM posts 
WHERE published = true;

-- Posts with more than 100 views
SELECT title, view_count 
FROM posts 
WHERE view_count > 100;

-- Posts created in 2025
SELECT title, created_at 
FROM posts 
WHERE created_at >= '2025-01-01' 
  AND created_at < '2026-01-01';

-- Multiple conditions with OR
SELECT title, published 
FROM posts 
WHERE published = true 
   OR view_count > 50;

Pattern Matching with LIKE

-- Posts with "PostgreSQL" in title
SELECT title 
FROM posts 
WHERE title LIKE '%PostgreSQL%';

-- Posts starting with "Database"
SELECT title 
FROM posts 
WHERE title LIKE 'Database%';

-- Case-insensitive search (PostgreSQL-specific)
SELECT title 
FROM posts 
WHERE title ILIKE '%python%';

Sorting with ORDER BY

-- Most recent posts first
SELECT title, created_at 
FROM posts 
ORDER BY created_at DESC;

-- Alphabetical by title
SELECT title 
FROM posts 
ORDER BY title ASC;

-- Multiple sort criteria
SELECT title, view_count, created_at 
FROM posts 
ORDER BY view_count DESC, created_at DESC;

-- Nulls last (PostgreSQL-specific)
SELECT title, excerpt 
FROM posts 
ORDER BY excerpt NULLS LAST;

Limiting and Pagination

-- Top 5 most viewed posts
SELECT title, view_count 
FROM posts 
ORDER BY view_count DESC 
LIMIT 5;

-- Posts 6-10 (pagination)
SELECT title, view_count 
FROM posts 
ORDER BY view_count DESC 
LIMIT 5 OFFSET 5;

-- Page 3, with 10 posts per page
SELECT title 
FROM posts 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;  -- (page - 1) * page_size

Checking for NULL Values

-- Posts without an excerpt
SELECT title 
FROM posts 
WHERE excerpt IS NULL;

-- Posts WITH an excerpt
SELECT title, excerpt 
FROM posts 
WHERE excerpt IS NOT NULL;

Combining Conditions

-- Published posts with content, sorted by views
SELECT title, view_count 
FROM posts 
WHERE published = true 
  AND content IS NOT NULL 
  AND view_count > 0
ORDER BY view_count DESC 
LIMIT 10;

UPDATE: Modifying Data Safely

UPDATE is powerful but dangerous. One mistake and you could modify all your data.

Basic UPDATE

-- Update a specific post's title
UPDATE posts 
SET title = 'PostgreSQL: The Complete Guide'
WHERE slug = 'postgresql-tips-tricks';

-- Verify with SELECT
SELECT title, slug FROM posts WHERE slug = 'postgresql-tips-tricks';

The Critical Importance of WHERE

⚠️ WARNING: This updates EVERY row!

-- DON'T DO THIS unless you really mean it!
UPDATE posts 
SET published = true;  -- All posts now published!

Always use WHERE unless you truly want to update every row:

-- Safe: Updates specific post
UPDATE posts 
SET published = true 
WHERE id = 5;

Updating Multiple Columns

-- Publish a draft and set excerpt
UPDATE posts 
SET 
    published = true,
    excerpt = 'A comprehensive guide to PostgreSQL features',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 3;

Increment a Counter

-- Increment view count when someone visits a post
UPDATE posts 
SET view_count = view_count + 1 
WHERE slug = 'postgresql-tips-tricks';

-- Verify
SELECT title, view_count FROM posts WHERE slug = 'postgresql-tips-tricks';

UPDATE with RETURNING

See what you changed:

UPDATE posts 
SET view_count = view_count + 10 
WHERE id = 1
RETURNING id, title, view_count;

Conditional UPDATE

-- Only update if view count is below 100
UPDATE posts 
SET excerpt = 'Needs more promotion' 
WHERE view_count < 100 AND published = true;

UPDATE Based on Calculation

-- Set excerpt from first 100 characters of content
UPDATE posts 
SET excerpt = LEFT(content, 100) || '...'
WHERE excerpt IS NULL AND content IS NOT NULL;

DELETE: Removing Data Responsibly

DELETE is even more dangerous than UPDATE. There's usually no undo.

Basic DELETE

-- Delete a specific post
DELETE FROM posts 
WHERE id = 999;

-- Verify it's gone
SELECT * FROM posts WHERE id = 999;  -- Returns no rows

The WHERE Clause is CRITICAL

⚠️ DANGER: This deletes EVERYTHING!

-- DON'T DO THIS unless you want to delete all data!
DELETE FROM posts;  -- ALL POSTS GONE!

Always use WHERE:

-- Safe: Delete specific post
DELETE FROM posts WHERE id = 5;

-- Delete drafts older than 6 months
DELETE FROM posts 
WHERE published = false 
  AND created_at < CURRENT_DATE - INTERVAL '6 months';

DELETE with RETURNING

See what you deleted:

DELETE FROM posts 
WHERE view_count = 0 AND published = false
RETURNING id, title;

Soft Deletes (My Preferred Approach)

Instead of actually deleting, mark as deleted:

-- Add a deleted_at column (run this once)
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMP;

-- "Delete" by setting timestamp
UPDATE posts 
SET deleted_at = CURRENT_TIMESTAMP 
WHERE id = 10;

-- Query only non-deleted posts
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Actually delete soft-deleted posts older than 1 year
DELETE FROM posts 
WHERE deleted_at < CURRENT_DATE - INTERVAL '1 year';

Soft deletes saved me multiple times when I accidentally "deleted" important data.

Aggregate Functions: Analyzing Your Data

Aggregate functions let you analyze data across multiple rows.

COUNT: How Many?

-- Total number of posts
SELECT COUNT(*) FROM posts;

-- Number of published posts
SELECT COUNT(*) FROM posts WHERE published = true;

-- Number of posts with content
SELECT COUNT(content) FROM posts;  -- Doesn't count NULLs

-- Count distinct slugs (should equal total posts due to UNIQUE constraint)
SELECT COUNT(DISTINCT slug) FROM posts;

SUM: Add It Up

-- Total views across all posts
SELECT SUM(view_count) FROM posts;

-- Total views for published posts
SELECT SUM(view_count) 
FROM posts 
WHERE published = true;

AVG: Find the Average

-- Average view count
SELECT AVG(view_count) FROM posts;

-- Average views for published posts
SELECT AVG(view_count) 
FROM posts 
WHERE published = true;

-- Round to 2 decimal places
SELECT ROUND(AVG(view_count), 2) AS avg_views
FROM posts;

MIN and MAX: Extremes

-- Least and most viewed posts
SELECT 
    MIN(view_count) AS min_views,
    MAX(view_count) AS max_views
FROM posts;

-- Oldest and newest posts
SELECT 
    MIN(created_at) AS oldest_post,
    MAX(created_at) AS newest_post
FROM posts;

Combining Aggregates

-- Comprehensive stats
SELECT 
    COUNT(*) AS total_posts,
    COUNT(*) FILTER (WHERE published = true) AS published_count,
    COUNT(*) FILTER (WHERE published = false) AS draft_count,
    SUM(view_count) AS total_views,
    ROUND(AVG(view_count), 2) AS avg_views,
    MAX(view_count) AS max_views
FROM posts;

GROUP BY: Organizing Results

GROUP BY lets you aggregate data by category.

Basic Grouping

-- Count posts by published status
SELECT 
    published,
    COUNT(*) AS post_count
FROM posts 
GROUP BY published;

-- Output:
--  published | post_count
-- -----------+------------
--  false     |         12
--  true      |         43

GROUP BY with Multiple Aggregates

-- Stats by published status
SELECT 
    published,
    COUNT(*) AS post_count,
    SUM(view_count) AS total_views,
    ROUND(AVG(view_count), 2) AS avg_views
FROM posts 
GROUP BY published;

Filtering Groups with HAVING

HAVING is like WHERE, but for groups:

-- Only show statuses with more than 10 posts
SELECT 
    published,
    COUNT(*) AS post_count
FROM posts 
GROUP BY published
HAVING COUNT(*) > 10;

WHERE vs HAVING

-- Filter rows BEFORE grouping (WHERE)
-- Then filter groups AFTER grouping (HAVING)
SELECT 
    published,
    COUNT(*) AS post_count,
    AVG(view_count) AS avg_views
FROM posts 
WHERE created_at >= '2025-01-01'  -- Filter individual rows
GROUP BY published
HAVING COUNT(*) > 5;               -- Filter groups

Group by Date

-- Posts created per month
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS post_count
FROM posts 
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Advanced Grouping Example

-- Top 5 months by total views
SELECT 
    DATE_TRUNC('month', created_at)::DATE AS month,
    COUNT(*) AS posts_created,
    SUM(view_count) AS total_views,
    ROUND(AVG(view_count), 2) AS avg_views_per_post
FROM posts 
WHERE published = true
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY total_views DESC
LIMIT 5;

Improving Our Blog Schema

Let's create a complete, production-ready schema with everything we've learned:

-- Drop existing tables
DROP TABLE IF EXISTS posts CASCADE;
DROP TABLE IF EXISTS authors CASCADE;

-- Create authors table
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    bio TEXT,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create improved posts table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT,
    excerpt VARCHAR(500),
    published BOOLEAN DEFAULT false,
    featured BOOLEAN DEFAULT false,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP,
    
    -- Constraints
    CONSTRAINT check_title_length CHECK (LENGTH(title) >= 3),
    CONSTRAINT check_view_count_positive CHECK (view_count >= 0)
);

-- Create indexes for common queries
CREATE INDEX idx_posts_published ON posts(published);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_authors_email ON authors(email);

Understanding Constraints

CHECK Constraints enforce business rules:

-- Title must be at least 3 characters
CONSTRAINT check_title_length CHECK (LENGTH(title) >= 3)

-- View count can't be negative
CONSTRAINT check_view_count_positive CHECK (view_count >= 0)

If you try to violate these, PostgreSQL will reject the operation:

-- This will fail!
INSERT INTO posts (title, slug) 
VALUES ('AB', 'short-title');
-- ERROR: check constraint "check_title_length" is violated

Understanding Indexes

Indexes make queries fast. We'll dive deep in Part 4, but here's the basics:

-- Without index: Full table scan (slow for large tables)
-- With index: Direct lookup (fast!)

CREATE INDEX idx_posts_published ON posts(published);
-- Now this is fast:
SELECT * FROM posts WHERE published = true;

Real-World Query Patterns

Here are queries I use constantly in real applications:

Latest Published Posts (Blog Homepage)

SELECT 
    id,
    title,
    slug,
    excerpt,
    view_count,
    created_at
FROM posts 
WHERE published = true 
ORDER BY created_at DESC 
LIMIT 10;

Featured Posts (Sidebar)

SELECT title, slug, view_count
FROM posts 
WHERE published = true AND featured = true
ORDER BY view_count DESC 
LIMIT 5;

Search by Title or Content

SELECT title, excerpt, slug
FROM posts 
WHERE published = true 
  AND (
    title ILIKE '%database%' 
    OR content ILIKE '%database%'
  )
ORDER BY created_at DESC;

SELECT 
    title,
    slug,
    view_count,
    created_at
FROM posts 
WHERE published = true 
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY view_count DESC 
LIMIT 10;

Content Analytics Dashboard

SELECT 
    COUNT(*) AS total_posts,
    COUNT(*) FILTER (WHERE published = true) AS published,
    COUNT(*) FILTER (WHERE published = false) AS drafts,
    COUNT(*) FILTER (WHERE featured = true) AS featured,
    SUM(view_count) AS total_views,
    ROUND(AVG(view_count), 2) AS avg_views,
    MAX(created_at) AS latest_post_date
FROM posts;

Posts Needing Attention (Low Views)

SELECT 
    title,
    view_count,
    created_at,
    CURRENT_DATE - created_at::DATE AS days_old
FROM posts 
WHERE published = true 
  AND view_count < 50 
  AND created_at < CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC;

Common Mistakes and How to Avoid Them

Mistake 1: Forgetting WHERE in UPDATE/DELETE

-- WRONG: Updates/deletes ALL rows
UPDATE posts SET title = 'Oops';
DELETE FROM posts;

-- RIGHT: Specific row(s)
UPDATE posts SET title = 'Fixed' WHERE id = 5;
DELETE FROM posts WHERE id = 5;

My rule: Always write SELECT first, then convert to UPDATE/DELETE:

-- Step 1: Verify with SELECT
SELECT * FROM posts WHERE view_count = 0 AND published = false;

-- Step 2: Convert to DELETE if results look right
DELETE FROM posts WHERE view_count = 0 AND published = false;

Mistake 2: Using = NULL Instead of IS NULL

-- WRONG: Always returns no results
SELECT * FROM posts WHERE excerpt = NULL;

-- RIGHT: Use IS NULL
SELECT * FROM posts WHERE excerpt IS NULL;

Mistake 3: Not Using Transactions for Multiple Changes

-- If second INSERT fails, first one still happens!
INSERT INTO posts (title, slug) VALUES ('Post 1', 'post-1');
INSERT INTO posts (title, slug) VALUES ('Post 2', 'post-1');  -- Duplicate slug!

-- RIGHT: Use a transaction (we'll cover this more in Part 3)
BEGIN;
INSERT INTO posts (title, slug) VALUES ('Post 1', 'post-1');
INSERT INTO posts (title, slug) VALUES ('Post 2', 'post-2');
COMMIT;  -- Only if both succeed

Mistake 4: Ignoring Data Types

-- WRONG: Storing numbers as text
INSERT INTO posts (view_count) VALUES ('not a number');  -- Error!

-- WRONG: Comparing wrong types
SELECT * FROM posts WHERE view_count = '100';  -- Works but slow

-- RIGHT: Use proper types
SELECT * FROM posts WHERE view_count = 100;

Mistake 5: Not Using LIMIT on Large Tables

-- WRONG: Could return millions of rows
SELECT * FROM posts;

-- RIGHT: Limit your results
SELECT * FROM posts ORDER BY created_at DESC LIMIT 100;

What I Learned About SQL

Looking back at my journey from Excel corruption to SQL mastery, here are my key insights:

1. SQL is a Conversation with Your Data

At first, SQL felt like typing commands into a black box. Then I realized: you're having a conversation. "Hey PostgreSQL, SELECT (show me) the title (these fields) FROM posts (from this table) WHERE published = true (that meet this criteria)."

2. Start with SELECT, Always

Before any UPDATE or DELETE, I always write a SELECT query first. Verify I'm targeting the right rows, then modify the query. This habit has saved me countless times.

3. SERIAL Changed Everything

Auto-incrementing IDs freed my mind. No more manually tracking the next ID number. No more duplicate ID errors. Just let the database handle it.

4. Defaults and Constraints Are Your Friends

DEFAULT CURRENT_TIMESTAMP means I never forget to set created_at. NOT NULL prevents incomplete data. CHECK constraints enforce business rules. These aren't restrictions—they're guardrails that prevent mistakes.

5. PostgreSQL Error Messages Are Actually Helpful

Unlike some systems that give cryptic errors, PostgreSQL tells you exactly what went wrong:

ERROR:  duplicate key value violates unique constraint "posts_slug_key"
DETAIL:  Key (slug)=(my-post) already exists.

Read the error. It's usually telling you exactly how to fix it.

6. RETURNING is Magical

The ability to see what you just inserted/updated/deleted in one query is incredibly powerful. Use it!

Next Steps

Congratulations! You've mastered CRUD operations, the foundation of all database work. You now understand:

✅ Creating tables with proper types, defaults, and constraints ✅ Inserting data efficiently with SERIAL and RETURNING ✅ Querying with WHERE, ORDER BY, LIMIT, and pagination ✅ Updating data safely with proper WHERE clauses ✅ Deleting responsibly (and why soft deletes are better) ✅ Aggregate functions (COUNT, SUM, AVG) ✅ Grouping and analyzing data with GROUP BY ✅ Real-world query patterns

But we still have a major limitation: our tables exist in isolation. We can't:

Link posts to their authors
Connect posts to categories
Track comments on posts
Ensure referential integrity

In Part 3: Relationships and Data Integrity, we'll learn:

Foreign keys and how they work
One-to-many relationships (authors → posts)
Many-to-many relationships (posts ↔ categories)
JOIN operations to query related data
CASCADE deletion and referential integrity
Transactions and ACID properties
Indexing for performance

The journey continues! 🚀

Practice Exercise: Before moving to Part 3, try this:

Add an author_id column to the posts table
Insert some authors
Update posts to link them to authors
Write a query to find all posts by a specific author
Try to delete an author and see what happens

These exercises will prepare you perfectly for understanding relationships!

PreviousIntroduction to Databases and PostgreSQL NextRelationships and Data Integrity

Last updated 1 month ago

hashtagTable of Contents

hashtagIntroduction: The Week I Lost Everything

hashtagUnderstanding CRUD: The Foundation

hashtagHow SQL Queries Flow Through PostgreSQL

hashtagCREATE: Building Better Tables

hashtagThe SERIAL Data Type: Auto-Incrementing IDs

hashtagBreaking Down the Improvements

hashtagCreating the Authors Table

hashtagVerification

hashtagINSERT: Adding Data the Right Way

hashtagBasic INSERT with Auto-Incrementing ID

hashtagINSERT with RETURNING Clause

hashtagINSERT Multiple Rows Efficiently

hashtagINSERT with Conflict Handling (UPSERT)

hashtagInserting Authors

hashtagSELECT: Querying Like a Pro

hashtagBasic SELECT Patterns

hashtagFiltering with WHERE

hashtagPattern Matching with LIKE

hashtagSorting with ORDER BY

hashtagLimiting and Pagination

hashtagChecking for NULL Values

hashtagCombining Conditions

hashtagUPDATE: Modifying Data Safely

hashtagBasic UPDATE

hashtagThe Critical Importance of WHERE

hashtagUpdating Multiple Columns

hashtagIncrement a Counter

hashtagUPDATE with RETURNING

hashtagConditional UPDATE

hashtagUPDATE Based on Calculation

hashtagDELETE: Removing Data Responsibly

hashtagBasic DELETE

hashtagThe WHERE Clause is CRITICAL

hashtagDELETE with RETURNING

hashtagSoft Deletes (My Preferred Approach)

hashtagAggregate Functions: Analyzing Your Data

hashtagCOUNT: How Many?

hashtagSUM: Add It Up

hashtagAVG: Find the Average

hashtagMIN and MAX: Extremes

hashtagCombining Aggregates

hashtagGROUP BY: Organizing Results

hashtagBasic Grouping

hashtagGROUP BY with Multiple Aggregates

hashtagFiltering Groups with HAVING

hashtagWHERE vs HAVING

hashtagGroup by Date

hashtagAdvanced Grouping Example

hashtagImproving Our Blog Schema

hashtagUnderstanding Constraints

hashtagUnderstanding Indexes

hashtagReal-World Query Patterns

hashtagLatest Published Posts (Blog Homepage)

hashtagFeatured Posts (Sidebar)

hashtagSearch by Title or Content

hashtagPopular Posts from Last 30 Days

hashtagContent Analytics Dashboard

hashtagPosts Needing Attention (Low Views)

hashtagCommon Mistakes and How to Avoid Them

hashtagMistake 1: Forgetting WHERE in UPDATE/DELETE

hashtagMistake 2: Using = NULL Instead of IS NULL

hashtagMistake 3: Not Using Transactions for Multiple Changes

hashtagMistake 4: Ignoring Data Types

hashtagMistake 5: Not Using LIMIT on Large Tables

hashtagWhat I Learned About SQL

hashtag1. SQL is a Conversation with Your Data

hashtag2. Start with SELECT, Always

hashtag3. SERIAL Changed Everything

hashtag4. Defaults and Constraints Are Your Friends

hashtag5. PostgreSQL Error Messages Are Actually Helpful

hashtag6. RETURNING is Magical

hashtagNext Steps

Table of Contents