Course Outline
Refresher: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Utilization of conversion functions
- Nested function structures
- Retrieving current date and time via various functions
- Implementation of CASE expressions
Data Aggregation via Aggregate Functions
- Overview of aggregate functions
- Handling of NULL values in aggregate contexts
- The GROUP BY clause
- Grouping strategies using varied columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping using ROLLUP and CUBE operators
- Identifying summary levels with GROUPING
- The GROUPING SETS operator
- Creating crosstabs using PIVOT
Data Retrieval Across Multiple Tables
- Exploring various types of joins
- Use of table aliases
- INNER JOIN operations
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate contexts for employing subqueries
- Differences between single-row and multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Multi-row subquery operators including IN, ALL, and ANY
- Recursive subquery patterns
Analytic Functions
- Application scenarios
- Window functions and window definitions
- Data partitioning
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and 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 fundamental SQL and Microsoft SQL Server, demonstrating proficiency in the following areas:
- Constructing basic
SELECTqueries to extract data from single or multiple tables. - Utilizing
WHEREclauses and elementary filtering criteria. - Applying standard SQL functions, including those for character, numeric, and date manipulation.
- Comprehending core data types and their conversion processes.
- Executing basic
JOINoperations. - Employing aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Understanding and implementing
GROUP BYandHAVINGlogic. - Having hands-on experience with databases, data analysis, or reporting tasks.
As an advanced-level curriculum, this course assumes participants are already confident with foundational SQL concepts before delving into complex subjects like subqueries, sophisticated aggregation, set operators, and analytic/window functions.
Target Audience
This course is specifically curated 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
