SQL WHERE, ORDER BY & LIMIT: Filter & Sort Data

SQL WHERE, ORDER BY & LIMIT Tutorial: Filter & Sort Data

📖 Introduction: From "Show Everything" to "Show Exactly What I Want"

In Module 2, you learned to select data. Now you'll learn to control it. Real-world databases contain millions of rows — you never want to see everything at once. You need to filter, sort, and limit results.

Think of it like online shopping:

Filter (WHERE): Show only laptops under $1000

Sort (ORDER BY): Sort by price: low to high

Limit (LIMIT): Show only the first 10 results

🛠️ Setting Up Practice Data

Let's create a products table for this module:

sql
1CREATE TABLE products (
2    product_id INT PRIMARY KEY,
3    product_name VARCHAR(100),
4    category VARCHAR(50),
5    price DECIMAL(10,2),
6    stock_quantity INT,
7    rating DECIMAL(2,1),
8    brand VARCHAR(50)
9);
10
11INSERT INTO products VALUES
12(1, 'Wireless Mouse', 'Electronics', 29.99, 150, 4.5, 'Logitech'),
13(2, 'Gaming Laptop', 'Electronics', 1299.99, 25, 4.8, 'ASUS'),
14(3, 'Office Chair', 'Furniture', 199.99, 45, 4.2, 'Herman Miller'),
15(4, 'USB-C Cable', 'Electronics', 12.99, 500, 3.9, 'Anker'),
16(5, 'Standing Desk', 'Furniture', 499.99, 15, 4.6, 'FlexiSpot'),
17(6, 'Mechanical Keyboard', 'Electronics', 149.99, 80, 4.7, 'Keychron'),
18(7, 'Desk Lamp', 'Furniture', 39.99, 200, 4.0, 'BenQ'),
19(8, 'Monitor 27 inch', 'Electronics', 349.99, 30, 4.4, 'Dell'),
20(9, 'Bookshelf', 'Furniture', 89.99, 60, 3.8, 'IKEA'),
21(10, 'Webcam HD', 'Electronics', 79.99, 120, 4.3, 'Logitech');

1️⃣ WHERE — Conditional Filtering

Purpose: WHERE acts like a gatekeeper. Only rows that meet your condition(s) pass through.

Syntax:

sql
1SELECT column(s) FROM table_name WHERE condition;

Comparison Operators

Operator	Meaning	Example
`=`	Equal to	`price = 29.99`
`<>` or `!=`	Not equal to	`brand <> 'Apple'`
`>`	Greater than	`price > 100`
`<`	Less than	`stock_quantity < 50`
`>=`	Greater than or equal	`rating >= 4.0`
`<=`	Less than or equal	`price <= 50`

Example 1: Filter by Exact Match (`=`)

sql
1SELECT product_name, price FROM products
2WHERE category = 'Electronics';

Output:

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| Wireless Mouse    | 29.99  |
| Gaming Laptop     |1299.99 |
| USB-C Cable       | 12.99  |
| Mechanical Keyboard|149.99 |
| Monitor 27 inch   | 349.99 |
| Webcam HD         | 79.99  |
+-------------------+--------+

Example 2: Filter by Greater Than (`>`)

sql
1SELECT product_name, price, stock_quantity FROM products
2WHERE price > 100;

Output:

+-------------------+--------+----------------+
| product_name      | price  | stock_quantity |
+-------------------+--------+----------------+
| Gaming Laptop     |1299.99 | 25             |
| Office Chair      | 199.99 | 45             |
| Standing Desk     | 499.99 | 15             |
| Mechanical Keyboard|149.99 | 80             |
| Monitor 27 inch   | 349.99 | 30             |
+-------------------+--------+----------------+

Example 3: Filter by Not Equal (`<>`)

sql
1SELECT product_name, brand FROM products
2WHERE brand <> 'Logitech';

Output:

+-------------------+---------------+
| product_name      | brand         |
+-------------------+---------------+
| Gaming Laptop     | ASUS          |
| Office Chair      | Herman Miller |
| USB-C Cable       | Anker         |
| Standing Desk     | FlexiSpot     |
| Mechanical Keyboard| Keychron     |
| Desk Lamp         | BenQ          |
| Bookshelf         | IKEA          |
| Monitor 27 inch   | Dell          |
+-------------------+---------------+

