Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit conversions
- Conversion functions
- Nested functions
- Retrieving the current date and time using various functions
- CASE expressions
Aggregating data with aggregate functions
- Aggregate functions
- Aggregate functions and NULL values
- GROUP BY clause
- Grouping by various columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional data grouping with ROLLUP and CUBE operators
- Identifying summary rows using GROUPING
- GROUPING SETS operator
- Crosstabs using PIVOT
Fetching data from multiple tables
- Various types of joins
- Table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINs
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts and locations for subquery usage
- Single-row and multi-row subqueries
- Single-row subquery operators
- Using aggregate functions within subqueries
- Multi-row subquery operators - IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Purpose and application
- Window functions and window types
- Partitions
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Participants are expected to possess a solid working knowledge of basic SQL and Microsoft SQL Server, with the ability to:
- Compose basic
SELECTqueries to extract data from single or multiple tables. - Utilise
WHEREclauses and standard filtering conditions. - Employ common SQL functions, including character, numeric, and date functions.
- Understand fundamental data types and conversion processes.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and implement
GROUP BYandHAVING. - Have some practical experience in database management, data analysis, or reporting.
As an advanced-level course, participants should already be confident with fundamental SQL concepts before tackling more complex topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Audience
This course 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
