Learn SQL

Where SQL is used

Reports and dashboards

Nearly every chart on a business dashboard has a SELECT with a GROUP BY underneath it, written and tuned by an analyst.

Web and mobile app backends

Users, orders and posts usually live in PostgreSQL, MySQL or SQLite. Frameworks generate much of the SQL; developers still read it when something is slow or wrong.

Data warehouses

Pipelines that clean and reshape data in warehouses such as BigQuery, Snowflake and Redshift are written mostly as SQL.

Software on devices

SQLite is a library rather than a server, so it is built into Android, iOS and the major web browsers to hold things such as history and settings.

One-off questions about a large file

When a CSV is too big for a spreadsheet, loading it into SQLite and querying it is often the quickest route to an answer.

Your first SQL program

Saved as first.sql. You can paste it straight into the playground to see it run.

CREATE TABLE people (
  name TEXT,
  age INTEGER
);

INSERT INTO people (name, age) VALUES ('Asha', 31);
INSERT INTO people (name, age) VALUES ('Ravi', 27);
INSERT INTO people (name, age) VALUES ('Mei', 45);

SELECT name, age FROM people ORDER BY age;

What it prints

name | age
-----+----
Ravi |  27
Asha |  31
Mei  |  45
(3 rows)

1. Lines 1 to 4 create a table called people with two columns, each with a name and a type: TEXT for words, INTEGER for whole numbers. The semicolon ends the statement; line breaks mean nothing. SQLite treats types as hints and will store 'thirty' in an INTEGER column; server databases such as PostgreSQL refuse.
2. Lines 6 to 8 add three rows. Text goes in single quotes, numbers are bare, and the values must come in the same order as the column list.
3. Line 10 asks for the name and age of every row, sorted by age, smallest first. Without ORDER BY the database may return rows in any order; a sorted result is the only order you are promised.
4. That output is what the playground on this page prints: a heading row, a rule, the rows, and a count. The formatting belongs to the tool rather than to SQL, so the same query looks different elsewhere — the sqlite3 command-line program prints Ravi|27 on one line per row until you turn on .headers on and .mode table, psql draws a bordered table, and the MySQL client draws a box of plus signs and dashes.

Try it in the SQL playground →

Run SQL on your own computer

SQLite is the easiest database to start with: one program, no server, no accounts, and each database kept in a single file. Everything in the roadmap below can be learned on it, and the queries carry over to the larger databases later.

1. 1

   Install SQLite

   macOS ships with the sqlite3 command. On Linux, install the sqlite3 package with your package manager. On Windows, download the sqlite-tools bundle from sqlite.org/download.html, unzip it, and run commands from inside that folder or add it to your PATH.

2. 2

   Check it works

   Open a terminal (Command Prompt or PowerShell on Windows, Terminal on macOS and Linux) and ask for the version. Any recent 3.x release is fine.

   Shell

   sqlite3 --version

3. 3

   Save the file and run it

   Put the first program in first.sql and run it from the same folder. The command creates demo.db if needed, runs every statement in order and prints the rows from the SELECT. To run it again from scratch, delete demo.db first; otherwise the CREATE TABLE fails and the INSERTs add three more rows.

   Shell

   sqlite3 demo.db < first.sql

4. 4

   Explore at the prompt

   Opening the database without a file gives a prompt where you can type one query at a time. Dot commands control the shell rather than the database: .tables lists tables, .headers on and .mode column make results easier to read, and .quit leaves.

   Shell

   sqlite3 demo.db

5. 5

   When you need a server

   PostgreSQL and MySQL (and its fork MariaDB) run as a separate server that many programs and people connect to at once, which is what a website or a team needs. Their clients, psql and mysql, play the part sqlite3 plays here. Install one when you have a reason to; the language is the same, with small differences in the corners.

A learning order for SQL

Stages, not a timetable. Each one exists because the next would not make sense without it, and how long each takes depends on how much you write.

1. Stage 1. Reading tables

   • SELECT and FROM
   • WHERE and comparison operators
   • ORDER BY
   • LIMIT
   • AND, OR and NOT

   Nearly every question anyone asks of a database begins with these few words. You can be useful to a team knowing nothing else.

2. Stage 2. Tidying what comes back

   • DISTINCT
   • calculated columns and aliases with AS
   • LIKE, IN and BETWEEN
   • NULL, IS NULL and IS NOT NULL
   • CASE
   • string and date functions

   Real data is messy, and NULL arrives here. Its three-valued logic is the first thing in SQL that behaves unlike ordinary arithmetic, and it deserves a slow look.

