Learn SQL from Scratch

Databases, tables, rows and columns

A database stores related tables. A table stores rows of records and columns of fields.

-- Example table idea:
learners(id, learner_name, course, readiness_score)

Real-life example: Student scores, course progress and customer records can all be stored as rows.

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Spreadsheet thinking vs database thinking

A spreadsheet is often one visible sheet. A database can connect several tables and query only the rows you need.

-- Spreadsheet question:
-- Which learners are below 60?

-- SQL question:
SELECT learner_name, readiness_score
FROM learners
WHERE readiness_score < 60;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Table structure

Each row should describe one record. Each column should describe one value about that record.

CREATE TABLE learners (
  learner_id INT,
  learner_name TEXT,
  course TEXT,
  readiness_score INT
);

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Primary key concept

A primary key is a unique identifier for each row. It helps connect tables safely.

-- learner_id can identify each learner uniquely
learner_id | learner_name
1          | Aisha
2          | Daniel

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

SELECT specific columns and SELECT *

Select only the columns you need. SELECT * is useful for quick checking, but reports should usually be specific.

SELECT learner_name, readiness_score
FROM learners;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Aliases with AS

Aliases rename output columns so reports are easier to read.

SELECT
  learner_name AS learner,
  readiness_score AS score
FROM learners;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Equality, comparisons, AND, OR, IN, BETWEEN and LIKE

Filters help find learners below a threshold, in a course, or matching a text pattern.

SELECT learner_name, readiness_score
FROM learners
WHERE readiness_score < 60
  AND course IN ('Python', 'SQL');

SELECT learner_name
FROM learners
WHERE learner_name LIKE 'A%';

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Sort ascending or descending

ORDER BY sorts rows. Use DESC for highest first.

SELECT learner_name, readiness_score
FROM learners
ORDER BY readiness_score DESC;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

LIMIT / TOP concept

LIMIT previews a few rows. Some SQL systems use TOP instead.

SELECT learner_name, readiness_score
FROM learners
ORDER BY readiness_score DESC
LIMIT 5;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

COUNT, SUM, AVG, MIN and MAX

Use aggregates to count learners, calculate averages and find highest or lowest scores.

SELECT
  COUNT(*) AS learner_count,
  AVG(readiness_score) AS average_score,
  MIN(readiness_score) AS lowest_score,
  MAX(readiness_score) AS highest_score
FROM learners;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

GROUP BY categories

GROUP BY creates summaries for each category.

SELECT course, AVG(readiness_score) AS average_score
FROM learners
GROUP BY course;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

HAVING vs WHERE

WHERE filters rows before grouping. HAVING filters groups after grouping and aggregation.

SELECT course, AVG(readiness_score) AS average_score
FROM learners
GROUP BY course
HAVING AVG(readiness_score) < 60;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

INNER JOIN and LEFT JOIN

A JOIN connects two tables by matching a shared value, usually an ID. An INNER JOIN returns matching records. A LEFT JOIN keeps all rows from the left table and matches what it can.

SELECT learners.learner_name, quiz_results.score
FROM learners
INNER JOIN quiz_results
  ON learners.learner_id = quiz_results.learner_id;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

CASE expressions

CASE turns numeric scores into readable labels such as Beginner, Building and Ready.

CASE
  WHEN score >= 80 THEN 'Ready'
  WHEN score >= 50 THEN 'Building'
  ELSE 'Beginner'
END AS readiness_label

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.

Combine SELECT, JOIN, GROUP BY, CASE and ORDER BY

A reporting query should show clear fields, readable labels and useful ordering.

SELECT
  learners.course,
  COUNT(*) AS attempts,
  AVG(quiz_results.score) AS average_score
FROM learners
JOIN quiz_results
  ON learners.learner_id = quiz_results.learner_id
GROUP BY learners.course
ORDER BY average_score ASC;

Why this matters: It helps you turn table data into the Student Progress Database Report.

How to understand this example: Read it from top to bottom. First notice the key word, then notice the value, element or result it controls.