2️⃣ Logical Operators — AND, OR, NOT

Combine multiple conditions to create precise filters.

Operator	Meaning	Result
`AND`	Both conditions must be true	`price > 100 AND stock > 50`
`OR`	At least one condition must be true	`brand = 'Logitech' OR brand = 'Dell'`
`NOT`	Reverses the condition	`NOT category = 'Electronics'`

Example 4: AND — Both Conditions Must Match

sql
1SELECT product_name, price, stock_quantity FROM products
2WHERE category = 'Electronics' AND price > 100;

Logic: Must be Electronics AND price over $100.

Output:

+-------------------+--------+----------------+
| product_name      | price  | stock_quantity |
+-------------------+--------+----------------+
| Gaming Laptop     |1299.99 | 25             |
| Mechanical Keyboard|149.99 | 80             |
| Monitor 27 inch   | 349.99 | 30             |
+-------------------+--------+----------------+

Note: USB-C Cable ($12.99) is Electronics but fails the price condition. Excluded!

Example 5: OR — Either Condition Matches

sql
1SELECT product_name, brand, price FROM products
2WHERE brand = 'Logitech' OR brand = 'Dell';

Logic: Brand is Logitech OR brand is Dell.

Output:

+-------------------+----------+-------+
| product_name      | brand    | price |
+-------------------+----------+-------+
| Wireless Mouse    | Logitech | 29.99 |
| Webcam HD         | Logitech | 79.99 |
| Monitor 27 inch   | Dell     |349.99 |
+-------------------+----------+-------+

Example 6: NOT — Exclude Matching Rows

sql
1SELECT product_name, category FROM products
2WHERE NOT category = 'Electronics';

Output:

+-------------------+-----------+
| product_name      | category  |
+-------------------+-----------+
| Office Chair      | Furniture |
| Standing Desk     | Furniture |
| Desk Lamp         | Furniture |
| Bookshelf         | Furniture |
+-------------------+-----------+

Example 7: Combining AND + OR (Use Parentheses!)

sql
1SELECT product_name, price, brand FROM products
2WHERE (brand = 'Logitech' OR brand = 'Dell')
3  AND price < 100;

Logic: Brand is Logitech or Dell AND price is under $100.

Output:

+-------------------+-------+----------+
| product_name      | price | brand    |
+-------------------+-------+----------+
| Wireless Mouse    | 29.99 | Logitech |
| Webcam HD         | 79.99 | Logitech |
+-------------------+-------+----------+

⚠️ Important: Always use parentheses when mixing AND and OR. Without them, AND is evaluated before OR, which can give unexpected results!

3️⃣ LIKE — Pattern Matching for Text

Purpose: Search for text patterns using wildcards.

Wildcard	Meaning	Example
`%`	Matches any sequence of characters (0 or more)	`'A%'` = starts with A
`_`	Matches exactly one character	`'A_'` = A followed by one char

Example 8: Starts With (`%` at end)

sql
1SELECT product_name, price FROM products
2WHERE product_name LIKE 'M%';

Logic: Product name starts with the letter M.

Output:

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| Mechanical Keyboard|149.99 |
| Monitor 27 inch   | 349.99 |
+-------------------+--------+

Example 9: Ends With (`%` at start)

sql
1SELECT product_name FROM products
2WHERE product_name LIKE '%Desk';

Logic: Product name ends with Desk.

Output:

+---------------+
| product_name  |
+---------------+
| Standing Desk |
+---------------+

Example 10: Contains (`%` on both sides)

sql
1SELECT product_name, brand FROM products
2WHERE product_name LIKE '%Mouse%';

Logic: Product name contains Mouse anywhere.

Output:

+----------------+----------+
| product_name   | brand    |
+----------------+----------+
| Wireless Mouse | Logitech |
+----------------+----------+

Example 11: Single Character Match (`_`)

sql
1SELECT product_name FROM products
2WHERE product_name LIKE 'D_sk%';

Logic: Starts with D, then any single character, then sk, then anything.

Output:

