Querying Data | PostgreSQL Mastery

📦 Our Practice Dataset

Before we start querying, we need some data to play with! Think of this as setting up your toy box before you start playing. We'll create a products table and fill it with items you might find in an online store.

🧒 ELI5 — Why Do We Need Sample Data?

Imagine you want to practice searching for things on Google, but the internet is completely empty — no websites, no pictures, nothing. That wouldn't be very useful, right? Same idea here! We need to put some data into our database first so we have something to search through. Think of it like filling a library with books before you start looking for your favorite author.

Run this SQL to create and populate our products table:

-- Step 1: Create the products table
CREATE TABLE products (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100),
    category   VARCHAR(50),
    price      DECIMAL(10,2),
    stock      INTEGER,
    rating     DECIMAL(2,1),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Step 2: Insert sample products
INSERT INTO products (name, category, price, stock, rating) VALUES
    ('Wireless Headphones',   'Electronics', 79.99,  150, 4.5),
    ('USB-C Cable',            'Electronics', 12.99,  500, 4.2),
    ('Smartphone Case',        'Electronics', 24.99,  300, 3.8),
    ('Running Shoes',          'Clothing',    89.99,  75,  4.7),
    ('Cotton T-Shirt',         'Clothing',    19.99,  200, 4.0),
    ('Winter Jacket',          'Clothing',    129.99, 45,  4.8),
    ('Organic Coffee Beans',   'Food',        14.99,  400, 4.6),
    ('Dark Chocolate Bar',     'Food',        5.99,   600, 4.3),
    ('Almond Butter',          'Food',        9.49,   180, 4.1),
    ('Python Crash Course',    'Books',       35.00,  120, 4.9),
    ('SQL in 10 Minutes',      'Books',       22.50,  95,  4.4),
    ('Data Science Handbook',  'Books',       45.00,  60,  NULL),
    ('Bluetooth Speaker',      'Electronics', 49.99,  0,   4.1),
    ('Yoga Mat',               'Clothing',    29.99,  90,  NULL);

Here's what our data looks like:

id	name	category	price	stock	rating
1	Wireless Headphones	Electronics	79.99	150	4.5
2	USB-C Cable	Electronics	12.99	500	4.2
3	Smartphone Case	Electronics	24.99	300	3.8
4	Running Shoes	Clothing	89.99	75	4.7
5	Cotton T-Shirt	Clothing	19.99	200	4.0
6	Winter Jacket	Clothing	129.99	45	4.8
7	Organic Coffee Beans	Food	14.99	400	4.6
8	Dark Chocolate Bar	Food	5.99	600	4.3
9	Almond Butter	Food	9.49	180	4.1
10	Python Crash Course	Books	35.00	120	4.9
11	SQL in 10 Minutes	Books	22.50	95	4.4
12	Data Science Handbook	Books	45.00	60	NULL
13	Bluetooth Speaker	Electronics	49.99	0	4.1
14	Yoga Mat	Clothing	29.99	90	NULL

💡 Pro Tip — Notice the NULLs!

See how products 12 and 14 have NULL for their rating? That means "we don't know the rating yet" — maybe nobody has reviewed them. We deliberately included NULLs because they cause a lot of confusion and we'll learn exactly how to handle them later in this lesson.

1️⃣ The WHERE Clause — Filtering Rows

🧒 ELI5 — What Is WHERE?

Imagine you walk into a giant bookstore with thousands of books. You don't want to look at every single book — that would take forever! Instead you walk up to the counter and say: "Show me only the mystery novels." The clerk goes through the shelves, picks out only the mystery books, and hands them to you. That's exactly what WHERE does. It tells PostgreSQL: "Don't give me everything — only give me the rows that match this condition."

The basic syntax is:

SELECT columns
FROM table_name
WHERE condition;

Comparison Operators

These are the tools you use inside WHERE to compare values:

Operator	Meaning	Example
`=`	Equal to	`price = 19.99`
`<>` or `!=`	Not equal to	`category <> 'Food'`
`>`	Greater than	`price > 50`
`<`	Less than	`stock < 100`
`>=`	Greater than or equal	`rating >= 4.5`
`<=`	Less than or equal	`price <= 25`

Example 1: Find All Electronics

SELECT name, category, price
FROM products
WHERE category = 'Electronics';

Result:

name	category	price
Wireless Headphones	Electronics	79.99
USB-C Cable	Electronics	12.99
Smartphone Case	Electronics	24.99
Bluetooth Speaker	Electronics	49.99

Example 2: Find Products Under $20

SELECT name, price
FROM products
WHERE price < 20;

Result:

