Training: SQL – Advanced Queries

Ref. SQL-02

Download PDF

Duration:

3

days

Exam:

Not certifying

Level:

Advanced

Funding:

Temptraining

ITTA | Trainings | IT Pro | Analytics & Data Management | SQL – Advanced Queries

Description

In this course, you will learn about advanced SQL expressions and operators, and how to use them efficiently to solve complex data extraction and insertion problems. You will learn how to deal with the needs of grouping, analyzing, sampling and retrieving hierarchical data.

Participant profiles

Developers, administrators, and advanced users who have to implement complex queries with SQL Server

Objectives

Use decisional grouping functions
Implement analytical and windowing functions
Use complex update commands with the MERGE instruction
Insert, modify and delete data with INSERT, UPDATE, DELETE statements

Prerequisites

SQL user with a few years of experience in SQL or having taken the following course : SQL – Fundamentals and Queries

Course content

Module 1 : Views

Creating a new view
Listing views in SQL Server
Removing a view
Creating an indexed view

Module 2 : Triggers

Creating a trigger in SQL Server
Creating a DDL trigger
Disabling triggers
Viewing the definition of a trigger
Listing all triggers in SQL Server
Removing triggers

Module 3 : Computed Columns

Introduction to SQL Computed Columns
Persisted computed columns
Adding computed columns to a table

Module 4 : Stored Procedures

Introduction to stored procedures
Parameters
Variables
Output Parameters

Module 5 : User Defined Functions

Introduction to SQL User Defined Functions
Scalar Functions
Table-Valued Functions
System Functions

Module 6 : Transactions and Error Handling

Introduction to SQL Transactions and Error Handling
How to handle exceptions gracefully in stored procedures
How to generate user-defined error messages
Raising an exception and transferring the execution

Module 7 : SQL Grouping Sets and Common Table Expressions

SQL Grouping Functions
Introduction to SQL Ranking Functions
What is a Common Table Expression?
Non-Recursive CTEs
Recursive CTE
Multiple CTE

Module 8 : Partitions

Introduction to SQL Partitions
Creating Table Partitions
Removing Table Partitions

Module 9 : Optimization

Describe SQL Server query plans
Explain estimated and actual query plans
Identify problematic query plans
Identify issues with a database design

Documentation

Digital courseware included

Complementary courses

Temptraining funding

ITTA is a partner of Temptraining, the continuing education fund for temporary workers. This training fund can subsidize continuing education for anyone who works for an employer subject to the Collective Work Agreement (CCT) Rental of services.