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 multiple columns
- Filtering aggregated data using the HAVING clause
- Multidimensional data grouping with ROLLUP and CUBE operators
- Identifying summaries using GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Retrieving 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
- Appropriate contexts 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
- Applications of analytic functions
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Participants should possess a solid working knowledge of foundational SQL and Microsoft SQL Server, with the ability to:
- Compose basic
SELECTqueries to retrieve data from single or multiple tables. - Apply
WHEREclauses and fundamental filtering conditions. - Utilize standard SQL functions, including character, numeric, and date operations.
- Understand basic data types and their conversions.
- Execute basic
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Grasp and apply
GROUP BYandHAVINGclauses. - Have some practical experience in database management, data analysis, or reporting.
As an advanced-level course, it is expected that participants are already comfortable with core SQL concepts before engaging with 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
