Query, summarise, join and change data in a relational database with SQL, practised in your browser.

SQL Fundamentals

## Why it matters

Almost every app you use keeps its data in a database: the orders in a shop, the patients in a clinic system, the tickets in a help desk, the scores in a mobile game. Data analysts query those databases to answer questions, developers write code that reads and changes them, and database administrators keep them fast and safe. The language all of these people share is **SQL** (Structured Query Language, often said "sequel").

Before writing any SQL, it helps to know what you are talking to. This lesson explains the four ideas everything else builds on: tables, rows, keys and schemas.

## A database is a set of tables

A **relational database** stores data in **tables**. A table looks like a spreadsheet sheet with strict rules:

- Each **column** has a name and a type, such as `INTEGER`, `TEXT` or `REAL` (a decimal number).
- Each **row** is one record: one customer, one order, one book.
- Every row has the same columns. A cell may be empty (SQL calls this `NULL`), but you cannot add an extra column to a single row.

Here is a small `books` table:

| id | title | author | price |
|---|---|---|---|
| 1 | The Pragmatic Programmer | Hunt and Thomas | 39.99 |
| 2 | Clean Code | Robert Martin | 34.50 |
| 3 | Dune | Frank Herbert | 9.99 |

The word "relational" comes from mathematics: a table is a *relation*, a set of rows that all share the same shape. In practice it also hints at the real power of the model: tables can be **related** to each other.

## Keys: how rows are identified and linked

A **primary key** is a column (or set of columns) whose value is unique for every row and never empty. In the table above, `id` is the primary key. Two books might share a title or a price, but never an `id`. When a program wants "book 3", it asks for the row whose `id` is 3.

A **foreign key** is a column in one table that stores the primary key of a row in another table. Imagine an `orders` table:

| id | book_id | quantity |
|---|---|---|
| 101 | 3 | 2 |
| 102 | 1 | 1 |

`book_id` is a foreign key pointing at `books.id`. Order 101 is for two copies of *Dune*. Instead of copying the title and price into every order, the database stores the book once and refers to it. If the price changes, it changes in one place. You will work with keys in depth in module 3.

## The schema: the shape of the data

The **schema** is the description of the database: which tables exist, which columns each has, their types, and the rules (constraints) such as "`id` is the primary key" or "`price` cannot be empty". A schema is written in SQL too:

```sql
CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  author TEXT NOT NULL,
  price  REAL
);
```

You will write your own `CREATE TABLE` statements in module 4. For now, the key point is that the schema is decided up front, and the database enforces it. That strictness is a feature: it keeps bad data out.

## What SQL looks like

SQL is **declarative**: you describe the result you want, and the database works out how to get it. To ask for cheap books you write:

```sql
SELECT title, price
FROM books
WHERE price < 20;
```

You did not say "loop over every row and check the price". The database might scan the table, or it might use an index (a lookup structure, like the index at the back of a book) to jump straight to the answer. Either way, you get:

| title | price |
|---|---|
| Dune | 9.99 |

SQL keywords such as `SELECT` and `FROM` are not case-sensitive, but writing them in capitals is a common convention that makes queries easier to read. Statements end with a semicolon.

## Database engines and dialects

SQL is a standard, but each database engine adds its own extras. Common engines are **PostgreSQL**, **MySQL**, **SQL Server**, **Oracle** and **SQLite**. The core you learn in this course (`SELECT`, `WHERE`, `GROUP BY`, `JOIN`, `INSERT`, `UPDATE`, `CREATE TABLE`) works almost the same everywhere. Function names and date handling differ more, and lessons point out the differences when they matter.

The exercises in this course run on **SQLite**, directly in your browser. SQLite is a full SQL database stored in a single file. It ships inside every smartphone and web browser, which makes it a good engine to learn on.

## Relational is not the only kind

You will also hear about "NoSQL" databases, such as document stores (MongoDB) and key-value stores (Redis). They trade some of the relational rules for flexibility or speed in specific situations. Relational databases remain the default for business data, and SQL is one of the most requested skills in data and software job postings, so it is worth learning well.

