Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Conversion utilities
- Nested function usage
- Retrieving current date and time via various functions
- CASE expression logic
Data Aggregation with Aggregate Functions
- Overview of aggregate functions
- Handling NULL values with aggregate functions
- The GROUP BY clause
- Grouping data by multiple columns
- Filtering aggregated results using the HAVING clause
- Multidimensional grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Data Retrieval from Multiple Tables
- Various types of joins
- Using table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios for using subqueries
- Single-row and multi-row subqueries
- Operators for single-row subqueries
- Using aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic Functions
- Purpose and application
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Learners are expected to possess a solid command of foundational SQL and Microsoft SQL Server concepts, specifically the ability to:
- Construct fundamental
SELECTqueries to extract data from single or multiple tables. - Apply
WHEREclauses and basic filtering criteria. - Utilize standard SQL functions, including those for character, numeric, and date manipulation.
- Grasp basic data types and conversion processes.
- Execute basic
JOINoperations. - Employ aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and apply
GROUP BYandHAVINGclauses. - Have hands-on experience with databases, data analysis, or reporting tasks.
As an advanced-level module, this course assumes that participants are already proficient in core SQL principles before advancing to complex topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
The course is tailored for data analysts and developers working with 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.
