SQL for Data Analysis Training Course

Participants can retrieve targeted records using precise filters, expressions and sorting rules.

• SELECT lists built with aliases, literals and calculated expressions
• WHERE predicates using comparison, IN, BETWEEN and LIKE operators
• AND, OR and NOT grouped with explicit Boolean logic
• NULL values tested correctly using IS NULL predicates
• ORDER BY clauses applied with multiple business sorting keys
• TOP, OFFSET and FETCH used for controlled result sampling

Participants can manage data types and conversions without introducing analytical errors.

• Implicit conversion risks identified between numeric, text and date columns
• CAST and CONVERT applied to reporting-ready output formats
• TRY_CAST used to isolate invalid source values safely
• Decimal precision and scale selected for financial calculations
• Unicode and non-Unicode character types distinguished for text data
• Conversion errors investigated through staged diagnostic query patterns

Participants can combine related tables while preserving the intended record population.

• Primary keys and foreign keys mapped across relational source tables
• INNER JOIN queries built for matched transaction and reference records
• LEFT JOIN queries used to identify missing related records
• FULL OUTER JOIN applied to reconciliation between two datasets
• Self joins used for manager, hierarchy and duplicate comparisons
• Join cardinality checked to prevent unintended row multiplication

Participants can calculate grouped measures and apply correct post-aggregation filters.

• COUNT, SUM, AVG, MIN and MAX calculated by reporting group
• GROUP BY clauses aligned with selected non-aggregated columns
• HAVING filters applied after aggregate measure calculation
• Conditional aggregation built with CASE expressions inside SUM
• Distinct counts used carefully for customer and transaction populations
• Ratio metrics protected against division by zero errors

Participants can create transparent business classifications and exception flags in SQL.

• CASE expressions built for status, banding and exception classification
• Searched CASE logic ordered to prevent overlapping category assignments
• COALESCE and ISNULL used for controlled missing-value substitution
• Business rules translated into explicit SQL decision branches
• Derived flags created for threshold breaches and missing attributes
• Classification logic documented through aliases and query comments

Participants can structure multi-step analysis using subqueries and common table expressions.

• Scalar subqueries used for benchmark and reference values
• Correlated subqueries applied to record-level comparative analysis
• EXISTS and NOT EXISTS used for relationship-based filtering
• Common table expressions separate logical stages of complex queries
• Multiple CTEs chained for readable analytical transformations
• Recursive CTEs introduced for organisational and account hierarchies

Participants can calculate rankings, running totals and period comparisons without collapsing detail.

• OVER clauses define analytical partitions and ordered calculation windows
• ROW_NUMBER assigns deterministic sequence numbers within business groups
• RANK and DENSE_RANK distinguish tied performance positions
• Running totals calculated with SUM window function frames
• LAG and LEAD compare current records with prior values
• Percentages of group totals derived using partitioned aggregates

Participants can analyse time-based data using reliable date calculations and reporting periods.

• DATEFROMPARTS and EOMONTH build standard reporting period boundaries
• DATEDIFF calculates elapsed days, months and service durations
• DATEADD shifts reporting windows for comparative period analysis
• Calendar tables support working-day and fiscal-period reporting
• Month-to-date and year-to-date measures built with date filters
• Time zones and datetime precision considered when interpreting event records

Participants can clean and standardise text fields for matching, classification and reporting.

• TRIM, LTRIM and RTRIM remove unwanted boundary spaces
• UPPER and LOWER standardise text before comparison operations
• SUBSTRING, LEFT and RIGHT extract coded field components
• CHARINDEX and PATINDEX locate patterns within source text
• CONCAT and CONCAT_WS assemble labelled reporting fields
• Collation settings considered when comparing case-sensitive text values

Participants can combine and reconcile result sets using SQL set operators.

• UNION combines compatible result sets while removing duplicate rows
• UNION ALL preserves duplicate rows for transaction-level analysis
• INTERSECT identifies records present in both comparison populations
• EXCEPT isolates records missing from a matched result set
• Column alignment checked before applying set operation queries
• Reconciliation outputs labelled with source and comparison status