name	price
USB-C Cable	12.99
Cotton T-Shirt	19.99
Organic Coffee Beans	14.99
Dark Chocolate Bar	5.99
Almond Butter	9.49

Example 3: Products That Are Out of Stock

SELECT name, stock
FROM products
WHERE stock = 0;

Result:

name	stock
Bluetooth Speaker	0

🌍 Real-Life Analogy

Think of WHERE like the search filters on Amazon. When you check "4 stars & up" or set a price range, you're building a WHERE clause! Amazon looks through millions of products and only shows you the ones matching your filters. PostgreSQL does the exact same thing with your data.

⚠️ Common Mistake — Strings Need Quotes!

Text values (strings) must be wrapped in single quotes: 'Electronics'. Numbers do not use quotes: price < 20. If you forget the quotes around a string, PostgreSQL will think it's a column name and throw an error!

2️⃣ Logical Operators: AND, OR, NOT

🧒 ELI5 — Combining Conditions

Back to the bookstore! Saying "Show me mystery novels" is one filter. But what if you want "mystery novels AND under $20"? Now you've combined TWO conditions. AND means both must be true. OR means at least one must be true. NOT flips a condition — it means "give me the opposite."

AND — Both Conditions Must Be True

-- Cheap electronics: category is Electronics AND price under $30
SELECT name, category, price
FROM products
WHERE category = 'Electronics'
  AND price < 30;

Result:

name	category	price
USB-C Cable	Electronics	12.99
Smartphone Case	Electronics	24.99

OR — At Least One Condition Must Be True

-- Products that are Food OR Books
SELECT name, category, price
FROM products
WHERE category = 'Food'
   OR category = 'Books';

Result:

name	category	price
Organic Coffee Beans	Food	14.99
Dark Chocolate Bar	Food	5.99
Almond Butter	Food	9.49
Python Crash Course	Books	35.00
SQL in 10 Minutes	Books	22.50
Data Science Handbook	Books	45.00

NOT — Flip the Condition

-- Everything EXCEPT clothing
SELECT name, category
FROM products
WHERE NOT category = 'Clothing';

⚡ Operator Precedence — AND Before OR!

⚠️ This Is a Classic Trap!

SQL evaluates AND before OR — just like multiplication before addition in math. This means A OR B AND C is actually A OR (B AND C), NOT (A OR B) AND C. When in doubt, always use parentheses to make your intent crystal clear!

Watch this tricky example:

-- ❌ CONFUSING: What does this actually do?
SELECT name, category, price
FROM products
WHERE category = 'Food'
   OR category = 'Books'
  AND price < 30;

-- PostgreSQL reads this as:
-- WHERE category = 'Food' OR (category = 'Books' AND price < 30)
-- That gives ALL Food + only cheap Books!

-- ✅ CLEAR: Use parentheses!
SELECT name, category, price
FROM products
WHERE (category = 'Food' OR category = 'Books')
  AND price < 30;

-- Now it means: (Food or Books) that are under $30

Truth Table — How AND, OR, NOT Work

Let's use a real example. Say we have a product: Dark Chocolate Bar — category = 'Food', price = 5.99.

Condition A	Condition B	A AND B	A OR B
category = 'Food' ✅	price < 10 ✅	✅ TRUE	✅ TRUE
category = 'Food' ✅	price > 10 ❌	❌ FALSE	✅ TRUE
category = 'Books' ❌	price < 10 ✅	❌ FALSE	✅ TRUE
category = 'Books' ❌	price > 10 ❌	❌ FALSE	❌ FALSE

💡 Pro Tip — Readability Matters

When you have complex conditions, put each condition on its own line and use indentation. Future-you (and your teammates) will thank you! Also, parentheses never hurt — even when they're technically not needed, they make your intent obvious.

3️⃣ ORDER BY — Sorting Results

🧒 ELI5 — What Is ORDER BY?

Imagine your music playlist is a total mess — songs from all different artists and years, all jumbled together. You tap the "Sort by Artist" button and suddenly everything is organized A–Z. Tap "Sort by Year" and the oldest songs come first. That's ORDER BY! It takes your query results and arranges them in whatever order you want.

ASC (Ascending) — The Default

-- Cheapest products first (ascending = lowest to highest)
SELECT name, price
FROM products
ORDER BY price ASC;

Result:

name	price
Dark Chocolate Bar	5.99
Almond Butter	9.49
USB-C Cable	12.99
Organic Coffee Beans	14.99
Cotton T-Shirt	19.99
... more rows ...	...

DESC (Descending) — Reverse Order

-- Most expensive products first
SELECT name, price
FROM products
ORDER BY price DESC;

Result:

