Skip to content
academia.sh
tr
Log in

Course Intermediate

Advanced SQL

By the end of this course

Start course

01

Compound Queries

Scalar, row, and table subqueries, the per-row cost of a correlated subquery, common table expressions, hierarchy traversal with recursive queries, window and ranking functions, and pivot operations.

  1. 01 Subqueries Where scalar, row, and table subqueries are written, derived tables, the difference between IN and EXISTS, and a null value silently emptying a subquery's result.
  2. 02 Correlated Subqueries Per-row evaluation of a subquery tied to the outer row, measuring the amount of work by counting, comparison with a join-based rewrite, and the duplication difference between EXISTS and a join.
  3. 03 Common Table Expressions Naming intermediate results with WITH, giving a column list, chaining expressions, reusing the same intermediate result, measuring the materialization decision, and use inside data-modifying statements.
  4. 04 Recursive Queries Building the anchor and recursive members with WITH RECURSIVE, moving down and up a tree, subtree totals, what happens with no cycle guard, and sequence generation.
  5. 05 Window Functions Aggregating over rows without losing them with the OVER clause, partitioning, the RANGE versus ROWS distinction in a frame definition, running totals and sliding windows, and access to a neighboring row.
  6. 06 Ranking Functions The tie behavior of row number, rank, and dense rank, tile and percentile distribution functions, selecting the top N within a partition, and deduplication with row number.
  7. 07 Pivot Operations Pivoting from long form to wide form with conditional aggregation, the FILTER clause, the two phrasings of unpivoting, and the requirement that the column list be known when the query is written.

02

Transactions

Transaction control, partial rollback with savepoints, isolation levels and read anomalies, row and table locks with deadlock, stored procedures and functions, and the implicit side effects of triggers.

  1. 01 Transaction Control Autocommit mode, starting an explicit transaction with BEGIN, COMMIT and ROLLBACK, why a statement error does not end the transaction by itself, and implicit rollback when a connection drops.
  2. 02 Savepoints Defining an intermediate stop inside a transaction with SAVEPOINT, partial rollback with ROLLBACK TO, why RELEASE is not a commit, how nested points behave, and returning to the same point more than once.
  3. 03 Isolation Levels The dirty read, non-repeatable read, and phantom row anomalies, the four standard isolation levels, a real observation with two connections, and describing the level that cannot be shown through a model.
  4. 04 Locking Behavior Shared and exclusive locks, lock granularity, a real conflict and its error message produced with two connections, lock upgrade conflict, the deadlock cycle, and lock timeout.
  5. 05 Stored Procedures and Functions The standard form of server-side procedures and functions, the distinction between procedure and function, the consequences of determinism, measuring the round-trip count, and comparison with views.
  6. 06 Triggers Trigger definition, running per row with BEFORE and AFTER, audit logging and updating derived state, enforcing a rule with RAISE, INSTEAD OF on a view, and the risk of recursion.

03

Query Performance

How an index lowers search cost, reading a query plan, syntax that disables an index, join order and algorithm choice, equivalent rewriting of subqueries, the planner's statistics source, and the effects of dynamic SQL on correctness, security, and the plan cache.

  1. 01 Index Concept The cost of a full table scan, what an index converts that cost into, the same query measured without and with an index, the index's cost on the space and write side, and how selectivity determines the payoff.
  2. 02 Reading Query Plans The structure and reading direction of the plan tree, the distinction between scan and search nodes, the covering index's trace in the plan, the order of join nodes, the sort node's measured cost, and the effect of column order in a composite index on the plan.
  3. 03 Syntax That Prevents Index Usage How using a function on a column disables the index, converting date filters into a range condition, the limit of a left-open pattern match, matching the collation rule to the index, and the expression index for when the function cannot be given up.
  4. 04 Join Optimization The cost multiplier of nested loop join, how decisive the index on the inner side is, the measured effect of join order, the loop's linear scaling with outer row count, and when hash join and merge join win out.
  5. 05 Subquery Reduction The per-row evaluation of a correlated subquery and its reduction to a single pass with a window function, the measured plans of the same question written with IN, EXISTS, and a join, the interaction between NOT IN and NULL, and the equivalence conditions for a rewrite.
  6. 06 Statistics and Cardinality The planner's row count estimate, the default values used before statistics are collected, the estimate's correction after ANALYZE, the estimate's measured effect on the plan, the consequences of stale statistics, and the average's failure to describe a skewed distribution.
  7. 07 Dynamic SQL Risks The correctness and security consequences of building query text by string concatenation, how a bound parameter processes the same input correctly, measuring parse and plan cost, the condition under which the plan cache works, and the safe construction of queries with variable shape.

Start typing to search.

↑↓ ↵ Esc navigate · open · close