Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Dedicated conversion functions
- Nested function structures
- Retrieving current date and time using various functions
- CASE expressions
Aggregating data with aggregate functions
- Overview of aggregate functions
- Behavior of aggregate functions with NULL values
- The GROUP BY clause
- Grouping data by diverse columns
- Filtering aggregated results using the HAVING clause
- Multidimensional data grouping via ROLLUP and CUBE operators
- Identifying summary levels using GROUPING
- The GROUPING SETS operator
- Creating crosstabs with PIVOT
Extracting data from multiple tables
- Various types of joins
- Implementing table aliases
- INNER JOIN operations
- LEFT, RIGHT, and FULL OUTER JOINs
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Situations where subqueries are applicable
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Employing aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Applications and use cases
- Window functions and window types
- Data partitioning
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Participants are expected to possess a solid working proficiency in basic SQL and Microsoft SQL Server, demonstrated by the ability to:
- Construct fundamental
SELECTqueries to extract data from single or multiple tables. - Utilize
WHEREclauses alongside basic filtering criteria. - Apply standard SQL functions, including those for character, numeric, and date operations.
- Comprehend basic data types and conversion processes.
- Perform basic
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understand and apply
GROUP BYandHAVINGclauses. - Have practical experience in database management, data analysis, or reporting.
As an advanced-level course, it assumes that participants are already comfortable with fundamental SQL concepts prior to tackling complex topics like subqueries, advanced aggregation, set operators, and analytic/window functions.
Target Audience
This program 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.