name	price
Winter Jacket	129.99
Running Shoes	89.99
Wireless Headphones	79.99
Bluetooth Speaker	49.99
Data Science Handbook	45.00
... more rows ...	...

Sorting by Multiple Columns

You can sort by more than one column. PostgreSQL sorts by the first column first, then uses the second column to break ties.

-- Sort by category A–Z, then within each category by price (cheapest first)
SELECT name, category, price
FROM products
ORDER BY category ASC, price ASC;

Result:

name	category	price
SQL in 10 Minutes	Books	22.50
Python Crash Course	Books	35.00
Data Science Handbook	Books	45.00
Cotton T-Shirt	Clothing	19.99
Yoga Mat	Clothing	29.99
... more rows ...	...	...

🌍 Real-Life Analogy

This is like sorting your playlist by artist name first, then by year released. So all the Adele songs are together, and within Adele's songs, the oldest song comes first. Two levels of organization!

NULLS FIRST / NULLS LAST — PostgreSQL Exclusive!

By default, NULLs appear last in ASC order and first in DESC order. PostgreSQL lets you control this explicitly:

-- Put products without ratings at the top
SELECT name, rating
FROM products
ORDER BY rating ASC NULLS FIRST;

-- Or push them to the bottom even in DESC order
SELECT name, rating
FROM products
ORDER BY rating DESC NULLS LAST;

💡 Pro Tip — NULLS FIRST/LAST

NULLS FIRST and NULLS LAST are PostgreSQL-specific features — not all databases have them. It's incredibly handy when building reports and you want to handle missing data gracefully (for example, showing unrated products at the end).

4️⃣ LIMIT & OFFSET — Pagination

🧒 ELI5 — What Are LIMIT and OFFSET?

When you Google something, you get millions of results — but Google only shows you 10 at a time. You see results 1–10 on page 1, then click "Next" to see results 11–20 on page 2. LIMIT is "how many results per page" and OFFSET is "skip this many results" (to get to the right page).

LIMIT — "Give Me Only N Rows"

-- Top 3 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Result:

name	price
Winter Jacket	129.99
Running Shoes	89.99
Wireless Headphones	79.99

OFFSET — "Skip N Rows First"

-- Page 1: first 5 products (sorted by name)
SELECT name, price
FROM products
ORDER BY name
LIMIT 5 OFFSET 0;

-- Page 2: next 5 products
SELECT name, price
FROM products
ORDER BY name
LIMIT 5 OFFSET 5;

-- Page 3: next 5 products
SELECT name, price
FROM products
ORDER BY name
LIMIT 5 OFFSET 10;

Page 1

LIMIT 5 OFFSET 0 → Rows 1–5

Page 2

LIMIT 5 OFFSET 5 → Rows 6–10

Page 3

LIMIT 5 OFFSET 10 → Rows 11–14

The formula for any page: OFFSET = (page_number - 1) × page_size

⚠️ Performance Warning — Large OFFSETs Are Slow!

When you use OFFSET 1000000, PostgreSQL still has to read and skip through one million rows before giving you the results. It's like flipping through 10,000 pages of a book to get to page 10,001 — painfully slow! For large datasets, use keyset pagination instead (also called "cursor-based pagination") where you filter by WHERE id > last_seen_id. We'll cover this in the advanced modules.

⚠️ Always Use ORDER BY With LIMIT!

Without ORDER BY, the order of rows is not guaranteed. So LIMIT 5 without ORDER BY might give you different 5 rows each time you run it! Always specify an order when using LIMIT.

5️⃣ LIKE & ILIKE — Pattern Matching

🧒 ELI5 — What Is LIKE?

Imagine you're playing a guessing game. You know the word starts with "ch" but you don't know the rest. You'd say: "I'm looking for words that start with ch-something." That's LIKE! The % symbol means "any characters can go here" and _ means "exactly one character goes here." It's like a fill-in-the-blank puzzle for text!

The Two Wildcards

Wildcard	Meaning	Example	Matches
`%`	Any number of characters (0 or more)	`'A%'`	A, Apple, Avocado, A123
`_`	Exactly one character	`'_at'`	Cat, Bat, Hat (NOT Chat)

Example 1: Names Starting With a Letter

-- Products whose name starts with 'W'
SELECT name
FROM products
WHERE name LIKE 'W%';

Result:

name
Wireless Headphones
Winter Jacket

Example 2: Names Containing a Word

-- Products with 'Coffee' anywhere in the name
SELECT name
FROM products
WHERE name LIKE '%Coffee%';

Result:

name
Organic Coffee Beans

Example 3: Names Ending With a Word

-- Products whose name ends with 'er'
SELECT name
FROM products
WHERE name LIKE '%er';

Result:

