Page 7 - SQL

SQL

AI generated

Constraints Explained: CHECK, UNIQUE, NOT NULL

Maxim Mironjuk - August 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.

SQL

AI generated

Data Type Choices With Long-Term Consequences

Maxim Mironjuk - August 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.

SQL

AI generated

Calculating Moving Averages Directly in SQL

Maxim Mironjuk - August 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.

SQL

AI generated

Schema Migrations: Versioning and Rollback Strategies

Maxim Mironjuk - August 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.

SQL

AI generated

Using Savepoints for Granular Rollbacks

Maxim Mironjuk - August 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.

SQL

AI generated

Learning to Read Execution Plans Across Databases

Maxim Mironjuk - August 08, 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.

SQL

AI generated

NULL Values in Aggregate Functions: Handling Them Correctly

Maxim Mironjuk - August 08, 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.

SQL

AI generated

Check Constraints for Complex Business Rules

Maxim Mironjuk - July 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.

SQL

AI generated

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

Maxim Mironjuk - July 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.

SQL

AI generated

Common Table Expressions: CTE Basics

Maxim Mironjuk - July 06, 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.