SQL Introduction

Almost every website or app you use keeps its data somewhere: user accounts, orders, blog posts, prices. Most of that data lives in a relational database, and the language used to talk to it is SQL. In this SQL introduction and the rest of the track you will learn SQL step by step, running real queries in your browser as you go.

What is SQL?

SQL stands for Structured Query Language. Some people say each letter (“S-Q-L”), others say “sequel” – both are fine. It is a language for asking a database questions and for changing the data inside it.

SQL is declarative. You describe what you want (“give me all customers in Chennai”) and the database works out how to find it. You do not write loops or tell it which file to open. This is why a few lines of SQL can replace pages of code in other languages.

With SQL you can:

• read data with SELECT
• add, change and remove rows with INSERT, UPDATE and DELETE
• create and change tables with CREATE TABLE and ALTER TABLE
• combine data from several tables with joins
• summarise data with totals, counts and averages

Tables, rows and columns

A relational database stores data in tables. A table looks like a spreadsheet: each column has a name and holds one kind of information, and each row is one record. Unlike a spreadsheet, every row in a table follows the same structure, and tables are linked to each other through shared values called keys.

All lessons in this track, including this SQL introduction, use the same small database for an imaginary grocery wholesaler. It has six tables:

Notice how Orders does not repeat the customer’s name and city. It only stores CustomerID. That number points to one row in Customers. Storing each fact once and linking to it is the main idea behind relational databases – it keeps data consistent and avoids typing the same address fifty times.

Your first query

The most common SQL statement is SELECT. The query below asks for every column (*) of every row in the Categories table. Press Try it Yourself, run it, then change Categories to Products and run it again.

SELECT * FROM Categories;

You can also list just the columns you need, separated by commas. The result keeps the order you write them in.

SELECT CustomerName, City, Country
FROM Customers;

Rules of SQL syntax

• Keywords are not case-sensitive. SELECT, select and Select all work. Writing keywords in capitals is a common habit because it makes queries easier to read.
• Statements end with a semicolon. Many tools let you skip it for a single statement, but it is required when you run several statements together, so get used to adding it.
• Spaces and line breaks do not matter. Put each clause on its own line once queries get longer.
• Text values go in single quotes, for example 'Chennai'. Numbers do not need quotes.
• Comments start with -- and run to the end of the line, or sit between /* and */.

-- List every table in this database (SQLite only)
SELECT name
FROM sqlite_master
WHERE type = 'table'; /* sqlite_master is SQLite's built-in catalogue */

SQL dialects: SQLite, MySQL and others

SQL is an international standard, but every database product adds its own extras and has a few gaps. These versions are called dialects. The core – SELECT, WHERE, JOIN, GROUP BY, INSERT – is almost identical everywhere.

Everything in this track runs in SQLite. When MySQL or SQL Server does something differently – for example, SQL Server uses TOP where SQLite and MySQL use LIMIT – the lesson will point it out, so your knowledge carries over when you later connect a PHP site to MySQL.

Next steps after this SQL introduction

In the next lesson, SQL SELECT, you will learn how to rename columns, calculate new values and remove duplicate rows. After that, SQL WHERE shows how to filter rows so you only get the data you asked for.

Further reading

• sqlite.org/lang.html
• sqlite.org/lang_select.html
• dev.mysql.com/doc/refman/8.4/en/select.html