name
Bluetooth Speaker
Almond Butter

Example 4: The Underscore Wildcard

-- Products with exactly 7-letter names starting with 'Y'
SELECT name
FROM products
WHERE name LIKE 'Y______%';
-- 'Y' + six underscores + any remaining chars

ILIKE — Case-Insensitive LIKE (PostgreSQL Only!)

-- LIKE is case-SENSITIVE:
SELECT name FROM products WHERE name LIKE '%coffee%';
-- Returns: NOTHING (because our data has 'Coffee' with capital C)

-- ILIKE ignores case:
SELECT name FROM products WHERE name ILIKE '%coffee%';
-- Returns: Organic Coffee Beans ✅

💡 Pro Tip — ILIKE Is Your Friend

In the real world, user-entered data is messy — people type "NEW YORK", "New York", "new york", etc. Always use ILIKE when searching user data so you don't miss results just because of capitalization differences. This is a PostgreSQL superpower that most other databases don't have!

Bonus: Regular Expressions with ~

-- PostgreSQL supports regex! The ~ operator is case-sensitive, ~* is case-insensitive
SELECT name FROM products
WHERE name ~ '^[A-D]';
-- Names starting with A, B, C, or D

Result:

name
Cotton T-Shirt
Dark Chocolate Bar
Almond Butter
Data Science Handbook
Bluetooth Speaker

6️⃣ BETWEEN — Range Queries

🧒 ELI5 — What Is BETWEEN?

Your mom says: "You can pick any cereal that costs between $3 and $6." That means $3 is OK, $6 is OK, and everything in between is OK — but $2.99 is too cheap and $6.01 is too expensive. BETWEEN works the same way. It checks if a value falls within a range, and both ends are included!

-- Products priced between $10 and $50 (inclusive)
SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 50;

Result:

name	price
USB-C Cable	12.99
Smartphone Case	24.99
Cotton T-Shirt	19.99
Organic Coffee Beans	14.99
Python Crash Course	35.00
SQL in 10 Minutes	22.50
Data Science Handbook	45.00
Bluetooth Speaker	49.99
Yoga Mat	29.99

⚠️ BETWEEN Is Inclusive on BOTH Ends!

BETWEEN 10 AND 50 is the same as price >= 10 AND price <= 50. Both 10 and 50 are included! This catches people off guard especially with dates. BETWEEN '2024-01-01' AND '2024-01-31' includes both January 1st and January 31st.

BETWEEN With Dates

-- Products created in the last 7 days
SELECT name, created_at
FROM products
WHERE created_at BETWEEN NOW() - INTERVAL '7 days' AND NOW();

NOT BETWEEN — Exclude a Range

-- Products that are NOT in the $10–$50 range
SELECT name, price
FROM products
WHERE price NOT BETWEEN 10 AND 50;

Result:

name	price
Wireless Headphones	79.99
Running Shoes	89.99
Winter Jacket	129.99
Dark Chocolate Bar	5.99
Almond Butter	9.49

7️⃣ IN — Matching a List of Values

🧒 ELI5 — What Is IN?

Your teacher says: "Raise your hand if your birthday is in January, March, or July." She named a list of specific months, and you check if yours is in that list. IN works exactly the same — you give it a list of values, and PostgreSQL checks if each row's value is in your list.

-- Products in specific categories
SELECT name, category
FROM products
WHERE category IN ('Food', 'Books');

This is much cleaner than writing:

-- Same result, but uglier and harder to maintain
SELECT name, category
FROM products
WHERE category = 'Food'
   OR category = 'Books';

NOT IN — Exclude a List

-- Everything EXCEPT Food and Books
SELECT name, category
FROM products
WHERE category NOT IN ('Food', 'Books');

Result:

name	category
Wireless Headphones	Electronics
USB-C Cable	Electronics
Smartphone Case	Electronics
Running Shoes	Clothing
Cotton T-Shirt	Clothing
Winter Jacket	Clothing
Bluetooth Speaker	Electronics
Yoga Mat	Clothing

IN With Numbers

-- Find products with specific IDs
SELECT name, id
FROM products
WHERE id IN (1, 5, 10);

Sneak Preview: IN With a Subquery

Here's where IN gets really powerful — you can use another query inside it:

-- Find products in categories that have items priced over $100
SELECT name, category, price
FROM products
WHERE category IN (
    SELECT DISTINCT category
    FROM products
    WHERE price > 100
);

Result: All Clothing products (because Winter Jacket costs $129.99).

💡 Pro Tip — IN vs. Multiple ORs

Always prefer IN ('a', 'b', 'c') over x = 'a' OR x = 'b' OR x = 'c'. It's shorter, easier to read, and PostgreSQL's optimizer handles them identically — so there's no performance difference. Just readability.

