Course Outline
Refresher: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit data conversion
- Use of conversion functions
- Nested function usage
- Retrieving current date and time using various functions
- Implementation of CASE expressions
Aggregating data with aggregate functions
- Overview of aggregate functions
- Handling NULL values in aggregations
- The GROUP BY clause
- Grouping by various columns
- Filtering aggregated results with the HAVING clause
- Multi-dimensional grouping via ROLLUP and CUBE operators
- Identifying summaries with the GROUPING function
- Utilizing the GROUPING SETS operator
- Creating crosstabs using PIVOT
Extracting data from multiple tables
- Varieties of join operations
- Using table aliases
- INNER JOIN syntax
- LEFT, RIGHT, and FULL OUTER JOINs
Set operations
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate contexts for using subqueries
- Distinguishing single-row vs. multi-row subqueries
- Operators for single-row subqueries
- Using aggregate functions within subqueries
- Multi-row subquery operators: IN, ALL, ANY
- Constructing recursive subqueries
Analytic functions
- Purpose and application
- Window functions and window types
- Data partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- The STRING_AGG function
- Statistical functions
Requirements
To succeed in this course, participants should possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, demonstrated by the ability to:
- Compose basic
SELECTstatements to fetch data from single or multiple tables. - Employ
WHEREclauses and standard filtering criteria. - Utilize standard SQL functions, including character, numeric, and date operations.
- Grasp basic data types and type conversions.
- Execute elementary
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Comprehend and implement
GROUP BYandHAVINGlogic. - Bring practical experience in database management, data analysis, or reporting.
As an advanced-level programme, it is assumed that participants are already confident with core SQL principles before tackling complex subjects like subqueries, advanced aggregation, set operators, and analytic/window functions.
Audience
This course is tailored for data analysts and developers working on reporting applications.
Custom Corporate Training
Training solutions designed exclusively for businesses.
- Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
- Flexible Schedule: Dates and times adapted to your team's agenda.
- Format: Online (live), In-company (at your offices), or Hybrid.
Price per private group, online live training, starting from 3200 € + VAT*
Contact us for an exact quote and to hear our latest promotions
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
