Advanced SQL and Database Optimisation Training Course

Participants can relate logical SQL constructs to relational operations and physical data access.

• Logical query-processing order for FROM, WHERE, GROUP BY and HAVING clauses
• Relational algebra concepts behind selection, projection, joins and aggregation
• Primary keys, foreign keys and functional dependencies affecting join correctness
• Normalisation trade-offs between update integrity, reporting convenience and query cost
• Data skew, null distributions and correlation within operational tables
• Fact and dimension table patterns used in analytical database workloads

Participants can write efficient complex joins and choose appropriate subquery transformations.

• Inner, outer, semi and anti joins matched to business rules
• Join predicates that preserve cardinality and prevent accidental row multiplication
• EXISTS and NOT EXISTS patterns for correlated existence tests
• Scalar subqueries, decorrelation opportunities and repeated execution costs
• LATERAL and APPLY joins for parameterised rowset evaluation
• Self-joins and temporal joins for hierarchy and history analysis

Participants can build analytical SQL using window functions without unnecessary self-joins.

• PARTITION BY and ORDER BY clauses defining analytic calculation scope
• ROW_NUMBER, RANK and DENSE_RANK for ordered business records
• Running totals using window frames and cumulative aggregate functions
• LAG and LEAD functions for period-on-period comparison logic
• FIRST_VALUE and LAST_VALUE frame semantics and common result errors
• Top-N per group queries using ranked common table expressions

Participants can structure multi-step SQL and traverse hierarchical data using recursive CTEs.

• Non-recursive CTEs for readable multi-stage query decomposition
• Materialisation behaviour and optimiser treatment across database platforms
• Recursive CTE anchor members and recursive member construction
• Hierarchy traversal for organisational, account and product structures
• Cycle detection and maximum recursion safeguards for malformed hierarchies
• Temporary result strategies using CTEs, derived tables and temporary tables

Participants can select efficient SQL patterns for combining, reshaping and parsing datasets.

• UNION ALL versus UNION duplicate elimination and sorting costs
• INTERSECT and EXCEPT for reconciliation and data-quality checks
• Conditional aggregation for pivot-style reporting without procedural loops
• PIVOT and UNPIVOT operators with platform-specific performance considerations
• JSON extraction functions, generated columns and indexed search paths
• String parsing methods that avoid repeated scalar function evaluation

Participants can diagnose transaction contention and select isolation behaviour for workload requirements.

• ACID properties applied to financial, operational and reporting transactions
• Read committed, repeatable read and serializable isolation semantics
• Snapshot isolation benefits, version-store costs and update conflict risks
• Lock modes, lock granularity and lock escalation mechanisms
• Blocking-chain analysis using session, statement and wait information
• Deadlock graphs interpreted through resource order and transaction scope

Participants can assess how storage structures and indexes affect query access paths.

• Heap tables, clustered storage and row-location lookup implications
• B-tree index structure, page splits and key ordering
• Index selectivity measured through distinct values and predicate distributions
• Composite index key order based on equality, range and sorting predicates
• Covering indexes using included columns and index-only access
• Write amplification, index maintenance and storage-cost trade-offs

Participants can identify estimation errors and improve optimiser statistics decisions.

• Cardinality estimates compared with actual rows at each plan operator
• Histograms representing skewed values and non-uniform predicate selectivity
• Stale statistics caused by bulk loads, deletes and data drift
• Column correlation and independence assumptions affecting multi-predicate estimates
• Sampling rates, statistics targets and auto-update thresholds
• Parameter sensitivity and value-dependent execution plan selection

Participants can interpret execution plans and isolate the operators driving elapsed time.

• EXPLAIN and EXPLAIN ANALYZE outputs distinguished by estimated and actual metrics
• Table scans, index seeks and bitmap scans matched to predicates
• Nested loop, hash and merge join algorithms compared
• Sort, hash aggregate and spool operators analysed for memory pressure
• Parallel execution exchanges, worker skew and coordination overhead
• Plan warnings involving spills, implicit conversions and residual predicates

Participants can rewrite SQL to reduce work while preserving query semantics.

• SARGable predicates replacing functions applied to indexed columns
• Predicate pushdown to reduce rows before joins and aggregation
• Join elimination where referential constraints prove redundant access
• Anti-join rewrites replacing costly NOT IN null semantics
• Set-based UPDATE and DELETE statements replacing row-by-row processing
• Pagination methods compared for offset cost and keyset efficiency

Participants can recommend index changes supported by query patterns and operational evidence.

• Filtered indexes targeted at selective, stable predicate subsets
• Partial indexes and expression indexes for specialised access patterns
• Columnstore indexes for scan-heavy analytical query workloads
• Full-text indexes distinguished from wildcard predicate search
• Duplicate and overlapping index detection through key-prefix analysis
• Rebuild, reorganise and vacuum decisions based on fragmentation evidence

Participants can design partitioning approaches that support retention, loading and query pruning.

• Range, list, hash and composite partitioning scheme selection
• Partition pruning requirements for predicates and partition keys
• Local and global index choices under partition maintenance operations
• Sliding-window retention using partition switch, detach and drop operations
• Bulk loading methods, minimal logging and batch commit sizing
• Archival table design for regulatory retention and historical reporting

Participants can assess procedural database code and temporary-object choices for performance.

• Temporary tables, table variables and CTEs compared for optimiser visibility
• Stored procedure parameter handling and plan-cache implications
• Dynamic SQL parameterisation and injection-safe construction methods
• Cursor replacement using window functions and set-based updates
• Batch processing loops controlled through chunk size and commit frequency
• Error handling patterns preserving transaction consistency and diagnostic detail

Participants can use runtime evidence to detect bottlenecks and manage plan regressions.

• Wait-event analysis separating CPU, I/O, lock and memory symptoms
• Query-store history used to compare plans and runtime regressions
• Oracle AWR reports interpreted for workload and top-SQL diagnosis
• PostgreSQL pg_stat_statements metrics for high-cost query identification
• Plan cache inspection for compilation frequency and parameter sensitivity
• Alert thresholds based on baseline deviation and service-level objectives

Participants can prepare performance changes that protect availability and data integrity.

• Online index operations and maintenance-window risk assessment
• Rollback plans for query, index and schema performance changes
• Canary release methods for database changes under controlled load
• Load-test scenarios covering peak concurrency, failure and recovery conditions
• Replication lag and read-replica routing performance considerations
• Change records documenting assumptions, dependencies, validation and ownership

Participants can establish governance routines for sustained database performance management.

• Capacity forecasts using growth rates, seasonality and workload projections
• CPU, memory, storage latency and network utilisation interpretation
• Database performance scorecards linking metrics to service objectives
• Query review standards for code changes and release approval
• Index ownership registers and scheduled maintenance responsibilities
• Tuning backlogs prioritised by benefit, risk, effort and dependency

Participants can build, review and present a complete evidence-based SQL and database optimisation plan.

• Workload baseline assembled from query metrics, plans and system observations
• Problem queries selected using impact, frequency and business criticality
• SQL rewrites tested against correctness, runtime and resource measurements
• Index and statistics recommendations justified through access-path evidence
• Concurrency and deployment risks assessed with mitigation and rollback actions
• Final optimisation plan presented with priorities, owners and validation criteria