PyCodeItPython trace & SQL practice
Interactive SQL Playground Track

Master SQL Queries & Query Optimization

Write and evaluate live SQL queries directly in your browser. Practice relational JOINs, aggregations, subqueries, CTEs, and window functions against pre-populated HR, E-Commerce, and Rideshare schemas.

In-Browser WASM EngineZero installation needed - execute SQL against real in-memory SQLite tables instantly.
Production SQL SkillsPractice the kinds of aggregation, join, and window queries that appear in real-world data pipelines.
Detailed Query GuidesLearn query execution ordering, B-Tree index scan optimization, and NULL handling.

How to use the SQL playground

Choose a topic or schema, read the table names and columns, and write a query in the workspace. Start with a small SELECT, inspect the rows it returns, then add one JOIN, filter, or aggregate at a time. For example, a LEFT JOIN keeps every row from the left table even when the right side has no match; checking the result after each step makes that row-preserving behavior visible.

The playground runs SQLite compiled to WebAssembly in your browser. Queries use the selected in-memory dataset and are not sent to a database server by this page. It is useful for learning query shape and result sets, but it does not reproduce every behavior or optimizer detail of PostgreSQL, MySQL, or a warehouse engine.

The key lesson is to separate row selection from grouping and window calculations. Practise with JOINs and window functions, then compare the result with the related guides in the learning library.

How to use this tool

Select a schema, inspect its tables, and build a query in small steps. Run the query only after predicting which rows and columns should appear.

Worked example

SELECT department, COUNT(*) AS people
FROM employees
GROUP BY department
ORDER BY department;

Static result: A result table with one row per department and its employee count, ordered alphabetically by department.

Controls

The schema selector loads an in-memory dataset; the editor accepts SQL; Run executes it; the result grid shows rows; Reset restores the sample database.

Honest limits

SQLite runs locally through WebAssembly, so queries are not sent to a database server. SQLite syntax and query plans can differ from PostgreSQL, MySQL, or a warehouse.

Continue with SQL learning guides.

Explore SQL Practice Topics

Scenario-Focused SQL Analytics Playbooks

Database Reasoning & Execution Guide

How to Master SQL Query Reasoning on PyCodeIt

The PyCodeIt SQL Playground delivers a zero-installation, browser-native relational database environment powered by WebAssembly SQLite. Rather than forcing you to memorize theoretical SQL syntax or install heavyweight local servers, our curriculum emphasizes query execution reasoning: understanding how relational database engines parse clauses, build hash tables, navigate B-Tree indexes, and partition streaming analytical windows.

Across our 12 structured modules (spanning 9 foundational tracks and 3 real-world scenario playbooks), you write queries against realistic relational schemas including E-Commerce Orders, HR Employee Directories, Rideshare Logistics, and Healthcare Auditing databases.

The Logical Query Processing Pipeline

The key to writing high-performance SQL is understanding that SQL is declarative, not procedural. The physical execution order differs drastically from written syntax:

  1. FROM & JOIN: The engine identifies tables, calculates Cartesian products, and applies `ON` filter conditions.
  2. WHERE: Filters individual rows BEFORE grouping. Non-matching records are discarded early to conserve buffer memory.
  3. GROUP BY: Groups remaining rows into partition buckets based on unique key combinations.
  4. HAVING: Evaluates aggregate conditions against grouped buckets, discarding buckets that fail threshold criteria.
  5. SELECT & WINDOW: Computes column projections, scalar expressions, and analytical window functions (`ROW_NUMBER`, `DENSE_RANK`).
  6. DISTINCT: Deduplicates identical output tuples.
  7. ORDER BY: Sorts the final result set using indexes or temporary in-memory B-Trees.
  8. LIMIT & OFFSET: Restricts the number of returned rows.

Worked Practice Example: Multi-Table JOIN with Conditional Aggregation

Consider an analytical reporting prompt: Find all departments with more than two employees whose total departmental payroll exceeds $150,000, along with their highest individual earner:

SELECT 
    d.department_name,
    COUNT(e.id) AS active_headcount,
    SUM(e.salary) AS total_payroll,
    MAX(e.salary) AS peak_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
WHERE e.status = 'active'
GROUP BY d.id, d.department_name
HAVING COUNT(e.id) > 2 AND SUM(e.salary) > 150000
ORDER BY total_payroll DESC;

Execution Trace:The engine first performs an inner join between `departments` and `employees` on `department_id`. The `WHERE` clause eliminates inactive staff before any grouping occurs. `GROUP BY` collapses records into department buckets. The `HAVING` clause discards small or low-budget departments. Finally, the remaining summary rows are sorted descending by total payroll.

Common Pitfalls in Relational SQL Queries

  • Non-SARGable WHERE Predicates: Wrapping indexed columns in functions (e.g. `WHERE strftime('%Y', order_date) = '2026'`) invalidates B-Tree indexes and forces costly full table scans.
  • Filtering Preserved Rows in Outer Joins: Placing right-table filter conditions in the `WHERE` clause instead of the `ON` clause silently converts a `LEFT JOIN` into an `INNER JOIN`.
  • The NOT IN with NULL Subquery Trap: If a subquery returns even a single NULL value, `NOT IN` evaluates to UNKNOWN for all rows, returning an empty result set. Always prefer `NOT EXISTS`.
  • Confusing COUNT(*) with COUNT(column): `COUNT(*)` counts all physical rows matching the query, while `COUNT(column)` ignores NULL values.

Who This SQL Hub Is Designed For

This interactive SQL curriculum is tailored for data engineers, backend developers, analytics engineers, data analysts, and computer science students building rigorous relational database query skills and production schema design.