Introduction to SQL

SQL (Structured Query Language) is the language for working with data in relational databases. Almost every app you use — online shops, banks, social networks, analytics dashboards — keeps its data in tables and talks to them with SQL. It's one of the most valuable and long-lived skills in software and data work.

Your first query

SELECT name, city, country
FROM customers
WHERE country = 'Portugal';

Even without knowing SQL, you can probably guess what this does: it fetches the name, city and country of every customer in Portugal. SQL is declarative — you describe the result you want, and the database works out how to get it.

Databases, tables, rows and columns

A relational database stores data in tables. Each table holds one kind of thing — customers, products, orders:

• A column is one attribute, with a name and a type: name, price, signup_date.
• A row (or record) is one item: one customer, one product.
• A primary key uniquely identifies each row, usually an id column.
• Tables are linked through foreign keys: an order stores the id of the customer who placed it.

The software that manages the data is a DBMS (database management system). It stores data safely on disk, lets many users work at once, enforces rules, and runs your SQL.

What SQL can do

One language, many databases

SQL is an international standard (ISO/IEC 9075), implemented by many database systems. The core — SELECT, joins, grouping, INSERT and so on — is the same everywhere, but each system adds its own functions and syntax, called its dialect.

This course teaches standard SQL that works across systems, and points out where the popular dialects differ. Lesson 52 summarises the differences.

Running SQL in this course

The Try it yourself editors run real SQL in your browser using SQLite (compiled to WebAssembly). Each run starts with a fresh copy of the course's demo shop database, so you can experiment freely — even deleting everything — and simply run again to start over.

Press Run (or Ctrl+Enter). Change 'Books' to 'Toys' and run it again.

SQL syntax basics

• Keywords aren't case-sensitive: SELECT, select and Select are the same. Writing keywords in capitals is a common convention that makes queries easier to scan.
• Whitespace doesn't matter: put clauses on separate lines for readability.
• Statements end with a semicolon, which is required when running several statements together.
• Text values use single quotes: 'Lisbon'. Double quotes are for identifiers (names of tables and columns) in standard SQL.
• Comments: -- to the end of the line or /* across lines */.

-- Products that are out of stock
SELECT name, stock
FROM products
WHERE stock = 0;  /* only one so far */

Try it yourself