SQL_Classroom_Lesson

1.

A COMPLETE BEGINNER LESSON
SQL
From tables
to answers
Terms, real-life examples,
and working code
students
student_id
name · class
menu_items
item_id
name · price_cents
purchases
purchase_id
student_id · item_id
SCHOOL CAFÉ • SQLITE • 3 CLASSROOM SESSIONS
01 / 45

2.

FOUNDATIONS
Your Route Through the Lesson
1. Understand the data
Tables, keys, relationships, and data types.
2. Ask questions
Select, filter, sort, join, and calculate.
3. Make safe changes
Create tables; insert, update, and delete rows.
First understand the structure. Then write and test the SQL.
SQL CLASSROOM / SQLITE
02 / 45

3.

FOUNDATIONS
Why Do We Need a Database?
Data
Recorded facts: a student name, an item price, or a
purchase date.
Database
An organized collection of data that we can store and
retrieve.
Useful questions
Who bought lunch? Which item sold most? How much was
paid?
A database helps us keep facts consistent and find answers
quickly.
SQL CLASSROOM / SQLITE
03 / 45

4.

FOUNDATIONS
Database, DBMS, and SQL
Database
The stored collection of tables and other database objects.
DBMS
The software that stores data, enforces rules, and runs
commands.
SQL
Structured Query Language: instructions we send to the
DBMS.
The database stores facts; the DBMS manages them; SQL gives
instructions.
SQL CLASSROOM / SQLITE
04 / 45

5.

FOUNDATIONS
A Table Has Rows and Columns
students — one row per student
student_id
name
class
1
Maya
10A
2
Leo
10A
3
Aisha
10B
4
Noah
10B
A row describes one record. A column describes one kind of fact.
SQL CLASSROOM / SQLITE
05 / 45

6.

FOUNDATIONS
Schema and Data Types
Schema
The blueprint: table names, columns, relationships, and
rules.
Data type
Describes values: INTEGER for whole numbers, TEXT for
words.
Constraints
Rules such as NOT NULL that protect required
information.
Choose a structure that matches what each value means.
SQL CLASSROOM / SQLITE
06 / 45

7.

FOUNDATIONS
Primary Key: A Row's Identity
Primary key
A chosen identifier that uniquely identifies each row.
Our example
student_id = 1 identifies Maya, even if her name later
changes.
Why not the name?
Two students may share a name. Names can also change.
Use a stable identifier to point to the correct record.
SQL CLASSROOM / SQLITE
07 / 45

8.

FOUNDATIONS
Foreign Keys Connect Tables
Foreign key
A column that refers to an identifier in a related table.
One to many
One student may have many purchases; each has at most
one student.
Optional relationship
A NULL student_id allows an anonymous purchase.
Store an identifier to connect a purchase with its student or menu
item.
SQL CLASSROOM / SQLITE
08 / 45

9.

FOUNDATIONS
Why Use Three Related Tables?
Everything in one list
Related tables
Student names, class
codes, and menu details
repeat on every purchase.
students stores people;
menu_items stores the
menu; purchases stores
events.
Store each kind of fact in its natural place, then connect it with
keys.
SQL CLASSROOM / SQLITE
09 / 45

10.

FOUNDATIONS
What Is a SQL Statement?
Read
SELECT retrieves information and produces a result table.
Change data
INSERT adds rows; UPDATE edits rows; DELETE removes
rows.
Define and control
CREATE defines tables; BEGIN, COMMIT, and ROLLBACK
control transactions.
A SQL statement is a complete instruction. A query retrieves
information.
SQL CLASSROOM / SQLITE
10 / 45

11.

FOUNDATIONS
Our Practice Environment: SQLite
SQL dialect
A system's version of SQL, with its own syntax and
functions.
SQLite
A lightweight database engine that is easy to use for
practice.
Our conventions
Money = integer cents. Dates = YYYY-MM-DD. paid = 0 or 1.
Run examples in SQLite and enable foreign keys on each
connection.
SQL CLASSROOM / SQLITE
11 / 45

12.

READ AND EXPLORE
Meet the café menu
menu_items · one row per menu item
item_id
name
price_cents
1
Tea
150
2
Sandwich
400
3
Cookie
200
4
Juice
250
Store money as integer cents: 150 cents means $1.50.
SQL CLASSROOM / SQLITE
12 / 45

13.

READ AND EXPLORE
Meet the purchase records
purchases · selected columns from the full table
purchase_id
student_id
item_id
quantity
paid
1
2
3
4
5
6
1
1
2
3
2
NULL
1
2
3
1
2
3
1
1
2
1
1
1
1
1
1
0
1
1
One purchase row is one receipt line; quantity may be greater
than 1.
SQL CLASSROOM / SQLITE
13 / 45