## Common mistakes

- **Treating a table like a spreadsheet.** Spreadsheets let you put anything anywhere. A table has fixed columns and one kind of thing per table: customers in one table, orders in another.
- **Storing the same fact twice.** Copying a customer's address into every order means the copies drift apart. Store it once and link to it with a key.
- **Using names as identifiers.** Names change and repeat. Use a dedicated key column such as `id`.

## Quick recap

- A relational database stores data in tables of rows and typed columns.
- A primary key uniquely identifies each row; a foreign key points at a row in another table.
- The schema defines tables, columns, types and rules, and the database enforces it.
- SQL is declarative: you describe the result, the engine finds it.
- This course uses SQLite; the core SQL carries over to PostgreSQL, MySQL and others.

## Try it

This lesson has no code exercise. Open the Do tab and take the quiz to check that the vocabulary has stuck before you write your first query in the next lesson.


Learn how relational databases store data in tables of rows and columns, and what keys and schemas are.

What a relational database is

What is a Relational Database?

SQL Explained in 100 Seconds

## Why it matters

Most SQL anyone writes is reading data: "which orders shipped late?", "what are our ten most expensive products?", "which users signed up this month?". All of those start with the same three clauses: `SELECT` chooses columns, `WHERE` chooses rows, and `ORDER BY` sorts them. Learn these well and you can already answer real questions.

## The shape of a query

```sql
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;
```

The clauses must appear in this order. Only `SELECT` and `FROM` are required. Examples below use this `books` table:

| id | title | author | genre | price | published_year |
|---|---|---|---|---|---|
| 1 | Dune | Frank Herbert | Science fiction | 9.99 | 1965 |
| 2 | The Martian | Andy Weir | Science fiction | 11.50 | 2011 |
| 3 | Educated | Tara Westover | Memoir | 14.00 | 2018 |
| 4 | Project Hail Mary | Andy Weir | Science fiction | 16.99 | 2021 |
| 5 | Becoming | Michelle Obama | Memoir | 18.75 | 2018 |

## SELECT: choose columns

`SELECT *` returns every column. It is handy for a first look, but in real queries list the columns you need. It is clearer, and it keeps working if someone adds a column later.

```sql
SELECT title, author FROM books;
```

You can rename a column in the output with `AS`, which is called an **alias**:

```sql
SELECT title AS book, price AS price_usd FROM books;
```

`SELECT DISTINCT` removes duplicate rows from the result. `SELECT DISTINCT author FROM books;` lists Andy Weir once, not twice.

## WHERE: choose rows

`WHERE` keeps only rows where the condition is true.

```sql
SELECT title, price
FROM books
WHERE price < 15;
```

Comparison operators: `=`, `<>` (not equal; `!=` also works in most engines), `<`, `<=`, `>`, `>=`. Text values go in **single quotes**: `WHERE genre = 'Memoir'`. Double quotes are for column names in standard SQL, so `"Memoir"` can surprise you.

Combine conditions with `AND`, `OR` and `NOT`:

```sql
SELECT title
FROM books
WHERE genre = 'Science fiction' AND published_year >= 2000;
```

When you mix `AND` and `OR`, use parentheses. `AND` binds tighter, so without them the query may not mean what you think:

```sql
-- Memoirs or sci-fi, but only the cheap ones
WHERE (genre = 'Memoir' OR genre = 'Science fiction') AND price < 15
```

Three shortcuts make conditions easier to read:

- `IN` matches any value in a list: `WHERE author IN ('Andy Weir', 'Tara Westover')`.
- `BETWEEN` is inclusive at both ends: `WHERE published_year BETWEEN 2010 AND 2020`.
- `LIKE` matches text patterns. `%` means "any run of characters" and `_` means "exactly one character": `WHERE title LIKE 'The %'` finds titles starting with "The ". In SQLite, `LIKE` ignores case for ASCII letters; in PostgreSQL it is case-sensitive (use `ILIKE` there).

## ORDER BY: sort the result

Without `ORDER BY`, the database may return rows in any order, and that order can change between runs. If the order matters, say so.

