What you need

  • Computer and internet: a laptop or desktop and a stable connection
  • System: Windows 10 or 11, macOS or Linux
  • Tools: SQLite 3.33 or newer (built into macOS; sudo apt install sqlite3 on Ubuntu; on Windows, WSL with Ubuntu), or MySQL or MariaDB

Download only from each tool's official site and check the address before installing.

What you will learn

You will create a small database table, fill it with data, and ask it precise questions with SQL: which rows to bring back, which columns to show, in what order, and how many. These few words cover a large share of the SQL you will write in your career.

Ewoi

SELECT only reads. You cannot damage anything with it, so experiment freely in this lesson.

Why databases exist

Every app you use keeps information: your M-Pesa transactions, your WhatsApp contacts, the products in an online shop. Keeping that in plain files breaks quickly. Two people saving at the same time overwrite each other, finding one record among millions is slow, and one bad save can corrupt everything.

A database solves this. It stores information in tables, lets many users read and write at once safely, and finds what you ask for in milliseconds, even among millions of rows.

Tables, rows and columns

A table looks like a spreadsheet:

id title level minutes
1 Your first commit beginner 15
2 Build a REST API intermediate 40
  • A column is one kind of information (title, level, minutes). Every column has a type: text, whole number, date, and so on.
  • A row is one record, here one lesson.
  • The id column gives each row a unique number, so you can always point to exactly one row. This is called the primary key.

Set up a practice database

You need a database program to type SQL into. The simplest is SQLite: a whole database in one file, with nothing to configure.

  • macOS: SQLite is already installed.
  • Ubuntu, Debian or WSL on Windows: run sudo apt update, then sudo apt install sqlite3. On Windows, do this lesson in the Ubuntu terminal from Linux files and permissions.

Check it works with sqlite3 --version. You should see a version starting with 3.; this lesson needs 3.33 or newer for the tidy column layout.

Start it with a new database file. The first two lines make a sql folder in your workspace and move into it; you work in ~/ewoi-learning/sql for the rest of this lesson:

mkdir -p ~/ewoi-learning/sql
cd ~/ewoi-learning/sql
sqlite3 lessons.db

You will see a sqlite> prompt. Everything you type now is SQL. Every SQL statement ends with a semicolon ;. To leave later, type .quit.

To make results readable, run these two SQLite settings first (they start with a dot and are not SQL):

.headers on
.mode column
Already using MySQL or MariaDB?Everything in this lesson works there too. Run CREATE DATABASE learning; and USE learning; first.

Step 1: Create a table

CREATE TABLE lessons (
  id INTEGER PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  level VARCHAR(20) NOT NULL,
  minutes INTEGER NOT NULL
);

Read it line by line:

Line Meaning
CREATE TABLE lessons Make a new table called lessons
id INTEGER PRIMARY KEY A whole-number column that uniquely identifies each row
title VARCHAR(200) NOT NULL Text up to 200 characters, and it must not be empty (NOT NULL)
level VARCHAR(20) NOT NULL Short text, required
minutes INTEGER NOT NULL A whole number, required

Step 2: Add some rows

INSERT INTO lessons (id, title, level, minutes) VALUES
  (1, 'How the web works', 'beginner', 20),
  (2, 'Your first commit', 'beginner', 15),
  (3, 'Branches without fear', 'beginner', 25),
  (4, 'Your first Laravel route', 'intermediate', 30),
  (5, 'Build a REST API', 'intermediate', 40),
  (6, 'Hash passwords properly', 'intermediate', 25),
  (7, 'Database indexes', 'advanced', 45);

Text goes inside single quotes 'like this'. Numbers do not need quotes.

Step 3: SELECT, your first question

Show everything:

SELECT * FROM lessons;

* means "every column". Read it as: "Select everything from lessons." You will see all seven rows:

id  title                     level         minutes
--  ------------------------  ------------  -------
1   How the web works         beginner      20
2   Your first commit         beginner      15
3   Branches without fear     beginner      25
4   Your first Laravel route  intermediate  30
5   Build a REST API          intermediate  40
6   Hash passwords properly   intermediate  25
7   Database indexes          advanced      45

If the rows look like 1|How the web works|beginner|20, with no headings, the two dot settings were skipped. Run .headers on and .mode column, then the query again.

Now ask for only the columns you need:

SELECT title, minutes FROM lessons;
Ewoi

In real apps, avoid SELECT star. Asking only for the columns you need keeps pages fast and avoids sending data nobody asked for.

Step 4: WHERE, choosing rows

WHERE filters rows. Only rows where the condition is true come back:

SELECT title, minutes FROM lessons WHERE level = 'beginner';

Read it as: "Select the title and minutes from lessons where the level is beginner." Three rows: How the web works, Your first commit and Branches without fear.

Conditions you can use:

Operator Meaning Example
= Equal to level = 'beginner'
<> or != Not equal to level <> 'advanced'
< > Less than, more than minutes < 30
<= >= Or equal minutes >= 25
BETWEEN a AND b In a range, including both ends minutes BETWEEN 20 AND 30
IN (...) One of a list level IN ('beginner', 'intermediate')
LIKE Matches a pattern; % means "anything" title LIKE '%API%'

Try a few:

SELECT title FROM lessons WHERE minutes < 30;
SELECT title FROM lessons WHERE title LIKE '%Laravel%';
SELECT title, level FROM lessons WHERE level IN ('intermediate', 'advanced');

Step 5: Combine conditions with AND and OR

SELECT title, minutes FROM lessons
WHERE level = 'intermediate' AND minutes <= 30;