⚠️ NOT IN and NULLs Don't Mix!

If the list in NOT IN contains a NULL, the entire result will be empty! This is because x NOT IN (1, 2, NULL) evaluates to UNKNOWN for every row. Use NOT IN only when you're 100% sure the list has no NULLs, or use NOT EXISTS instead (covered in the Joins module).

8️⃣ NULL Handling — The Trickiest Concept!

🧒 ELI5 — What Is NULL?

Imagine you ask your friend: "How many pets does Sarah have?" Your friend says: "I don't know." That's NULL! It's not zero (that would mean "Sarah has no pets"). It's not an empty string (that would mean "Sarah's pet name is blank"). NULL means "the answer is unknown." And here's the mind-bending part: if you don't know something, you can't compare it to anything! Is "I don't know" equal to 5? You don't know! Is "I don't know" equal to "I don't know"? You still don't know! Two unknowns might be different unknowns.

NULL Is NOT Zero, NOT Empty String, NOT False

0

"I have zero cookies"
(a known quantity)

''

"My name is blank"
(a known empty value)

NULL

"I don't know"
(completely unknown)

IS NULL and IS NOT NULL

You cannot use = to check for NULL. You must use IS NULL or IS NOT NULL:

-- ❌ WRONG — This will NEVER find NULLs!
SELECT name, rating
FROM products
WHERE rating = NULL;
-- Returns: NOTHING (even though 2 products have NULL ratings!)

-- ✅ CORRECT — Use IS NULL
SELECT name, rating
FROM products
WHERE rating IS NULL;

Result:

name	rating
Data Science Handbook	NULL
Yoga Mat	NULL

-- Products that DO have a rating
SELECT name, rating
FROM products
WHERE rating IS NOT NULL
ORDER BY rating DESC;

COALESCE — Provide a Default Value

🧒 ELI5 — What Is COALESCE?

Your mom asks what you want for dinner. You say: "Pizza. But if there's no pizza, then burgers. But if there are no burgers either, then just make pasta." That's COALESCE! It goes through a list of values and returns the first one that isn't NULL.

-- Replace NULL ratings with 'Not rated'
SELECT name,
       COALESCE(rating::text, 'Not rated') AS display_rating
FROM products;

Result (last few rows):

name	display_rating
SQL in 10 Minutes	4.4
Data Science Handbook	Not rated
Bluetooth Speaker	4.1
Yoga Mat	Not rated

NULLIF — The Opposite of COALESCE

-- NULLIF returns NULL if two values are equal
-- Useful to avoid division by zero!
SELECT name,
       stock,
       price / NULLIF(stock, 0) AS price_per_unit
FROM products;
-- For Bluetooth Speaker (stock = 0), NULLIF(0, 0) returns NULL
-- So price / NULL = NULL instead of a division-by-zero error!

Three-Valued Logic — The Full Truth Table

In most programming languages, things are either TRUE or FALSE. In SQL, there's a third option: UNKNOWN (anything involving NULL). This "three-valued logic" is the root of many SQL bugs.

Expression	Result	Why?
`NULL = NULL`	UNKNOWN	Two unknowns might be different
`NULL <> NULL`	UNKNOWN	Same reason
`NULL > 5`	UNKNOWN	Can't compare unknown to 5
`NULL AND TRUE`	UNKNOWN	If one side is unknown, result is unknown
`NULL AND FALSE`	FALSE	FALSE always wins with AND
`NULL OR TRUE`	TRUE	TRUE always wins with OR
`NULL OR FALSE`	UNKNOWN	If one side is unknown, result is unknown
`NOT NULL`	UNKNOWN	The opposite of unknown is still unknown

⚠️ WHERE Only Keeps TRUE Rows!

The WHERE clause only returns rows where the condition is TRUE. Rows that evaluate to FALSE or UNKNOWN are both filtered out. This is why WHERE rating = NULL returns nothing — it evaluates to UNKNOWN for every row, and UNKNOWN is not TRUE.

🌍 Real-Life Analogy

Imagine a courtroom. The verdict can be Guilty, Not Guilty, or Insufficient Evidence. NULL is like "Insufficient Evidence" — it's not the same as "Not Guilty." You can't say someone is innocent just because there isn't enough information. SQL treats UNKNOWN the same way — it refuses to make assumptions.

9️⃣ Column Aliases — Making Output Readable

🧒 ELI5 — What Are Aliases?

Your real name might be "Alexander" but everyone calls you "Alex." An alias is just a nickname you give to a column. Instead of the result showing a ugly column header like price * 1.1, you can give it a friendly name like price_with_tax.