14.

READ AND EXPLORE
Your first SELECT query
Real-life question: What is on our menu?
SELECT *
FROM menu_items;
• SELECT * returns every column.
• FROM chooses the source table.
RESULT Result: 4 rows · item_id, name, price_cents
SELECT asks for columns. FROM names the table.
SQL CLASSROOM / SQLITE
14 / 45

15.

READ AND EXPLORE
Choose columns and name results
Real-life question: Which fields belong on a price list?
SELECT name,
price_cents AS cents
FROM menu_items;
• A comma separates requested columns.
• AS changes the result label.
RESULT Result: 4 rows · name, cents
Return the fields you need; AS gives a result column a clear name.
SQL CLASSROOM / SQLITE
15 / 45

16.

READ AND EXPLORE
Filter rows with WHERE
Real-life question: What can I buy for 200 cents or less?
SELECT name, price_cents
FROM menu_items
WHERE price_cents <= 200;
• <= includes the boundary value.
• Matching items: Tea and Cookie.
RESULT 2 matching rows · output order is unspecified
WHERE keeps only rows whose condition is true.
SQL CLASSROOM / SQLITE
16 / 45

17.

READ AND EXPLORE
Combine conditions with AND and OR
Which purchases are paid AND belong to Maya OR Leo?
SELECT purchase_id
FROM purchases
WHERE paid = 1
AND (student_id = 1
OR student_id = 2);
• Parentheses group the allowed buyers.
• IDs 1, 2, 3, and 5 match.
AND requires both conditions. OR accepts either condition.
SQL CLASSROOM / SQLITE
17 / 45

18.

READ AND EXPLORE
Ranges and lists
Real-life question: Which items fit a 150–250 cent budget?
SELECT name, price_cents
FROM menu_items
WHERE price_cents BETWEEN 150 AND 250;
• Tea, Cookie, and Juice qualify.
• For a list, use item_id IN (1, 3).
BETWEEN includes both endpoints. IN checks membership in a list.
SQL CLASSROOM / SQLITE
18 / 45

19.

READ AND EXPLORE
Search text with LIKE
Real-life question: Which menu item starts with S?
SELECT name
FROM menu_items
WHERE name LIKE 'S%';
• % can match zero or more characters.
• The matching item is Sandwich.
In LIKE patterns, % means any sequence and _ means one
character.
SQL CLASSROOM / SQLITE
19 / 45

20.

READ AND EXPLORE
Sort and keep the first results
Real-life question: Which two items are the most expensive?
SELECT name, price_cents
FROM menu_items
ORDER BY price_cents DESC, item_id ASC
LIMIT 2;
• DESC means highest to lowest.
• item_id breaks any price ties.
RESULT Sandwich · 400 Juice · 250
ORDER BY defines the order. LIMIT restricts the number of rows.
SQL CLASSROOM / SQLITE
20 / 45

21.

READ AND EXPLORE
Remove duplicate result values
Real-life question: Which classes are represented?
SELECT DISTINCT class
FROM students
ORDER BY class;
• Repeated class values appear once.
• Stored student rows remain intact.
RESULT 10A · 10B
DISTINCT removes duplicate combinations in the selected result.
SQL CLASSROOM / SQLITE
21 / 45

22.

READ AND EXPLORE
Handle missing values with NULL
Real-life question: Which purchase has an unknown buyer?
SELECT purchase_id
FROM purchases
WHERE student_id IS NULL;
• Use IS NULL, not = NULL.
• Purchase 6 is anonymous.
NULL is missing or unknown data. Test it with IS NULL.
SQL CLASSROOM / SQLITE
22 / 45

23.

READ AND EXPLORE
Summarize with aggregate functions
Real-life question: How many receipt lines and units exist?
SELECT COUNT(*) AS receipt_lines,
SUM(quantity) AS units
FROM purchases;
• COUNT(*) includes every row.
• SUM adds the quantity values.
RESULT receipt_lines = 6 units = 7
COUNT(*) counts rows; SUM(quantity) counts units in this model.
SQL CLASSROOM / SQLITE
23 / 45

24.

READ AND EXPLORE
Create one summary per group
Real-life question: How many units of each item were bought?
SELECT item_id, SUM(quantity) AS units
FROM purchases
GROUP BY item_id
ORDER BY item_id;
• Rows with the same item_id form a group.
• Juice has no purchase group.
RESULT item 1 → 2 item 2 → 2 item 3 → 3
GROUP BY forms groups; an aggregate summarizes each group.
SQL CLASSROOM / SQLITE
24 / 45

25.

