What is SQL?

A relational database stores data in tables, like spreadsheets with strict rules: each row is one record and each column is one piece of information about it. SQL is how you talk to those tables. Almost every website, banking system and phone app that stores data uses SQL somewhere, which is why it is one of the most useful technical skills you can learn.

What does SQL stand for?

SQL stands for Structured Query Language. A query is a question you ask a database. People say it both as letters, "S-Q-L", and as "sequel". Both are fine, and the second comes from its original name, SEQUEL.

What is SQL used for?

SQL is used for four kinds of job:

• Reading data: finding, filtering, sorting and summing records, for example the ten best-selling products this week.
• Changing data: adding new records, updating them and deleting them, for example when you place an order or change your password.
• Building the database: creating tables, choosing what type of data each column holds, and setting rules such as "every order must belong to a real customer".
• Controlling access: deciding which users can read or change which tables.

Here is where you meet it in real life.

• Websites and apps: logins, shopping baskets, posts and messages usually live in a SQL database such as PostgreSQL, MySQL or Microsoft SQL Server.
• Your phone: SQLite, a small SQL database, is built into every Android phone and iPhone, and apps use it to store data on the device.
• Data analysis: analysts write SQL to answer business questions, like which regions are growing, and feed the answers into reports and dashboards.
• Science, finance and government: anywhere large amounts of structured records need to be kept accurate and searched quickly.

How SQL works: a simple example

Here is a small table called books. The id column is the primary key: a value that is different for every row, so each book can be picked out exactly.

To list the books published before 1960, oldest first:

SELECT title, year
FROM books
WHERE year < 1960
ORDER BY year;

Read it line by line. SELECT names the columns you want back. FROM names the table. WHERE keeps only the rows that match a condition. ORDER BY sorts what is left. The semicolon ends the statement.

Notice what you didn't say: how to find the rows. SQL is declarative. You describe the result you want, and the database works out the fastest way to get it, for example by using an index instead of reading every row. That is a big difference from languages like Python, where you write the steps yourself.

Try writing a SELECT query of your own, then press Mark to check it in our Databases course.

The main SQL commands

SQL has a lot of features, but most everyday work uses a small set of commands. They are often grouped like this.

Adding, changing and deleting rows

INSERT INTO books (id, title, author, year, price)
VALUES (6, 'Kindred', 'Octavia E. Butler', 1979, 9.25);

UPDATE books
SET price = 7.49
WHERE id = 3;

DELETE FROM books
WHERE id = 2;

The first statement adds a sixth book. The second cuts the price of The Hobbit, and the third removes Neuromancer. The WHERE clause matters enormously here. UPDATE books SET price = 7.49; with no WHERE changes the price of every book in the table.

Creating a table

CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  member VARCHAR(50) NOT NULL,
  due_date DATE
);

Each column gets a data type: whole numbers, text up to 50 characters, a date. NOT NULL means the value can't be left empty. REFERENCES books(id) makes book_id a foreign key: the database refuses a loan for a book that doesn't exist.

More SQL examples

Example 1: counting and averaging

How many books in the original table were published in 1960 or later, and what is their average price?

SELECT COUNT(*) AS book_count,
       AVG(price) AS average_price
FROM books
WHERE year >= 1960;

Three books match: Dune, Neuromancer and Hyperion. Their prices add up to 9.99 + 8.50 + 10.49 = 28.98, so the result is a count of 3 and an average of 28.98 ÷ 3 = 9.66. AS gives each result column a readable name.

Example 2: joining two tables

Suppose the loans table holds three loans: book 1 to Priya, book 3 to Tom and book 1 to Sam. It only stores book numbers, so to see titles you join it to books:

SELECT books.title, loans.member
FROM loans
JOIN books ON books.id = loans.book_id;

ON says how the rows match up: each loan's book_id equals a book's id. Storing each book once and pointing to it by number is the core idea of a relational database: no title is ever typed twice, so it can't be typed two different ways. The order of the rows isn't guaranteed unless you add ORDER BY.

