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

SQL Fundamentals

## Why it matters

Reading data is half the job. Apps add new sign-ups, change order statuses and remove expired sessions all day, and analysts and administrators regularly fix bad records by hand. The statements are short, but they change real data, often with no undo. This lesson covers `INSERT`, `UPDATE` and `DELETE`, and the habits that keep them safe.

## INSERT: add rows

Name the columns, then give the values in the same order:

```sql
INSERT INTO products (name, price, stock)
VALUES ('USB-C cable', 9.99, 120);
```

Always list the columns. `INSERT INTO products VALUES (...)` depends on the exact column order of the table, and breaks (or worse, puts values in the wrong columns) when someone adds a column. Columns you leave out get their default value, or `NULL` if there is no default. An `INTEGER PRIMARY KEY` column such as `id` is filled in automatically in SQLite.

Insert several rows in one statement:

```sql
INSERT INTO products (name, price, stock) VALUES
  ('Laptop stand', 34.00, 15),
  ('Webcam', 59.50, 8);
```

You can also insert the result of a query, for example to copy rows into an archive table:

```sql
INSERT INTO products_archive (id, name, price)
SELECT id, name, price FROM products WHERE discontinued = 1;
```

## UPDATE: change existing rows

```sql
UPDATE products
SET price = 11.99
WHERE id = 3;
```

`SET` can change several columns, and can use the current values:

```sql
UPDATE products
SET price = ROUND(price * 1.10, 2),
    stock = stock + 50
WHERE id = 3;
```

**The `WHERE` clause is the whole safety story.** Without it, `UPDATE products SET price = 11.99;` changes **every** product. Most databases do exactly what you asked, immediately.

## DELETE: remove rows

```sql
DELETE FROM products
WHERE discontinued = 1 AND stock = 0;
```

The same warning applies, more strongly: `DELETE FROM products;` empties the table. Deleted rows do not go to a recycle bin; getting them back means restoring a backup.

## A safe routine for changing data

Professionals follow a routine, and so should you:

1. **Write the `WHERE` as a `SELECT` first.** `SELECT * FROM products WHERE discontinued = 1 AND stock = 0;` shows exactly which rows will change. Check the count.
2. **Target by primary key when you can.** `WHERE id = 3` touches one known row. `WHERE name = 'Webcam'` might match two.
3. **Turn the `SELECT` into the `UPDATE` or `DELETE`**, keeping the same `WHERE`.
4. **Use a transaction** on real data: `BEGIN;` the change, check the result, then `COMMIT;` or `ROLLBACK;`. Transactions are covered in this module's last lesson.
5. **Never practise on production.** Use a copy, a local database or an exercise like this course's Do tab. In a job, changes to live data usually go through a reviewed script or migration, and many teams require a backup or a second person to check first.

## Soft deletes

Many applications never truly delete important records. Instead they mark them:

```sql
UPDATE users SET deleted_at = '2024-06-01' WHERE id = 42;
```

Queries then filter with `WHERE deleted_at IS NULL`. The data can be restored, and audit history is kept. Personal data is the exception: privacy laws and user requests may require real deletion, and that is usually handled by a deliberate, tested process.

## Seeing what changed

The database tells you how many rows an `UPDATE` or `DELETE` affected; command-line tools print it, and in code your library returns it. If you expected 1 and see 3,000, stop and roll back.

PostgreSQL and SQLite (3.35 and later) also support `RETURNING`, which hands back the changed rows:

```sql
UPDATE products SET stock = stock - 1 WHERE id = 3 RETURNING id, stock;
```

## Constraints can refuse a change

An `INSERT` or `UPDATE` can fail because it breaks a rule in the schema: a `NOT NULL` column left empty, a duplicate value in a `UNIQUE` column, or a foreign key pointing nowhere. That is the database protecting you. Read the error message; it names the constraint. The next lesson shows how to define these rules.

## Common mistakes

- **No `WHERE`**, changing or deleting every row.
- **A `WHERE` that matches more than you think.** Preview it with `SELECT`.
- **Omitting the column list in `INSERT`.**
- **Text without quotes**: `SET status = shipped` treats `shipped` as a column name. Write `'shipped'`.
- **Running experiments against live data.**

## Quick recap

- `INSERT INTO t (cols) VALUES (...)` adds rows; always list columns.
- `UPDATE t SET col = value WHERE ...` changes rows; `DELETE FROM t WHERE ...` removes them.
- Preview every `WHERE` with a `SELECT`, target by primary key, and wrap risky changes in a transaction.
- Consider soft deletes for records you might need again.

## Try it

Open the Do tab and update a small product catalogue: add a product, raise a price and remove discontinued stock, then show the result.


Add, change and remove rows with INSERT, UPDATE and DELETE, using WHERE and previews to stay safe.

INSERT, UPDATE, DELETE

SQL Query Basics: Insert, Select, Update, and Delete

## Why it matters

