Course Outline
Refresher: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Specific conversion functions
- Nested function usage
- Retrieving current date and time using various functions
- The CASE expression
Aggregating Data with Aggregate Functions
- Core aggregate functions
- Handling NULL values in aggregation
- The GROUP BY clause
- Grouping by multiple columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting Data from Multiple Tables
- Variations of join types
- Utilizing table aliases
- INNER JOIN operations
- LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts and locations for subquery implementation
- Distinguishing between single-row and 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
- Partitions
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
To fully benefit from this program, participants are expected to possess a solid working knowledge of basic SQL and Microsoft SQL Server, demonstrated by the ability to:
- Compose fundamental
SELECTqueries to extract data from single or multiple tables. - Utilize
WHEREclauses alongside basic filtering conditions. - Apply standard SQL functions, including those for character, numeric, and date manipulation.
- Recognize basic data types and perform necessary conversions.
- Execute fundamental
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Apply
GROUP BYandHAVINGclauses effectively. - Demonstrate practical experience in database management, data analysis, or reporting.
As an advanced-level course, it presupposes that learners are already confident with core SQL concepts before tackling complex subjects like subqueries, advanced aggregation, set operators, and analytic/window functions.
Target Audience
This curriculum is tailored for data analysts and developers specializing in 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