-- Without aliases — ugly column names
SELECT name, price, price * 1.1
FROM products
LIMIT 3;

Result:

name	price	?column?
Wireless Headphones	79.99	87.989
USB-C Cable	12.99	14.289
Smartphone Case	24.99	27.489

Yuck — ?column? is not helpful at all! Let's fix that with an alias:

-- With aliases — clean, readable headers
SELECT name AS product_name,
       price AS original_price,
       price * 1.1 AS price_with_tax,
       stock * price AS inventory_value
FROM products
LIMIT 3;

Result:

product_name	original_price	price_with_tax	inventory_value
Wireless Headphones	79.99	87.99	11998.50
USB-C Cable	12.99	14.29	6495.00
Smartphone Case	24.99	27.49	7497.00

💡 Pro Tip — Aliases With Spaces

Want spaces in your alias? Use double quotes: price * 1.1 AS "Price With Tax". But in practice, stick to snake_case (underscores instead of spaces) because it's easier to work with in code.

⚠️ You Cannot Use Aliases in WHERE!

The alias is assigned after the WHERE clause runs, so WHERE price_with_tax > 50 will cause an error. You must repeat the expression: WHERE price * 1.1 > 50. You can use aliases in ORDER BY, though!

🔟 DISTINCT — Removing Duplicates

🧒 ELI5 — What Is DISTINCT?

You ask your class: "What colors are you wearing?" People shout out: "Blue! Red! Blue! Green! Blue! Red!" You only want each unique color, not duplicates. So you write down: Blue, Red, Green. That's DISTINCT — it removes repeated values from your results.

-- Without DISTINCT — shows duplicates
SELECT category FROM products;
-- Returns: Electronics, Electronics, Electronics, Clothing, Clothing,
--          Clothing, Food, Food, Food, Books, Books, Books, Electronics, Clothing

-- With DISTINCT — unique values only
SELECT DISTINCT category FROM products;

Result:

category
Electronics
Clothing
Food
Books

DISTINCT ON — PostgreSQL Superpower!

This is a PostgreSQL-exclusive feature that most databases don't have. DISTINCT ON (column) gives you the first row for each unique value of that column (based on your ORDER BY).

-- Get the cheapest product in each category
SELECT DISTINCT ON (category)
       category, name, price
FROM products
ORDER BY category, price ASC;

Result:

category	name	price
Books	SQL in 10 Minutes	22.50
Clothing	Cotton T-Shirt	19.99
Electronics	USB-C Cable	12.99
Food	Dark Chocolate Bar	5.99

🌍 Real-Life Analogy

DISTINCT ON is like asking: "In each school subject, who scored the highest?" You get one row per subject — the top student. Without DISTINCT ON, you'd need a much more complex query with subqueries or window functions to achieve the same result.

💡 Pro Tip — ORDER BY Must Match DISTINCT ON

When using DISTINCT ON (column), the ORDER BY must start with that same column. Otherwise PostgreSQL doesn't know which row to pick as the "first" one for each group. Think of it as: first group them, then within each group, pick the first one based on the remaining sort columns.

1️⃣1️⃣ CASE Expressions — SQL's If-Then-Else

🧒 ELI5 — What Is CASE?

Remember those "Choose Your Own Adventure" books? If the dragon attacks, turn to page 50. If the dragon is sleeping, turn to page 75. Otherwise, turn to page 100. The CASE expression does the same thing in SQL — it checks conditions one by one and returns different results depending on which condition is true.

Searched CASE — Check Any Condition

-- Categorize products by price range
SELECT name,
       price,
       CASE
           WHEN price < 15  THEN 'Budget'
           WHEN price < 50  THEN 'Mid-Range'
           WHEN price < 100 THEN 'Premium'
           ELSE 'Luxury'
       END AS price_tier
FROM products
ORDER BY price;

Result:

name	price	price_tier
Dark Chocolate Bar	5.99	Budget
Almond Butter	9.49	Budget
USB-C Cable	12.99	Budget
Organic Coffee Beans	14.99	Budget
Cotton T-Shirt	19.99	Mid-Range
SQL in 10 Minutes	22.50	Mid-Range
Smartphone Case	24.99	Mid-Range
Yoga Mat	29.99	Mid-Range
Python Crash Course	35.00	Mid-Range
Data Science Handbook	45.00	Mid-Range
Bluetooth Speaker	49.99	Mid-Range
Wireless Headphones	79.99	Premium
Running Shoes	89.99	Premium
Winter Jacket	129.99	Luxury

Simple CASE — Match a Single Value

