SQL
Advanced Practice: Subqueries, CTEs and Window Functions
Twelve harder exercises - correlated subqueries, recursive CTEs, ranking, running totals and top N per group - with solutions and the reasoning behind...
#Advanced
Advanced level material.
60 notes tagged
SQL
SQL Injection and Parameterised Queries
Why string concatenation lets user input rewrite your query, and why prepared statements make it structurally impossible - with the cases parameters c...
HTML
Structured Data and JSON-LD
Machine readable facts about your page, using a shared vocabulary. It does not raise rankings, and it can change how your result looks entirely.
SQL
How a Query Executes and Reading EXPLAIN
What happens between typing a query and getting rows, and how to read an execution plan - the single most useful performance skill in SQL.
SQL
Composite, Unique and Covering Indexes
Multi column indexes and why their order decides everything: the leftmost prefix rule, selectivity, covering indexes and index only scans.
SQL
Deadlocks and Lock Contention
Why two transactions can wait for each other forever, how the database resolves it, and the design habits that prevent deadlocks and reduce contention...
SQL
Locks: Shared, Exclusive, Row and Table Level
How the database stops two transactions corrupting each other: lock modes, granularity, locking reads, and why an unindexed WHERE clause can lock a wh...
SQL
Isolation Levels and Read Phenomena
The four isolation levels, the three anomalies they prevent, and what each database actually defaults to - with the session by session demonstrations.
HTML
ARIA Basics: Roles, States and Properties
ARIA fills gaps that HTML cannot. It also breaks pages when used carelessly. The rules, the attributes worth knowing, and the ones to leave alone.
SQL
Trigger Basics: BEFORE, AFTER, INSERT, UPDATE and DELETE
Code the database runs automatically when data changes. Learn the six trigger points, the NEW and OLD row references, and how to write an audit trigge...
SQL
User Defined Functions
Functions return a value and can be used inside a query. Learn CREATE FUNCTION, determinism, the difference from procedures, and the performance trap.
SQL
Stored Procedures and Parameters
Named procedural code stored in the database. Learn CREATE PROCEDURE, IN, OUT and INOUT parameters, control flow, and when a procedure is the right to...