Create a Learning Roadmap for SQL and Related Technologies
10 min read ยท 2026-11-07
To create a learning roadmap for SQL and related technologies, start by choosing the role you want: analyst, data engineer, backend developer, or BI and database admin. Then learn one shared SQL core in this order: single-table queries, aggregation and joins, CTEs and window functions, schema design, and performance. After that, add the tools your role needs. At 6 to 8 hours a week on PostgreSQL, the plan below takes about six months. Every phase ends with a project and a check you can verify.
Most SQL roadmaps give one path for everyone. In real work, an analyst and a backend developer need the same first three months and very different last three. So this roadmap splits after the core. Each phase has weekly hours, a deliverable, and a pass check. That also makes it easy to show to a manager if your company pays for your training.
The roadmap at a glance
Goal: Write correct, readable, fast SQL on real data, then add the related technologies for one target role and prove it with a portfolio. Duration: 6 months at 6 to 8 hours a week (about 3 months at 15 hours a week, up to 12 months at 3 hours a week)
Setup and Query Basics (Weeks 1 to 3)
Get a working database and read data from one table with confidence.
- Install PostgreSQL locally or in Docker, plus a client such as DBeaver, pgAdmin, or psql.
- Load a sample database such as Pagila, Chinook, or Northwind, and list its tables and columns.
- Write SELECT queries with aliases, WHERE, ORDER BY, and LIMIT, using IN, BETWEEN, LIKE, and IS NULL.
- Change values with string and date functions, CASE expressions, and COALESCE.
- Practice INSERT, UPDATE, and DELETE on a copy of a table, inside a transaction you can roll back.
Milestone: Answer 20 single-table questions about the sample database without looking up syntax.
Aggregation and Joins (Weeks 4 to 7)
Combine and summarize data across related tables without duplicating rows.
- Summarize with COUNT, SUM, AVG, MIN, MAX, and GROUP BY, then filter groups with HAVING.
- Write INNER, LEFT, and FULL OUTER joins and predict the row count before you run them.
- Use self joins and anti joins to find hierarchies and missing records.
- Draw the entity relationship diagram of the sample database before writing multi-table queries.
Milestone: A monthly revenue report by customer segment that joins at least four tables, with row counts checked after each join.
Intermediate Querying (Weeks 8 to 11)
Write complex analytical queries that a teammate can still read.
- Split long logic into named steps with common table expressions (WITH).
- Write correlated and uncorrelated subqueries, and learn when EXISTS is the clearer choice.
- Rank and compare rows with ROW_NUMBER, RANK, LAG, LEAD, and running totals.
- Combine result sets with UNION, UNION ALL, INTERSECT, and EXCEPT.
- Walk an org chart or category tree with a recursive CTE.
Milestone: A cohort retention query that groups users by signup month and tracks their activity over the following months.
Schema Design and Data Modeling (Weeks 12 to 15)
Design tables that keep data correct and easy to query.
- Create tables with the right data types, primary keys, and foreign keys.
- Normalize a messy spreadsheet to third normal form and write down why you made each split.
- Enforce rules with NOT NULL, UNIQUE, CHECK, and default values.
- Write upserts and multi-step changes inside transactions.
- Keep schema changes in migration files instead of editing tables by hand.
Milestone: A normalized schema for a booking or e-commerce system, loaded with test data and documented with a diagram.
Performance and Tuning (Weeks 16 to 19)
Understand how the database runs a query and make slow ones fast.
- Read execution plans with EXPLAIN and EXPLAIN ANALYZE.
- Add single and composite indexes, and find the cases where the planner ignores them.
- Rewrite queries that force full scans, such as functions wrapped around indexed columns.
- Learn isolation levels, locking basics, and how deadlocks happen.
- Use views and materialized views for reporting logic you repeat.
Milestone: Three slow queries on a large dataset made measurably faster, with the before and after plans saved.
Role Track and Related Technologies (Weeks 20 to 26)
Add the tools of your target role and publish proof that you can use them.
- Pick one track from the role section below and learn its tools in the order listed.
- Rebuild one earlier project inside that track's stack.
- Do timed practice on platforms like LeetCode Database or StrataScratch, two or three sessions a week.
- Publish two projects on GitHub with the schema, the queries, and a short written summary of what you found.
Milestone: Two finished projects in your track that someone else can clone, run, and understand in under ten minutes.
How to Adapt the SQL Roadmap to Your Role
The first five phases are the same for everyone, because joins, window functions, and schema design show up in every SQL job. The last phase is where your goal changes what you learn. Pick the track before you start. It decides which projects you build along the way and which tools you install early.
If you are not sure which track fits, look at the job posts you would apply to and count which tools come up most. That list beats any generic advice. Here are the four common tracks and the related technologies for each, in a sensible order:
- Data analyst: Excel or Google Sheets for quick checks, then one BI tool (Power BI or Tableau), then basic statistics, then Python with pandas for cleaning and charts.
- Data engineer: Python, then a cloud warehouse (BigQuery, Snowflake, or Redshift), then ELT and dbt for modeling and tests, then an orchestrator such as Airflow or Dagster, then Git and basic Docker.
- Backend developer: one application language (Python, JavaScript, Java, or Go), parameterized queries and an ORM, migrations, transactions under load, then caching and connection pooling.
- BI developer or database admin: for BI, semantic models, dashboards, and data refresh schedules. For DBA work, backups and restores, user roles and permissions, replication, monitoring, and upgrades.
Choosing a Database and Setting Up Your Environment
Use PostgreSQL as your main learning database. It follows the SQL standard closely, supports CTEs, window functions, and JSON, and costs nothing to run on your laptop. SQLite is a good choice if you want zero setup in week one, because it is a single file with no server. MySQL is also fine if your company already uses it.
Don't worry too much about this choice. MySQL, SQL Server, Snowflake, and BigQuery mostly differ in function names and a few syntax details, so your core skills carry over. Switch dialects only when your track or job asks for it, and keep one clean setup on hand:
- A local PostgreSQL server, or a Docker container you can reset in seconds.
- One graphical client and the psql command line, so you can work in both.
- A Git repository with one folder per phase for your queries and notes.
- Two sample databases: one small for drills and one large public dataset for performance work.
Understanding Logical Query Order
Many beginner bugs come from one fact: the database does not run a query in the order you write it. It handles FROM and joins first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, and finally LIMIT. That is why you cannot use a SELECT alias inside WHERE, and why filters on aggregates go in HAVING.
Get used to writing queries starting from FROM. First decide which tables you need and how they connect. Then decide which rows to keep and how to group them. Only at the end, choose which columns to return. This habit helps you avoid join and grouping mistakes, and it gets you ready to read execution plans in phase 5.
A Realistic Weekly Schedule With Checkpoints
The best pace is one you can keep. Most public SQL roadmaps give a range: a few months at an intensive pace, around half a year at a steady pace, and up to a year if you study a little at a time. The schedule above assumes 6 to 8 hours a week, which fits most people with a full-time job.
Split each week so you keep moving forward and also remember what you learned:
At the end of each phase, run a checkpoint before you move on. Take the milestone, give yourself a time limit, and solve it without notes. If you can't, stay one more week on that phase. Each phase builds on the one before, so a weak spot in joins will show up again in window functions.
- Two short sessions (45 to 60 minutes): a new concept plus 10 to 15 small exercises.
- One long session (2 to 3 hours): work on the phase project with real data.
- One review session (30 minutes): redo last week's exercises from memory and write down what you got wrong.
Portfolio Projects for Each Phase
Sample databases are great for drills, but real datasets teach you the messy part: duplicates, mixed date formats, missing values, and vague definitions. Download public data from government open data portals, Kaggle, or city transit feeds. Write your questions in plain English before you write any SQL.
Write up each project the same way: the schema, the questions, the queries, the checks you ran, and what you found. Clear metric definitions, row counts checked after joins, and honest notes about the limits of the data show more skill than clever syntax. One project per phase is enough:
- Phases 1 and 2: a city bike share analysis by station, hour, and day of the week.
- Phase 3: customer cohorts and retention from a raw orders file.
- Phase 4: a booking database for a clinic or library, with the queries an app would need.
- Phase 5: a profile of a large public dataset, with the slowest reports tuned using indexes.
- Phase 6: the same project rebuilt in your track's stack, such as a Power BI dashboard, a dbt project with tests, or a small API.
Turning Your SQL Plan Into Something You Can Present
If your manager or company sponsors your training, a list of topics won't convince anyone. Show the plan the way you would show a work project: phases with dates, a deliverable at the end of each one, the risks, and how you will measure success. This also helps when you study alone, because you can see where you are.
Keep it to one page per phase and include these items:
- Milestone and deliverable: the project link and the check from the roadmap above.
- Business link: one report or task at work the new skill can take over, such as a weekly export done by hand.
- Risks: busy months, no access to production data, or a dialect gap, each with a fallback plan.
- Success indicators: things anyone can check, like a query merged into a shared repo, a dashboard used by the team, or a timed checkpoint passed.
Is SQL Enough to Get a Job?
SQL is a core skill for analyst, data, and backend roles, but it is rarely the only one. It works together with the tools of the track you choose, which is why the last phase exists. A portfolio that shows SQL plus one related tool, with clear write-ups, says more than a long list of courses.
If you already have a data job and want to grow in it, a structured first quarter helps too. A 30, 60, and 90 day plan turns new SQL skills into visible results at work.
Practice Resources and Certifications
For the first phases, interactive sites give instant feedback: SQLBolt, Select Star SQL, and PostgreSQL Exercises cover the basics well. The PostgreSQL documentation has a tutorial that's worth reading from start to finish. For performance, Use The Index, Luke explains indexes clearly. Reading real dbt projects on GitHub shows how teams structure and test analytics queries.
Certifications are optional. They help most when a job post asks for them by name. Pick one that matches your track, after your projects are done:
- General SQL: Oracle Database SQL Certified Associate.
- Analyst and BI: Microsoft Power BI Data Analyst (PL-300).
- Data engineering: dbt Analytics Engineering certification or Snowflake SnowPro Core.
- Database admin: Microsoft Azure Database Administrator Associate (DP-300).
Common mistakes to avoid
- Learning syntax with no target role leaves you with skills nobody asked for, so pick a track before week one and choose projects that fit it.
- Using SELECT star everywhere hides what you actually need, so list your columns once you are past exploring.
- Joining tables without checking row counts quietly duplicates data, so count rows before and after every join.
- Treating NULL like a normal value breaks filters, so use IS NULL and COALESCE and remember that comparing to NULL returns unknown.
- Building queries by pasting user input into strings invites SQL injection, so always use parameterized queries in application code.
- Doing only puzzle problems leaves your design skills weak, so build at least one schema from scratch and tune at least one slow query.
Frequently asked questions
How long does it take to learn SQL?
The basics (SELECT, WHERE, GROUP BY, and joins) take a few weeks of regular practice. Getting comfortable with window functions, CTEs, schema design, and tuning takes about six months at 6 to 8 hours a week. Fifteen hours a week can cut that to about three months, and a lighter pace can stretch it to a year.
Should I learn SQL before Python?
For analyst and BI roles, yes: SQL gets you working on real data sooner, and Python adds cleaning and charts later. For data engineering, learn them side by side from phase 3 onward. For backend work, learn your application language and SQL together, since you will use them together every day.
What should I learn after SQL?
It depends on your track. Analysts add a BI tool such as Power BI or Tableau, then Python with pandas. Data engineers add a cloud warehouse, dbt, and an orchestrator. Backend developers add an ORM, migrations, and caching. DBAs add backups, permissions, replication, and monitoring.
Which database should I start with: PostgreSQL, MySQL, SQL Server, or SQLite?
PostgreSQL is the best default because it follows the standard closely and supports modern features. SQLite is the easiest if you want no server in week one. If your company runs MySQL or SQL Server, start there instead, since the core skills carry over and you can use what you learn at work right away.
How do I practice SQL every day?
Do 10 to 15 short exercises on an interactive site on weekdays and give one longer session a week to your phase project. Every Monday, redo last week's exercises from memory. Real datasets matter more than puzzle sites, because they make you deal with messy data and unclear questions.