-- Translate category names for a report
SELECT name,
       CASE category
           WHEN 'Electronics' THEN '⚡ Tech Gadgets'
           WHEN 'Clothing'    THEN '👕 Apparel'
           WHEN 'Food'        THEN '🍕 Groceries'
           WHEN 'Books'       THEN '📚 Reading'
           ELSE '❓ Other'
       END AS friendly_category
FROM products;

CASE in WHERE and ORDER BY

-- Custom sort: show in-stock items first, then out-of-stock
SELECT name, stock,
       CASE WHEN stock > 0 THEN 'In Stock' ELSE 'Out of Stock' END AS availability
FROM products
ORDER BY
    CASE WHEN stock > 0 THEN 0 ELSE 1 END,
    name;

⚠️ CASE Evaluates Top-to-Bottom!

CASE checks conditions in order and stops at the first match. If a product has price = 5, it matches WHEN price < 15 and never reaches WHEN price < 50. Order your conditions from most specific to least specific!

🏋️ Practice Exercises

Time to practice! Try solving these yourself before revealing the answers. Use the products table we created at the beginning.

Exercise 1: The Bargain Hunter

Find all products that are in the 'Food' category AND cost less than $10. Show the name and price, sorted by price ascending.

Reveal Solution

SELECT name, price
FROM products
WHERE category = 'Food'
  AND price < 10
ORDER BY price ASC;

Returns: Dark Chocolate Bar ($5.99), Almond Butter ($9.49)

Exercise 2: The Category Explorer

List all unique categories in our products table, sorted alphabetically.

Reveal Solution

SELECT DISTINCT category
FROM products
ORDER BY category;

Returns: Books, Clothing, Electronics, Food

Exercise 3: The Name Detective

Find all products whose name contains the word "Chocolate" or "Coffee" (case-insensitive). Show name, category, and price.

Reveal Solution

SELECT name, category, price
FROM products
WHERE name ILIKE '%chocolate%'
   OR name ILIKE '%coffee%';

Exercise 4: The Paginator

Show the 3rd page of products (3 per page), ordered by price from highest to lowest. Hint: page 3 means OFFSET = (3-1) × 3 = 6.

Reveal Solution

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 6;

Exercise 5: The NULL Wrangler

Show all products with their rating. For products without a rating, display "Unrated" instead of NULL. Name the column display_rating. Sort by rating descending, with NULLs last.

Reveal Solution

SELECT name,
       COALESCE(rating::text, 'Unrated') AS display_rating
FROM products
ORDER BY rating DESC NULLS LAST;

Exercise 6: The Report Builder (Challenge!)

Create a report showing each product's name, price, and a stock_status column that says: "Out of Stock" if stock = 0, "Low Stock" if stock < 50, "In Stock" if stock < 200, and "Well Stocked" for everything else. Only include products priced between $10 and $100. Sort by stock ascending.

Reveal Solution

SELECT name,
       price,
       CASE
           WHEN stock = 0   THEN 'Out of Stock'
           WHEN stock < 50  THEN 'Low Stock'
           WHEN stock < 200 THEN 'In Stock'
           ELSE 'Well Stocked'
       END AS stock_status
FROM products
WHERE price BETWEEN 10 AND 100
ORDER BY stock ASC;

📝 Quiz — Test Your Knowledge!

Question 1: What does `WHERE price BETWEEN 10 AND 50` do?

Question 2: Which is the correct way to check for NULL?

Question 3: What does `ILIKE` do that `LIKE` doesn't?

Question 4: What is the result of `NULL = NULL` in SQL?

Question 5: What is special about `DISTINCT ON` in PostgreSQL?

Question 6: Why should you always use `ORDER BY` with `LIMIT`?

Question 7: What does `COALESCE(rating, 0)` return?

🎉 What's Next?

11

Query techniques learned

30+

SQL examples practiced

7

Quiz questions conquered

Congratulations! You've mastered the fundamentals of querying data in PostgreSQL. Here's a quick recap of what you learned:

Concept	What It Does	Key Syntax
WHERE	Filters rows	`WHERE price > 50`
AND / OR / NOT	Combines conditions	`WHERE a AND (b OR c)`
ORDER BY	Sorts results	`ORDER BY price DESC`
LIMIT / OFFSET	Paginates results	`LIMIT 10 OFFSET 20`
LIKE / ILIKE	Pattern matching	`WHERE name ILIKE '%phone%'`
BETWEEN	Range queries	`WHERE price BETWEEN 10 AND 50`
IN	List matching	`WHERE category IN ('A','B')`
IS NULL	Check for NULL	`WHERE rating IS NULL`
COALESCE	Default for NULLs	`COALESCE(rating, 0)`
DISTINCT / DISTINCT ON	Remove duplicates	`SELECT DISTINCT category`
CASE	Conditional logic	`CASE WHEN ... THEN ... END`

