Tag: SQL

1. Constraints Explained: CHECK, UNIQUE, NOT NULL

   Aug 22, 2025

   Constraints enforce data integrity where it can be enforced most reliably: directly in the database. This article shows CHECK, UNIQUE and NOT NULL with real syntax, explains named constraints, migrations on existing data, and why database-level rules stay essential even with clean validation in the application code.

   Read more

2. Data Type Choices With Long-Term Consequences

   Aug 13, 2025

   The data type of a column can only be changed with considerable effort once a table has grown, which is why the initial decision deserves particular care. This article covers INT versus BIGINT for primary keys, realistic VARCHAR lengths, DECIMAL instead of FLOAT for monetary amounts, and the most common time zone pitfalls with timestamps, with concrete SQL for every decision.

   Read more

3. Calculating Moving Averages Directly in SQL

   Aug 12, 2025

   A moving average smooths noisy time series directly in the database: with AVG() OVER() and a precisely defined ROWS BETWEEN window, you calculate rolling averages for trend analysis, without loading raw data into an application or resorting to slow self-joins.

   Read more

4. Schema Migrations: Versioning and Rollback Strategies

   Aug 11, 2025

   A schema migration without clear versioning and a rollback plan quickly turns into a risk on a large live table. This article shows naming conventions for migration files, the difference between forward-only and reversible migrations, the expand-contract pattern, and safe approaches for NOT NULL columns on tables with millions of rows, with concrete SQL for every step.

   Read more

5. Using Savepoints for Granular Rollbacks

   Aug 10, 2025

   A savepoint marks an intermediate point inside a running transaction that you can later roll back to, without giving up the entire transaction. Especially in batch processing, this tool lets you discard individual failed items while keeping the work already completed successfully.

   Read more

6. Learning to Read Execution Plans Across Databases

   Aug 8, 2025

   An execution plan shows how a database actually runs a query, which indexes it uses, in what order it joins tables and how expensive each step is estimated to be. Anyone who can read this execution plan finds performance problems in minutes instead of guessing, regardless of whether the query runs against MySQL, PostgreSQL or SQL Server.

   Read more

7. NULL Values in Aggregate Functions: Handling Them Correctly

   Aug 8, 2025

   NULL values in aggregate functions are treated differently by COUNT, SUM and AVG, and these exact differences regularly lead to wrong average values and misleading reports. This article explains why COUNT(*) and COUNT(column) return different results, how AVG can be skewed by NULL, and how COALESCE, NULLIF and deliberate GROUP BY control this NULL behavior on purpose.

   Read more

8. Check Constraints for Complex Business Rules

   Jul 21, 2025

   A CHECK constraint can do far more than enforce a positive price. Multi-column conditions, state machines for status fields, IMMUTABLE functions, and EXCLUDE constraints against overlapping ranges turn a simple range check into a tool for complex business rules that would otherwise only be captured with triggers or application code.

   Read more

9. GROUP BY with ROLLUP and CUBE: Multi-Level Aggregation in SQL

   Jul 17, 2025

   Anyone stitching subtotals and grand totals together from several separate queries with UNION is solving a problem that GROUP BY ROLLUP already handles natively. With ROLLUP, CUBE, and GROUPING SETS, multi-level aggregations arrive in a single statement, consistent and performant.

   Read more

10. Common Table Expressions: CTE Basics

    Jul 6, 2025

    A Common Table Expression names a subquery with the WITH clause and makes it referenceable in the main query like a table. Instead of nesting subqueries multiple levels deep, you get a query that reads top to bottom, states its logic once, and can be traced step by step, whether in PostgreSQL, MySQL, SQL Server, or Oracle.

    Read more