Advanced SQL Querying Training Course

Participants can select join patterns that preserve intended row counts and prevent duplicate results.

• INNER, LEFT, RIGHT and FULL joins compared by retained rows
• Join cardinality assessed using one-to-one and one-to-many relationships
• Duplicate multiplication diagnosed with pre-join row-count checks
• Semi-joins implemented with EXISTS for membership testing
• Anti-joins written to identify unmatched records and control exceptions
• Self-joins applied to manager, successor and related-party relationships

Participants can create accurate grouped measures, conditional aggregations and reconciliation totals.

• Conditional aggregation built with CASE expressions inside aggregate functions
• GROUPING SETS produce departmental, regional and enterprise subtotals
• ROLLUP and CUBE compared for hierarchical summary requirements
• Distinct counts evaluated for accuracy, cost and business meaning
• HAVING clauses filter aggregated groups without changing source records
• Reconciliation totals compare detail, summary and source-system populations

Participants can use subqueries and CTEs to express staged business logic clearly.

• Scalar subqueries return single values for row-level comparisons
• Correlated subqueries evaluated against each outer query row
• EXISTS and IN compared for NULL handling and optimizer choices
• Common table expressions separate named stages of query logic
• Multiple CTEs sequence filtering, enrichment and aggregation steps
• Derived tables used when intermediate calculations require independent grouping

Participants can apply window functions to calculate measures without collapsing detail rows.

• OVER clauses define partitions, ordering and window frames
• ROW_NUMBER assigns deterministic sequence numbers within business groups
• RANK and DENSE_RANK handle ties in performance comparisons
• Running totals calculated using ordered cumulative window frames
• LAG and LEAD compare transactions with prior or subsequent events
• FIRST_VALUE and LAST_VALUE retrieve boundary values with explicit frames

Participants can analyse time-based activity, retention and period comparisons using SQL.

• Calendar tables support complete periods and working-day analysis
• Date truncation groups events into reporting weeks, months and quarters
• Period-over-period comparisons calculated with LAG and date logic
• Cohort assignment based on first purchase, registration or claim date
• Retention rates derived from cohort activity across subsequent periods
• Gaps-and-islands techniques identify consecutive activity and inactivity periods

Participants can handle missing values, conversions and exceptional data without distorting results.

• NULL comparisons tested using IS NULL and IS NOT NULL
• COALESCE and NULLIF control defaults and divide-by-zero conditions
• CASE expressions encode explicit business classifications and exception states
• CAST and CONVERT manage numeric, date and character transformations
• Implicit conversions identified as sources of errors and poor performance
• Rounding rules applied consistently to currency, rates and quantities

Participants can combine and compare result sets to identify differences and produce controlled outputs.

• UNION and UNION ALL selected according to duplicate requirements
• INTERSECT identifies records shared by independently generated populations
• EXCEPT and MINUS reveal records missing from comparison datasets
• Column compatibility checked before combining result sets
• Reconciliation queries compare source extracts against reported outputs
• Symmetric difference patterns expose mismatches between two record populations

Participants can build recursive queries for organisational, product and relationship hierarchies.

• Recursive CTE anchor members establish hierarchy starting points
• Recursive members join each level to its parent records
• Hierarchy depth calculated for reporting lines and assembly structures
• Path strings constructed to display complete ancestor relationships
• Cycle detection prevents endless recursion in flawed relationship data
• Bill-of-materials rollups calculate component requirements across multiple levels

Participants can identify common causes of slow SQL and formulate targeted improvements.

• Execution plans read from operators, estimates, costs and row flows
• Table scans distinguished from index seeks and index scans
• Predicate sargability assessed for index-friendly search conditions
• Cardinality estimate errors traced to skewed or stale statistics
• Join algorithms compared: nested loops, hash joins and merge joins
• Query rewrites tested while preserving required result-set semantics

Participants can recommend index and query changes based on observed access patterns.