Example 3: grouping

How many times has each book been borrowed?

SELECT books.title, COUNT(*) AS times_borrowed
FROM loans
JOIN books ON books.id = loans.book_id
GROUP BY books.title;

GROUP BY gathers the rows for each title into one, and COUNT(*) counts the rows in each group. The result is Dune with 2 and The Hobbit with 1. Books never borrowed don't appear, because an ordinary join only keeps rows that match. A LEFT JOIN from books would keep them.

The order SQL runs in

You write a query starting with SELECT, but the database works through it in a different logical order. Knowing it explains many puzzling errors.

1. FROM and JOIN: gather the rows
2. WHERE: filter single rows
3. GROUP BY: form groups
4. HAVING: filter groups
5. SELECT: pick and calculate the columns
6. ORDER BY: sort
7. LIMIT: keep the first few

That is why WHERE COUNT(*) > 1 fails: when WHERE runs, there are no groups to count yet. Use HAVING COUNT(*) > 1 instead. It is also why many databases won't let you use a column name made with AS inside WHERE.

SQL vs MySQL, PostgreSQL and other databases

SQL is the language. MySQL, PostgreSQL, SQLite, Microsoft SQL Server and Oracle are database systems that understand it, a bit like different makes of car that all use the same road rules. The core commands work the same on all of them, but each adds its own extras and has small differences. For example, MySQL and PostgreSQL use LIMIT 10 to return ten rows, SQL Server uses SELECT TOP 10, and the official standard uses FETCH FIRST 10 ROWS ONLY.

SQL vs NoSQL

NoSQL databases store data in other shapes: documents (MongoDB), key-value pairs (Redis), wide columns or graphs. They can be easier to spread across many servers and handle data whose shape varies, but they usually give up some of SQL's strict rules. SQL databases are the usual choice when data has a clear structure and must stay consistent, like orders and payments. Many systems use both.

Test yourself: decide whether each situation suits SQL or NoSQL, then press Mark.

A short history of SQL

In 1970, Edgar F. Codd at IBM published the relational model: store data in tables and describe what you want with mathematics, not with step-by-step instructions. IBM researchers Donald Chamberlin and Raymond Boyce designed a language for it, and in 1974 they published SEQUEL, the Structured English Query Language. A trademark clash forced a shorter name, SQL. Oracle sold the first commercial SQL database in 1979, ANSI made SQL a standard in 1986 and ISO followed in 1987. The standard has been updated many times since, most recently as SQL:2023.

Is SQL hard to learn?

The basics are not. SELECT, FROM, WHERE, ORDER BY and the four data commands cover a huge share of real work, and you can learn them in a few sessions. Joins, grouping and designing good tables take longer. The best way to learn is to write queries against a real table and check the results, which is how our Databases course works: it moves from tables and keys through queries and joins to SQL vs NoSQL and ORMs. Writing SQL safely matters too: building a query by pasting in user input opens the door to SQL injection (see how to prevent SQL injection attacks), which our Hack a Website course shows from the attacker's side.

Common mistakes

• Forgetting WHERE on UPDATE or DELETE. Without it, every row changes or disappears. Run the same WHERE with SELECT first to see which rows you will hit.
• Testing for NULL with =. WHERE due_date = NULL matches nothing. Use WHERE due_date IS NULL.
• Using WHERE to filter groups. Filter groups with HAVING, which runs after GROUP BY.
• Mixing up quotes. Text values go in single quotes, 'Leeds'. Double quotes are for names of columns or tables in standard SQL.
• Expecting rows in a set order. Without ORDER BY, the database can return rows in any order.
• Building queries from user input by joining strings. Use parameterised queries, so input is always treated as data, never as SQL.

Read next

Computer Science · DatabasesSQL practice online: 9 exercises with answersFree SQL practice online: two small tables and nine exercises, from SELECT and WHERE to GROUP BY, HAVING and JOINs, each with the query and the result.