Skip to main content

Learning path

SQL learning path: everything to study, topic by topic

Comprehensive SQL assessment covering SELECT queries, joins, aggregations, DDL, data modeling, window functions, stored procedures, query optimization, and partitioning.

Short answer

To learn SQL, work through 7 modules and 50 topics in the order they build on each other. It starts with SQL Basics, then moves on to Querying & Joins and DDL & Data Modeling. Use the map as a checklist, or measure your level and study only what is missing.

7
modules
50
topics
50
min to assess

What you will study

Topics are grouped by module. Open one to see its objectives and key concepts.

1 · SQL Basics7 topics
  • SELECT Statements

    Basic SELECT queries, column selection, aliases, DISTINCT, LIMIT/OFFSET

    Objectives

    • Write correct SELECT queries with column selection
    • Use aliases and DISTINCT appropriately

    Key concepts

    • SELECT
    • FROM
    • AS
    • DISTINCT
    • LIMIT
    • OFFSET
    • column aliases
    • table aliases
  • Filtering & Sorting

    WHERE clause, comparison operators, LIKE, IN, BETWEEN, IS NULL, ORDER BY, logical operators

    Objectives

    • Filter data with compound conditions
    • Handle NULL comparisons correctly

    Key concepts

    • WHERE
    • AND/OR
    • LIKE
    • IN
    • BETWEEN
    • IS NULL
    • ORDER BY
    • ASC/DESC
  • Data Types & Operators

    Numeric, string, date/time, boolean types, type casting, arithmetic and string operators

    Objectives

    • Choose appropriate data types for columns
    • Perform type casting and understand implicit coercion

    Key concepts

    • INTEGER
    • VARCHAR
    • DATE
    • TIMESTAMP
    • BOOLEAN
    • DECIMAL
    • CAST
    • type coercion
  • Built-in Functions

    String functions, date functions, numeric functions, COALESCE, NULLIF, CASE expressions

    Objectives

    • Apply string, date, and numeric functions in queries
    • Use CASE expressions for conditional logic

    Key concepts

    • UPPER/LOWER
    • SUBSTRING
    • TRIM
    • COALESCE
    • NULLIF
    • CASE WHEN
    • DATE_TRUNC
    • ROUND
  • SQL vs NoSQL

    Relational vs document databases, ACID vs BASE consistency models, schema vs schemaless, CAP theorem, use-case selection criteria

    Objectives

    • Compare relational and document database paradigms
    • Choose the appropriate database type for a given use case

    Key concepts

    • relational databases
    • document databases
    • ACID vs BASE
    • schema vs schemaless
    • CAP theorem
    • use-case selection
    • consistency models
    • scalability trade-offs
  • String Functions

    CONCAT, SUBSTRING, REPLACE, UPPER, LOWER, TRIM, LENGTH, CHARINDEX, LIKE patterns

    Objectives

    • Apply string manipulation functions in SQL queries
    • Handle NULL behavior in string functions

    Key concepts

    • CONCAT
    • SUBSTRING
    • REPLACE
    • UPPER/LOWER
    • TRIM
    • LENGTH
    • CHARINDEX
    • LIKE patterns
  • Numeric Functions

    ROUND, FLOOR, CEILING, ABS, MOD, POWER, CAST, integer division, floating-point precision

    Objectives

    • Apply numeric functions for calculations in SQL
    • Understand precision and rounding behavior

    Key concepts

    • ROUND
    • FLOOR
    • CEILING
    • ABS
    • MOD
    • POWER
    • CAST
    • precision