Every table you have queried was created by someone who chose its columns, types and rules. Those choices decide what data can get in. A well-designed table rejects a missing email, a duplicate username or a negative price before it ever reaches a report. Developers write these definitions in **migrations**, and database administrators review them, so being able to read and write `CREATE TABLE` is a core skill.

## The basic form

```sql
CREATE TABLE members (
  id        INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  name      TEXT NOT NULL,
  plan      TEXT NOT NULL DEFAULT 'free',
  joined_on TEXT NOT NULL
);
```

Each line is a column: a name, a type, then optional **constraints**. Commas separate the columns; there is no comma after the last one.

## Choosing types

Common types across engines:

| Kind of data | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| Whole numbers | `INTEGER` | `INTEGER`, `BIGINT` | `INT`, `BIGINT` |
| Decimals | `REAL` | `NUMERIC(10,2)`, `DOUBLE PRECISION` | `DECIMAL(10,2)`, `DOUBLE` |
| Text | `TEXT` | `TEXT`, `VARCHAR(n)` | `VARCHAR(n)`, `TEXT` |
| True / false | `INTEGER` (0/1) | `BOOLEAN` | `BOOLEAN` (stored as `TINYINT(1)`) |
| Dates and times | `TEXT` (ISO format) | `DATE`, `TIMESTAMP` | `DATE`, `DATETIME` |

Two notes. First, for money, prefer an exact `NUMERIC`/`DECIMAL` type where the engine has one, or store whole cents as integers: floating-point types like `REAL` can show tiny rounding errors (`0.1 + 0.2` is not exactly `0.3`). Second, SQLite is unusually relaxed about types: by default it will store text in an `INTEGER` column. Since version 3.37 you can add `STRICT` after the closing parenthesis to make it enforce types like other engines. Constraints, below, are enforced either way.

## Constraints

- **`PRIMARY KEY`**: unique and identifies the row. In SQLite, `INTEGER PRIMARY KEY` also auto-numbers new rows. PostgreSQL uses `GENERATED ALWAYS AS IDENTITY` (or the older `SERIAL`), MySQL `AUTO_INCREMENT`.
- **`NOT NULL`**: the column must have a value. Make this the default for anything the application needs; leaving it out allows `NULL`.
- **`UNIQUE`**: no two rows may share the value. Use it for emails, usernames and codes.
- **`DEFAULT value`**: used when an `INSERT` leaves the column out. Here, new members get `'free'`.
- **`CHECK (condition)`**: any rule you can write as a condition: `CHECK (price >= 0)`, `CHECK (plan IN ('free', 'pro'))`.
- **`REFERENCES other(col)`**: a foreign key, as in module 3.

Constraints that involve several columns go at the end, after the column list:

```sql
CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL REFERENCES students(id),
  course_id   INTEGER NOT NULL REFERENCES courses(id),
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id)
);
```

## Seeing a constraint work

```sql
INSERT INTO members (email, name, joined_on)
VALUES ('ana@example.com', 'Ana', '2024-02-01');   -- ok, plan becomes 'free'

INSERT INTO members (email, name, joined_on)
VALUES ('ana@example.com', 'Ana again', '2024-02-02');
-- Error: UNIQUE constraint failed: members.email
```

The second insert is refused and the table is unchanged. That error is the schema doing its job.

## Inspecting a table

Each engine can describe a table's columns. In SQLite, the `pragma_table_info` function returns one row per column:

```sql
SELECT name, type, "notnull", dflt_value, pk
FROM pragma_table_info('members');
```

PostgreSQL uses `\d members` in its `psql` tool or the `information_schema.columns` view; MySQL uses `DESCRIBE members`.

## Changing and removing tables

- `ALTER TABLE members ADD COLUMN phone TEXT;` adds a column. Engines differ in what else `ALTER TABLE` can do; SQLite supports adding, renaming and dropping columns but not changing a column's type.
- `DROP TABLE members;` deletes the table **and all its data**. `DROP TABLE IF EXISTS` avoids an error if it is already gone.
- `CREATE TABLE IF NOT EXISTS` skips creation when the table exists.

On a shared or live database, these statements belong in a reviewed migration script, never typed by hand.

## Naming conventions

Pick a style and stick to it. A common one: lowercase `snake_case`, plural table names (`members`), `id` for the primary key, and `<thing>_id` for foreign keys (`member_id`). Avoid spaces and reserved words such as `order` or `group` as names.

## Common mistakes

- **A trailing comma** after the last column.
- **Forgetting `NOT NULL`**, so required data can be missing.
- **Floating-point types for money.**
- **Dates in non-ISO text** such as `'02/01/2024'`.
- **Reserved words as names**, which then need quoting everywhere.
- **Dropping a table to "start fresh"** on a database other people use.

## Quick recap

- `CREATE TABLE` defines columns, types and constraints.
- `PRIMARY KEY`, `NOT NULL`, `UNIQUE`, `DEFAULT`, `CHECK` and `REFERENCES` keep bad data out.
- Types differ by engine; SQLite is flexible unless you use `STRICT`.
- `ALTER TABLE` and `DROP TABLE` change schemas; use migrations on shared databases.

