📖 Tutorial Guide

The SQL Bible

Every clause you need for the IEB Data & Information Management section, explained with runnable examples.

🔍 1. SELECT — Reading Data

Every query starts with SELECT (which columns) and FROM (which table). * means every column.

sql

SELECT * FROM Students;

SELECT first_name, last_name FROM Students;

🧮 2. WHERE — Filtering Rows

WHERE filters which rows are returned. Combine conditions with AND / OR, and use single quotes around text values.

sql

SELECT * FROM Students WHERE grade = 11;

SELECT * FROM Students
WHERE grade = 12 AND gender = 'F';

Tip:Text and dates need quotes ('F', '2024-01-01'); numbers don't.

🔢 3. ORDER BY — Sorting Results

ORDER BY sorts the result set. ASC (default) is smallest/A first, DESC is largest/Z first.

sql

SELECT first_name, last_name
FROM Students
ORDER BY last_name ASC;

📊 4. Aggregate Functions

COUNT, SUM, AVG, MIN and MAX summarise many rows into a single value.

sql

SELECT COUNT(*) FROM Students;
SELECT AVG(score) FROM Marks;
SELECT MAX(score), MIN(score) FROM Marks;

🗃️ 5. GROUP BY — Summarising per Category

GROUP BY splits rows into buckets (e.g. one bucket per subject) so an aggregate function runs separately for each bucket. HAVING filters groups, the way WHERE filters rows.

sql

SELECT subject_id, AVG(score) AS avg_score
FROM Marks
GROUP BY subject_id
HAVING AVG(score) >= 60;

Tip:WHERE filters BEFORE grouping happens; HAVING filters AFTER. You can't use an aggregate function inside WHERE.

🔗 6. JOIN — Combining Tables

A JOIN combines rows from two tables that share a related column (usually a primary key / foreign key pair).

sql

SELECT st.first_name, st.last_name, m.score
FROM Students st
JOIN Marks m ON st.student_id = m.student_id;

Tip:Give tables short aliases (st, m) so you don't have to repeat the full table name on every column.

🪆 7. Subqueries

A query nested inside another query — the inner query runs first and feeds the outer one.

sql

SELECT first_name, last_name
FROM Students
WHERE student_id IN (
    SELECT student_id FROM Marks WHERE score > 90
);

✏️ 8. INSERT, UPDATE, DELETE

The three statements that change data instead of just reading it.

sql

INSERT INTO Students (student_id, first_name, last_name, grade, gender)
VALUES (11, 'Thabo', 'Radebe', 10, 'M');

UPDATE Students SET grade = 11 WHERE student_id = 11;

DELETE FROM Students WHERE student_id = 11;

Tip:Always pair UPDATE and DELETE with a WHERE clause — without one, every row in the table is affected.

Ready to put it into practice?

Every technique above shows up in the SQL Practice problem set.

Try SQL Practice →