2 · Querying & Joins7 topics
  • Joins

    INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, self joins, join conditions

    Objectives

    • Choose and apply appropriate join types
    • Understand NULL behavior in outer joins

    Key concepts

    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL OUTER JOIN
    • CROSS JOIN
    • self join
    • ON clause
    • multi-column join
  • Aggregations

    COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, aggregate with DISTINCT

    Objectives

    • Write aggregate queries with grouping and filtering
    • Distinguish WHERE from HAVING

    Key concepts

    • COUNT
    • SUM
    • AVG
    • MIN/MAX
    • GROUP BY
    • HAVING
    • COUNT(DISTINCT)
    • aggregate vs scalar
  • Subqueries

    Scalar subqueries, correlated subqueries, EXISTS, IN with subqueries, derived tables

    Objectives

    • Write and optimize subqueries
    • Choose between correlated and non-correlated subqueries

    Key concepts

    • scalar subquery
    • correlated subquery
    • EXISTS
    • NOT EXISTS
    • IN subquery
    • derived table
    • subquery in SELECT
  • Set Operations

    UNION, UNION ALL, INTERSECT, EXCEPT, combining result sets with compatible schemas

    Objectives

    • Combine result sets using set operations
    • Understand UNION vs UNION ALL performance

    Key concepts

    • UNION
    • UNION ALL
    • INTERSECT
    • EXCEPT
    • column compatibility
    • duplicate elimination
    • ordering combined results
  • Common Table Expressions

    WITH clause, named CTEs, multiple CTEs, CTE vs subquery readability and performance

    Objectives

    • Refactor complex queries using CTEs
    • Understand CTE execution and materialization behavior

    Key concepts

    • WITH clause
    • named CTE
    • multiple CTEs
    • CTE vs subquery
    • CTE vs temp table
    • readability
    • CTE materialization
  • Date/Time Functions

    DATE, TIMESTAMP, DATEADD, DATEDIFF, EXTRACT, timezone handling, date formatting, rolling window calculations

    Objectives

    • Perform date arithmetic and extraction in SQL queries
    • Handle timezone-aware date comparisons correctly

    Key concepts

    • DATE
    • TIMESTAMP
    • DATEADD
    • DATEDIFF
    • EXTRACT
    • timezone handling
    • INTERVAL
    • date arithmetic
  • Conditional Functions

    CASE expressions, COALESCE, NULLIF, IIF, searched vs simple CASE, NULL handling strategies

    Objectives

    • Write CASE expressions for complex conditional logic
    • Use COALESCE and NULLIF for robust NULL handling

    Key concepts

    • CASE expression
    • searched CASE
    • simple CASE
    • COALESCE
    • NULLIF
    • IIF
    • NULL handling
    • conditional aggregation
3 · DDL & Data Modeling6 topics
  • Table DDL

    CREATE TABLE, ALTER TABLE, DROP TABLE, column definitions, DEFAULT values, temporary tables

    Objectives

    • Create and modify table structures
    • Write safe DDL with IF NOT EXISTS

    Key concepts

    • CREATE TABLE
    • ALTER TABLE
    • DROP TABLE
    • ADD COLUMN
    • ALTER COLUMN
    • DEFAULT
    • TEMPORARY TABLE
    • IF NOT EXISTS
  • Constraints & Keys

    PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, referential integrity, CASCADE

    Objectives

    • Design tables with appropriate constraints
    • Understand referential integrity and cascade behavior

    Key concepts

    • PRIMARY KEY
    • FOREIGN KEY
    • UNIQUE
    • NOT NULL
    • CHECK
    • ON DELETE CASCADE
    • composite key
    • referential integrity
  • Normalization & Design

    1NF through BCNF, functional dependencies, denormalization trade-offs, ER modeling

    Objectives

    • Normalize schemas to 3NF
    • Make informed denormalization decisions

    Key concepts

    • 1NF
    • 2NF
    • 3NF
    • BCNF
    • functional dependency
    • transitive dependency
    • denormalization
    • update anomaly
  • Indexes

    B-tree indexes, composite indexes, unique indexes, partial indexes, index scan vs seq scan

    Objectives

    • Design effective indexing strategies
    • Understand index trade-offs for reads vs writes

    Key concepts

    • CREATE INDEX
    • B-tree
    • composite index
    • unique index
    • partial index
    • covering index
    • index selectivity
    • write overhead
  • Views & Materialized Views

    CREATE VIEW, updatable views, materialized views, view refresh strategies

    Objectives

    • Create views for abstraction and security
    • Choose between regular and materialized views

    Key concepts

    • CREATE VIEW
    • updatable view
    • materialized view
    • REFRESH MATERIALIZED VIEW
    • view security
    • view performance
    • WITH CHECK OPTION
  • Temporary Tables

    Creating and managing temporary tables, session and transaction scope, comparison with CTEs and table variables, indexing strategies, cleanup behavior

    Objectives

    • Create and manage temporary tables with appropriate scope and cleanup
    • Choose between temp tables, CTEs, and table variables based on use case

    Key concepts

    • CREATE TEMP TABLE
    • session scope
    • transaction scope
    • temp table vs CTE
    • temp table vs table variable
    • indexing temp tables
    • temp table performance
    • cleanup strategies
