Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Use of conversion functions
- Nested function structures
- Retrieving current date and time via various functions
- The CASE expression
Aggregating Data with Aggregate Functions
- Core aggregate functions
- Handling NULL values in aggregations
- The GROUP BY clause
- Grouping across 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 with PIVOT
Data Retrieval from Multiple Tables
- Overview of different join types
- Implementing table aliases
- INNER JOIN specifics
- LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION operations
- UNION ALL operations
- INTERSECT operations
- EXCEPT operations
Subqueries
- Applicability and placement of subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, and ANY
- Recursive subquery structures
Analytic Functions
- Practical applications
- Window functions and window types
- Defining partitions
- Ranking function capabilities
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Enrollees are expected to possess a solid working knowledge of basic SQL and Microsoft SQL Server, demonstrating proficiency in the following areas:
- Constructing fundamental
SELECTqueries to extract data from single or multiple tables. - Applying
WHEREclauses and standard filtering conditions. - Leveraging common SQL functions, including character, numeric, and date operations.
- Comprehending basic data types and their conversions.
- Executing basic
JOINoperations. - Utilizing aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understanding and implementing
GROUP BYandHAVINGclauses. - Having practical experience in database management, data analysis, or reporting.
As this is an advanced-level course, participants should be fully comfortable with core SQL concepts before tackling complex topics like subqueries, advanced aggregation, set operators, and analytic/window functions.
Audience
This program is tailored for data analysts and reporting application developers.
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
