Course Outline
Revision: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Application of conversion functions
- Nesting functions
- Retrieving the current date and time using various functions
- Utilising CASE expressions
Aggregating data via aggregate functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- Implementing the GROUP BY clause
- Grouping data by various columns
- Filtering aggregated data using the HAVING clause
- Grouping multidimensional data with ROLLUP and CUBE operators
- Identifying summaries using GROUPING
- Employing the GROUPING SETS operator
- Creating crosstabs with PIVOT
Extracting data from multiple tables
- Exploring different types of joins
- Utilising table aliases
- Implementing INNER JOIN
- Applying LEFT, RIGHT, and FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Identifying appropriate scenarios for subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Operators for multi-row subqueries: IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Practical applications
- Window functions and window types
- Partitions
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Participants are required to possess a solid working knowledge of basic SQL and Microsoft SQL Server, demonstrated by the ability to:
- Compose fundamental
SELECTqueries to extract data from single or multiple tables. - Implement
WHEREclauses and standard filtering criteria. - Apply common SQL functions, including character, numeric, and date operations.
- Comprehend fundamental data types and conversions.
- Execute basic
JOINoperations. - Utilise aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Apply and interpret
GROUP BYandHAVING. - Bring some practical experience in database management, data analysis, or reporting.
As this is an advanced-level course, attendees are assumed to be proficient in foundational SQL concepts prior to tackling complex subjects like subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This program is tailored for data analysts and reporting application developers.
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
