Page 12 - SQL

A regular view is nothing more than a stored query that gets re-executed on every access and therefore always returns current data, but occupies no storage for the results. A materialized view stores the result physically and, on access, only reads that stored data, making it faster but potentially stale until a refresh synchronizes the data again.

Insert or update in a single statement sounds like a simple problem, yet every database solves it with its own syntax, its own atomicity guarantees, and its own pitfalls. Anyone who wants to keep upsert code portable across MySQL, PostgreSQL, SQL Server, Oracle, and SQLite needs to understand these differences before the first production switch happens.

Treating HAVING and WHERE as interchangeable filter clauses produces queries that either work by accident or run needlessly slow. The difference lies in execution order: WHERE filters before aggregation, HAVING after, and that is exactly what decides what can be filtered at all.

The ACID properties are the promise every relational database makes that a transaction takes effect either completely or not at all, that integrity rules always hold, that concurrent transactions cannot corrupt each other, and that confirmed data survives a crash. Understanding what actually breaks without each of these four properties leads to better decisions when designing transactions.

A recursive CTE solves exactly the task where a normal query fails: traversing tree structures of unknown depth in a single SQL statement. With WITH RECURSIVE, category trees, org charts, and bills of materials are evaluated without application code and without a fixed nesting depth, as long as the anchor query, the recursive part, and the termination condition are defined cleanly.

A deadlock occurs when two transactions block each other because each is waiting for a resource the other one holds. Consistent lock ordering in application code, short transactions, and robust retry logic prevent most deadlocks before the database ever has to step in.

A deadlock log entry looks like cryptic noise at first glance, but it contains all the information needed to reconstruct the cause precisely. Analyzing deadlocks from logs systematically, instead of dismissing them as rare chance events, reveals the underlying lock order in the code and fixes it permanently instead of just waiting for the next reconnect.