Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Conversion functions
- Nested functions
- Retrieving current date and time using various functions
- CASE expressions
Aggregating data with aggregate functions
- Overview of aggregate functions
- Handling NULL values in aggregate functions
- The GROUP BY clause
- Grouping by different columns
- Filtering aggregated data with the HAVING clause
- Multidimensional grouping using ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs with PIVOT
Retrieving data from multiple tables
- Types of joins
- Table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate use cases for subqueries
- 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
- Application and usage
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Participants should possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, demonstrating the ability to:
- Compose basic
SELECTqueries to extract data from single or multiple tables. - Utilize
WHEREclauses and basic filtering conditions. - Apply common SQL functions, including character, numeric, and date functions.
- Comprehend basic data types and type conversion.
- Execute basic
JOINoperations. - Employ aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understand and implement
GROUP BYandHAVING. - Have practical experience in database management, data analysis, or reporting.
As this is an advanced-level course, attendees are expected to be comfortable with core SQL concepts before tackling more complex subjects like subqueries, advanced aggregation, set operators, and analytic/window functions.
Audience
The course 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
