Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Utilizing conversion functions
- Function nesting techniques
- Retrieving current date and time via various functions
- The CASE expression
Aggregating Data with Aggregate Functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- The GROUP BY clause
- Grouping data by multiple columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional grouping with ROLLUP and CUBE operators
- Identifying summary rows using GROUPING
- The GROUPING SETS operator
- Creating cross-tabulations with PIVOT
Data Retrieval from Multiple Tables
- Various types of joins
- Using table aliases
- INNER JOIN operations
- LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts for implementing subqueries
- Single-row versus multi-row subqueries
- Single-row subquery operators
- Incorporating 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
Learners are expected to possess a solid proficiency in foundational SQL and Microsoft SQL Server operations, demonstrating the ability to:
- Compose basic
SELECTstatements to extract data from single or multiple tables. - Implement
WHEREclauses and standard filtering logic. - Utilize standard SQL functions, including character, numeric, and date manipulation.
- Grasp fundamental data types and conversion processes.
- Execute basic
JOINoperations. - Employ aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Apply the
GROUP BYandHAVINGclauses effectively. - Bring practical experience in database management, data analysis, or report generation.
As an advanced-level curriculum, this course assumes participants are already at ease with core SQL principles before delving into sophisticated concepts like subqueries, complex aggregation, set operators, and analytic or window functions.
Target Audience
This program is tailored for data analysts and developers specializing in 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
