Course Outline
Review: SQL Functions and Expressions
- Functions for characters, numbers, and dates/times
- Explicit and implicit type conversions
- Utilization of conversion functions
- Nested function structures
- Retrieving the current date and time using various functions
- Implementation of CASE expressions
Data Aggregation via Aggregate Functions
- Core aggregate functions
- Handling NULL values in aggregation
- The GROUP BY clause
- Grouping data by different columns
- Filtering aggregated results using the HAVING clause
- Multi-dimensional grouping with ROLLUP and CUBE operators
- Identifying rollup summaries using GROUPING
- Application of the GROUPING SETS operator
- Creating cross-tabulations using PIVOT
Data Retrieval from Multiple Tables
- Various types of joins
- Use of table aliases
- INNER JOIN implementation
- LEFT, RIGHT, and FULL OUTER JOINs
Set Operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Contexts for utilizing subqueries
- Single-row versus 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 scenarios
- Window functions and window types
- Partitioning
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
Candidates are expected to possess a solid practical foundation in basic SQL and Microsoft SQL Server, demonstrated by the ability to:
- Compose basic
SELECTqueries to extract data from single or multiple tables. - Employ
WHEREclauses alongside standard filtering conditions. - Utilize common SQL functions, including those for character, numeric, and date operations.
- Grasp fundamental data types and conversion processes.
- Execute basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Interpret and implement
GROUP BYandHAVINGclauses. - Hold prior hands-on experience with database management, data analysis, or reporting.
As this is an advanced-level program, learners should be proficient in core SQL principles before tackling more intricate subjects like subqueries, complex aggregation, set operators, and analytic/window functions.
Audience
The curriculum is 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