4 · Advanced SQL12 topics
  • Window Functions

    ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, NTILE, running totals, partitioned aggregates

    Objectives

    • Write complex analytical queries using window functions
    • Understand window frame specifications

    Key concepts

    • ROW_NUMBER
    • RANK/DENSE_RANK
    • LEAD
    • LAG
    • NTILE
    • PARTITION BY
    • window frame
    • ROWS BETWEEN
  • Recursive Queries

    Recursive CTEs, tree traversal, hierarchical data, cycle detection, path enumeration

    Objectives

    • Write recursive CTEs for hierarchical data
    • Implement cycle detection in recursive queries

    Key concepts

    • WITH RECURSIVE
    • anchor member
    • recursive member
    • tree traversal
    • cycle detection
    • path enumeration
    • depth limiting
  • PIVOT & Conditional Aggregation

    PIVOT/UNPIVOT, CASE-based pivoting, FILTER clause, crosstab queries

    Objectives

    • Transform row data into columnar reports
    • Write portable cross-database pivot queries

    Key concepts

    • PIVOT
    • UNPIVOT
    • CASE aggregation
    • FILTER clause
    • crosstab
    • dynamic pivot
    • row-to-column transformation
  • Advanced Join Patterns

    Anti-joins, semi-joins, lateral joins, range joins, inequality joins

    Objectives

    • Apply advanced join patterns for complex data relationships
    • Choose between anti-join implementations

    Key concepts

    • anti-join
    • semi-join
    • LATERAL JOIN
    • range join
    • inequality join
    • NOT EXISTS pattern
    • CROSS APPLY
  • Transactions & Concurrency

    BEGIN/COMMIT/ROLLBACK, ACID properties, isolation levels, deadlocks, locking strategies

    Objectives

    • Design correct transaction boundaries
    • Choose appropriate isolation levels

    Key concepts

    • BEGIN
    • COMMIT
    • ROLLBACK
    • SAVEPOINT
    • ACID
    • isolation levels
    • deadlock
    • row locking
  • ACID Properties Deep-Dive

    Atomicity, Consistency, Isolation, Durability in depth, isolation levels deep-dive, MVCC, write-ahead log, phantom reads, serialization anomalies

    Objectives

    • Explain ACID properties with practical database internals knowledge
    • Analyze isolation level trade-offs in concurrent workloads

    Key concepts

    • atomicity
    • consistency
    • isolation
    • durability
    • MVCC
    • write-ahead log
    • phantom reads
    • serialization anomalies
  • MERGE & UPSERT

    MERGE statement, WHEN MATCHED/NOT MATCHED clauses, upsert patterns across databases, ON CONFLICT (PostgreSQL), INSERT ON DUPLICATE KEY (MySQL), conditional updates

    Objectives

    • Write MERGE statements and upsert patterns across major SQL databases
    • Choose the appropriate upsert strategy based on database platform and use case

    Key concepts

    • MERGE syntax
    • WHEN MATCHED
    • WHEN NOT MATCHED
    • upsert patterns
    • ON CONFLICT (PostgreSQL)
    • INSERT ON DUPLICATE KEY (MySQL)
    • conditional updates
    • merge performance
  • Advanced Window Functions & Analytics

    Mastering complex window function patterns: running totals, gaps and islands, sessionization, and percentile calculations

    Objectives

    • Solve gaps-and-islands and sessionization problems using window functions
    • Apply frame specifications (ROWS vs RANGE) correctly for time-series calculations

    Key concepts

    • window frames
    • ROWS vs RANGE
    • LAG/LEAD
    • gaps and islands
    • sessionization
    • PERCENTILE_CONT
    • LISTAGG/STRING_AGG
    • recursive patterns
  • Recursive CTEs & Graph Queries

    Using recursive CTEs for hierarchical and graph data traversal, cycle detection, and path finding in SQL databases

    Objectives

    • Write recursive CTEs for hierarchical traversal with cycle detection
    • Compare adjacency list, nested sets, and closure table approaches for tree data

    Key concepts

    • recursive CTE
    • anchor member
    • recursive member
    • MAXRECURSION
    • cycle detection
    • shortest path
    • hierarchical data
    • adjacency list vs nested sets
  • Temporal Data in SQL

    Slowly changing dimensions (SCD), temporal tables, bi-temporal modeling, AS OF queries, valid-time and transaction-time tracking

    Objectives

    • Implement SCD patterns and temporal tables for historical data tracking
    • Design bi-temporal models distinguishing valid time from transaction time

    Key concepts

    • SCD Type 1/2/3
    • temporal tables
    • bi-temporal modeling
    • AS OF queries
    • valid time
    • transaction time
    • effective dating
    • temporal joins
  • Graph Queries in SQL

    Recursive CTEs for graph traversal, hierarchical data modeling, path queries, connectivity analysis, shortest path, and transitive closure

    Objectives

    • Implement graph algorithms using recursive CTEs for path finding and connectivity
    • Choose appropriate graph representation for different query patterns

    Key concepts

    • graph traversal
    • shortest path in SQL
    • transitive closure
    • connectivity analysis
    • adjacency list
    • closure table
    • BFS in SQL
    • topological sort
  • Modern SQL Features

    MATCH_RECOGNIZE for pattern matching, JSON functions, PIVOT/UNPIVOT, QUALIFY clause, LATERAL joins, GROUPING SETS, and SQL:2023 features

    Objectives

    • Apply modern SQL features for pattern matching, JSON processing, and advanced grouping
    • Write portable SQL leveraging SQL:2016/2023 standard features

    Key concepts

    • MATCH_RECOGNIZE
    • JSON_TABLE
    • JSON_QUERY
    • QUALIFY
    • LATERAL JOIN
    • GROUPING SETS
    • CUBE/ROLLUP
    • FETCH FIRST WITH TIES
