Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Conversion functions
- Nested function usage
- Retrieving current date and time via various functions
- CASE expressions
Aggregating Data with Aggregate Functions
- Core aggregate functions
- Handling NULL values in aggregate functions
- The GROUP BY clause
- Grouping data by multiple columns
- Filtering aggregated results using the HAVING clause
- Multidimensional data grouping with ROLLUP and CUBE operators
- Identifying summary rows using GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Fetching Data from Multiple Tables
- Varieties of joins
- Table aliases
- INNER JOINs
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate contexts for using subqueries
- Single-row vs. multi-row subqueries
- Single-row subquery operators
- Using aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, and ANY
- Recursive subqueries
Analytic Functions
- Purpose and application
- Window functions and window types
- Data partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
To succeed in this program, participants should possess a solid grasp of foundational SQL and Microsoft SQL Server concepts, including the ability to:
- Compose basic
SELECTqueries to extract data from single or multiple tables. - Implement
WHEREclauses and elementary filtering conditions. - Utilize standard SQL functions, including those for characters, numbers, and dates.
- Comprehend basic data types and type conversion mechanisms.
- Execute fundamental
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understand and utilize
GROUP BYandHAVINGclauses. - Bring practical experience with database management, data analysis, or reporting tools.
As this is an advanced-level course, participants are expected to be confident with core SQL concepts before engaging with complex topics like subqueries, advanced aggregation, set operators, and analytic/window functions.
Audience
This course is tailored for data analysts and developers of reporting applications.
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.
