Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Conversion function applications
- Nested function usage
- Retrieving current date and time via various functions
- CASE expressions
Aggregating Data with Aggregate Functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- The GROUP BY clause
- Grouping data by various columns
- Filtering aggregated results using the HAVING clause
- Handling multidimensional data with ROLLUP and CUBE operators
- Identifying summary rows with the GROUPING function
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting Data from Multiple Tables
- Various types of joins
- Use of table aliases
- INNER JOIN mechanics
- LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Scenarios and locations for subquery usage
- Distinguishing between single-row and multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Recursive subqueries
Analytic Functions
- Application and usage
- Window functions and window types
- Data partitioning
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Participants are expected to possess a solid command of foundational SQL and Microsoft SQL Server concepts, demonstrating the following capabilities:
- Composing basic
SELECTstatements to extract data from single or multiple tables. - Employing
WHEREclauses along with fundamental filtering criteria. - Utilizing standard SQL functions, including character, numeric, and date operations.
- Applying an understanding of basic data types and type conversions.
- Executing elementary
JOINoperations. - Implementing aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understanding and applying
GROUP BYandHAVINGclauses. - Having practical hands-on experience with database management, data analysis, or reporting tasks.
As an advanced-level program, participants should feel confident with fundamental SQL principles before engaging with more sophisticated topics, including subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This course is specifically 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
