Learn SQL

SQL Intro

A database holds one or more tables. A table is a grid of columns (fields) and rows (records). The examples below use this sample Customers table:

With SQL you can ask the database questions ("queries") and get back exactly the rows and columns you asked for.

SQL Syntax

SQL keywords are not case sensitive (select works like SELECT), but writing keywords in UPPERCASE is the convention. Statements end with a semicolon ; — required here, and good practice everywhere. Text values go in single quotes: 'Berlin'. Numbers do not: 42.

SELECT

The SELECT statement retrieves data from a table. Use * for all columns, or name the columns you want:

SELECT * FROM Customers;

SELECT CustomerName, City FROM Customers;

SELECT DISTINCT

SELECT DISTINCT returns only different (unique) values, removing duplicates:

SELECT DISTINCT Country FROM Customers;

WHERE

The WHERE clause filters rows, keeping only those that match a condition:

SELECT * FROM Customers
WHERE Country = 'Mexico';

Comparison operators: = equal, <> (or !=) not equal, >, <, >=, <=.

SELECT * FROM Customers
WHERE CustomerID > 2;

AND, OR and NOT

Combine conditions: AND requires all to be true, OR requires at least one, NOT negates. Use parentheses to group:

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'London';

SELECT * FROM Customers
WHERE NOT Country = 'Sweden';

SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Munich');

ORDER BY

Sorts the result. Ascending (ASC) is the default; DESC sorts descending:

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

-- sort by several columns
SELECT * FROM Customers
ORDER BY Country, CustomerName;

LIMIT

LIMIT caps how many rows come back — handy for peeking at a big table, or combined with ORDER BY to get "the top one":

SELECT * FROM Customers LIMIT 3;

-- the customer with the highest ID
SELECT * FROM Customers
ORDER BY CustomerID DESC
LIMIT 1;

NULL values

NULL means "no value". You cannot test it with =; use IS NULL or IS NOT NULL:

SELECT * FROM Customers
WHERE City IS NULL;

SELECT * FROM Customers
WHERE City IS NOT NULL;

LIKE and wildcards

LIKE searches for a pattern. % matches any number of characters (including none); _ matches exactly one character:

-- starts with 'A'
SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

-- ends with 'n'
SELECT * FROM Customers WHERE City LIKE '%n';

-- contains 'or'
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

-- 'L' then any two characters then 'ea'
SELECT * FROM Customers WHERE City LIKE 'L__ea';

IN

IN is shorthand for several OR conditions on the same column:

SELECT * FROM Customers
WHERE Country IN ('Germany', 'UK', 'Mexico');

SELECT * FROM Customers
WHERE Country NOT IN ('Sweden');

BETWEEN

BETWEEN selects values in a range, inclusive of both ends. Works for numbers, text and dates:

SELECT * FROM Customers
WHERE CustomerID BETWEEN 2 AND 4;

Aliases (AS)

An alias gives a column or a table a temporary name, to make output readable or queries shorter. The AS keyword is optional for tables:

SELECT CustomerName AS Name, City AS Town
FROM Customers;

SELECT c.CustomerName
FROM Customers c;

JOIN

A JOIN combines rows from two tables using a related column — usually an id. Given an Orders table with a CustomerID column, this lists each order with its customer's name:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

JOIN (inner join) returns only rows with a match in both tables. LEFT JOIN returns all rows from the left table, with NULLs where the right table has no match:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Aggregate functions

Aggregate functions compute one value from many rows:

SELECT COUNT(*) FROM Customers;        -- how many rows
SELECT MIN(CustomerID) FROM Customers; -- smallest value
SELECT MAX(CustomerID) FROM Customers; -- largest value
SELECT AVG(CustomerID) FROM Customers; -- average
SELECT SUM(CustomerID) FROM Customers; -- total

GROUP BY

GROUP BY collects rows that share a value into groups, usually to aggregate per group — "how many customers in each country":

SELECT Country, COUNT(*) AS customers
FROM Customers
GROUP BY Country
ORDER BY customers DESC;

HAVING

WHERE filters rows before grouping; HAVING filters the groups after aggregation:

SELECT Country, COUNT(*) AS customers
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 5;

Changing data (reference)

Full SQL also creates tables and edits rows. You will meet these when you build your own database (or submit a case as a SQL dump), though the investigation console here is read-only:

CREATE TABLE Persons (ID INTEGER PRIMARY KEY, Name TEXT, City TEXT);

INSERT INTO Persons (Name, City) VALUES ('Tom Hardy', 'London');

UPDATE Persons SET City = 'Oslo' WHERE Name = 'Tom Hardy';

DELETE FROM Persons WHERE Name = 'Tom Hardy';

Practise what you learned

The fastest way to make SQL stick is to use it on a problem. Every case on this site is a small database; explore it with SELECT name FROM sqlite_master WHERE type = 'table';, peek at tables with SELECT * FROM some_table LIMIT 3;, and follow the clues with the statements above.