5 · SQL Programming5 topics
  • Stored Procedures

    CREATE PROCEDURE, parameters, control flow, error handling, cursors

    Objectives

    • Write stored procedures with proper error handling
    • Decide when stored procedures are appropriate

    Key concepts

    • CREATE PROCEDURE
    • IN/OUT/INOUT parameters
    • IF/ELSE
    • WHILE loop
    • DECLARE
    • cursor
    • exception handling
    • RAISE
  • User-Defined Functions

    Scalar functions, table-valued functions, deterministic vs non-deterministic, IMMUTABLE/STABLE

    Objectives

    • Create reusable functions with correct volatility
    • Choose between scalar and table-valued functions

    Key concepts

    • CREATE FUNCTION
    • RETURNS
    • scalar function
    • table-valued function
    • IMMUTABLE
    • STABLE
    • VOLATILE
    • LANGUAGE plpgsql
  • Triggers & Events

    BEFORE/AFTER triggers, row-level vs statement-level, NEW/OLD references, audit trails

    Objectives

    • Implement triggers for audit and data integrity
    • Understand trigger execution order and performance impact

    Key concepts

    • CREATE TRIGGER
    • BEFORE/AFTER
    • INSERT/UPDATE/DELETE
    • row-level
    • statement-level
    • NEW/OLD
    • audit trail
    • trigger ordering
  • Dynamic SQL

    EXECUTE, PREPARE, parameterized queries, SQL injection prevention, dynamic DDL

    Objectives

    • Write safe dynamic SQL with parameterization
    • Prevent SQL injection in dynamic queries

    Key concepts

    • EXECUTE
    • PREPARE
    • EXECUTE IMMEDIATE
    • parameterized query
    • SQL injection
    • quote_ident
    • format()
    • dynamic DDL
  • Cursors

    Declaring and using cursors, fetch operations, cursor types, performance implications, set-based alternatives, ref cursors for result set passing

    Objectives

    • Implement cursor-based processing when set-based operations are insufficient
    • Evaluate when cursors are appropriate versus set-based alternatives

    Key concepts

    • DECLARE CURSOR
    • FETCH operations
    • cursor types (forward-only, scrollable)
    • cursor vs set-based operations
    • performance implications
    • implicit cursors
    • cursor alternatives
    • ref cursors