+---------------+
| product_name  |
+---------------+
| Desk Lamp     |
+---------------+

Example 12: NOT LIKE — Exclude Patterns

sql
1SELECT product_name FROM products
2WHERE product_name NOT LIKE '%Cable%';

Output: All products except those containing "Cable".

4️⃣ BETWEEN — Range Filtering

Purpose: Select values within a range (inclusive of both endpoints).

Syntax:

sql
1WHERE column_name BETWEEN value1 AND value2;

Example 13: Price Range

sql
1SELECT product_name, price FROM products
2WHERE price BETWEEN 50 AND 200;

Logic: Price is $50 or more** AND **$ 200 or less.

Output:

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| Office Chair      | 199.99 |
| Mechanical Keyboard|149.99 |
| Webcam HD         | 79.99  |
+-------------------+--------+

Note: BETWEEN is inclusive. price BETWEEN 50 AND 200 means price >= 50 AND price <= 200.

Example 14: Date Range (if you had dates)

sql
1SELECT * FROM orders
2WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

Example 15: NOT BETWEEN

sql
1SELECT product_name, price FROM products
2WHERE price NOT BETWEEN 50 AND 500;

Output: Products cheaper than $50 or more expensive than$ 500.

5️⃣ IN — Match Multiple Specific Values

Purpose: Check if a value matches any item in a list. Cleaner than multiple OR conditions.

Syntax:

sql
1WHERE column_name IN (value1, value2, value3);

Example 16: IN with Text

sql
1SELECT product_name, brand, price FROM products
2WHERE brand IN ('Logitech', 'Dell', 'ASUS');

Equivalent to:

sql
1WHERE brand = 'Logitech' OR brand = 'Dell' OR brand = 'ASUS';

Output:

+-------------------+----------+--------+
| product_name      | brand    | price  |
+-------------------+----------+--------+
| Wireless Mouse    | Logitech | 29.99  |
| Gaming Laptop     | ASUS     |1299.99 |
| Webcam HD         | Logitech | 79.99  |
| Monitor 27 inch   | Dell     | 349.99 |
+-------------------+----------+--------+

Example 17: IN with Numbers

sql
1SELECT product_name, stock_quantity FROM products
2WHERE stock_quantity IN (15, 25, 45);

Output:

+-------------------+----------------+
| product_name      | stock_quantity |
+-------------------+----------------+
| Gaming Laptop     | 25             |
| Office Chair      | 45             |
| Standing Desk     | 15             |
+-------------------+----------------+

Example 18: NOT IN

sql
1SELECT product_name, brand FROM products
2WHERE brand NOT IN ('Logitech', 'IKEA');

6️⃣ ORDER BY — Sorting Results

Purpose: Arrange results in a specific order (ascending or descending).

Syntax:

sql
1SELECT columns FROM table
2ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Keyword	Meaning	Default?
`ASC`	Ascending (A→Z, 0→9)	✅ Yes
`DESC`	Descending (Z→A, 9→0)	❌ No

Example 19: Sort by Price (Low to High)

sql
1SELECT product_name, price FROM products
2ORDER BY price ASC;

Output:

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| USB-C Cable       | 12.99  |
| Wireless Mouse    | 29.99  |
| Desk Lamp         | 39.99  |
| Bookshelf         | 89.99  |
| Webcam HD         | 79.99  |
| Mechanical Keyboard|149.99 |
| Office Chair      | 199.99 |
| Monitor 27 inch   | 349.99 |
| Standing Desk     | 499.99 |
| Gaming Laptop     |1299.99 |
+-------------------+--------+

Example 20: Sort by Price (High to Low)

sql
1SELECT product_name, price FROM products
2ORDER BY price DESC;

Example 21: Sort by Multiple Columns

sql
1SELECT category, product_name, price FROM products
2ORDER BY category ASC, price DESC;

Logic: First sort by category (A→Z), then within each category, sort by price (high→low).

Output:

