Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Conversion functions
- Nested function usage
- Retrieving current date and time via 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 results using the HAVING clause
- Multi-dimensional grouping with ROLLUP and CUBE operators
- Identifying roll-up summaries using GROUPING
- The GROUPING SETS operator
- Creating crosstabs with PIVOT
Extracting data from multiple tables
- Various types of joins
- Use of table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set operations
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios and placements for subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions in subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Application of analytic functions
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Attendees are expected to possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, specifically the ability to:
- Compose basic
SELECTstatements to extract data from single or multiple tables. - Implement
WHEREclauses and standard filtering conditions. - Utilize common SQL functions, including those for character manipulation, numeric operations, and date handling.
- Comprehend basic data types and necessary conversions.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understand and apply
GROUP BYandHAVINGclauses. - Hold practical experience with database management, data analysis, or reporting tasks.
As an advanced-level course, participants should already be comfortable with core SQL concepts before delving into complex topics such as subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This program 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
