PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

Database & SQL Fundamentals

Master relational database concepts, SQL command pillars (DQL, DDL, DML, DCL), data types, and foundational SQL syntax.

Relational Theory & Optimization Architecture

SQL Fundamentals establish the foundational declarative grammar of relational databases based on Edgar F. Codd's relational calculus. Unlike imperative languages where a developer scripts iterative loops and explicit memory buffers, SQL requires you to declare the mathematical characteristics of the final relation you desire. The database engine's query optimizer is tasked with compiling that declaration into an efficient execution tree of physical operators—such as sequential table scans, index seeks, and projections.

Physical Storage & Buffer Pool Mechanics

At the physical layer, relational engines organize data into fixed-size storage pages (typically 8KB or 16KB). When evaluating a fundamental SELECT ... FROM statement with a WHERE filter, the storage engine reads pages from disk into the database buffer pool cache. The execution engine then iterates through tuples, evaluating the boolean predicates defined in your filter against the tuple attributes before copying the matching columns into temporary projection slots.

Core Projection and Predicate Lifecycle

The physical execution lifecycle of a fundamental query proceeds in strict logical stages: First, FROM identifies and locks the target relation. Second, WHERE evaluates boolean predicates row-by-row, eliminating tuples that evaluate to FALSE or UNKNOWN. Third, SELECT projects the specified column expressions and evaluates any scalar computations. Finally, the resulting stream of tuples is formatted into wire protocol buffers for client consumption. Because SELECT executes after WHERE, column aliases defined in SELECT are not yet bound during WHERE evaluation.

Worked Query Walkthrough: Filtering Active Engineering Salaries

SELECT employee_id, first_name, salary * 1.1 AS projected_salary
FROM employees
WHERE department = 'Engineering' AND is_active = 1;

Step-by-Step Row Evaluation Matrix

employee_idfirst_namesalarydepartmentis_activeWHERE Resultprojected_salary
101Marcus95000Engineering1PASS (TRUE)104500.0
102Elena110000Marketing1FAIL (Dept)[Discarded]
103Priya125000Engineering0FAIL (Active)[Discarded]
104David105000Engineering1PASS (TRUE)115500.0

Runtime Trace Analysis

The query processor scans the employees relation. Row 101 evaluates department = 'Engineering' (TRUE) and is_active = 1 (TRUE); both conjunctions hold, so the projection computes 95000 * 1.1 = 104500.0. Row 102 fails the department predicate and is dropped before projection. Row 103 fails the active check. Row 104 satisfies all predicates and projects 115500.0. Only surviving rows 101 and 104 enter the network buffer.

Common Traps, Anti-Patterns & Debugging Solutions

1. Attempting Equality Comparison with NULL

Problematic Syntax (Anti-Pattern)SELECT * FROM employees WHERE manager_id = NULL;
Optimized AlternativeSELECT * FROM employees WHERE manager_id IS NULL;

In ANSI SQL, NULL represents an unknown or missing quantity. Under three-valued logic (3VL), comparing any value to NULL using '=' yields UNKNOWN rather than TRUE. Predicates that evaluate to UNKNOWN are discarded by the WHERE clause.

2. Referencing SELECT Aliases inside WHERE Predicates

Problematic Syntax (Anti-Pattern)SELECT salary * 12 AS annual_comp FROM employees WHERE annual_comp > 100000;
Optimized AlternativeSELECT salary * 12 AS annual_comp FROM employees WHERE (salary * 12) > 100000;

The WHERE clause evaluates prior to the SELECT clause in the logical query processing order. When WHERE is evaluated, the alias 'annual_comp' does not exist in the engine's symbol table.

3. Unbounded SELECT * in Production Services

Problematic Syntax (Anti-Pattern)SELECT * FROM orders WHERE status = 'PENDING';
Optimized AlternativeSELECT order_id, customer_id, total_amount FROM orders WHERE status = 'PENDING';

SELECT * defeats covering indexes, inflates network payload serialization, and creates fragile application dependencies when schema migrations alter underlying column positions.

Production Strategy & Query Engineering Best Practices

When explaining SQL foundations, highlight three-valued logic (TRUE, FALSE, UNKNOWN) and logical operator evaluation order. Always demonstrate awareness that NULL requires IS NULL and state clearly that WHERE clauses execute before column projections, preventing alias reuse.

Interactive WebAssembly SQL Playground

Client-Side SQLite Engine
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
CustomerCustomerID, CustomerName, ContactName, Country6 rowsE-commerce customer directory
OrdersOrderID, CustomerID, OrderDate, TotalAmount, Status7 rowsCustomer order transaction history
ProductProductID, ProductName, CategoryID, Price, Stock7 rowsProduct catalog inventory
Interactive Query Workspace

Run custom SQL queries against the Customer, Orders, Product schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.

Frequently Asked Questions on Database & SQL Fundamentals

Why does SELECT column = NULL return nothing or UNKNOWN in SQL?

In ANSI SQL, NULL represents an unknown or missing value rather than a literal zero or empty string. Standard equality operators (= or !=) evaluate to UNKNOWN when compared against NULL. Always use IS NULL or IS NOT NULL for accurate checks.

Why can I not use column aliases defined in SELECT inside the WHERE clause?

The WHERE clause is evaluated before the SELECT clause in SQL execution order. Because column aliases are created during the SELECT phase, the database engine does not yet recognize them when evaluating WHERE conditions.

What is the difference between CHAR, VARCHAR, and TEXT data types?

CHAR is fixed-length, padding unused space with blank spaces. VARCHAR is variable-length up to a specified character limit. TEXT stores variable-length strings without requiring a predefined length boundary.

How does boolean short-circuiting operate in SQL WHERE clauses?

Unlike languages like Python or C++, SQL is declarative; the query optimizer is free to evaluate predicates in whatever order minimizes estimated cost, meaning short-circuiting cannot be relied upon to prevent runtime errors like division by zero.

Explore Other SQL Practice Tracks