```sql
SELECT title, price
FROM books
ORDER BY price DESC;
```

`ASC` (ascending, the default) sorts low to high; `DESC` sorts high to low. You can sort by several columns. The second breaks ties in the first:

```sql
SELECT title, author, published_year
FROM books
ORDER BY author ASC, published_year DESC;
```

Andy Weir's two books appear together, newest first.

## LIMIT: only the top rows

`LIMIT n` keeps the first `n` rows of the sorted result. Combined with `ORDER BY`, it answers "top N" questions:

```sql
-- The two most expensive books
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 2;
```

| title | price |
|---|---|
| Becoming | 18.75 |
| Project Hail Mary | 16.99 |

`LIMIT` works in SQLite, PostgreSQL and MySQL. SQL Server uses `SELECT TOP 2 ...` instead, and the standard form is `FETCH FIRST 2 ROWS ONLY`.

## How the database reads your query

You write `SELECT` first, but the database logically processes `FROM` first, then `WHERE`, then `SELECT`, then `ORDER BY`, then `LIMIT`. That explains a rule that trips up beginners: in most engines you cannot use a `SELECT` alias inside `WHERE`, because `WHERE` runs before the alias exists. You *can* use an alias in `ORDER BY`, because sorting happens after `SELECT`.

## Common mistakes

- **Double quotes for text.** Use `'Memoir'`, not `"Memoir"`.
- **`= NULL`.** Missing values are never equal to anything. Use `IS NULL` (covered in module 2).
- **Forgetting parentheses** when mixing `AND` and `OR`.
- **Relying on the default order.** No `ORDER BY` means no guaranteed order.
- **`LIMIT` without `ORDER BY`.** "The first 10 rows" is meaningless unless you say first by what.

## Quick recap

- `SELECT` picks columns (`*` for all, `AS` to rename, `DISTINCT` to drop duplicates).
- `WHERE` filters rows with comparisons, `AND`/`OR`/`NOT`, `IN`, `BETWEEN` and `LIKE`.
- `ORDER BY ... ASC|DESC` sorts; extra columns break ties.
- `LIMIT` keeps the top rows of a sorted result.

## Try it

Open the Do tab. You will query a small bookshop catalogue: filter by genre and price, then sort the result.


Choose columns with SELECT, filter rows with WHERE, and sort and limit results with ORDER BY and LIMIT.

SELECT, WHERE, ORDER BY

How to Filter with the WHERE clause in SQL

SQL - ORDER BY Keyword - W3Schools.com

## Why it matters

Real data is rarely in the exact shape you need. Emails arrive with stray capitals and spaces, names are split across two columns, prices need tax added, and dates need to become months or years for a report. SQL lets you compute new columns on the fly with **expressions** and **functions**, without changing the stored data.

## Expressions: calculated columns

Anywhere you can name a column in `SELECT`, you can write an expression instead. Give it an alias so the result column has a useful name.

```sql
SELECT name,
       price,
       quantity,
       price * quantity AS line_total
FROM order_items;
```

Arithmetic uses `+`, `-`, `*`, `/` and `%` (remainder). One trap: in SQLite, PostgreSQL and SQL Server, dividing two integers gives an integer. `7 / 2` is `3`, not `3.5`. Make one side a decimal to get a decimal result: `7 / 2.0` or `CAST(7 AS REAL) / 2` gives `3.5`.

## Math functions

- `ROUND(x, n)` rounds to `n` decimal places: `ROUND(19.987, 2)` is `19.99`.
- `ABS(x)` gives the absolute value.
- `MIN(a, b)` and `MAX(a, b)` with two or more arguments return the smaller or larger value in SQLite (other engines call these `LEAST` and `GREATEST`). With one argument they are aggregates, covered in the next module.

```sql
SELECT name, ROUND(price * 1.08, 2) AS price_with_tax
FROM products;
```

## String functions

Text functions are where engines differ most, so here are the SQLite forms with notes on others:

