Lesson 1: SELECT Basics — Asking Questions
Master the fundamentals of SQL queries: SELECT, FROM, WHERE, filtering, pattern matching, NULL handling, sorting, and limiting results.
Lesson 1: SELECT Basics — Asking Questions 💻
Introduction: Talking to Your Database 🗣️
Imagine walking into a massive library with millions of books. You need specific information, but you can't read every book. Instead, you ask a librarian: "Show me all mystery novels published after 2020, sorted by author." That librarian is SQL (Structured Query Language), and the library is your database.
SQL is the universal language for asking databases questions. Whether you're using PostgreSQL, MySQL, SQLite, or SQL Server, the core SQL commands work the same way. Today, you'll learn to ask your first questions using SELECT statements.
Core Concepts: Building Your First Queries 🏗️
The SELECT Statement: Your Basic Question
Every SQL query starts with a question: "What data do I want to see?" The SELECT statement is how you ask that question.
Basic Structure:
SELECT column_name
FROM table_name;
Think of a database table like a spreadsheet:
- Rows = individual records (like customers, products, orders)
- Columns = attributes or properties (like name, price, date)
+----+------------+-------+--------+
| id | name | price | stock |
+----+------------+-------+--------+
| 1 | Laptop | 999 | 15 |
| 2 | Mouse | 25 | 150 |
| 3 | Keyboard | 75 | 80 |
| 4 | Monitor | 350 | 45 |
+----+------------+-------+--------+
Table: products
Selecting Specific Columns:
SELECT name, price FROM products;
This returns only the name and price columns. Use commas to separate multiple columns.
Selecting All Columns:
SELECT * FROM products;
The asterisk (*) means "all columns." It's quick for exploration, but in production code, specify exact columns for clarity and performance.
💡 Tip: SQL keywords (SELECT, FROM, WHERE) are case-insensitive, but it's convention to write them in UPPERCASE for readability.
The WHERE Clause: Filtering Your Results 🔍
Most of the time, you don't want ALL records—you want specific ones. The WHERE clause filters results based on conditions.
Basic Structure:
SELECT column_name
FROM table_name
WHERE condition;
Comparison Operators:
+----------+----------------------+
| Operator | Meaning |
+----------+----------------------+
| = | Equal to |
| != | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal |
| >= | Greater than or equal|
+----------+----------------------+
Example:
SELECT name, price
FROM products
WHERE price > 100;
This returns only products with prices greater than 100.
Combining Conditions: AND, OR, NOT 🔗
Real questions often need multiple conditions. Use logical operators to combine them:
AND - Both conditions must be true:
SELECT name, price
FROM products
WHERE price > 50 AND stock < 100;
OR - At least one condition must be true:
SELECT name, price
FROM products
WHERE price < 30 OR stock > 100;
NOT - Negates a condition:
SELECT name
FROM products
WHERE NOT price > 100;
(This finds products with price ≤ 100)
Combining Multiple Operators:
SELECT name, price
FROM products
WHERE (price > 50 AND stock > 20) OR name = 'Mouse';
💡 Tip: Use parentheses to control evaluation order, just like in math. Without them, AND is evaluated before OR.
Logical Flow Diagram:
WHERE condition
|
┌────┴────┐
TRUE FALSE
|
Include row Skip row
Pattern Matching with LIKE 🎯
Sometimes you don't know the exact value—you're looking for a pattern. The LIKE operator searches for text patterns.
Wildcards:
- % = any number of characters (including zero)
- _ = exactly one character
+----------+------------------------+
| Pattern | Matches |
+----------+------------------------+
| 'A%' | Starts with A |
| '%a' | Ends with a |
| '%app%' | Contains 'app' |
| '_at' | 3 letters ending in 'at'|
| 'L____' | 5 letters starting with L|
+----------+------------------------+
Examples:
SELECT name FROM products WHERE name LIKE 'M%';
-- Finds: Mouse, Monitor
SELECT name FROM products WHERE name LIKE '%top';
-- Finds: Laptop
SELECT name FROM products WHERE name LIKE '%o%';
-- Finds: Laptop, Mouse, Monitor
⚠️ Case Sensitivity: LIKE behavior varies by database. PostgreSQL is case-sensitive; use ILIKE for case-insensitive searches. MySQL is case-insensitive by default.
Handling NULL Values 🕳️
NULL represents missing or unknown data. It's not zero, not empty string—it's the absence of a value.
Important: You CANNOT use = or != with NULL. Use special operators:
SELECT name FROM products WHERE description IS NULL;
-- Finds products with no description
SELECT name FROM products WHERE description IS NOT NULL;
-- Finds products with a description
Why NULL is Special:
+-------------------+---------+
| Expression | Result |
+-------------------+---------+
| NULL = NULL | NULL |
| NULL != NULL | NULL |
| NULL > 5 | NULL |
| 5 + NULL | NULL |
| NULL IS NULL | TRUE |
+-------------------+---------+
NULL "infects" operations—any calculation involving NULL returns NULL.
🧠 Mnemonic: Think of NULL as "No Understanding of Literal Logic"—it doesn't behave like normal values.
Sorting Results: ORDER BY 📊
Results come in no guaranteed order unless you specify. The ORDER BY clause sorts results.
Basic Structure:
SELECT column_name
FROM table_name
ORDER BY column_name ASC|DESC;
- ASC = ascending (default, smallest to largest, A to Z)
- DESC = descending (largest to smallest, Z to A)
Examples:
SELECT name, price FROM products ORDER BY price ASC;
-- Cheapest first
SELECT name, price FROM products ORDER BY price DESC;
-- Most expensive first
SELECT name, price FROM products ORDER BY name;
-- Alphabetical (ASC is default)
Multiple Sort Columns:
SELECT name, price, stock
FROM products
ORDER BY price DESC, stock ASC;
This sorts by price (descending) first. When prices are equal, it sorts by stock (ascending).
Sort Order Visualization:
Unsorted: [3, 1, 4, 1, 5]
|
ORDER BY value ASC
|
v
Sorted: [1, 1, 3, 4, 5]
Limiting Results: LIMIT 🎚️
Sometimes you only want the first few results—say, the top 10 products or a sample for testing.
LIMIT restricts the number of rows returned:
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;
This returns the 5 most expensive products.
⚠️ Database Differences:
- PostgreSQL, MySQL, SQLite: LIMIT
- SQL Server: TOP (e.g.,
SELECT TOP 5 name FROM products) - Oracle: ROWNUM or FETCH FIRST
For universal compatibility, learn your specific database's syntax, but LIMIT is most common.
💡 Tip: Always use ORDER BY with LIMIT. Without it, you get arbitrary rows—not useful!
Detailed Examples 📖
Example 1: Finding Affordable Products in Stock
Scenario: An online store wants to show customers products under $100 that are currently in stock.
SELECT name, price, stock
FROM products
WHERE price < 100 AND stock > 0
ORDER BY price ASC;
Breakdown:
- SELECT name, price, stock - We want these three columns
- FROM products - Looking in the products table
- WHERE price < 100 AND stock > 0 - Two conditions: affordable AND available
- ORDER BY price ASC - Show cheapest first
Result:
+----------+-------+-------+
| name | price | stock |
+----------+-------+-------+
| Mouse | 25 | 150 |
| Keyboard | 75 | 80 |
+----------+-------+-------+
Why it works: The AND operator ensures BOTH conditions are met. Sorting by price helps customers find the best deals first.
Example 2: Searching for Products by Name
Scenario: A customer types "key" in the search box. Find all matching products.
SELECT name, price
FROM products
WHERE name LIKE '%key%'
ORDER BY name;
Breakdown:
- LIKE '%key%' - The % wildcards mean "key" can appear anywhere in the name
- Case-insensitive in most databases, so matches "Keyboard", "Monkey Wrench", "Turkey Baster"
- ORDER BY name - Alphabetical results
Result:
+----------+-------+
| name | price |
+----------+-------+
| Keyboard | 75 |
+----------+-------+
Real-world usage: Search functionality in e-commerce sites uses LIKE extensively. For better performance at scale, use full-text search features.
Example 3: Top 3 Most Expensive Products
Scenario: Display featured premium products on the homepage.
SELECT name, price
FROM products
WHERE stock > 0
ORDER BY price DESC
LIMIT 3;
Breakdown:
- WHERE stock > 0 - Only show available products (don't advertise out-of-stock items)
- ORDER BY price DESC - Most expensive first
- LIMIT 3 - Only top 3
Result:
+----------+-------+
| name | price |
+----------+-------+
| Laptop | 999 |
| Monitor | 350 |
| Keyboard | 75 |
+----------+-------+
Why the order matters: Without ORDER BY, LIMIT would give you 3 random products. Always pair them.
Example 4: Finding Products with Missing Descriptions
Scenario: Quality control needs to find products without descriptions to update them.
SELECT id, name, price
FROM products
WHERE description IS NULL
ORDER BY id;
Breakdown:
- description IS NULL - Finds records where description was never set
- NOT using = NULL (common mistake!)
- ORDER BY id - Systematic order for team to work through
Common mistake avoided:
-- WRONG:
WHERE description = NULL -- Returns nothing!
-- RIGHT:
WHERE description IS NULL -- Works correctly
Business value: Data quality checks like this keep your database clean and complete.
Common Mistakes ⚠️
1. Using = with NULL
-- WRONG:
SELECT * FROM products WHERE description = NULL;
-- Returns zero rows even if NULL values exist
-- RIGHT:
SELECT * FROM products WHERE description IS NULL;
2. Forgetting Quotes Around Text
-- WRONG:
SELECT * FROM products WHERE name = Mouse;
-- Database thinks Mouse is a column name!
-- RIGHT:
SELECT * FROM products WHERE name = 'Mouse';
-- Single quotes for text values
3. Confusing AND/OR Logic
-- This probably doesn't do what you think:
SELECT * FROM products
WHERE price > 100 OR price < 50 AND stock > 20;
-- AND is evaluated first!
-- Clearer with parentheses:
SELECT * FROM products
WHERE (price > 100 OR price < 50) AND stock > 20;
4. Using LIMIT Without ORDER BY
-- WRONG (unpredictable):
SELECT * FROM products LIMIT 5;
-- Which 5? Random!
-- RIGHT (intentional):
SELECT * FROM products ORDER BY price DESC LIMIT 5;
-- Top 5 by price
5. Case Sensitivity with LIKE
-- PostgreSQL:
SELECT * FROM products WHERE name LIKE 'laptop';
-- Doesn't match 'Laptop' (case-sensitive)
-- Use ILIKE for case-insensitive:
SELECT * FROM products WHERE name ILIKE 'laptop';
-- Matches 'Laptop', 'LAPTOP', 'laptop'
6. Wildcard Position Matters
LIKE 'M%' -- Starts with M (fast)
LIKE '%M' -- Ends with M (slower)
LIKE '%M%' -- Contains M (slowest)
Leading wildcards prevent index usage, making queries slow on large tables.
Key Takeaways 🎯
- SELECT specifies which columns to retrieve; FROM specifies which table
- WHERE filters rows based on conditions (comparison operators: =, !=, <, >, <=, >=)
- AND requires all conditions true; OR requires at least one true
- LIKE matches patterns using % (any characters) and _ (one character)
- NULL requires IS NULL or IS NOT NULL (never use = or !=)
- ORDER BY sorts results (ASC ascending, DESC descending)
- LIMIT restricts the number of rows returned
- Always use quotes around text values:
'text' - SQL keywords are case-insensitive, but write them UPPERCASE by convention
- Pair ORDER BY with LIMIT for predictable results
🤔 Did you know? SQL was invented in the 1970s at IBM. The original name was SEQUEL (Structured English Query Language), but it was shortened to SQL due to trademark issues. Some people still pronounce it "sequel" while others say "S-Q-L"—both are correct!
📚 Further Study
- W3Schools SQL Tutorial - https://www.w3schools.com/sql/ - Interactive SQL exercises with instant feedback
- PostgreSQL Official Documentation - SELECT - https://www.postgresql.org/docs/current/sql-select.html - Comprehensive reference for SELECT syntax
- SQLBolt - Interactive Lessons - https://sqlbolt.com/ - Learn SQL through hands-on practice with immediate results
📋 Quick Reference Card
╔════════════════════════════════════════════════════╗
║ SQL SELECT BASICS CHEAT SHEET ║
╠════════════════════════════════════════════════════╣
║ SELECT col1, col2 FROM table; ║
║ → Get specific columns ║
║ ║
║ SELECT * FROM table; ║
║ → Get all columns ║
║ ║
║ WHERE price > 100 ║
║ → Filter: =, !=, <, >, <=, >= ║
║ ║
║ WHERE price > 50 AND stock > 10 ║
║ → Both conditions must be true ║
║ ║
║ WHERE price < 30 OR stock > 100 ║
║ → At least one condition true ║
║ ║
║ WHERE name LIKE 'M%' ║
║ → Starts with M (% = any chars, _ = one char) ║
║ ║
║ WHERE description IS NULL ║
║ → Find NULL values (not = NULL!) ║
║ ║
║ ORDER BY price DESC ║
║ → Sort (ASC ascending, DESC descending) ║
║ ║
║ LIMIT 10 ║
║ → Return only first 10 rows ║
║ ║
║ Full Example: ║
║ SELECT name, price FROM products ║
║ WHERE price < 100 AND stock > 0 ║
║ ORDER BY price ASC ║
║ LIMIT 5; ║
╚════════════════════════════════════════════════════╝
🔧 Try this: Open a SQL environment (try https://sqliteonline.com/ for instant practice) and create a simple table with a few rows. Then practice each type of query from this lesson. The best way to learn SQL is to write it!