READ AND EXPLORE
Filter groups with HAVING
Real-life question: Which item sold at least three units?
SELECT item_id, SUM(quantity) AS units
FROM purchases
GROUP BY item_id
HAVING SUM(quantity) >= 3;
• HAVING tests the summary for each group.
• Item 3 qualifies: 3 Cookies.
WHERE filters rows before grouping. HAVING filters the resulting
groups.
SQL CLASSROOM / SQLITE
25 / 45

26.

READ AND EXPLORE
Read the query as a sequence
1 · Find and filter
FROM / JOIN → WHERE
2 · Group and summarize
GROUP BY → HAVING → SELECT
3 · Present the result
ORDER BY → LIMIT
Written clause order and logical processing order are different.
SQL CLASSROOM / SQLITE
26 / 45

27.

COMBINE AND REPORT
Follow the relationships
students
menu_items
student_id PRIMARY KEY
name · class
item_id PRIMARY KEY
name · price_cents
1 → many
1 → many
purchases
purchase_id · student_id · item_id
Each purchase stores quantity, selling price, date, and paid status.
Join tables through identifiers that describe the same
relationship.
SQL CLASSROOM / SQLITE
27 / 45

28.

COMBINE AND REPORT
Combine matching rows with JOIN
Real-life question: Show each purchase with its item name.
SELECT p.purchase_id, m.name, p.quantity
FROM purchases AS p
INNER JOIN menu_items AS m
ON p.item_id = m.item_id
ORDER BY p.purchase_id;
• p and m are short table aliases.
• ON states how the rows match.
RESULT 6 rows · Tea, Sandwich, Cookie, Tea, Sandwich, Cookie
INNER JOIN returns matching combinations from both tables.
SQL CLASSROOM / SQLITE
28 / 45

29.

COMBINE AND REPORT
Keep unmatched rows with LEFT JOIN
Real-life question: Include students with zero purchases.
SELECT s.name, COUNT(p.purchase_id) AS lines
FROM students AS s
LEFT JOIN purchases AS p
ON s.student_id = p.student_id
GROUP BY s.student_id, s.name
ORDER BY s.student_id;
• Noah stays in the result with 0 lines.
RESULT Maya 2 · Leo 2 · Aisha 1 · Noah 0
LEFT JOIN keeps every left-side row, even when no match exists.
SQL CLASSROOM / SQLITE
29 / 45

30.

COMBINE AND REPORT
Calculate paid revenue
Real-life question: How much money has actually been paid?
SELECT SUM(quantity * unit_price_cents)
AS revenue_cents
FROM purchases
WHERE paid = 1;
• Use the stored selling price.
• Unpaid rows are excluded.
RESULT revenue_cents = 1550 · $15.50
Revenue = quantity × price at purchase, summed over paid rows.
SQL CLASSROOM / SQLITE
30 / 45

31.

COMBINE AND REPORT
Build a café report step by step
Which items earned at least 300 cents from paid
purchases? Rank them by revenue.
1 Connect purchases to menu_items.
2 Keep paid rows, then total each item.
3 Filter the totals, then sort highest first.
Translate the business question into a source, condition, and
summary.
SQL CLASSROOM / SQLITE
31 / 45

32.

COMBINE AND REPORT
The completed café report
Answer: at least 300 cents of paid revenue per item.
SELECT m.name,
SUM(p.quantity * p.unit_price_cents) AS revenue
FROM purchases p JOIN menu_items m
ON p.item_id = m.item_id
WHERE p.paid = 1
GROUP BY m.item_id, m.name
HAVING SUM(p.quantity * p.unit_price_cents) >= 300
ORDER BY revenue DESC, m.item_id;
RESULT Sandwich → 800 cents Cookie → 600 cents
A useful query connects data, applies business rules, and returns
a clear answer.
SQL CLASSROOM / SQLITE
32 / 45

33.

CREATE AND CHANGE
Create a table with CREATE TABLE
Real-life question: What fields must a student record contain?
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
class TEXT NOT NULL
);
• Types describe the intended values.
• Constraints express data rules.
CREATE TABLE defines the structure before INSERT adds records.
SQL CLASSROOM / SQLITE
33 / 45

34.

CREATE AND CHANGE
Protect data with constraints
Identity and references
PRIMARY KEY identifies; FOREIGN KEY links valid records.
Required and valid values
NOT NULL requires data; CHECK tests a rule.
Uniqueness and defaults
UNIQUE prevents repeats; DEFAULT supplies omitted
values.
Constraints make the database enforce rules, even across
different applications.
SQL CLASSROOM / SQLITE
34 / 45

35.

