Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Dedicated conversion functions
- Nested function usage
- Retrieving current date and time using various functions
- CASE expressions
Aggregating Data via Aggregate Functions
- Core aggregate functions
- Handling NULL values in aggregations
- The GROUP BY clause
- Grouping by multiple columns
- Filtering aggregated results with the HAVING clause
- Multidimensional grouping using ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting Data from Multiple Tables
- Various join types
- Utilizing table aliases
- INNER JOINs
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios and placement for subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Integrating aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic Functions
- Practical applications
- Window functions and window types
- Partitioning data
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Attendees are expected to possess a solid working grasp of fundamental SQL and Microsoft SQL Server, including the capability to:
- Construct basic
SELECTqueries to extract data from single or multiple tables. - Incorporate
WHEREclauses and elementary filtering logic. - Utilize standard SQL functions, including character, numeric, and date-based operations.
- Recognize standard data types and handle necessary conversions.
- Execute basic
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Apply and understand
GROUP BYandHAVINGclauses. - Bring practical experience in database management, data analysis, or reporting.
As an advanced-level course, learners should already feel confident with core SQL concepts before tackling complex subjects like subqueries, advanced aggregation, set operators, and analytic or window functions.
Audience
This curriculum 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
