Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Utilization of conversion functions
- Nested function structures
- Retrieving current date and time via various functions
- Logic implementation using CASE expressions
Data Aggregation using Aggregate Functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- Implementation of the GROUP BY clause
- Grouping strategies across different columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping via ROLLUP and CUBE operators
- Identifying summary levels using GROUPING
- Advanced grouping with the GROUPING SETS operator
- Creating crosstabs using PIVOT
Data Retrieval from Multiple Tables
- Exploration of various join types
- Usage of table aliases
- INNER JOIN implementation
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION syntax
- UNION ALL usage
- INTERSECT operations
- EXCEPT exclusions
Subqueries
- Contexts for subquery implementation
- Distinguishing between single-row and multi-row subqueries
- Single-row subquery operators
- Integrating aggregate functions within subqueries
- Multi-row operators: IN, ALL, and ANY
- Recursive subquery logic
Analytic Functions
- Practical applications
- Window functions and window types
- Data partitioning
- Ranking functions
- LAG/LEAD function usage
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG aggregation
- Statistical analysis functions
Requirements
To successfully complete this module, participants must possess a solid foundation in basic SQL and Microsoft SQL Server, specifically the ability to:
- Construct fundamental
SELECTqueries for data retrieval from single or multiple tables. - Implement
WHEREclauses and standard filtering conditions. - Leverage common SQL functions, including character, numeric, and date utilities.
- Grasp essential data types and conversion processes.
- Execute basic
JOINoperations. - Utilize aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Apply and interpret
GROUP BYandHAVINGclauses. - Demonstrate practical experience with database management, data analysis, or reporting tools.
As an advanced-level program, this course assumes familiarity with core SQL concepts before exploring complex topics like subqueries, sophisticated aggregation, set operators, and analytic/window functions.
Target Audience
This curriculum is tailored for data analysts and reporting application developers.
Testimonials (4)
data was personalised to our organisations
Vincent Long - ASSMANG PTY LTD
Course - T-SQL Fundamentals with SQL Server Training Course
personalised to our understanding and data
Vincent Long - ASSMANG PTY LTD
Course - Business Intelligence with SSAS
The training was well structured and interactive
kgotla Moncho - Martin Engineering Africa
Course - MS 20761 : Querying Data with Transact SQL
The instructor brought his A game again as he superbly took my staff through the customized training with expert timing, knowledge, support, and rapport with my staff.
