Skip to content
Varnook
All courses
SQLIntermediate

Database Design and Data Changes

Change data safely, then design, link and tune your own tables in SQLite.

Recommended first: SQL Fundamentals. This course builds on it.

Start courseView syllabus

Free to browse. You sign in at the first lesson so your progress is saved.

What you will be able to do

  • Add, change and remove rows safely with INSERT, UPDATE and DELETE
  • Create tables with sensible types, primary keys and constraints
  • Link tables with foreign keys, including many-to-many relationships
  • Split a flat, repetitive table into related tables
  • Use indexes, views and transactions where they help

Syllabus

7 modules and 22 lessons. Each lesson is a short read followed by an exercise with tests.

Module 1

Changing data

Add, change and remove rows, and check exactly what changed.6 lessons, about 1 hour
  1. 1Adding a rowAdd a new row to a table with INSERT.12 min
  2. 2Adding several rows at onceInsert many rows with a single INSERT statement.10 min
  3. 3Copying rows with INSERT and SELECTFill a table with the results of a query.12 min
  4. 4Changing rows with UPDATEChange values in existing rows, and only the rows you mean to.12 min
  5. 5Removing rows safelyDelete rows with DELETE, and check what will go before you run it.14 min
  6. 6Seeing changes with RETURNINGGet back the rows an INSERT, UPDATE or DELETE touched.10 min
Module 2

Creating tables

Design your own tables, with sensible column types and primary keys.3 lessons, about 40 min
  1. 7Creating a tableDefine a new table and its columns with CREATE TABLE.12 min
  2. 8How SQLite treats column typesUnderstand type affinity and check how values are stored with typeof.14 min
  3. 9Primary keys and row idsLearn how INTEGER PRIMARY KEY and AUTOINCREMENT choose ids.14 min
Module 3

Constraints

Let the database turn away bad data before it gets in.3 lessons, about 38 min
  1. 10Required values and defaultsUse NOT NULL and DEFAULT so every row has sensible values.12 min
  2. 11Keeping values uniqueStop duplicates with UNIQUE, and skip them with INSERT OR IGNORE.12 min
  3. 12Rules with CHECKWrite your own rules for what a column may contain.14 min
Module 4

Relationships

Connect tables with foreign keys, for one-to-many and many-to-many data.3 lessons, about 44 min
  1. 13Foreign keysMake sure a reference to another table always points at a real row.14 min
  2. 14One-to-many relationshipsModel a parent with many children, and decide what happens when the parent goes.14 min
  3. 15Many-to-many relationshipsConnect two tables through a junction table.16 min
Module 5

Normalisation

Spot repeated data and split it into tables that stay consistent.2 lessons, about 32 min
  1. 16Spotting repeated dataRecognise the warning signs of a table that needs splitting.12 min
  2. 17Splitting a flat tableMove repeated data into its own table and link it back with a key.20 min
Module 6

Changing structure

Alter existing tables, speed up lookups with indexes and save queries as views.3 lessons, about 40 min
  1. 18Changing a table with ALTER TABLEAdd and rename columns in a table that already holds data.12 min
  2. 19Speeding up lookups with indexesCreate an index and check whether a query uses it.14 min
  3. 20Saving queries as viewsGive a query a name and use it like a table.14 min
Module 7

Transactions

Group changes so they all happen together, or not at all.2 lessons, about 26 min
  1. 21Grouping changes with a transactionUse BEGIN and COMMIT to apply several changes as one.14 min
  2. 22Undoing with ROLLBACKThrow away every change since BEGIN.12 min