Hamropedia

Phase 6 of 8 · 7 lessons

Building tables and changing data

Create tables with the right rules, add, change and remove rows safely, group changes into transactions, and design tables that don't contradict themselves.

So far you have only read data. Real systems also write it — and writing is where data gets damaged. Constraints stop bad rows getting in, transactions stop half-finished changes, and normalisation stops the same fact being stored twice and drifting apart.

By Manoj Kumar Thapa

AI Software Engineer ·

0 of 7 done

Lessons, in order

  1. 6.1

    CREATE TABLE and ALTER TABLE

    Must know

    CREATE TABLE defines a new table — its name, its columns, their types and rules — and ALTER TABLE changes an existing table, for example by adding a column.
  2. 6.2

    Constraints: the rules that keep bad data out

    Must know

    Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.
  3. 6.3

    INSERT, UPDATE and DELETE

    Must know

    INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows — and both UPDATE and DELETE act on every row their WHERE matches, or on every row of the table if there is no WHERE.
  4. 6.4

    Transactions and ACID

    Must know

    A transaction groups several statements into one all-or-nothing unit — BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them — and ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.
  5. 6.5

    Normalisation: designing tables that don't contradict themselves

    Must know

    Normalisation is designing tables so each fact is stored in exactly one place — usually to third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.
  6. 6.6

    Views: saved queries you can use like tables

    Should know

    A view is a named, saved SELECT that you query like a table; it stores no data of its own, so it always shows the current data, while a materialised view stores the result and must be refreshed.
  7. 6.7

    DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP

    Should know

    SQL commands fall into four families — DDL changes structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK) — and DELETE, TRUNCATE and DROP remove rows, all rows, or the whole table.