Course Outline
Revision: SQL Functions and Expressions
- Character, numeric, and DateTime functions
- Explicit and implicit type conversion
- Conversion utilities
- Nested function usage
- Retrieving current date and time using various functions
- CASE expressions
Aggregating data with aggregate functions
- Overview of aggregate functions
- Handling aggregate functions with NULL values
- The GROUP BY clause
- Grouping across multiple columns
- Filtering aggregated results with the HAVING clause
- Multidimensional grouping via ROLLUP and CUBE operators
- Identifying summary rows using GROUPING
- The GROUPING SETS operator
- Cross-tabulations using PIVOT
Extracting data from multiple tables
- Variations of join types
- Table aliasing
- INNER JOIN operations
- LEFT, RIGHT, and FULL OUTER JOINS
Set operations
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- Appropriate contexts and locations for subqueries
- Single-row versus multi-row subqueries
- Operators for single-row subqueries
- Incorporating aggregate functions within subqueries
- Operators for multi-row subqueries: IN, ALL, ANY
- Recursive subqueries
Analytical functions
- Applications and use cases
- Window functions and window types
- Partitions
- Ranking functions
- LAG and LEAD functions
- FIRST_VALUE and LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Requirements
Learners are required to possess a solid working knowledge of fundamental SQL and Microsoft SQL Server, demonstrating the capacity to:
- Compose basic
SELECTstatements to extract data from single or multiple tables. - Utilise
WHEREclauses along with fundamental filtering criteria. - Apply standard SQL functions, including character, numeric, and date utilities.
- Comprehend basic data types and type conversion processes.
- Execute fundamental
JOINoperations. - Implement aggregate functions such as
COUNT,SUM,AVG,MIN, andMAX. - Interpret and apply
GROUP BYandHAVINGclauses. - Have some practical exposure to database management, data analysis, or reporting environments.
As this is an advanced-level programme, participants are expected to be at ease with core SQL concepts before delving into complex subjects such as subqueries, advanced aggregation, set operators, and analytic or window functions.
Target Audience
This course is intended for data analysts and developers of 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 2600 € + 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