CREATE AND CHANGE
Add records with INSERT
Real-life question: Register a new student called Eva.
INSERT INTO students
(student_id, name, class)
VALUES
(5, 'Eva', '10B');
• Columns and values have matching order.
• New row: 5 · Eva · 10B
List the target columns so each value has a clear destination.
SQL CLASSROOM / SQLITE
35 / 45

36.

CREATE AND CHANGE
Change a record with UPDATE
Real-life question: Change the current Tea price to 175 cents.
UPDATE menu_items
SET price_cents = 175
WHERE item_id = 1;
• SET specifies the replacement value.
• WHERE chooses the record to change.
Preview the WHERE condition, then use that same condition in
UPDATE.
SQL CLASSROOM / SQLITE
36 / 45

37.

CREATE AND CHANGE
Remove a record with DELETE
Real-life question: Remove the unsold Juice menu item.
DELETE FROM menu_items
WHERE item_id = 4;
• The unsold Juice row is the target.
• Foreign keys protect referenced items.
DELETE removes selected rows. DROP TABLE removes the table
structure.
SQL CLASSROOM / SQLITE
37 / 45

38.

WORK SAFELY
Transactions: changes as a team
What if we mark Aisha's purchase paid, then decide to cancel that
change?
BEGIN TRANSACTION;
UPDATE purchases
SET paid = 1
WHERE purchase_id = 4;
SELECT paid FROM purchases
WHERE purchase_id = 4;
ROLLBACK;
• Inside this classroom transaction, the SELECT returns 1.
COMMIT saves changes; ROLLBACK cancels uncommitted changes.
SQL CLASSROOM / SQLITE
38 / 45

39.

WORK EFFICIENTLY
Indexes: a faster way to find rows
How might a large school find one student's purchases more
quickly?
CREATE INDEX idx_purchases_student
ON purchases (student_id);
SELECT purchase_id, purchased_on
FROM purchases
WHERE student_id = 2;
• A book index points to relevant pages; a database index points
toward matching rows.
An index can speed up matching searches, but costs storage and
write work.
SQL CLASSROOM / SQLITE
39 / 45

40.

WORK SAFELY
Bind values with parameters
How should an application search for a name entered by a
student?
sql = (
"SELECT name FROM students "
"WHERE name = ?"
)
params = ("Maya",)
rows = connection.execute(sql, params).fetchall()
• Python's sqlite3 uses ? placeholders and a separate sequence of
values.
Keep the SQL structure fixed; pass user input separately as data.
SQL CLASSROOM / SQLITE
40 / 45

41.

CHECK YOUR REASONING
Three mistakes to catch early
Easy mistakes
Better habits
student_id = NULL
SUM(unit_price_cents)
UPDATE purchases SET
paid = 1
student_id IS NULL
SUM(quantity *
unit_price_cents)
Check the WHERE condition
before UPDATE.
Check missing values, quantities, and the exact rows a change will
affect.
SQL CLASSROOM / SQLITE
41 / 45

42.

TRY IT YOURSELF
Practice A: filter and sort prices
Show each menu item's name and price_cents for
items costing at least 200 cents. Sort from cheapest
to most expensive.
1 Read from menu_items; select only name and price_cents.
2 At least 200 means price_cents >= 200.
3 Use ORDER BY price_cents ASC.
Use SELECT for columns, WHERE for rows, and ORDER BY for
display order.
SQL CLASSROOM / SQLITE
42 / 45

43.

TRY IT YOURSELF
Practice B: who paid how much?
Show every student's name and total paid spending
in cents, including zero totals. Sort highest first.
Explain the difference from all paid revenue.
1 Start with students; LEFT JOIN purchases using student_id.
2 Put p.paid = 1 in ON; sum quantity * unit_price_cents.
3 Group by student_id and name; use COALESCE for zero totals.
A LEFT JOIN can keep students with no matching paid purchases.
SQL CLASSROOM / SQLITE
43 / 45

44.

CHECK UNDERSTANDING
Exit ticket: explain your choices
Name a primary key and foreign key. Explain
WHERE versus HAVING. Write a query for total paid
revenue in cents, then predict its result.
1 Identify rows uniquely, then identify a reference between
tables.
2 Think about filtering rows before grouping and groups after
grouping.
3 Multiply each purchase's quantity by its recorded unit price.
A strong SQL answer includes a correct query and a clear
explanation.
SQL CLASSROOM / SQLITE
44 / 45

45.

KEEP LEARNING
Your SQL toolkit and next steps
Read and summarize
SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER
BY, LIMIT
Create and change
CREATE TABLE, INSERT, UPDATE, DELETE; use keys,
constraints, and transactions.
Keep building
Next: subqueries, CTEs, window functions, execution plans,
and more practice.
Turn a real question into tables, conditions, calculations, and a
checked result.
SQL CLASSROOM / SQLITE
45 / 45