Participants can stage intermediate data sets for repeatable multi-step analytical work.

• Local temporary tables created for session-specific intermediate results
• SELECT INTO used to create staged analytical data sets
• INSERT statements load controlled records into temporary tables
• Temporary table indexes added for larger staged query workloads
• Table variables compared with temporary tables for analytical tasks
• DROP TABLE statements used to manage temporary object lifecycle

Participants can test analytical data for completeness, validity, consistency and duplication.

• Null-rate profiling identifies missing mandatory analytical attributes
• Duplicate detection uses grouping and ROW_NUMBER partition patterns
• Referential-integrity exceptions identified through anti-join queries
• Range checks flag impossible values and threshold breaches
• Reconciliation totals compared across source, staging and output tables
• Data-quality findings recorded with rule, impact and remediation owner

Participants can create views that provide consistent and reusable analytical data definitions.

• CREATE VIEW statements package approved reporting query logic
• Column aliases establish stable business-facing field names
• View dependencies reviewed before changing underlying source objects
• Schema binding considered for controlled view and table dependencies
• Indexed view constraints distinguished from standard view use cases
• ALTER VIEW scripts maintained for version-controlled reporting changes

Participants can build parameterised stored procedures for controlled report generation.

• CREATE PROCEDURE statements define reusable report-generation routines
• Input parameters control date ranges, regions and status filters
• Default parameter values support common reporting selections
• Parameter validation prevents invalid date and category requests
• EXEC execution syntax tested with named parameter values
• Dynamic SQL risks understood for parameterisation and injection prevention

Participants can apply transaction and error-handling controls to safe data-update scripts.

• BEGIN TRANSACTION and COMMIT define controlled multi-step updates
• ROLLBACK restores prior data when validation checks fail
• TRY and CATCH blocks capture SQL Server runtime errors
• XACT_STATE determines valid transaction recovery actions after failure
• THROW returns meaningful error messages to calling processes
• Read-only analysis separated from approved data correction procedures

Participants can identify common query bottlenecks and propose evidence-based SQL Server improvements.

• Actual execution plans reveal costly operators and row estimates
• Clustered and nonclustered indexes matched to query access patterns
• Table scans and index seeks interpreted within execution plans
• SARGable predicates written to support efficient index usage
• Statistics freshness assessed when cardinality estimates appear inaccurate
• Query rewrites tested against elapsed time and logical reads

Participants can apply core SQL Server access-control principles to analytical data use.

• Principle of least privilege applied to reporting database access
• Database users, roles and permissions distinguished in SQL Server
• GRANT, DENY and REVOKE statements interpreted for object access
• Sensitive columns identified for restricted reporting and extracts
• Row-level security concepts considered for regional data access
• Query outputs reviewed for personally identifiable information exposure

Participants can recognise and query star-schema structures used in reporting and data warehouses.

• Fact tables distinguished from descriptive dimension tables
• Grain defined before building measures and joining dimensions
• Star schemas support consistent filtering and grouped reporting
• Surrogate keys and business keys compared for analytical joins
• Slowly changing dimensions introduced for historical attribute reporting
• Conformed dimensions support comparable measures across subject areas

Participants can prepare documented SQL outputs suitable for controlled dashboard and report use.

• Reporting views designed with stable fields and clear aliases
• Query outputs shaped at the appropriate analytical grain
• Date dimensions prepared for period filtering and trend reporting
• Numeric measures separated from formatted display values
• Refresh-friendly queries avoid unnecessary sorting and presentation logic
• Power BI import and DirectQuery considerations compared for SQL sources

Participants can build, review and present a complete SQL Server analysis answering a defined business question.

• Business question translated into scope, metrics, filters and assumptions
• Source tables profiled for relationships, grain and data-quality risks
• Staged Transact-SQL queries built from joins through final metrics
• Validation checks reconcile outputs with source totals and exceptions
• Execution plan reviewed for material performance improvement opportunities
• Findings presented with query documentation, limitations and recommended actions