Course Outline
Review: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Application of conversion functions
- Nested function usage
- Retrieving current date and time using various functions
- Implementing CASE expressions
Aggregating Data with Aggregate Functions
- Overview of aggregate functions
- Handling aggregate functions in relation to NULL values
- Utilizing the GROUP BY clause
- Grouping data by multiple distinct columns
- Filtering aggregated results using the HAVING clause
- Multidimensional data grouping via ROLLUP and CUBE operators
- Identifying summary rows with GROUPING
- Using the GROUPING SETS operator
- Creating crosstabs using PIVOT
Data Retrieval from Multiple Tables
- Exploring various join types
- Using table aliases
- Applying INNER JOINs
- Working with LEFT, RIGHT, and FULL OUTER JOINS
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Determining appropriate contexts 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
- Exploring recursive subqueries
Analytic Functions
- Purposes and applications
- Window functions and window types
- Data partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Learners are expected to possess a solid working command of fundamental SQL and Microsoft SQL Server concepts, demonstrating proficiency in the following areas:
- Composing basic
SELECTqueries to extract data from single or multiple tables. - Utilizing
WHEREclauses alongside standard filtering conditions. - Applying common SQL functions, including character, numeric, and date-related operations.
- Comprehending basic data types and conversion processes.
- Executing fundamental
JOINoperations. - Implementing aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Interpreting and applying
GROUP BYandHAVINGclauses. - Gaining practical exposure to database management, data analysis, or reporting tasks.
As an advanced-level course, it is assumed that participants are already at ease with core SQL principles before exploring more intricate topics like subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This program is tailored for data analysts and developers working on 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