3. Stage 3. Summarising

   • COUNT, SUM, AVG, MIN and MAX
   • GROUP BY
   • HAVING
   • sorting by a summary

   This is where reports come from: totals by month, customers by region. It is also where plausible-but-wrong results come from, so the rules about which columns may appear deserve care.

4. Stage 4. Joining tables

   • primary and foreign keys
   • INNER JOIN
   • LEFT JOIN
   • joining three or more tables
   • self-joins

   Relational databases split information across tables on purpose, and the join is how it comes back together. It is the central idea of SQL and the one that takes longest to feel natural.

5. Stage 5. Changing data and defining tables

   • INSERT, UPDATE and DELETE
   • CREATE TABLE, types and constraints
   • ALTER TABLE
   • transactions: BEGIN, COMMIT and ROLLBACK

   Reading comes first because writing can destroy. By now the WHERE clause is second nature, which is what UPDATE and DELETE require, and transactions give you a way back when it is not.

6. Stage 6. Going deeper

   • subqueries
   • common table expressions with WITH
   • window functions
   • indexes and EXPLAIN
   • dialect differences
   • calling SQL from Python or another language

   These separate a query you can write from one you can trust on a large table. Indexes and query plans answer most questions about why a query is slow.

Mistakes beginners make in SQL

Running UPDATE or DELETE without a WHERE clause

DELETE FROM orders; removes every row, and UPDATE customers SET status = 'closed'; changes every customer, with no confirmation and, outside a transaction, no undo. Write a SELECT with the intended WHERE first, check it returns the rows you mean, then change the first word.

Testing for NULL with = or !=

NULL means unknown, and a comparison with an unknown is itself unknown, so WHERE email = NULL matches nothing and WHERE email != NULL matches nothing either. Use IS NULL and IS NOT NULL. The same rule is why COUNT(email) skips empty values while COUNT(*) counts every row.

Selecting a column that is not in the GROUP BY

SELECT city, name, COUNT(*) FROM customers GROUP BY city asks for one row per city, but each city has many names. PostgreSQL refuses to guess and reports an error; SQLite and some MySQL configurations quietly pick one, which looks like an answer and is not. Every selected column belongs in the GROUP BY or inside an aggregate.

Mixing up single and double quotes

Single quotes hold text values ('Asha'); double quotes name a column or table ("first name"). So WHERE name = "Asha" asks PostgreSQL for a column called Asha, which does not exist; SQLite and MySQL let it pass, which is how the habit forms. Numbers take no quotes at all.

Listing two tables without saying how they connect

SELECT * FROM orders, customers, or a JOIN with no ON clause, pairs every row of one table with every row of the other: a thousand orders and a thousand customers become a million meaningless rows. Write JOIN ... ON with the columns that link the tables, and check the row count against what you expected.

Strengths and trade-offs

Who SQL is for

SQL is for nearly everyone who works with data, which is a wider group than programmers: analysts, product managers, marketers, scientists and finance staff all write it, often without any other language. For developers it is not optional, because whatever the main language, the data usually sits in a relational database. It is not a first language for someone who wants to build complete programs, since it cannot do that on its own; pair it with Python or JavaScript.

Questions about learning SQL

Is SQL a programming language?

It is a language, and you write code in it, but it is a query language rather than a general-purpose one: you describe the data you want and the database decides the steps. You cannot build an application in SQL alone, which is why most people use it alongside Python, JavaScript or Java. Knowing it well is a skill in its own right.

Which database should I learn on?

SQLite for the first stretch: it installs in a minute, keeps each database in one file, and needs no server or passwords. Move to PostgreSQL when a team or a website needs to share the data; it is free, follows the SQL standard closely, and its documentation is thorough. MySQL and MariaDB are common in web hosting and worth recognising.

Do I need to know programming before SQL?

No. Many analysts, marketers and scientists write SQL daily without another language, and the first four stages of the roadmap assume nothing. Programming helps later, when you want to run queries from a script; at first, loops-and-variables thinking can even get in the way, because SQL works on whole sets of rows at once.

How is a database different from a spreadsheet?

A spreadsheet shows every cell and lets you type anywhere. A database table has fixed columns, holds far more rows than you would scroll through, and shows nothing until you ask in SQL. That is a nuisance for a shopping list and a relief once the data has a hundred thousand rows or lives in several tables that need combining.

The primary source

When this guide and the official documentation disagree, the documentation is right and we would like to hear about it.

• SQLite documentation ↗
• SQL as understood by SQLite ↗
• PostgreSQL documentation ↗