## Try it

Open the Do tab and define a `members` table with the right types and constraints.


Define a table with column types and constraints such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT and CHECK.

CREATE TABLE and constraints

Constraints - SQL - Tutorial 8

Constraints in SQL

## Why it matters

Two problems appear as soon as a database is used by real people. First, the same long query gets copied into ten reports and slowly drifts apart. Second, some changes take several statements, and if the third one fails, the first two have already happened. **Views** solve the first problem by giving a query a name. **Transactions** solve the second by making several statements succeed or fail as one.

## Views: a saved query with a name

```sql
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE deleted_at IS NULL;
```

Now you can query it like a table:

```sql
SELECT name FROM active_customers WHERE email LIKE '%@example.com';
```

A normal view stores **no data**. Every time you query it, the database runs the saved `SELECT` against the current tables, so it is always up to date.

Views are useful for:

- **Reuse**: define "active customer" or "monthly revenue" once, and every report uses the same logic.
- **Simplicity**: hide a five-table join behind a simple name for analysts or a BI tool.
- **Security**: grant a team access to a view that leaves out salary or personal columns, instead of the whole table (in engines with user permissions, such as PostgreSQL and SQL Server).

Change or remove a view with `DROP VIEW active_customers;` and create it again (PostgreSQL also has `CREATE OR REPLACE VIEW`). Dropping a view never deletes table data.

Some engines also offer **materialized views**, which store the result and must be refreshed. They trade freshness for speed on expensive queries. SQLite does not have them.

In SQLite you cannot `INSERT` into or `UPDATE` a view directly (unless you add special `INSTEAD OF` triggers). Other engines allow it for simple views. Either way, treat views mainly as a way to read.

## Transactions: all or nothing

Moving money between two accounts takes two updates:

```sql
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
```

If the system crashes between them, 25 has disappeared. A **transaction** groups them:

```sql
BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;
```

Until `COMMIT`, the changes are pending. If anything goes wrong, `ROLLBACK;` undoes everything since `BEGIN`, and the database is exactly as it was. Other users never see the halfway state where one account has been debited but the other not yet credited.

SQLite and PostgreSQL write `BEGIN` (or `BEGIN TRANSACTION`); MySQL also accepts `START TRANSACTION`; SQL Server uses `BEGIN TRANSACTION`. Outside an explicit transaction, most databases **autocommit**: each statement is its own transaction and is saved immediately.

## ACID in plain words

Transactions are described by four properties:

- **Atomic**: all of the statements happen, or none do.
- **Consistent**: constraints hold before and after; a transaction that would break a rule is refused.
- **Isolated**: concurrent transactions do not see each other's half-finished work. Engines offer isolation levels that trade strictness for speed; that is a topic for later courses.
- **Durable**: once committed, the change survives a crash or power cut.

## Using transactions as a safety net

Transactions are also a habit for manual changes, as promised in the INSERT/UPDATE/DELETE lesson:

```sql
BEGIN;
DELETE FROM sessions WHERE expires_at < '2024-01-01';
SELECT COUNT(*) FROM sessions;   -- check the result
-- happy?  COMMIT;   not happy?  ROLLBACK;
```

Keep transactions short. While one is open, it can hold locks that make other users wait, so do not start one and walk away. Also note that in some engines (MySQL, Oracle) schema statements like `CREATE TABLE` commit automatically, so they cannot be rolled back. PostgreSQL and SQLite can roll them back.

## Putting them together

A typical small task: apply a change safely, then give the team a view to check it.

```sql
BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

CREATE VIEW account_summary AS
SELECT owner,
       balance,
       CASE WHEN balance < 50 THEN 'low' ELSE 'ok' END AS status
FROM accounts;

SELECT * FROM account_summary;
```

Because the view is computed on demand, any later transfer shows up in it automatically.

## Common mistakes

- **Forgetting `COMMIT`.** Your changes may be discarded when the connection closes, and locks are held meanwhile.
- **Assuming a view stores a snapshot.** A normal view always reflects current data.
- **Using `SELECT *` in a view** that others depend on; later column changes can surprise them. List the columns.
- **Long-running transactions** that block other users.
- **Expecting `ROLLBACK` to undo schema changes** in engines that autocommit DDL.

## Quick recap

- `CREATE VIEW name AS SELECT ...` names a query; it stores no data and is always current.
- Views give reuse, simpler access and controlled exposure of columns.
- `BEGIN ... COMMIT` makes several statements one atomic unit; `ROLLBACK` cancels it.
- ACID: atomic, consistent, isolated, durable.
- Keep transactions short and use them around risky manual changes.

## Try it

Open the Do tab and make a transfer inside a transaction, then build a view that summarises the accounts.


Group changes into an all-or-nothing transaction with BEGIN and COMMIT, and save a reusable query as a view.

Views and transactions

MySQL VIEWS are awesome

What is a Database Transaction? Be ACID compliant!

Add, change and remove rows safely, define tables with types and constraints, and use views and transactions.