Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Conversion functions
- Nested functions
- Retrieving the current date and time using various functions
- CASE expressions
Aggregating data with aggregate functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- The GROUP BY clause
- Grouping by multiple columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping using ROLLUP and CUBE operators
- Identifying summary rows with the GROUPING function
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting 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 contexts for subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Using aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Applications 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
Learners are expected to have a solid grasp of fundamental SQL concepts and Microsoft SQL Server, with the ability to:
- Construct basic
SELECTstatements to extract data from single or multiple tables. - Implement
WHEREclauses along with standard filtering conditions. - Utilize common SQL functions, including those for characters, numbers, and dates.
- Understand standard data types and their conversions.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Utilize
GROUP BYandHAVINGclauses effectively. - Possess practical experience in database management, data analysis, or reporting.
As this is an advanced-level course, participants should feel confident in foundational SQL principles before tackling complex topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Target 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
