Page 5 - SQL

JSON columns give a relational database exactly the modeling freedom that otherwise serves as an argument for a NoSQL switch, without giving up transactions, joins, and referential integrity. Whoever correctly indexes, queries, and validates JSONB in PostgreSQL or JSON in MySQL covers many semi-structured use cases within the existing database.

Prepared statements stop classic SQL injection at the input field, but far from every vector runs through a single form field. Second-order injection, blind injection without a visible error message, out-of-band exfiltration via DNS, and injection in identifier contexts such as ORDER BY bypass exactly the safeguards teams usually rely on.

Approximate aggregation replaces exact calculations like COUNT DISTINCT or percentiles with probabilistic algorithms that deliver a result on billions of rows in a fraction of a second, with a controlled error usually under one percent. This article explains how HyperLogLog and t-digest work, when approximate aggregation pays off, and when exact calculation remains the right choice.

A good index strategy does not depend on which ORM or framework sits on top of the database, but on fundamental principles such as selectivity, column order and the deliberate use of covering indexes. Once you understand these principles, you make better indexing decisions regardless of whether the application is written in PHP, Java, Python or plain SQL.

A plain GROUP BY over a date column is rarely enough for time series aggregation, because days without events simply do not show up in the result. This article explains how to build time buckets with DATE_TRUNC, how calendar tables and generate_series reliably fill missing periods, and how to compute comparison periods like previous year or previous week directly in SQL.

When requests suddenly hang while CPU usage stays low, lock contention is often the reason. With performance_schema in MySQL and pg_locks in PostgreSQL, lock contention can be diagnosed systematically, instead of guessing anew with every incident which session is blocking which other one.

Nested aggregation computes a metric from values that are already aggregated, such as average daily revenue from daily sums, instead of directly from individual rows. Since SQL forbids nesting aggregate functions like AVG(SUM(column)) directly at the same SELECT level, this article shows how subqueries and CTEs implement nested aggregation cleanly and correctly.

Eventual consistency is not a compromise for poorly built systems but a deliberate consequence of the CAP theorem in every distributed system, including read replicas in relational databases. Whoever concretely understands consistency models, conflict resolution, and replication lag makes better decisions about where a short delay is acceptable and where it is not.

Weighted aggregates account for the fact that not every value in a report should count equally, for example because one order contains more items than another, or an exam counts more than a quiz. This article shows why a plain AVG returns misleading averages in such cases, and how weighted average and weighted sum, using SUM(value times weight) divided by SUM(weight), compute the correct result.

Anyone who inserts, updates or deletes large data volumes one row at a time produces lock escalation, bloated transaction logs and runtimes measured in hours. With well designed batching, temporarily disabled indexes and correctly sized chunks, the same bulk operations become a task of minutes, without blocking the database's live operation.