Learn SQL: where to start?

What is SQL?

SQL (Structured Query Language) is the language used to query relational databases: data stored in tables, with rows and columns, like a spreadsheet but much bigger. You use it to read, filter, group and combine that data. PostgreSQL, MySQL, SQLite, SQL Server and Oracle all speak SQL, with small syntax differences.

It is a skill in demand far beyond developers: data analysts, product managers, marketing, finance… As soon as you work with data, being able to write a query saves hours.

How long does it take to learn SQL?

The basics (reading, filtering, sorting, grouping, joining two tables) take a few weeks with a little practice every day: twenty regular minutes beat three hours once a month. Advanced topics, like CTEs and window functions, then take a few more weeks. What matters most is not reading, but writing queries.

Step 1: read data

Start by learning to pick columns, filter rows and sort the result. It is the basis of every other query.

• SELECT, aliases, DISTINCT: pick the columns. Card · exercises with solutions
• WHERE: filter rows (=, >, BETWEEN, IN, LIKE). Card · exercises with solutions
• ORDER BY, LIMIT: sort and keep the first rows. Card · exercises with solutions

Step 2: summarise data

Next, learn to compute totals, averages and counts, overall and then per group.

• COUNT, SUM, AVG, MIN, MAX: aggregates. Card · exercises with solutions
• GROUP BY: one summary per group. Card · exercises with solutions
• HAVING: filter groups. Card · exercises with solutions

Step 3: combine several tables

This is the step that really makes the difference: useful data is almost always spread across several tables.

• JOIN: link two tables. Card · exercises with solutions
• Subqueries: a query inside a query. Card · exercises with solutions
• NULL, COALESCE: handle missing values. Card · exercises with solutions
• CASE: row-by-row conditions. Card · exercises with solutions
• UNION, INTERSECT, EXCEPT: combine results. Card · exercises with solutions
• EXISTS: test whether related rows exist. Card · exercises with solutions

Step 4: go further

These topics set experienced profiles apart and often come up in interviews.

• Text and dates: UPPER, SUBSTR, LIKE, strftime… Card · exercises with solutions
• CTEs (WITH): break a complex query into steps. Card · exercises with solutions
• Window functions (OVER): rankings, running totals, comparisons with the previous row. Card · exercises with solutions
• Recursive CTEs: sequences and hierarchies. Card · exercises with solutions

The full course in 20 levels

Each level of the course has its own page: the lesson, the syntax, a worked example with its result, the key points, the common pitfalls and the tasks of its 5 exercises.

Beginner

1. Reading a table with SELECT: Show the columns you want from a table, rename them and compute new ones.
2. Filtering rows with WHERE: Keep only the rows that meet a condition.
3. Combining conditions: Write filters with several conditions and search inside text.
4. Sorting, limiting, removing duplicates: Present results in the order you want, keep only the first ones and remove duplicates.
5. Summarising a table: aggregates: Compute a total, an average, a minimum or a row count over a whole table.
6. Working with text and dates: Transform text and extract information from dates.

Intermediate

7. Grouping with GROUP BY: Compute an aggregate for each group of rows: total per seller, average per subject…
8. Filtering groups with HAVING: Keep only the groups that meet a condition on an aggregate.
9. Linking two tables with JOIN: Combine information from two tables linked by an id.
10. LEFT JOIN and multiple joins: Keep rows without a match, chain several joins and join a table to itself.
11. Conditions in the result: CASE: Compute a different value depending on a condition, and count rows per category.
12. Handling missing values (NULL): Understand how NULL behaves and replace missing values.
13. Combining results: UNION, INTERSECT, EXCEPT: Stack, intersect or subtract the results of two queries.

Pro

14. Queries inside queries: subqueries: Use the result of a query inside another one.
15. EXISTS and correlated subqueries: Test whether related rows exist and compare each row with its own group.
16. Structuring with WITH (CTE): Split a complex query into named, readable steps.
17. Window functions: ranking: Number and rank rows without grouping them, overall or within each group.
18. Window functions: running totals and averages: Show a group total, a running total or a share of the total next to each row.
19. Window functions: LAG, LEAD and frames: Compare a row with the previous or the next one, and compute over a sliding window.
20. Recursive queries: WITH RECURSIVE: Generate series and walk through hierarchies of unknown depth.

Practice a little every day

Reading a course is not enough: you remember by writing queries, getting them wrong, then fixing them. Three ways to practice with SpeedQL:

• the step-by-step learning path: 20 levels, each with a lesson and 5 exercises, to move forward one step at a time;
• the custom exercises: no timer, on the topics you choose, with hints;
• the timed game: to gain speed once you know the basics.

The most common beginner mistakes

• Writing column = NULL instead of column IS NULL: a comparison with NULL is never true.
• Showing a column in SELECT without putting it in GROUP BY or in an aggregate.
• Putting a condition on COUNT or SUM in WHERE instead of HAVING.
• Forgetting the ON condition of a join, which combines every row with every other row.
• Using NOT IN with a subquery that can return NULL: prefer NOT EXISTS.

What next?

Once you have practiced the 16 topics, install a real database (SQLite or PostgreSQL, both free) and query a dataset you care about: sport, films, your own spending… If you are preparing for an interview, review the SQL interview questions with answers, and keep the SQL cheat sheet at hand, with its table of differences between SQLite, PostgreSQL and MySQL.