SQL - data processing

The training is designed for people who know the basics of SQL query development or who want to refresh their knowledge in this area.

About the training
Training on working with SQL language for a selected SQL database server including DML and DDL - updating data and creating database objects. General-purpose training aimed at people involved in active data processing.

Purpose of the training
To familiarize with the terminology and tools necessary for working with a database. To learn the extension of the knowledge of SQL language and its use in everyday work with a database.

What will Sharepoint improve in your work?
You will learn how to retrieve data efficiently. You will shorten data flow paths by skipping CSV, Excel files, etc. You will gain the ability to use the latest analytical tool. You will increase your confidence at work by learning new skills. You will dramatically increase your chances for promotion. You will radically increase the level of your professional competence.

Where will you use the acquired knowledge?
You will increase the efficiency of your work related to creating reports and statements in spreadsheets and BI tools. You will also notice an increase in your comfort in accessing data.

What will you learn?
You will improve information sharing with colleagues. You will be able to create a unified reporting system. Standardization of methods for generating reports and creating statements for analysis will no longer be a problem for you. At the same time, you will move the security of the created analyses to a new higher level.

SQL Views (View)

Convert queries into the form of views and manage them. They allow you to reuse a query once created. This significantly reduces the time spent on code development.

Conditional functions

Conditional transformation of data in queries. In a nutshell, these are functionalities similar to the If function known from Microsoft Excel. Here the CAST function will be useful

Subqueries 

Nesting queries, creating subqueries and conditions based on subqueries. The topic helps to understand the construction of multi-level queries, where one is generated based on another.

Aggregation

Counting and grouping data using aggregation functions and data cubes the use of CUBE, ROLLUP and GROUPING SETS. Here you will learn functions such as SUM(), MIN(), MAX(), COUNT(), AVG(). You will also filter record sets before WHERE grouping and after HAVING grouping.

Window functions 

Working with defined data areas. Calculations based on ordered rows. Use of the OVER clause with PARTITION BY and ORDER BY along with the aggregation functions SUM(), MIN(), MAX(), COUNT(), AVG() and the ranking and analytical functions typical of windows RANK(), ROW_NUMBER(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE().

CTE table expressions

Creation and use of pre-calculated database queries. Methods for quickly creating multi-level queries with easy-to-maintain structured code.

Database data types

Conversion and normalization of data retrieved from SQL Server. Taking advantage of the opportunities the database provides to work with specific data types such as text, numeric values and date. Limitations and possibilities provided by the ability to convert one into the other. The use of the CAST command. Handling problems and errors associated with transformations.

PIVOT/UNPIVOT type functions

Transforming data by rearranging columns (pivot table layout). Create reports that significantly reduce the amount of data retrieved. These can be PIVOT/UNPIVOT commands or CTE-based subqueries that work similarly.

Query optimization

 Ways to speed up query execution. How to make query execution faster and less taxing on the database server. You will learn a tool to help you plan the query processing steps.

DDL language

Action Queries - DML (Data Modification Language) - helps to process data directly on the server. Data modification means working with commands such as INSERT INTO - adding records, SELECT INTO creating a table from a SELECT type 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.