Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type 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 aggregations
- The GROUP BY clause
- Grouping across multiple columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional grouping with ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting data from multiple tables
- Various types of joins
- Utilizing table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate contexts for using subqueries
- Single-row and multi-row subqueries
- Single-row subquery operators
- Incorporating aggregate functions within subqueries
- Multi-row subquery operators including IN, ALL, and ANY
- Recursive subqueries
Analytic functions
- Application scenarios
- Window functions and window types
- 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 are expected to possess a solid working command of fundamental SQL and Microsoft SQL Server capabilities, specifically the ability to:
- Construct basic
SELECTstatements to extract data from single or multiple tables. - Utilize
WHEREclauses for applying initial filtering conditions. - Operate with standard SQL functions, including those for character, numeric, and date handling.
- Recognize fundamental data types and perform necessary conversions.
- Execute basic
JOINoperations. - Employ aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and apply
GROUP BYandHAVINGclauses. - Demonstrate practical experience in database management, data analysis, or reporting.
As an advanced-level course, it assumes that learners are already proficient with core SQL concepts before advancing to complex topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Audience
This program is tailored for data analysts and developers specializing in 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.
