sqlite3

Explains SQL's GROUP_CONCAT function using a posts-and-tags example, showing how to concatenate grouped row values into a single comma-separated string.

Explains how NULL behaves in SQL calculations and how to use the COALESCE function to supply default values, using a products table example.

Explains when to use SQL's EXISTS versus IN using a customers and orders example, covering differences in NULL handling and query performance.

Explains the difference between SQL's UNION and UNION ALL by combining two class roster tables, showing how duplicate removal affects results and performance.

Explains the difference between SQL's WHERE and HAVING clauses using an order aggregation example, clarifying when to filter before versus after aggregation.

Explains SQL self joins using an employee-manager hierarchy example, showing how to join a table with itself using aliases to look up related rows.

Explains how to use SQL's CASE expression to assign grades based on scores and combine it with aggregate functions to build conditional cross-tabulation queries.

Explains SQL Common Table Expressions (CTEs) by comparing a subquery-based query with a CTE-based rewrite to show improved readability and reusability.

Explains the differences between SQL window functions RANK, DENSE_RANK, and ROW_NUMBER for ranking data with tied scores, using a sample student scores table.

Explains how to set up a Python environment for running SQL using SQLite and Polars, then covers creating tables and basic SELECT, WHERE, and ORDER BY queries.