Course Outline
Review: SQL Functions and Expressions
- Functions for characters, numbers, and DateTime
- Explicit versus implicit conversion
- Conversion utilities
- Nested function usage
- Retrieving current date and time via various functions
- CASE expression logic
Aggregating data with aggregate functions
- Core aggregate functions
- Interaction between aggregate functions and NULL values
- The GROUP BY clause
- Grouping strategies using distinct columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping via ROLLUP and CUBE operators
- Identifying summary rows using GROUPING
- The GROUPING SETS operator
- Creating crosstabs with PIVOT
Extracting data from multiple tables
- Various types of joins
- Table aliasing
- INNER JOIN mechanics
- LEFT, RIGHT, and FULL OUTER JOINS
Set operations
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts for subquery implementation
- Single-row and multi-row subquery structures
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Operators for multi-row subqueries - IN, ALL, ANY
- Recursive subquery patterns
Analytic functions
- Application scenarios
- Window functions and window types
- Data partitioning
- Ranking utilities
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Candidates are expected to possess a solid working command of fundamental SQL and Microsoft SQL Server, specifically including the capability to:
- Construct basic
SELECTstatements to extract data from one or multiple tables. - Utilize
WHEREclauses alongside standard filtering conditions. - Interact with prevalent SQL functions, including those for characters, numbers, and dates.
- Comprehend foundational data types and their conversion processes.
- Execute elementary
JOINoperations. - Implement aggregate functions like
COUNT,SUM,AVG,MIN, andMAX. - Apply and understand the mechanics of
GROUP BYandHAVING. - Demonstrate practical exposure to database management, data analysis, or reporting tasks.
As a high-level course, attendees are presumed to be at ease with core SQL principles before delving into intricate subjects such as subqueries, sophisticated aggregation, set operators, and analytic/window functions.
Target Audience
This curriculum 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.
