SQL (Structured Query Language) is how we talk to databases.
Think of it like ordering specific items from a giant digital menu.

The Basics

The "Hello World" of SQL. Tell the DB What and Where.

-- Get specific columns
SELECT first_name, house
FROM student;

-- Get EVERYTHING (*)
SELECT *
FROM student;
Structure:
SELECT [Columns] FROM [Table];

Filtering

Using WHERE to narrow down results.

SELECT * FROM student
WHERE house = 'Gryffindor';
= Equal
!= Not Equal
> Greater
< Less
AND / OR (Combine)

Advanced Filters

1. LIKE (Pattern Matching)

% matches anything.

WHERE name LIKE 'P%' -- Starts 'P'
WHERE name LIKE '%a' -- Ends 'a'
WHERE name LIKE 'S_ _' -- Starts 'S' and has two more characters
2. BETWEEN (Ranges)
WHERE id BETWEEN 10 AND 20
WHERE dateofbirth BETWEEN '2020-01-01' AND '2020-12-31'-- Dates are always YYYY-MM-DD;
3. IN (List)
WHERE house IN ('Ravenclaw', 'Hufflepuff');

Sorting & Limits

ORDER BY

Sort results. Default is A-Z.

  • ASC: Small to Big (A-Z).
  • DESC: Big to Small (Z-A).
SELECT * FROM student
ORDER BY house ASC
LIMIT 5;

Maths Functions

Perform calculations on columns.

COUNT(*) Count rows
SUM(pts) Total
AVG(pts) Average
MAX(pts) Largest
SELECT COUNT(*)
FROM student
WHERE house = 'Hufflepuff';

Grouping

Calculate totals per category.

SELECT house, COUNT(*)
FROM student
GROUP BY house;
HAVING Clause

Like WHERE, but for groups.

GROUP BY house
HAVING COUNT(*) > 10;

Joining Tables

Connecting tables using a common ID.

Student Table
- Name, house_id
House Table
- id, House_Name
SELECT student.name, house.house_name
FROM student
JOIN house ON student.house_id = house.id; -- Link FK to PK

Renaming (AS)

Give calculated columns names.

SELECT SUM(points) AS Total
FROM house_points;
Total
450

Recipe 1: House Cup

Goal: List Houses and total points, sorted high to low.
SELECT house, SUM(points) AS Total
FROM house_points
GROUP BY house
ORDER BY Total DESC;
1. SUM total.
2. AS renames.
3. GROUP BY combines.
4. DESC sorts.

Recipe 2: The Patil Twin

Goal: Find student "Patil" in "Ravenclaw".
SELECT first_name
FROM person
WHERE last_name = 'Patil'
AND house = 'Ravenclaw';
Why AND? Both must be true. OR would get Patils from other houses too!

Order of Keywords (SFWGHO)

SQL is strict. You MUST write your query in this order, or it will error.

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
Mnemonic: "Some French Waiters Grow Huge Onions"

Common Errors & Fixes

Missing Quotes
Text needs quotes. Numbers don't.
WHERE name = Harry ❌ (Thinks it's a column)
WHERE name = 'Harry' ✅
Wrong Order
Did you put ORDER BY before WHERE? Check SFWGHO!
Typos / Spelling
SELEC? studnet?
Computers can't guess. Check spelling.
Ambiguous Column
If Joining tables with same col name (e.g. ID), specify table: student.id.
Grouping Rule
Selecting a column but not aggregating it?
SELECT house, count(*) requires GROUP BY house.
Missing Semicolon ;
Always end your query with ;