
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.

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
idcolumn 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, thensudo 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
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;

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 tablereads data;WHEREchooses which rows.- Combine conditions with
AND,ORand brackets. ORDER BYsorts,LIMITkeeps the top few,COUNT,AVGandGROUP BYsummarise.- 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:
- All beginner lessons, shortest first.
- The single longest lesson.
- How many lessons take under 30 minutes.
- Every lesson whose title contains the word "first".
- 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

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.


