This is an example of a simple banner
☀️ It's summer at ITTA! Enjoy a 10% discount until July 31st on a selection of courses 🌴 ×

Training: SQL – Advanced Queries

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

SQL Training - Advanced Queries

Discover our advanced SQL training and master the essential techniques to optimize your databases. By taking this course, you will learn how to create and manage views, use triggers, implement calculated columns and stored procedures, and develop user-defined functions.

You will also explore transactions and error handling, SQL grouping and common table expressions, partition management, and SQL performance optimization. Join us to transform your expertise through our SQL training and effectively address complex data challenges.

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.

Additional information

SQL Training – Advanced Queries: Become an Expert in SQL Server

Mastering advanced SQL queries is a crucial asset for any professional working with databases. Our “SQL – Advanced Queries” training is designed to provide you with an in-depth understanding of techniques and best practices in SQL Server. Learn how to optimize your queries, automate tasks, and efficiently manage your data.

Optimize Your Views for Enhanced Performance

Creating and managing views is fundamental to structuring your data in a logical and accessible manner. By learning to create, list, and delete views, as well as design indexed views, you can improve query performance and simplify access to crucial information.

SQL Triggers: Automation and Responsiveness

SQL triggers are powerful tools for automating responses to database modifications. Beyond creating simple triggers, you will learn to manage DDL triggers to monitor and react to changes in your database structure, temporarily disable triggers for maintenance operations, and view trigger definitions for more transparent management.

Computed Columns: Energize Your Data

Computed columns add a dynamic layer to your tables by allowing automatic calculation of derived values. Discover how to use persisted computed columns to enhance query performance by pre-calculating and storing results.

Stored Procedures: Centralization and Reusability

Using stored procedures centralizes your application logic, reducing redundancy and facilitating maintenance. Our training will guide you on how to pass and manage different types of parameters, use internal variables for cleaner code, and optimize stored procedures for maximum performance.

User-Defined Functions: Modularity and Flexibility

User-defined functions (UDF) offer increased modularity and reusability in your SQL scripts. Learn to create scalar functions for simple calculations, table-valued functions for more complex data transformations, and effectively use system functions for common operations.

Transactions and Error Handling: Reliability and Robustness

Effective management of SQL transactions and errors is essential for maintaining data integrity. Discover how to encapsulate critical operations within transactions, elegantly handle exceptions, and generate custom error messages for faster problem detection and resolution.

Common Table Expressions and Groupings: Simplify Your Queries

Common table expressions (CTE) and grouping functions simplify and optimize your complex queries. Learn to use non-recursive and recursive CTEs, as well as ranking and grouping functions, to write clearer and more efficient queries.

Table Partitioning: Efficient Large Data Management

Table partitioning is essential for efficiently managing large amounts of data. By creating and deleting table partitions, you can improve query performance and ease the maintenance of your databases.

Query Optimization: Maximum Performance

Query optimization is a key aspect of ensuring high performance in SQL Server. Learn to analyze query plans, identify problematic plans, and apply best practices in database design to resolve bottlenecks and maximize query efficiency.

Invest in Your Expertise with Our Advanced Training

Our “SQL – Advanced Queries” training is a valuable investment in your career. By mastering these advanced techniques, you will be able to transform your database management and optimize your application performance. Join us for an enriching learning experience and become an expert in SQL Server.

Prix de l'inscription
CHF 2'150.-
Inclus dans ce cours
  • Training provided by an industry expert
  • Digital documentation and materials
  • Achievement Badge

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