• Composite index key order matched to filtering and joining predicates
• Covering indexes evaluated using included columns and lookup avoidance
• Selective predicates prioritised when designing useful access paths
• Functions on indexed columns examined for lost index access
• Index maintenance trade-offs considered for write-heavy transactional tables
• Plan comparisons document performance effects before production deployment

Participants can write controlled data-change statements that preserve integrity and auditability.

• INSERT statements load validated rows from staged query results
• UPDATE statements target intended records using explicit qualification criteria
• DELETE operations preceded by count checks and transaction safeguards
• MERGE patterns synchronise source and target records under defined rules
• OUTPUT and RETURNING clauses capture changed rows for audit trails
• Upsert risks assessed for concurrency, duplicates and unexpected matches

Participants can apply transaction controls and recognise concurrency risks in SQL operations.

• ACID properties connected to reliable multi-step database operations
• BEGIN, COMMIT and ROLLBACK control units of work
• Isolation levels compared for visibility, locking and consistency effects
• Dirty reads, non-repeatable reads and phantom reads distinguished
• Deadlock conditions identified from competing lock acquisition patterns
• Savepoints support controlled recovery within longer data-change transactions

Participants can build SQL checks that identify incomplete, invalid and inconsistent data.

• Completeness checks identify mandatory attributes missing from critical records
• Referential integrity exceptions found through anti-join validation queries
• Domain checks test permitted codes, ranges and date relationships
• Duplicate detection combines partitioning with business-key definitions
• Outlier queries flag unusual values using statistical and rule-based thresholds
• Control totals document record counts, amounts and exception populations

Participants can structure reusable SQL scripts that support controlled reporting requirements.

• Named parameters isolate report dates, entities and threshold values
• Views encapsulate stable logic for approved reporting datasets
• Temporary tables compared with CTEs for staged processing needs
• Stored procedure design separates inputs, processing and returned results
• SQL comments document assumptions, source tables and business definitions
• Formatting conventions improve review, maintenance and change control

Participants can account for access controls and sensitive data when constructing queries.

• Least-privilege access applied to tables, views and stored procedures
• Row-level security effects checked against expected report populations
• Column masking implications assessed for identifiers and sensitive attributes
• Parameterized queries reduce SQL injection exposure in application code
• Sensitive extracts minimised through purpose-specific field selection
• Audit requirements documented for data changes and privileged queries

Participants can identify vendor-specific SQL differences and write portable query logic where appropriate.

• ANSI/ISO SQL constructs separated from vendor-specific extensions
• Date arithmetic differences compared across PostgreSQL, SQL Server and Oracle
• Pagination syntax reviewed using LIMIT, FETCH and OFFSET patterns
• Identifier quoting rules checked before cross-platform script migration
• String concatenation and NULL behaviour tested across database dialects
• Execution-plan tooling compared for vendor-specific diagnostic workflows

Participants can organise a complex analytical request into testable, efficient SQL stages.

• Requirements decomposed into population, measures, rules and presentation outputs
• Source-table profiling establishes grain, completeness and relationship assumptions
• Staged CTE architecture separates extraction, enrichment and calculation logic
• Validation queries test each stage against agreed control totals
• Performance checkpoints assess high-cost joins and aggregations early
• Query documentation records assumptions, limitations and approval decisions

Participants can review SQL for correctness, performance, security and maintainability before release.

• Code review checklists assess joins, filters, aggregations and NULL handling
• Expected row counts defined for each critical query stage
• Boundary conditions tested for dates, thresholds and empty populations
• Execution plans reviewed after representative parameter values are applied
• Rollback plans prepared for production data-change statements
• Release notes capture dependencies, permissions and operational ownership

Participants can build, review and present a complete analytical query pack for a realistic business requirement.

• Business requirement translated into documented data and calculation specifications
• Source population defined with defensible filters and relationship assumptions
• Multi-stage SQL developed using CTEs, joins and window functions
• Data-quality controls quantify exclusions, duplicates and reconciliation differences
• Execution plan findings support targeted performance improvements and index recommendations
• Peer review evaluates correctness, maintainability, security and reporting usefulness