Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Use of conversion functions
- Nested function logic
- 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 with the HAVING clause
- Multi-dimensional grouping using ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting data across multiple tables
- Overview of different join types
- Using table aliases
- INNER JOIN mechanics
- LEFT, RIGHT, and FULL OUTER JOINs
Set operations
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios for utilizing subqueries
- Single-row vs. multi-row subqueries
- Operators for single-row subqueries
- Integrating aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subquery implementation
Analytic functions
- Practical applications of analytic functions
- Window functions and window types
- Data partitioning
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Learners should possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, demonstrating the ability to:
- Construct basic
SELECTstatements to fetch data from single or multiple tables. - Implement
WHEREclauses along with basic filtering conditions. - Utilize standard SQL functions, including character, numeric, and date-based utilities.
- Grasp fundamental data types and their conversions.
- Execute basic
JOINoperations. - Apply aggregate functions like
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and utilize
GROUP BYandHAVINGclauses. - Have hands-on experience with database management, data analysis, or reporting tools.
As this is an advanced-level program, participants are expected to have a strong command of core SQL concepts before diving into complex areas such as subqueries, advanced aggregation, set operators, and analytic/window functions.
Target Audience
This course is specifically tailored for data analysts and developers working with 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
