Course Outline
Review: SQL Functions and Expressions
- Functions for characters, numbers, and DateTime
- Explicit versus implicit type conversion
- Conversion utilities
- Nested function structures
- Retrieving current date and time via various functions
- The CASE expression
Aggregating data with aggregate functions
- Overview of aggregate functions
- Handling NULL values with aggregate functions
- The GROUP BY clause
- Grouping data across various columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional data grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating cross-tabulations using PIVOT
Extracting data from multiple tables
- Variations of joins
- Utilizing table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Situations and locations for employing 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 subqueries
Analytic functions
- Applications and use cases
- Window functions and window types
- Data partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Participants are expected to possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, demonstrating the ability to:
- Compose basic
SELECTstatements to extract data from single or multiple tables. - Utilize
WHEREclauses alongside standard filtering criteria. - Apply prevalent SQL functions, including character, numeric, and date operations.
- Grasp fundamental data types and the process of type conversion.
- Execute basic
JOINoperations. - Employ aggregate functions like
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and implement
GROUP BYandHAVINGclauses. - Bring practical hands-on experience in database management, data analysis, or reporting.
As an advanced-level course, learners should already feel at ease with core SQL principles before delving into more intricate subjects such as subqueries, sophisticated 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