+-----------+-------------------+--------+
| category  | product_name      | price  |
+-----------+-------------------+--------+
| Electronics| Gaming Laptop    |1299.99 |
| Electronics| Monitor 27 inch   | 349.99 |
| Electronics| Mechanical Keyboard|149.99|
| Electronics| Webcam HD         | 79.99  |
| Electronics| Wireless Mouse    | 29.99  |
| Electronics| USB-C Cable       | 12.99  |
| Furniture | Standing Desk     | 499.99 |
| Furniture | Office Chair      | 199.99 |
| Furniture | Bookshelf         | 89.99  |
| Furniture | Desk Lamp         | 39.99  |
+-----------+-------------------+--------+

Example 22: Sort by Rating, Then Name

sql
1SELECT product_name, rating FROM products
2ORDER BY rating DESC, product_name ASC;

7️⃣ LIMIT / TOP / FETCH — Limiting Results

Purpose: Return only a specific number of rows. Essential for large datasets.

Database	Syntax
MySQL / PostgreSQL / SQLite	`LIMIT number`
SQL Server	`TOP number`
Oracle	`FETCH FIRST number ROWS ONLY`

Example 23: MySQL / PostgreSQL — LIMIT

sql
1SELECT product_name, price FROM products
2ORDER BY price DESC
3LIMIT 3;

Output: Top 3 most expensive products.

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| Gaming Laptop     |1299.99 |
| Standing Desk     | 499.99 |
| Monitor 27 inch   | 349.99 |
+-------------------+--------+

Example 24: LIMIT with OFFSET (Skip rows)

sql
1SELECT product_name, price FROM products
2ORDER BY price DESC
3LIMIT 3 OFFSET 3;

Logic: Skip the first 3, then return the next 3. (Useful for pagination!)

Output: Products ranked 4th, 5th, 6th by price.

Example 25: SQL Server — TOP

sql
1SELECT TOP 3 product_name, price FROM products
2ORDER BY price DESC;

Example 26: Oracle — FETCH FIRST

sql
1SELECT product_name, price FROM products
2ORDER BY price DESC
3FETCH FIRST 3 ROWS ONLY;

Example 27: Combine Everything!

sql
1SELECT product_name, brand, price, rating
2FROM products
3WHERE category = 'Electronics'
4  AND price BETWEEN 50 AND 500
5  AND rating >= 4.0
6ORDER BY rating DESC, price ASC
7LIMIT 5;

Logic:

Filter: Only Electronics, $50–$ 500, rating 4.0+
Sort: Highest rating first, then lowest price
Limit: Return only top 5

Output:

+-------------------+----------+--------+--------+
| product_name      | brand    | price  | rating |
+-------------------+----------+--------+--------+
| Mechanical Keyboard| Keychron |149.99 | 4.7    |
| Monitor 27 inch   | Dell     |349.99 | 4.4    |
| Webcam HD         | Logitech | 79.99 | 4.3    |
+-------------------+----------+--------+--------+

📝 Complete Command Reference

Command	What It Does	Example
`WHERE`	Filter rows by condition	`WHERE price > 100`
`AND`	Both conditions true	`WHERE a=1 AND b=2`
`OR`	At least one true	`WHERE a=1 OR b=2`
`NOT`	Reverse condition	`WHERE NOT x=5`
`LIKE`	Pattern matching	`WHERE name LIKE 'A%'`
`BETWEEN`	Range (inclusive)	`WHERE price BETWEEN 10 AND 100`
`IN`	Match any in list	`WHERE brand IN ('A','B')`
`ORDER BY`	Sort results	`ORDER BY price DESC`
`LIMIT`	Restrict row count	`LIMIT 10`

🚀 Hands-On Project: Product Search Filter

Build a search query for an e-commerce site with multiple conditions.

Scenario

A customer wants to find:

Electronics or Furniture
Price between $20 and$ 400
Rating 4.0 or higher
Not from brand 'IKEA'
Sorted by highest rating, then lowest price
Show only top 5 results

Solution

sql
1SELECT 
2    product_name,
3    brand,
4    category,
5    price,
6    rating
7FROM products
8WHERE category IN ('Electronics', 'Furniture')
9  AND price BETWEEN 20 AND 400
10  AND rating >= 4.0
11  AND brand <> 'IKEA'
12ORDER BY rating DESC, price ASC
13LIMIT 5;

Output:

