All roadmaps

SQL developer

SQL developer path

Go from your first SELECT to joins, CTEs, window functions and fast queries, with practice at every stage.

  • about 12 weeks at an hour a day
  • 6 stages, 39 steps
Start step 1See the roadmap

How databases work

about 1 week · 4 steps

After this stage: You can describe tables, keys and relationships.

  1. What is a database?LessonFreeThe ideas behind the tables.01
  2. Primary keys and foreign keysLessonFreeHow tables relate.02
  3. Relational basicsLessonLockedThe rules of relational data.03
  4. Tables, rows and columnsLessonFreeThe vocabulary every query uses.04

Query like a developer

about 3 weeks · 13 steps

After this stage: You can answer most reporting questions with one clean query.

  1. NULL handlingLessonLockedThree-valued logic and its traps.05
  2. Aggregations and groupingLessonLockedGROUP BY and HAVING.06
  3. Multi-table joinsLessonLockedINNER, LEFT and what each keeps.07
  4. Subqueries and CTEsLessonLockedBreak a hard query into steps.08
  5. CASE expressionsLessonLockedif and else inside a query.09
  6. Window functionsLessonLockedRank, running totals and LAG.10
  7. SQL 50 practice sheetPractice50 ordered problems.11
  8. Set operationsLessonLockedUNION, INTERSECT and EXCEPT.12
  9. Recursive CTEsLessonLockedWalk org charts and category trees.13
  10. Querying JSON columnsLessonLockedSemi-structured data inside SQL.14
  11. Pivoting and unpivotingLessonLockedReshape results for reports.15
  12. Advanced aggregationsLessonLockedGrouping sets, rollup and cube.16
  13. Advanced analytics patternsLessonLockedFunnels, retention and gaps.17

Change data safely

about 2 weeks · 5 steps

After this stage: You can insert, update, delete and clean data without losing rows.

  1. SQL that changes dataLessonLockedINSERT, UPDATE, DELETE and CREATE.18
  2. Data cleaning and string/date workLessonLockedFix text and dates inside the query.19
  3. Deduplication and upsertsLessonLockedRemove repeats and load only what changed.20
  4. Case study: update old rows, add new rowsCase studyAn upsert on real-looking data.21
  5. Case study: the duplicate click problemCase studyFind repeats before they inflate a number.22

Fast and correct

about 2 weeks · 8 steps

After this stage: You can read a query plan and explain why a query is slow.

  1. Indexing and optimizationLessonLockedWhy an index helps and what it costs.23
  2. Query plans: hash, merge, nested loopLessonLockedRead what the engine decided.24
  3. Transactions, ACID and isolationLessonLockedWhat happens when two writers meet.25
  4. Case study: who earns more than their manager?Case studyA self join in a small company.26
  5. SQL projectsProjectCohort, window and tuning capstones.27
  6. Partitioning and clusteringLessonLockedHow big tables stay quick to scan.28
  7. Views vs materialized viewsLessonLockedReuse logic without repeating it.29
  8. PK, FK and CHECK constraintsLessonLockedLet the database reject bad rows.30

Design the schema

about 3 weeks · 6 steps

After this stage: You can design tables, keys and a reporting model and defend the trade-offs.

  1. Normalization vs denormalizationLessonLockedWhen to split tables and when to flatten them.31
  2. OLTP vs OLAP mental modelLessonLockedTwo workloads, two designs.32
  3. Star schema: facts, dimensions, grainLessonLockedThe standard reporting shape.33
  4. Surrogate keys and conformed dimensionsLessonLockedKeys that survive change.34
  5. Slowly changing dimensionsLessonLockedKeep history when a value changes.35
  6. Capstone: model a star from ordersLessonLockedDesign one end to end.36

Interview round

about 1 week · 3 steps

After this stage: You can explain your queries and answer the usual questions.

  1. SQL questions for freshersInterviewInterview answers with examples.37
  2. DBMS questions for freshersInterviewKeys, normalization and transactions.38
  3. SQL certificate examToolA timed exam that gives you a link to share.39

After this roadmap