6 · Performance & Optimization8 topics
  • Query Optimization

    EXPLAIN plans, index strategies, query rewriting, statistics, cost-based optimization

    Objectives

    • Analyze and interpret query execution plans
    • Apply optimization strategies to slow queries

    Key concepts

    • EXPLAIN ANALYZE
    • sequential scan
    • index scan
    • nested loop
    • hash join
    • merge join
    • query planner
    • statistics
  • Partitioning & Sharding

    Table partitioning (range, list, hash), partition pruning, sharding strategies, data lifecycle

    Objectives

    • Design partitioning strategies for large tables
    • Choose between partitioning and sharding approaches

    Key concepts

    • range partitioning
    • list partitioning
    • hash partitioning
    • partition pruning
    • partition maintenance
    • sharding
    • shard key selection
    • data lifecycle
  • Data Security

    GRANT, REVOKE, row-level security, column-level security, encryption at rest, auditing, SQL injection prevention

    Objectives

    • Design database security policies for multi-tenant applications
    • Implement defense-in-depth strategies for sensitive data

    Key concepts

    • GRANT
    • REVOKE
    • row-level security
    • column-level security
    • encryption
    • auditing
    • SQL injection prevention
    • privilege cascading
  • Query Plan Optimization & Internals

    Reading and optimizing query execution plans: index selection, join algorithms, cardinality estimation, and statistics management

    Objectives

    • Read and interpret query execution plans to identify bottlenecks
    • Understand join algorithm selection and cardinality estimation errors

    Key concepts

    • EXPLAIN ANALYZE
    • nested loop vs hash join
    • cardinality estimation
    • index seeks vs scans
    • statistics histograms
    • predicate pushdown
    • parallel query
  • SQL-Based Statistical Analysis

    Implementing statistical analysis directly in SQL: moving averages, standard deviation, z-scores, and regression calculations

    Objectives

    • Implement z-scores, moving averages, and regression calculations in pure SQL
    • Use aggregate functions CORR, REGR_SLOPE, and STDDEV for statistical analysis

    Key concepts

    • STDDEV/VAR
    • moving averages
    • z-score calculation
    • linear regression in SQL
    • CORR function
    • time series in SQL
    • pivot/unpivot
  • Database Partitioning Strategies

    Designing and implementing table partitioning for large-scale databases: range, list, hash partitioning, and partition pruning

    Objectives

    • Design a partitioning strategy for a multi-billion-row table
    • Understand partition pruning and how query predicates affect partition elimination

    Key concepts

    • range partitioning
    • list partitioning
    • hash partitioning
    • partition pruning
    • partition key selection
    • partition elimination
    • partition maintenance
  • Advanced SQL Debugging

    Execution plan analysis, identifying bottlenecks, index advisor interpretation, wait statistics, lock contention diagnosis, and query regression analysis

    Objectives

    • Diagnose query performance regressions using execution plans and wait statistics
    • Interpret index advisor recommendations and understand parameter sniffing

    Key concepts

    • execution plan analysis
    • bottleneck identification
    • index advisor
    • wait statistics
    • lock contention
    • query regression
    • plan cache analysis
    • parameter sniffing
  • SQL Performance at Scale

    Partitioning strategies for billions of rows, materialized views, query caching, parallel execution, and workload management for concurrent queries

    Objectives

    • Design SQL workload management for high-concurrency production systems
    • Implement caching and materialization strategies for query performance at scale

    Key concepts

    • parallel query execution
    • query caching strategies
    • materialized view refresh
    • workload management
    • resource governors
    • connection pooling
    • batch processing
    • query prioritization
