Last reviewed: Next review due:
What is SQL and why should journalists learn it?
SQL (Structured Query Language) is the standard language for querying relational databases. A relational database stores data in tables — like spreadsheet tabs — but allows you to join, filter, and aggregate millions of rows in seconds. The Land Registry price-paid dataset has over 28 million transactions: a query to find all properties sold in Leeds for more than £500,000 in 2024 takes under a second in SQLite; it would crash Excel.
The free tool to start with is DB Browser for SQLite. Download it from sqlitebrowser.org. Import any CSV, and you can start writing queries immediately.
Core SQL with UK examples
SELECT and WHERE
-- Land Registry: properties sold in Leeds for over £500k in 2024
SELECT town_city, street, price, date_of_transfer
FROM price_paid
WHERE town_city = 'LEEDS'
AND price > 500000
AND date_of_transfer >= '2024-01-01'
ORDER BY price DESC;GROUP BY and aggregation
-- Average house price by county in 2024
SELECT county,
COUNT(*) AS transactions,
ROUND(AVG(price), 0) AS avg_price,
ROUND(MIN(price), 0) AS min_price,
ROUND(MAX(price), 0) AS max_price
FROM price_paid
WHERE date_of_transfer >= '2024-01-01'
GROUP BY county
ORDER BY avg_price DESC;JOIN — linking two tables
-- Companies House: directors with addresses in a specific postcode
-- LEFT JOIN to find companies with NO registered officers
SELECT c.company_name, c.company_status, o.officer_name
FROM companies c
LEFT JOIN officers o ON c.company_number = o.company_number
WHERE o.officer_name IS NULL -- companies with no director on record
ORDER BY c.company_name;Subqueries for multi-step analysis
-- Find councils that spent more than average on consultancy
SELECT authority_name, SUM(amount) AS total_consultancy
FROM council_spending
WHERE expense_type LIKE '%consultancy%'
GROUP BY authority_name
HAVING SUM(amount) > (
SELECT AVG(authority_total) FROM (
SELECT SUM(amount) AS authority_total
FROM council_spending
WHERE expense_type LIKE '%consultancy%'
GROUP BY authority_name
)
)
ORDER BY total_consultancy DESC;When SQL is the right tool
- 1Datasets over 500,000 rows — Land Registry, full Companies House bulk, police crime data.
- 2Analysis requiring multiple JOINs that would be unwieldy with XLOOKUP.
- 3Repeated analysis on data that updates monthly or quarterly.
- 4Building a database that will be queried programmatically by a web application.
- 5Sharing reproducible analysis — SQL queries are self-documenting.
Red flags
- NULL values in your JOIN column — NULLs do not match anything, so rows silently disappear; use IS NULL checks.
- Case-sensitive string comparisons — 'Leeds' and 'LEEDS' may not match; use UPPER() or LOWER().
- Forgetting to GROUP BY all non-aggregated columns — most SQL engines will error or return unpredictable results.
- Treating COUNT(*) as equivalent to COUNT(column) — COUNT(*) counts rows; COUNT(column) skips NULLs.
- Using LIKE '%keyword%' on millions of rows without an index — it will be very slow.
SQL analysis checklist
- I have imported the CSV correctly and checked the column names and types.
- I have run SELECT COUNT(*) to verify the total row count matches the source file.
- I have checked for NULL values in key columns before JOINing.
- I have standardised text case with UPPER() or LOWER() before string comparisons.
- I have verified my GROUP BY totals match a separate COUNT(*) of the full table.
- I have saved my queries in a .sql file so my analysis is reproducible.
Get the data
Download Land Registry price-paid data and Companies House bulk files from their respective portals. If the data you need is not published, use our FOI Request Builder.
Common mistakes
- Using SELECT * in production queries — always specify the columns you need.
- Confusing WHERE and HAVING — WHERE filters rows before grouping; HAVING filters after.
- Not backing up the SQLite file before running DELETE or UPDATE statements.
- Assuming the CSV column order matches the database schema after import.
- Forgetting that SQLite dates are stored as text — use date functions carefully.
Related guides
Primary sources
Frequently asked questions
What database should a journalist use to learn SQL?
Start with SQLite via DB Browser for SQLite — it is free, requires no server setup, and stores the entire database in a single file you can email or back up. The SQL syntax is nearly identical to PostgreSQL and MySQL. Download DB Browser from sqlitebrowser.org. Import your CSV, and you can run queries within minutes.
What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns only rows that have a matching value in both tables. A LEFT JOIN returns all rows from the left table, and matched rows from the right table — with NULLs where there is no match. For journalism, LEFT JOINs are often more useful because they reveal which entries in your main table have no match in the reference data, which can itself be the story.
How do I import the Land Registry price-paid CSV into SQLite?
Download the full or partial price-paid CSV from landregistry.data.gov.uk. In DB Browser for SQLite: File > Import > Table from CSV File. Name the table 'price_paid'. The file has no header row — the columns are: transaction_id, price, date_of_transfer, postcode, property_type, old_new, duration, paon, saon, street, locality, town_city, district, county, ppd_category, record_status. You may need to add these as column names manually.
Related guides
Primary sources
- Land Registry Price Paid Data downloads— HM Land Registry
- Companies House bulk data products— Companies House
- SQLite documentation— SQLite
- DB Browser for SQLite (free download)— DB Browser Project
- ONS datasets catalogue— ONS
- NRS Scotland — statistics and data— National Records of Scotland