Training: SQL – Advanced Queries

Ref. SQL-02
Duration:
3
 days
Exam:
Not certifying
Level:
Advanced

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

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.
Prix de l'inscription
CHF 2'150.-
Inclus dans ce cours
  • Training provided by an industry expert
  • Digital documentation and materials
  • Achievement Badge

mer22Mai(Mai 22)09:00ven24(Mai 24)17:00VirtuelFeaturedVirtual Etiquettes de sessionSQL-02

mer22Mai(Mai 22)09:00ven24(Mai 24)17:00Genève, Route des Jeunes 33B, 1227 CarougeFeatured Etiquettes de sessionSQL-02

lun01juil09:00mer03(juil 3)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionSQL-02

lun01juil09:00mer03(juil 3)17:00VirtuelVirtual Etiquettes de sessionSQL-02

lun05Aoû(Aoû 5)09:00mer07(Aoû 7)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionSQL-02

lun05Aoû(Aoû 5)09:00mer07(Aoû 7)17:00VirtuelVirtual Etiquettes de sessionSQL-02

lun09Sep(Sep 9)09:00mer11(Sep 11)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionSQL-02

lun09Sep(Sep 9)09:00mer11(Sep 11)17:00VirtuelVirtual Etiquettes de sessionSQL-02

lun14Oct(Oct 14)09:00mer16(Oct 16)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionSQL-02

lun14Oct(Oct 14)09:00mer16(Oct 16)17:00VirtuelVirtual Etiquettes de sessionSQL-02

lun18Nov(Nov 18)09:00mer20(Nov 20)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionSQL-02

lun18Nov(Nov 18)09:00mer20(Nov 20)17:00VirtuelVirtual Etiquettes de sessionSQL-02

lun23Déc(Déc 23)09:00mer25(Déc 25)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionSQL-02

lun23Déc(Déc 23)09:00mer25(Déc 25)17:00VirtuelVirtual Etiquettes de sessionSQL-02

Horaires d’ouverture

Du lundi au vendredi, de 8:30 à 18:00.

Contactez-nous

Votre demande

Opening hours

Monday to Friday, from 8:30 am to 06:00 pm.

Contact us

Your request

X
X
X
X