SQL for Business Intelligence Training Course

Participants can inspect database structures and write readable, controlled SQL statements.

• PostgreSQL connection settings, schemas and role permissions examined safely
• Query editors configured with saved connections and readable result panes
• Database catalog views queried to inspect tables, columns and constraints
• SQL statement structure traced from SELECT through ORDER BY
• Comments, aliases and formatting conventions applied for maintainable queries
• Read-only query practices separated from data-changing SQL commands

Participants can retrieve controlled result sets using precise filters and derived fields.

• SELECT lists constructed with explicit columns rather than wildcard selection
• WHERE predicates combined using AND, OR and parentheses
• NULL semantics handled with IS NULL, COALESCE and NULLIF
• IN, BETWEEN and LIKE chosen for appropriate filter conditions
• CASE expressions standardise categories and derive reporting labels
• ORDER BY and LIMIT produce controlled result samples

Participants can join source tables without creating unplanned omissions or duplications.

• INNER JOIN returns matched records using explicit business keys
• LEFT JOIN preserves base populations for exception and coverage reporting
• Join conditions tested against expected row counts and totals
• Many-to-many joins diagnosed before they inflate metric calculations
• Self-joins compare employee hierarchies, accounts and prior-period records
• Anti-joins identify missing reference data and unmatched transactions

Participants can calculate summarised measures and reconcile outputs to trusted controls.

• GROUP BY summarises measures at customer, product and month grain
• HAVING filters aggregated groups after measure calculations
• COUNT DISTINCT measures unique entities without duplicate transaction inflation
• Aggregate NULL handling verified with COUNT, SUM, AVG and COALESCE
• Conditional aggregation produces multiple KPI slices in one query
• Reconciliation totals compare query outputs against trusted source reports

Participants can create reliable time-based reporting calculations using explicit period rules.

• Date dimensions distinguish calendar, fiscal and reporting period attributes
• DATE_TRUNC groups transactions into weeks, months and quarters
• Interval arithmetic calculates ageing, service durations and due dates
• Rolling periods defined with clear inclusive and exclusive boundaries
• Time-zone conversion rules documented before timestamp-based performance reporting
• Fiscal calendar joins align operational dates with finance reporting

Participants can structure multi-step SQL transformations into reusable reporting datasets.

• Layered queries separate source extraction, transformation and presentation logic
• Common table expressions organise multi-step sales performance calculations
• Subqueries filter populations using correlated and uncorrelated patterns
• UNION ALL consolidates aligned datasets without unintended deduplication
• Views publish stable reporting datasets with controlled column definitions
• Reusable query layers built for an executive sales dashboard

Participants can identify, classify and document data-quality issues before publication.

• Profiling queries expose null rates, duplicates and invalid codes
• Referential integrity checks compare transaction keys with master data
• Reconciliation rules test record counts, amounts and reporting populations
• Outlier detection uses percentile, z-score and business-rule thresholds
• Data quality exceptions categorised by source, owner and resolution status
• Validation queries embedded before BI dataset publication and refreshes

Participants can calculate rankings, trends and period comparisons without losing transaction detail.

• Window functions calculate metrics without collapsing detailed transaction rows
• PARTITION BY defines independent customer, branch and product calculations
• ROW_NUMBER and RANK resolve latest records and leaderboard positions
• LAG and LEAD compare periods within ordered analytical partitions
• Running totals and moving averages support trend analysis
• Window frames specified to prevent unintended cumulative calculations

Participants can translate business questions into documented and testable SQL metrics.

• Business questions translated into measurable KPI definitions and calculation rules
• Numerator, denominator and inclusion rules documented for rate metrics
• Metric grain aligned with available facts before aggregation
• SQL queries implement revenue, retention and service-level indicators
• Drill-down paths designed from executive totals to transaction detail
• KPI acceptance criteria agreed with finance and operational owners

Participants can design reporting structures using dimensional modelling principles.

• Kimball dimensional modelling separates business processes, facts and dimensions
• Declaring grain precedes fact table design and measure selection
• Conformed dimensions support consistent metrics across multiple subject areas
• Slowly changing dimension Types 1 and 2 compared
• Additive, semi-additive and non-additive facts classified before reporting
• Star schema designs reviewed against dashboard query requirements

Participants can diagnose slow queries and apply practical performance improvements.

• EXPLAIN plans interpreted for scans, joins, sorts and aggregation costs
• Predicate placement reduces rows before expensive joins and sorts
• Indexes matched to filter, join and ordering patterns
• Sargable predicates preserve index use on date and text columns
• Query rewrites replace unnecessary DISTINCT, functions and repeated subqueries
• Performance baselines record runtime, rows scanned and resource indicators

Participants can design repeatable SQL processes for refreshed reporting datasets.

• Staging tables isolate raw extracts from curated reporting datasets
• Incremental load logic selects new and changed records safely
• Watermark columns support repeatable extraction and late-arriving data handling
• MERGE patterns compared with insert-update processing for warehouse loads
• Idempotent SQL scripts prevent duplicate loads during reruns
• Refresh dependencies sequenced from dimensions through facts to dashboards

Participants can prepare SQL datasets for controlled use in BI reporting tools.

• Semantic models separate business measures from report visualisation choices
• Power BI import and DirectQuery modes compared for reporting use
• Parameterised SQL supports controlled report filters and reusable datasets
• Views expose approved fields while shielding source-system complexity
• Row-level security requirements translated into query and model constraints
• Dashboard extracts validated against SQL result sets before publication

Participants can document, review and govern SQL reporting assets appropriately.

• Data lineage records source tables, transformations, owners and refresh schedules
• Business glossaries define approved metric names, formulas and exclusions
• Least-privilege access separates analysts, report consumers and administrators
• Personally identifiable information masked through approved SQL access patterns
• Peer review checklists assess correctness, readability, performance and documentation
• Version-controlled SQL release notes record changes, risks and rollback steps

Participants can build, review and present a complete validated SQL reporting dataset with KPI definitions, quality controls and performance evidence.

• Requirements translated into a scoped reporting dataset and KPI specification
• Source tables profiled, joined and transformed using documented SQL logic
• Validation tests reconcile headline metrics with supplied control totals
• Window functions and dimensional design answer trend and segmentation questions
• Performance improvements evidenced through EXPLAIN plans and query revisions
• Peer review presents assumptions, findings, limitations and implementation actions