Databases and SQL

Learn how relational databases store data and how to query it with SQL. Tables, SELECT, filtering, joins, aggregates, safe updates, transactions, indexes and schema design.

Beginner8 lessonsAbout 44 minFree

Almost every application keeps its data in a database, and most of those speak SQL. This course teaches the SQL you will use every day, using one small example database from start to finish: a shop with customers, products and orders.

What you will learn

  • What a relational database is, and how tables, rows, keys and relationships fit together
  • Creating tables with the right data types and constraints
  • Reading data with SELECT, WHERE, ORDER BY and LIMIT
  • Combining tables with JOIN
  • Summarising data with GROUP BY and aggregate functions
  • Changing data safely with INSERT, UPDATE, DELETE and transactions
  • Making queries fast with indexes, and designing a schema that stays clean

Before you start

You need no programming background. To run the examples, use any of these:

  • SQLite: install it, run sqlite3 shop.db, and paste the queries. Nothing else to set up.
  • PostgreSQL or MySQL: install locally or use a free hosted database.
  • An online playground: search for "SQL playground" and pick one that supports PostgreSQL or SQLite.

The examples use standard SQL that works in PostgreSQL, MySQL and SQLite. Where the three differ, the lesson says so.

Lessons

  1. 01What is a database?6 minTables, rows and columns, primary and foreign keys, relational versus NoSQL databases, and what SQL actually is.
  2. 02Creating tables6 minCREATE TABLE, choosing data types, constraints that keep data valid, and building the shop database used for the rest of the course.
  3. 03Reading data with SELECT5 minChoose columns, rename them with aliases, calculate new values, remove duplicates, and understand the order SQL runs a query in.
  4. 04Filtering and sorting5 minWHERE with comparison and logical operators, IN, BETWEEN, LIKE, handling NULL, sorting on several columns, and paging through results.
  5. 05Joining tables6 minINNER JOIN, LEFT JOIN, joining three or more tables, self joins, and the mistakes that quietly multiply or drop rows.
  6. 06Grouping and aggregates5 minCOUNT, SUM, AVG, MIN and MAX, GROUP BY and HAVING, subqueries, common table expressions, and a first look at window functions.
  7. 07Changing data and transactions5 minINSERT, UPDATE and DELETE without accidents, upserts, transactions and the ACID guarantees, and how to keep SQL injection out of your application.
  8. 08Indexes and schema design6 minHow indexes make queries fast and what they cost, reading a query plan, normalisation in plain words, and a checklist for designing tables.