+-------------------+----------+-----------+--------+--------+
| product_name      | brand    | category  | price  | rating |
+-------------------+----------+-----------+--------+--------+
| Mechanical Keyboard| Keychron | Electronics|149.99 | 4.7    |
| Standing Desk     | FlexiSpot| Furniture | 499.99 | 4.6    |
| Monitor 27 inch   | Dell     | Electronics|349.99 | 4.4    |
| Webcam HD         | Logitech | Electronics| 79.99 | 4.3    |
| Office Chair      | Herman Miller| Furniture|199.99 | 4.2    |
+-------------------+----------+-----------+--------+--------+

Note: Standing Desk ( $499.99) is excluded because it exceeds$ 400. The actual top 5 would adjust accordingly!

✅ Module 3 Summary

Concept	Command	Remember
Filter rows	`WHERE condition`	Only matching rows pass through
Combine conditions	`AND`, `OR`, `NOT`	Use parentheses with mixed operators
Text patterns	`LIKE '%text%'`	`%` = any chars, `_` = one char
Range filter	`BETWEEN a AND b`	Inclusive of both ends
List matching	`IN (a, b, c)`	Cleaner than multiple ORs
Sort results	`ORDER BY col DESC`	ASC is default
Limit output	`LIMIT n`	Essential for large datasets

🎯 Practice Exercises

Find all products with stock less than 50.
Find products where brand is 'Logitech' and price is under $50.
Find products whose name contains "Desk".
Find products with price between $100 and$ 500.
Find products from brands 'ASUS', 'Dell', or 'BenQ'.
Sort all products by stock_quantity ascending.
Get the top 3 highest-rated products.
Combine: Electronics, price > $50, sorted by price high→low, limit to 3 results.

🎓 What's Next?

In Module 4, you'll master Aggregate Functions (COUNT, SUM, AVG, MAX, MIN) and GROUP BY to summarize data. You'll learn to answer questions like "What's the average salary per department?" and "How many products are in each category?" 🚀

SQL WHERE, ORDER BY & LIMIT Tutorial: Filter & Sort Data

📖 Introduction: From "Show Everything" to "Show Exactly What I Want"

🛠️ Setting Up Practice Data

1️⃣ WHERE — Conditional Filtering

Comparison Operators

Example 1: Filter by Exact Match (=)

Example 2: Filter by Greater Than (>)

Example 3: Filter by Not Equal (<>)

2️⃣ Logical Operators — AND, OR, NOT

Example 4: AND — Both Conditions Must Match

Example 5: OR — Either Condition Matches

Example 6: NOT — Exclude Matching Rows

Example 7: Combining AND + OR (Use Parentheses!)

3️⃣ LIKE — Pattern Matching for Text

Example 8: Starts With (% at end)

Example 9: Ends With (% at start)

Example 10: Contains (% on both sides)

Example 11: Single Character Match (_)

Example 12: NOT LIKE — Exclude Patterns

4️⃣ BETWEEN — Range Filtering

Example 13: Price Range

Example 14: Date Range (if you had dates)

Example 15: NOT BETWEEN

5️⃣ IN — Match Multiple Specific Values

Example 16: IN with Text

Example 17: IN with Numbers

Example 18: NOT IN

6️⃣ ORDER BY — Sorting Results

Example 19: Sort by Price (Low to High)

Example 20: Sort by Price (High to Low)

Example 21: Sort by Multiple Columns

Example 22: Sort by Rating, Then Name

7️⃣ LIMIT / TOP / FETCH — Limiting Results

Example 23: MySQL / PostgreSQL — LIMIT

Example 24: LIMIT with OFFSET (Skip rows)

Example 25: SQL Server — TOP

Example 26: Oracle — FETCH FIRST

Example 27: Combine Everything!

📝 Complete Command Reference

🚀 Hands-On Project: Product Search Filter

Scenario

Solution

✅ Module 3 Summary

🎯 Practice Exercises

🎓 What's Next?

Example 1: Filter by Exact Match (`=`)

Example 2: Filter by Greater Than (`>`)

Example 3: Filter by Not Equal (`<>`)

Example 8: Starts With (`%` at end)

Example 9: Ends With (`%` at start)

Example 10: Contains (`%` on both sides)

Example 11: Single Character Match (`_`)