Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Conversion utilities
- Nested function usage
- Retrieving the current date and time via various functions
- CASE expressions
Aggregating data with aggregate functions
- Core aggregate functions
- Handling NULL values in aggregates
- The GROUP BY clause
- Grouping data by multiple columns
- Filtering aggregated results using the HAVING clause
- Multidimensional grouping with ROLLUP and CUBE operators
- Identifying summaries via GROUPING
- The GROUPING SETS operator
- Creating crosstabs with PIVOT
Extracting data from multiple tables
- Various types of joins
- Table aliasing
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios and placements for subqueries
- Single-row versus multi-row subqueries
- Single-row subquery operators
- Utilising aggregate functions within subqueries
- Multi-row subquery operators – IN, ALL, 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
Participants are expected to possess a solid command of fundamental SQL and Microsoft SQL Server, including the capability to:
- Compose basic
SELECTqueries to retrieve data from single or multiple tables. - Utilise
WHEREclauses and elementary filtering conditions. - Manage common SQL functions, including character, numeric, and date-related operations.
- Comprehend basic data types and conversion processes.
- Employ basic
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Interpret and apply
GROUP BYandHAVING. - Demonstrate some practical experience in database management, data analysis, or reporting.
As an advanced-level course, it is assumed that participants are already at ease with core SQL concepts before advancing to complex topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This course is tailored for data analysts and developers working on 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.