🔮 Coming Up Next: Aggregations & Grouping

Now that you can find data, it's time to summarize it! In the next module, you'll learn how to count rows, calculate averages, find minimums and maximums, and group your data to create powerful summary reports. Think of it like going from "show me all the sales" to "what were the total sales per region this month?" — that's where the real insights live!

🔍 Querying Data

📦 Our Practice Dataset

🧒 ELI5 — Why Do We Need Sample Data?

💡 Pro Tip — Notice the NULLs!

1️⃣ The WHERE Clause — Filtering Rows

🧒 ELI5 — What Is WHERE?

Comparison Operators

Example 1: Find All Electronics

Example 2: Find Products Under $20

Example 3: Products That Are Out of Stock

🌍 Real-Life Analogy

⚠️ Common Mistake — Strings Need Quotes!

2️⃣ Logical Operators: AND, OR, NOT

🧒 ELI5 — Combining Conditions

AND — Both Conditions Must Be True

OR — At Least One Condition Must Be True

NOT — Flip the Condition

⚡ Operator Precedence — AND Before OR!

⚠️ This Is a Classic Trap!

Truth Table — How AND, OR, NOT Work

💡 Pro Tip — Readability Matters

3️⃣ ORDER BY — Sorting Results

🧒 ELI5 — What Is ORDER BY?

ASC (Ascending) — The Default

DESC (Descending) — Reverse Order

Sorting by Multiple Columns

🌍 Real-Life Analogy

NULLS FIRST / NULLS LAST — PostgreSQL Exclusive!

💡 Pro Tip — NULLS FIRST/LAST

4️⃣ LIMIT & OFFSET — Pagination

🧒 ELI5 — What Are LIMIT and OFFSET?

LIMIT — "Give Me Only N Rows"

OFFSET — "Skip N Rows First"

⚠️ Performance Warning — Large OFFSETs Are Slow!

⚠️ Always Use ORDER BY With LIMIT!

5️⃣ LIKE & ILIKE — Pattern Matching

🧒 ELI5 — What Is LIKE?

The Two Wildcards

Example 1: Names Starting With a Letter

Example 2: Names Containing a Word

Example 3: Names Ending With a Word

Example 4: The Underscore Wildcard

ILIKE — Case-Insensitive LIKE (PostgreSQL Only!)

💡 Pro Tip — ILIKE Is Your Friend

Bonus: Regular Expressions with ~

6️⃣ BETWEEN — Range Queries

🧒 ELI5 — What Is BETWEEN?

⚠️ BETWEEN Is Inclusive on BOTH Ends!

BETWEEN With Dates

NOT BETWEEN — Exclude a Range

7️⃣ IN — Matching a List of Values

🧒 ELI5 — What Is IN?

NOT IN — Exclude a List

IN With Numbers

Sneak Preview: IN With a Subquery

💡 Pro Tip — IN vs. Multiple ORs

⚠️ NOT IN and NULLs Don't Mix!

8️⃣ NULL Handling — The Trickiest Concept!

🧒 ELI5 — What Is NULL?

NULL Is NOT Zero, NOT Empty String, NOT False

IS NULL and IS NOT NULL

COALESCE — Provide a Default Value

🧒 ELI5 — What Is COALESCE?

NULLIF — The Opposite of COALESCE

Three-Valued Logic — The Full Truth Table

⚠️ WHERE Only Keeps TRUE Rows!

🌍 Real-Life Analogy

9️⃣ Column Aliases — Making Output Readable

🧒 ELI5 — What Are Aliases?

💡 Pro Tip — Aliases With Spaces

⚠️ You Cannot Use Aliases in WHERE!

🔟 DISTINCT — Removing Duplicates

🧒 ELI5 — What Is DISTINCT?

DISTINCT ON — PostgreSQL Superpower!

🌍 Real-Life Analogy

💡 Pro Tip — ORDER BY Must Match DISTINCT ON

1️⃣1️⃣ CASE Expressions — SQL's If-Then-Else

🧒 ELI5 — What Is CASE?

Searched CASE — Check Any Condition

Simple CASE — Match a Single Value

Question 1: What does `WHERE price BETWEEN 10 AND 50` do?

Question 3: What does `ILIKE` do that `LIKE` doesn't?

Question 4: What is the result of `NULL = NULL` in SQL?

Question 5: What is special about `DISTINCT ON` in PostgreSQL?

Question 6: Why should you always use `ORDER BY` with `LIMIT`?

Question 7: What does `COALESCE(rating, 0)` return?