Course Outline
SQL Views
Convert queries into reusable views and manage them effectively. Views allow you to reuse a query once it has been created, significantly reducing the time spent on code development.
Conditional Functions
Apply conditional transformations within queries. Essentially, these function similarly to the IF logic found in Microsoft Excel. The CAST function will also be covered as a useful tool in this context.
Subqueries
Navigate nested queries by creating subqueries and defining conditions based on them. This topic helps clarify the structure of multi-level queries, where one query is built upon the results of another.
Aggregation
Count and group data using aggregation functions and data cube features, including the use of CUBE, ROLLUP, and GROUPING SETS. You will learn to use functions such as SUM(), MIN(), MAX(), COUNT(), and AVG(). You will also learn to filter record sets before grouping (WHERE) and after grouping (HAVING).
Window Functions
Work with defined data areas and perform calculations based on ordered rows. This includes using the OVER clause with PARTITION BY and ORDER BY, alongside aggregation functions (SUM(), MIN(), MAX(), COUNT(), AVG()) and ranking/analytical window functions such as RANK(), ROW_NUMBER(), LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE().
Common Table Expressions (CTE)
Create and utilize pre-calculated database queries. Learn methods for quickly building multi-level queries with structured, easy-to-maintain code.
Database Data Types
Convert and normalize data retrieved from SQL Server. Leverage the database's capabilities for working with specific data types such as text, numeric values, and dates. Understand the limitations and possibilities of type conversion and how to use the CAST command. Additionally, learn to handle problems and errors associated with data transformations.
PIVOT/UNPIVOT Functions
Transform data by rearranging columns (pivot table layout). Create reports that significantly reduce the volume of retrieved data. You will learn to use PIVOT/UNPIVOT commands or CTE-based subqueries that operate similarly.
Query Optimization
Discover methods to accelerate query execution and reduce the load on the database server. You will learn to use tools that help plan and optimize query processing steps.
DDL Language (Data Definition Language)
SQL Objects - Data Definition Language (DDL) enables you to create objects such as tables, views, and procedures using SQL. You will learn commands such as CREATE (to create an object), ALTER (to modify an object), and DROP (to delete an object).
DML Language (Data Manipulation Language)
Action Queries - DML (Data Manipulation Language) helps process data directly on the server. Data modification involves working with commands such as INSERT INTO (adding records), SELECT INTO (creating a table from a SELECT query), UPDATE (modifying data), and DELETE (removing records).
Requirements
This training is designed for individuals who already know the basics of SQL query development or wish to refresh their knowledge in this area.
Testimonials (3)
personalised to our understanding and data
Vincent Long - ASSMANG PTY LTD
Course - Business Intelligence with SSAS
The training instruments provided.
- UNIFI
Course - NoSQL Database with Microsoft Azure Cosmos DB
The adjustment made in the lecture/lessons by the trainer once he understood the current SSIS application that we are bound to maintain. The topics became more suitable/usable to us.