7 · Engineering & Specialized SQL5 topics
  • SQL for Data Engineering

    ELT patterns in SQL, incremental loads, merge/upsert strategies, staging tables, idempotent pipelines, and data pipeline orchestration using SQL

    Objectives

    • Design idempotent SQL-based data pipelines with incremental load patterns
    • Implement staging and merge strategies for reliable data ingestion

    Key concepts

    • ELT patterns
    • incremental loads
    • staging tables
    • idempotent pipelines
    • CDC patterns
    • data lake SQL
    • batch vs micro-batch
    • pipeline orchestration
  • SQL Security

    SQL injection prevention, role-based access control, row-level security policies, dynamic data masking, column encryption, and privilege management

    Objectives

    • Implement defense-in-depth SQL security with RLS, masking, and parameterization
    • Design role hierarchies and access policies for multi-tenant databases

    Key concepts

    • SQL injection prevention
    • parameterized queries
    • role-based access control
    • row-level security
    • dynamic data masking
    • column encryption
    • privilege escalation prevention
    • audit logging
  • SQL in Cloud Warehouses

    BigQuery, Snowflake, and Redshift SQL dialects, cloud-specific optimizations, clustering, distribution keys, and serverless query patterns

    Objectives

    • Write optimized SQL for cloud data warehouses considering cost and performance
    • Choose appropriate distribution and clustering strategies per platform

    Key concepts

    • BigQuery SQL dialect
    • Snowflake SQL features
    • Redshift distribution keys
    • clustering keys
    • serverless queries
    • external tables
    • semi-structured data
    • cost optimization
  • SQL for Analytics Engineering

    dbt-style SQL, CTEs as building blocks, modular SQL design, ref() patterns, staging/intermediate/mart layer organization, and SQL-based testing

    Objectives

    • Design modular SQL transformations following analytics engineering best practices
    • Organize SQL models into staging, intermediate, and mart layers

    Key concepts

    • modular SQL design
    • staging layer
    • intermediate models
    • mart layer
    • CTE composition
    • SQL style guides
    • model dependencies
    • incremental models
  • SQL Testing & Validation

    Data quality checks in SQL, assertion queries, reconciliation patterns, schema tests, freshness checks, and regression testing for transformations

    Objectives

    • Write SQL-based data quality assertions and reconciliation queries
    • Design automated testing strategies for SQL transformations

    Key concepts

    • data quality assertions
    • reconciliation queries
    • schema tests
    • freshness checks
    • not-null tests
    • uniqueness validation
    • referential integrity checks
    • row count monitoring

Levels: Junior, Mid, Senior, and Expert

Explain it in your own words

Graded by AI · no account · 1 question

No options to guess from: you write, the AI grades against the topic's rubric and tells you what level your answer reads at.

0 / 600

Already studying SQL?

The assessment measures topic by topic, and your plan skips what you already know.

Measure my level

Open to read. An account is only needed to be measured.

Frequently asked questions

Where should I start studying SQL?

Start with SQL Basics: SELECT Statements, Filtering & Sorting, and Data Types & Operators. The map orders topics by what they depend on, so each one prepares the next.

How long does a full SQL assessment take?

A full assessment of this path takes about 50 minutes. Study time depends on where you start; a diagnostic shows which topics you can skip.

Do I need an account to use this page?

No. The map, objectives and key concepts are open. You only need an account to be measured, get your gap by topic and receive a plan.