The Librarian Analogy
Think of a database as a library and SQL as your conversation with the librarian:
| What You Want | SQL Equivalent |
|---|---|
| "Show me all books by Stephen King" | SELECT (read) |
| "Add this new book to the shelf" | INSERT (create) |
| "Change the location of this book" | UPDATE (modify) |
| "Remove this damaged book" | DELETE (remove) |
SQL is the language you use to ask for, add, change, or remove data.
It's been a common standard for relational databases for decades.
The Basic Query Structure
Every SQL query follows a logical flow:
SELECT what_columns ← What information do you want?
FROM which_table ← Where is it stored?
WHERE conditions ← Which rows match?
ORDER BY sorting ← How should it be sorted?
LIMIT how_many; ← How many results?
Reading It Like English
"Get names of the 10 youngest active users"
SELECT name
FROM users
WHERE status = 'active'
ORDER BY age ASC
LIMIT 10;
Translation:
┌────────────┬────────────────────────┐
│ SQL Clause │ English Meaning │
├────────────┼────────────────────────┤
│ SELECT │ "Get the name column" │
│ FROM │ "From the users table" │
│ WHERE │ "Where status=active" │
│ ORDER BY │ "Sorted by age" │
│ LIMIT │ "First N results" │
└────────────┴────────────────────────┘
CRUD Operations
The four fundamental data operations:
Create (INSERT)
Adds new rows to a table.
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 28);
Before: After:
┌────────────────────┐ ┌────────────────────────┐
│ (empty table) │ → │ Alice | alice@... | 28 │
└────────────────────┘ └────────────────────────┘
Read (SELECT)
Retrieves data from tables. Most common operation.
| Query | Result |
|---|---|
SELECT * FROM users; | All columns, all rows |
SELECT name FROM users; | Just names |
SELECT * FROM users WHERE age > 25; | Age-filtered rows |
Update (UPDATE)
Modifies existing rows.
UPDATE users SET age = 29 WHERE name = 'Alice';
⚠️ Important: Be careful with WHERE.
Without WHERE: UPDATE users SET age = 29;
→ Changes EVERY user to age 29!
Delete (DELETE)
Removes rows from a table.
DELETE FROM users WHERE name = 'Alice';
⚠️ Important: Be careful with WHERE.
Without WHERE: DELETE FROM users;
→ Deletes ALL users!
Filtering with WHERE
WHERE clauses narrow down which rows your query affects.
Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
= | Equals | age = 30 |
<> or != | Not equals | status <> 'deleted' |
> | Greater than | price > 100 |
< | Less than | quantity < 5 |
>= | Greater or equal | rating >= 4 |
<= | Less or equal | date <= CURRENT_DATE |
Combining Conditions
| Operator | Usage |
|---|---|
AND | Both conditions are true: age > 18 AND status = 'active' |
OR | Either condition can be true: country = 'USA' OR country = 'Canada' |
NOT | Inverts the condition: NOT status = 'deleted' |
Special Operators
| Operator | Purpose | Example |
|---|---|---|
IN | Match any in list | country IN ('USA', 'UK', 'Canada') |
BETWEEN | Range (inclusive) | age BETWEEN 18 AND 65 |
LIKE | Pattern matching | email LIKE '%@gmail.com' |
IS NULL | Check for null | deleted_at IS NULL |
Pattern Matching with LIKE
% = Any characters (0 or more)
_ = Exactly one character
'A%' → Starts with A (Alice, Albert, Amy)
'%@gmail.com' → Gmail addresses
'_ob' → 3 letters ending in 'ob' (Bob, Rob)
Sorting and Pagination
ORDER BY
Controls the order of results.
| Query | Result |
|---|---|
ORDER BY name | Alphabetical (A-Z) |
ORDER BY name DESC | Reverse alphabetical (Z-A) |
ORDER BY created_at DESC | Newest first |
ORDER BY country, name | By country, then name |
LIMIT and OFFSET
Controls how many results you get.
First 10 results:
LIMIT 10
Pagination (page 3 with 10 per page):
LIMIT 10 OFFSET 20
Page 1: OFFSET 0 (rows 1-10)
Page 2: OFFSET 10 (rows 11-20)
Page 3: OFFSET 20 (rows 21-30)
Aggregation Functions
Summarize data across multiple rows.
| Function | Purpose | Example |
|---|---|---|
COUNT(*) | Count rows | How many users? |
SUM(amount) | Total of values | Total sales? |
AVG(age) | Average value | Average age? |
MIN(price) | Smallest value | Cheapest product? |
MAX(price) | Largest value | Most expensive? |
GROUP BY
Aggregates per group, not just overall.
"Count users per country"
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;
Result:
┌─────────┬────────────┐
│ country │ user_count │
├─────────┼────────────┤
│ USA │ 500 │
│ UK │ 300 │
│ Canada │ 200 │
└─────────┴────────────┘
HAVING vs WHERE
Both filter, but at different stages:
WHERE: Filters BEFORE grouping (on raw rows)
HAVING: Filters AFTER grouping (on aggregated results)
"Countries with more than 100 users"
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;
WHERE can't use COUNT() - it runs before grouping!
JOINs: Combining Tables
The real power of SQL: connecting related data.
Why JOINs Exist
Users Table: Orders Table:
┌────┬───────┐ ┌────┬─────────┬────────┐
│ id │ name │ │ id │ user_id │ amount │
├────┼───────┤ ├────┼─────────┼────────┤
│ U_A│ Alice │ │ O_A│ U_A │ small │
│ U_B│ Bob │ │ O_B│ U_A │ large │
└────┴───────┘ └────┴─────────┴────────┘
Problem: Orders has a user_id (U_A), but you want the name (Alice)
Solution: JOIN tables using the id = user_id relationship
Types of JOINs
| Type | Result |
|---|---|
| INNER JOIN | Rows that match in both tables |
| LEFT JOIN | All from left + matches from right |
| RIGHT JOIN | All from right + matches from left |
| FULL JOIN | All from both (matches or not) |
INNER JOIN Example
Rows where both tables have matching data.
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;
Result:
┌───────┬────────┐
│ name │ amount │
├───────┼────────┤
│ Alice │ small │
│ Alice │ large │
└───────┴────────┘
Bob has no orders → Bob doesn't appear
LEFT JOIN Example
All rows from left table, even without matches.
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
Result:
┌───────┬────────┐
│ name │ amount │
├───────┼────────┤
│ Alice │ small │
│ Alice │ large │
│ Bob │ NULL │ ← Bob appears with NULL
└───────┴────────┘
Creating and Modifying Tables
CREATE TABLE
CREATE TABLE users (
id INT PRIMARY KEY, ← Unique identifier
name VARCHAR(100) NOT NULL, ← Required, max 100 chars
email VARCHAR(255) UNIQUE, ← No duplicates allowed
age INT, ← Optional number
created_at TIMESTAMP DEFAULT NOW() ← Auto-fills with current time
);
Common Data Types
| Type | Usage |
|---|---|
INT | Whole numbers |
VARCHAR(n) | Text up to n characters |
TEXT | Unlimited text |
BOOLEAN | True/false |
TIMESTAMP | Date and time |
DECIMAL(p,s) | Precise numbers (money) |
Common Constraints
| Constraint | Purpose |
|---|---|
PRIMARY KEY | Unique row identifier |
NOT NULL | Value required |
UNIQUE | No duplicate values |
FOREIGN KEY | References another table |
DEFAULT | Value if not specified |
Common Mistakes
1. Forgetting WHERE in UPDATE/DELETE
Be careful with this:
UPDATE users SET status = 'inactive';
DELETE FROM users;
Both can affect many rows.
Add a WHERE clause when you mean to target specific rows.
2. N+1 Query Problem
BAD: For each of 100 users, query their orders separately
= 1 + 100 = 101 queries
GOOD: JOIN users and orders in one query
= 1 query
Performance difference: Seconds vs milliseconds
3. SELECT * in Production
BAD: SELECT * FROM users
(Fetches all 50 columns, including unused ones)
GOOD: SELECT name, email FROM users
(What you need)
Better performance, clearer intent.
4. Not Using Indexes
If your query is slow, you probably need an index on the columns in your WHERE clause. Indexes make searches fast.
FAQ
Q: What's the difference between SQL and NoSQL?
SQL: structured tables, relationships, ACID compliance, powerful JOINs NoSQL: flexible schema, horizontal scaling, various data models
Q: Is SQL case-sensitive?
Keywords (SELECT, FROM) are not case-sensitive. Whether data is case-sensitive depends on your database configuration.
Q: What's a primary key?
A unique identifier for each row. Usually an auto-incrementing integer or UUID. Every table should have one.
Q: What's a foreign key?
A column that references a primary key in another table. It creates a relationship between tables (like user_id in orders referencing id in users).
Q: Should I learn specific database syntax?
Core SQL is the same everywhere. Learn the standard first, then database-specific features (PostgreSQL, MySQL, SQLite) as needed.
Summary
SQL is the standard language for interacting with relational databases, used for decades.
Key Takeaways:
- CRUD operations: INSERT, SELECT, UPDATE, DELETE
- WHERE filters rows, HAVING filters groups
- JOINs combine related tables
- ORDER BY sorts, LIMIT paginates
- GROUP BY with aggregates summarizes data
- Use WHERE with UPDATE and DELETE when you mean to target specific rows
- JOINs beat multiple queries (avoid N+1)
- Index columns you filter on
Master these fundamentals and you can work with any SQL database!
Related Concepts
Leave a Comment
Comments (0)
Be the first to comment on this concept.
Comments are approved automatically.