Start learning →

‹ All tracks

Database Management Systems

Learn SQL and relational databases by running real queries in your browser: tables, SELECT, joins, grouping, subqueries, keys, design and normalization, indexes and transactions.

Level
Beginner
Time
About 42 hours
Modules
6
Lessons
15
Exercises
47

Almost every application keeps its data in a database, and SQL is the language for asking that data questions. This course teaches SQL from the first table to transactions, using SQLite, a real database system that runs inside your browser. Everything you write runs against real tables, and you can step through each statement and watch the tables change.

You start by creating tables and asking simple questions, then sort, change and summarise data, link tables with keys and joins, and write subqueries, views and set operations. The second half is about design: entity-relationship models, normalization, indexes and transactions. The lessons point out where MySQL, PostgreSQL and Oracle differ from SQLite, and each exercise is checked against hidden tests with their own data.

What you will learn

  • Create tables and add, change and delete rows
  • Answer questions with SELECT, WHERE, ORDER BY, GROUP BY and HAVING
  • Combine tables with joins, subqueries and set operations
  • Protect data with keys and constraints
  • Design a database with an ER model and normalize it
  • Explain indexes and transactions, and use them correctly

Skills you will gain

  • Translating a question in words into a SQL query
  • Predicting the rows a query returns, including with NULL
  • Designing tables so that each fact is stored once
  • Reading query plans
  • Changing data safely

Before you start

  • Comfortable using a computer and typing precisely
  • No programming experience is needed
  • School-level arithmetic

Where it is used

  • Web and mobile application back ends
  • Data analysis and reporting
  • Business systems such as banking, shops and student records
  • Technical interviews for developer and analyst roles

Modules

Module 1: Tables and basic queries

What a database is, how to create tables and add rows, and how to choose, sort and de-duplicate the rows a query returns.

  1. 1

    Databases and tables

    What a database is, why programs keep data in one instead of plain files, and how a table holds data in rows and columns.

  2. 2

    Creating tables and adding rows

    CREATE TABLE with column types and defaults, INSERT one row or many, NULL for a missing value, and ALTER TABLE and DROP TABLE.

  3. 3

    Choosing rows and columns: SELECT and WHERE

    Pick columns and calculate new ones with SELECT; keep only the rows you want with WHERE, AND, OR, BETWEEN, IN and LIKE; and handle NULL with IS NULL.

  4. 4

    Sorting, limiting and DISTINCT

    ORDER BY one or more columns, LIMIT and OFFSET for the top rows and for pages, and DISTINCT to remove repeated rows.

Module practice and quiz →

Module 2: Changing and summarising data

Changing and deleting rows safely, and turning many rows into counts, totals and averages, per group.

  1. 1

    Changing and deleting rows

    UPDATE changes values and DELETE removes rows, both only where the WHERE condition holds. Without WHERE they touch every row.

  2. 2

    Counting and totals: GROUP BY and HAVING

    COUNT, SUM, AVG, MIN and MAX sum up many rows; GROUP BY gives one result row per group; HAVING keeps only some groups.

Module practice and quiz →

Module 3: Keys and joins

Rules that keep bad data out, keys that identify and link rows, and joins that bring linked tables back together in a query.

  1. 1

    Keys and constraints

    Rules the DBMS enforces for you: NOT NULL, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY, plus what candidate, primary and foreign keys are.

  2. 2

    Joining tables

    Combine rows from several tables: CROSS JOIN pairs everything, INNER JOIN keeps matching pairs, LEFT JOIN keeps every row of the left table, and a self join matches a table with itself.

Module practice and quiz →

Module 4: Algebra, subqueries and views

The relational algebra under SQL, queries inside queries, CASE expressions, views and set operations.

  1. 1

    Relational algebra

    The mathematics under SQL: select σ, project π, product ×, join ⋈, union ∪, difference − and rename ρ, each with the SQL that does the same.

  2. 2

    Subqueries and WITH

    A query inside a query: compare with a value worked out by another SELECT, test membership with IN and EXISTS, correlate a subquery with each row, and name steps with WITH.

  3. 3

    CASE, views and set operations

    Turn values into labels with CASE, save a query as a view, and combine query results with UNION, INTERSECT and EXCEPT.

Module practice and quiz →

Module 5: Database design

Planning a database with an entity-relationship model, turning it into tables, and normalizing tables so that each fact is stored once.

  1. 1

    Designing a database: the ER model

    Entities, attributes and relationships; one-to-one, one-to-many and many-to-many; participation and weak entities; and the rules that turn an ER diagram into tables.

  2. 2

    Normalization

    Why one big table goes wrong (update, insert and delete anomalies), functional dependencies, and the normal forms 1NF, 2NF, 3NF and BCNF that split it into good tables.

Module practice and quiz →

Module 6: Indexes and transactions

How indexes make searches fast and what they cost, and how transactions keep data correct when changes go wrong or happen at the same time.

  1. 1

    Indexes and how queries find rows

    A full table scan reads every row; an index, usually a B+ tree, finds rows in a few steps. CREATE INDEX, EXPLAIN QUERY PLAN, and the cost of every index.

  2. 2

    Transactions and concurrency

    BEGIN, COMMIT and ROLLBACK; the ACID properties; savepoints; what goes wrong when transactions run at once, and how isolation levels, locks and logs prevent it.

Module practice and quiz →

Projects

Final assessment →

What to learn next