Use this guide if you want to query data with SQL for work, analysis or app building. You need a computer and either Python (which includes SQLite support) or a free SQLite browser tool.
Step by step
Set up SQLite
SQLite is a free database that stores everything in a single file, so it needs no server. Install a free graphical tool such as DB Browser for SQLite, or use the sqlite3 command-line tool, or Python's built-in sqlite3 module. Create a new database file called practice.db in a practice folder.
Create a table
Write a CREATE TABLE statement for something simple, such as books with columns for id, title, author, year and rating. Give each column a type, such as INTEGER or TEXT, and make id the primary key. Run it and check the empty table appears.
Add some data
Use INSERT INTO statements to add ten or so rows of realistic sample data. Include a few repeats, such as two books by the same author, so later queries are interesting. Run SELECT * FROM books to see everything you have added.
Filter and sort
Use SELECT with a WHERE clause to find rows that match a condition, such as books after a certain year. Add ORDER BY to sort the results and LIMIT to show only the first few. Try combining conditions with AND and OR.
Summarise with groups
Use COUNT, AVG, MIN and MAX to summarise your data. Add GROUP BY to get one result per group, such as the number of books by each author. Use HAVING to filter groups, for example authors with more than one book.
Update and delete carefully
Before any UPDATE or DELETE, run the same WHERE clause with SELECT to see exactly which rows will change. Forgetting the WHERE clause changes every row in the table. Keep a copy of your database file before practising these commands.
Ready-to-use checklist
- SQLite tool installed
- Practice database file created
- First table created
- Sample rows inserted
- WHERE, ORDER BY and LIMIT tried
- GROUP BY summary written
- SELECT run before UPDATE or DELETE
- Backup copy of the database made
Practical tips
- Write SQL keywords in capitals and names in lower case to make queries easier to read.
- Save useful queries in a text file with a short comment, so you build your own reference.
- When you move on to real apps, use parameterised queries rather than joining user input into SQL, to prevent SQL injection.
Common problems
You get 'no such table'.
Check the spelling and that you are connected to the right database file. A tool may create a new empty database if the file path is wrong.
Your UPDATE changed every row.
The WHERE clause was missing or too broad. Restore from your backup copy and always test the WHERE clause with SELECT first.
Text comparisons do not find what you expect.
Check for extra spaces and differences in capital letters. Use LIKE with the percent sign for partial matches, such as titles containing a word.
This guide gives general information, not personal legal, financial or medical advice. Rules and prices change, so check the current position with the official service before acting.
This guide is free. Unlock all ABCDayZ guides from £3.99 – one payment, no automatic renewal.