Course Outline
SQL Views (View)
Convert queries into the form of views and manage them. Views allow you to reuse a query once created, significantly reducing the time spent on code development.
Conditional Functions
Conditional transformation of data within queries. In essence, these are functionalities similar to the IF function known from Microsoft Excel. The CAST function will also be introduced here for useful data conversion.
Subqueries
Nesting queries, creating subqueries, and establishing conditions based on subqueries. This topic helps in understanding the construction of multi-level queries, where one query is generated based on another.
Aggregation
Counting and grouping data using aggregation functions and data cubes, including the use of CUBE, ROLLUP, and GROUPING SETS. You will learn functions such as SUM(), MIN(), MAX(), COUNT(), and AVG(). You will also learn to filter record sets before GROUPING with WHERE and after GROUPING with HAVING.
Window Functions
Working with defined data areas and performing calculations based on ordered rows. Utilize the OVER clause with PARTITION BY and ORDER BY along with aggregation functions like SUM(), MIN(), MAX(), COUNT(), AVG(), and ranking/analytical functions typical of windows: RANK(), ROW_NUMBER(), LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE().
CTE Table Expressions
Create and use pre-calculated database queries. Learn methods for quickly building multi-level queries with structured, easy-to-maintain code.
Database Data Types
Conversion and normalization of data retrieved from SQL Server. Leverage the opportunities provided by the database to work with specific data types such as text, numeric values, and dates. Understand the limitations and possibilities of converting one type to another using the CAST command. Learn to handle problems and errors associated with transformations.
PIVOT/UNPIVOT Type Functions
Transform data by rearranging columns (pivot table layout). Create reports that significantly reduce the amount of retrieved data. This can be achieved using PIVOT/UNPIVOT commands or CTE-based subqueries that function similarly.
Query Optimization
Explore ways to speed up query execution. Learn how to make query execution faster and less taxing on the database server. You will also learn a tool to help plan query processing steps.
DDL Language
Action Queries - DML (Data Modification Language) - helps to 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 (deleting records).
DML Language
SQL Objects - Data Definition Language (DDL) - allows you to create objects such as tables, views, and procedures using SQL. You will learn commands such as CREATE (create an object), ALTER (modify an object), and DROP (delete an object).
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 (2)
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.