AND means both must be true. OR means at least one must be true:

SELECT title FROM lessons
WHERE level = 'advanced' OR minutes < 20;

When you mix them, use brackets to say exactly what you mean, just like in maths:

SELECT title FROM lessons
WHERE (level = 'beginner' OR level = 'intermediate') AND minutes > 20;

Ask your AI assistant

Here is my table: lessons(id, title, level, minutes). In plain English, I want: [describe your question]. Write the SQL, then explain each part of the query to me line by line so I can write it myself next time.

Step 6: Sorting and limiting

ORDER BY sorts the results. ASC is smallest first (the default), DESC is largest first:

SELECT title, minutes FROM lessons ORDER BY minutes DESC;

LIMIT keeps only the first few rows. Together they answer "top" questions:

SELECT title, minutes FROM lessons ORDER BY minutes DESC LIMIT 3;

"The three longest lessons."

Step 7: Counting and summarising

SELECT COUNT(*) FROM lessons WHERE level = 'beginner';
SELECT AVG(minutes) FROM lessons;
SELECT level, COUNT(*) AS total FROM lessons GROUP BY level;
Part Meaning
COUNT(*) How many rows matched
AVG(minutes) The average of a column; also SUM, MIN, MAX
AS total Give the result column a friendly name
GROUP BY level Make one result row per level, counting each group

You should get 3, then 28.5714285714286 (200 minutes shared across 7 lessons), then one row each for advanced (1), beginner (3) and intermediate (3).

The order SQL expects

The parts of a SELECT must appear in this order, even if you only use some of them:

SELECT columns
FROM table
WHERE conditions
GROUP BY column
ORDER BY column
LIMIT number;

Common beginner mistakes

Problem Cause Fix
Parse error: no such column: beginner Text without quotes is read as a column name Write 'beginner' with single quotes
Nothing happens after pressing Enter The statement has no ; yet, so the database waits for more Type ; and press Enter
Parse error: near "WHERE": syntax error Parts in the wrong order, often ORDER BY before WHERE Follow the order above
No rows returned The condition matched nothing, often a spelling or case difference Run SELECT DISTINCT level FROM lessons; to see the real values

A word about safety

When your app builds SQL from what a user typed, never glue their text into the query yourself. That opens the door to SQL injection, where a visitor types SQL that your database then runs. Frameworks such as Laravel pass values safely for you through query builders and bound parameters. You will see this in Build a REST API.

Recap

  • A database stores data in tables of rows and columns.
  • SELECT columns FROM table reads data; WHERE chooses which rows.
  • Combine conditions with AND, OR and brackets.
  • ORDER BY sorts, LIMIT keeps the top few, COUNT, AVG and GROUP BY summarise.
  • SELECT never changes data, so it is safe to practise.

Your practice task

Add five lessons of your own with INSERT, then write a query for each question:

  1. All beginner lessons, shortest first.
  2. The single longest lesson.
  3. How many lessons take under 30 minutes.
  4. Every lesson whose title contains the word "first".
  5. The average length of each level.

Ask your AI assistant

I wrote this SQL for the question "[your question]": [paste your query]. Do not rewrite it for me yet. Tell me if it answers the question correctly, and if not, give me a hint so I can fix it myself.

Save your work with Git

The database file lessons.db changes every time you use it, so professionals keep the SQL that builds it in Git instead. Leave SQLite with .quit, then, still in ~/ewoi-learning/sql, let SQLite write that SQL for you:

sqlite3 lessons.db .dump > lessons.sql

.dump prints every CREATE TABLE and INSERT needed to rebuild the database, and > saves it in lessons.sql. Open lessons.sql in your editor to see your table and all your rows as SQL.

Now create practice.sql in your editor and paste your five practice queries into it, each ending with ;. Run the whole file at once to check it:

sqlite3 -header -column lessons.db < practice.sql

-header -column are the command-line versions of .headers on and .mode column, and < feeds the file to SQLite. You should see five result tables. Then commit both files and keep every .db file out of history:

git init
echo "*.db" > .gitignore
git add .gitignore lessons.sql practice.sql
git commit -m "Add lessons database and practice queries"
git status

git status should say nothing to commit, working tree clean. Anyone (including future you) can rebuild the database with sqlite3 lessons.db < lessons.sql in an empty folder. Your folder now holds:

sql/
├── .gitignore
├── lessons.db     (ignored)
├── lessons.sql
└── practice.sql

Check your understanding

1. Which query returns only the titles of beginner lessons?

SELECT chooses the column (title), and WHERE chooses the rows (level is beginner). Text values need single quotes.

2. What does ORDER BY minutes DESC LIMIT 1 give you?

DESC sorts from largest to smallest, and LIMIT 1 keeps only the first row, which is the longest.

3. You typed a query and pressed Enter, but nothing happened. Most likely?

SQL waits until it sees a semicolon. Type ; and press Enter.

4. Why should you avoid SELECT * in real applications?

Ask only for what you need. It keeps pages fast and avoids exposing data by accident.

Get the code on GitHub

Found this useful?

Every lesson stays free. Tap the thumbs up so we know what works, or say thanks with a soda or a coffee: it pays for hosting and new lessons.

Buy me a sodaBuy me a coffee

Want more on this?

Tell us which part was not clear, ask for a follow-up lesson, or request a lesson on any topic. We read every request.

I would like
Only to tell you when it is ready.

WhatsApp

Join the channelChat with us

Free lessons and updates in the channel. Questions about learning or training? Chat with us.