Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Use of conversion functions
- Nested function structures
- Retrieving current date and time via various functions
- Implementation of CASE expressions
Aggregating Data with Aggregate Functions
- Overview of aggregate functions
- Behavior of aggregate functions with NULL values
- The GROUP BY clause
- Grouping by multiple columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional data grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Data Retrieval from Multiple Tables
- Types of joins
- Use of table aliases
- INNER JOIN implementations
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts for subquery usage
- Single-row and multi-row subqueries
- Operators for single-row subqueries
- 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 knowledge of foundational SQL and Microsoft SQL Server, demonstrating proficiency in the following areas:
- Constructing basic
SELECTstatements to extract data from single or multiple tables. - Applying
WHEREclauses and fundamental filtering logic. - Utilizing standard SQL functions, including character, numeric, and date operations.
- Understanding core data types and conversion mechanisms.
- Executing basic
JOINoperations. - Employing aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understanding and implementing
GROUP BYandHAVINGclauses. - Having practical experience in database management, data analysis, or reporting.
As this is an advanced-level course, participants should be confident with fundamental SQL concepts before engaging with complex topics such as subqueries, sophisticated 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 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