| Task | SQLite | Notes |
|---|---|---|
| Join text | `first_name \|\| ' ' \|\| last_name` | MySQL uses `CONCAT(a, b)`; PostgreSQL supports both |
| Upper / lower case | `UPPER(s)`, `LOWER(s)` | same everywhere |
| Remove spaces at the ends | `TRIM(s)` | also `LTRIM`, `RTRIM` |
| Length | `LENGTH(s)` | SQL Server uses `LEN` |
| Part of a string | `SUBSTR(s, start, length)` | positions start at 1; others use `SUBSTRING` |
| Find and replace | `REPLACE(s, 'old', 'new')` | same everywhere |
| Position of text | `INSTR(s, '@')` | PostgreSQL uses `POSITION('@' IN s)` |

Functions nest, and they run from the inside out. To clean an email address, trim it first and then lower-case it:

```sql
SELECT LOWER(TRIM(email)) AS email_clean
FROM customers;
```

`'  Ada.Lovelace@Example.COM '` becomes `'ada.lovelace@example.com'`.

To get the domain part of an email, find the `@` and take everything after it:

```sql
SELECT email,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers;
```

Leaving out the length argument of `SUBSTR` takes the rest of the string.

## Dates in SQLite

SQLite has no separate date type. Dates are usually stored as text in ISO format, `'YYYY-MM-DD'` (for example `'2024-03-15'`), which sorts correctly as text. Functions work on that format:

- `date('2024-03-15', '+30 days')` gives `'2024-04-14'`.
- `strftime('%Y', signup_date)` gives the year as text, `'2024'`; `'%m'` gives the month, `'%Y-%m'` gives `'2024-03'`.
- `julianday(b) - julianday(a)` gives the number of days between two dates.

```sql
SELECT id,
       strftime('%Y-%m', order_date) AS order_month,
       julianday(shipped_date) - julianday(order_date) AS days_to_ship
FROM orders;
```

Other engines have real `DATE` types and different functions (`EXTRACT(YEAR FROM d)` in PostgreSQL, `YEAR(d)` in MySQL and SQL Server, `DATEDIFF` for differences). The idea is the same; check your engine's documentation for names. Always store dates in ISO format: `'03/15/2024'` sorts wrongly and is ambiguous across countries.

## Converting types with CAST

`CAST(value AS type)` converts between types. `strftime('%Y', d)` returns text; `CAST(strftime('%Y', d) AS INTEGER)` returns a number you can do arithmetic with.

## Worked example

A marketing team wants a contact list with full names, clean emails and the year each customer joined:

```sql
SELECT id,
       first_name || ' ' || last_name AS full_name,
       LOWER(TRIM(email))             AS email_clean,
       CAST(strftime('%Y', signup_date) AS INTEGER) AS signup_year
FROM customers;
```

The stored data does not change. You are only shaping the output. Fixing the stored data uses `UPDATE`, covered in module 4.

## Common mistakes

- **Integer division.** `5 / 2` is `2` in SQLite. Use `5 / 2.0`.
- **Using `+` to join strings.** In SQLite `'a' + 'b'` tries arithmetic and returns `0`. Use `||`.
- **Forgetting aliases.** An unnamed expression gets a column name like `LOWER(TRIM(email))`, which is hard to use later.
- **Non-ISO dates.** Date functions expect `YYYY-MM-DD`; other formats return `NULL`.
- **Expecting functions to be portable.** Core arithmetic is; string and date function names vary by engine.

## Quick recap

- Expressions compute new columns; name them with `AS`.
- Watch integer division; use a decimal to get decimals.
- `||`, `UPPER`, `LOWER`, `TRIM`, `LENGTH`, `SUBSTR`, `REPLACE` and `INSTR` reshape text.
- SQLite dates are ISO text; `date`, `strftime` and `julianday` work on them.
- `CAST` converts types.

## Try it

Open the Do tab and build a clean contact list from a messy customer table.


Compute new columns with arithmetic and with string, date and math functions, without changing the stored data.

Expressions and functions

SQL String Functions Tutorial (LEFT, RIGHT, POSITION, CONCAT, LOWER, REPLACE)

Learn what a relational database is, then filter, sort and reshape rows with SELECT, WHERE, ORDER BY and functions.