Course Outline
Recap: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit conversion
- Conversion functions
- Nested functions
- Retrieving the current date and time using various functions
- CASE expressions
Aggregating data using aggregate functions
- Aggregate functions
- Handling NULL values with aggregate functions
- The GROUP BY clause
- Grouping data by various columns
- Filtering aggregated data using the HAVING clause
- Multidimensional data grouping with ROLLUP and CUBE operators
- Identifying summaries using GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Retrieving data from multiple tables
- Different types of joins
- Table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOINs
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Applicable contexts for subqueries
- Single-row and multi-row subqueries
- Single-row subquery operators
- Aggregate functions within subqueries
- Multi-row subquery operators - IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Purpose and usage
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Participants are expected to possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, including the capability to:
- Compose basic
SELECTqueries to extract data from single or multiple tables. - Employ
WHEREclauses alongside standard filtering conditions. - Utilise common SQL functions, covering character, numeric, and date operations.
- Understand fundamental data types and conversion processes.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and utilise
GROUP BYandHAVING. - Have hands-on experience in database management, data analysis, or reporting.
As an advanced-level course, participants should already be at ease with core SQL concepts before delving into more complex subjects like subqueries, advanced aggregation, set operators, and analytic or window functions.
Audience
This course 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
