Introduction to databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

Glossary of Database Terms

Practice

Assignment

• Teachers

Core program

Retrieving data is the most common job in a database. SQL gives you powerful tools to filter, sort, and combine data.

Shaping Your Results

Sorting (ORDER BY)

By default, the database returns rows in no specific order. Use ORDER BY to sort them.

-- Sort students by age (Lowest to Highest / Ascending)
SELECT * FROM students
ORDER BY age ASC;

-- Sort by age (Highest to Lowest / Descending)
SELECT * FROM students
ORDER BY age DESC;

-- Sort by last name, then first name
SELECT * FROM students
ORDER BY last_name ASC, first_name ASC;

Limiting Results (LIMIT)

Useful if you only want the "Top 10" or for pagination (showing 10 items per page).

-- Get the 5 youngest students
SELECT * FROM students
ORDER BY age ASC
LIMIT 5;

-- Get results 11-20 (Skip 10, take 10)
SELECT * FROM students
LIMIT 10 OFFSET 10;

Aggregation and Grouping

Sometimes you don't want the raw data; you want summaries (like totals or averages).

Aggregate Functions

• COUNT(*): Counts the number of rows.
• SUM(column): Adds up values.
• AVG(column): Calculates the average.
• MIN(column): Finds the lowest value.
• MAX(column): Finds the highest value.

-- How many students do we have?
SELECT COUNT(*) FROM students;

-- What is the average age of our students?
SELECT AVG(age) FROM students;

Grouping (GROUP BY)

Used to aggregate data per category.

-- Count how many students are enrolled in each class
-- (Assuming we have joined tables, see below)
SELECT class_id, COUNT(student_id) as student_count
FROM enrollments
GROUP BY class_id;

JOINS: Combining Tables

This is the most important concept in SQL. Since we split our data into multiple tables, we need JOIN to bring them back together.

INNER JOIN

Returns only rows where there is a match in BOTH tables. If a student has no classes, they won't appear.

-- Get a list of students and the classes they are taking
SELECT students.first_name, classes.title
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN classes ON enrollments.class_id = classes.id;

Note: We join students to enrollments, and then enrollments to classes. This links the chain.

LEFT JOIN (or LEFT OUTER JOIN)

Returns ALL rows fsrom the "Left" table (the one mentioned first), and matching rows from the "Right" table. If there is no match, the columns from the right table will be NULL (empty).

-- Get ALL students, and their classes if they have any.
-- If a student has no classes, 'title' will be NULL.
SELECT students.first_name, classes.title
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id
LEFT JOIN classes ON enrollments.class_id = classes.id;

Which one to use?

• Use INNER JOIN when you only want complete records (e.g., only students who are actually enrolled).
• Use LEFT JOIN when you want to list everything from the main table, even if it has no matches (e.g., a list of all students, even those not in a class).

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

*https://hackyourfuture.net/*

Found a mistake or have a suggestion? Let us know in the feedback form.