Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Conversion utilities
- Nested function structures
- Retrieving current date and time using various functions
- The CASE expression
Aggregating Data with Aggregate Functions
- Overview of aggregate functions
- Handling NULL values with aggregates
- The GROUP BY clause
- Grouping data across multiple columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Retrieving Data 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
- Placement and usage of subqueries
- Single-row and multi-row subqueries
- Operators for single-row subqueries
- Employing aggregate functions within subqueries
- Operators for multi-row subqueries: IN, ALL, ANY
- Recursive subqueries
Analytic Functions
- Application and use cases
- Window functions and window types
- Partitions
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Candidates should possess a solid command of fundamental SQL and Microsoft SQL Server concepts, specifically the ability to:
- Construct basic
SELECTstatements to extract data from single or multiple tables. - Implement
WHEREclauses and standard filtering criteria. - Utilize standard SQL functions, including character, numeric, and date-handling utilities.
- Comprehend basic data types and type casting mechanisms.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Interpret and apply
GROUP BYandHAVINGclauses. - Demonstrate hands-on experience with database management, data analysis, or reporting tools.
As an advanced-level program, this course assumes participants are already at ease with core SQL principles before advancing to complex subjects like subqueries, sophisticated aggregation, set operators, and analytic/window functions.
Audience
This training 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 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.
James - Shawnee Mission School District
Course - Administering in Microsoft SQL Server
The lecture about cte
