SQL (Structured Query Language) is the world’s most widely used database language, utilized by a large variety of database systems for creating queries and manipulating data. Advanced SQL Developers are currently greatly in demand by the IT industry, and a high level of understanding of advanced SQL techniques can put individuals at an advantage when planning to enter into this field of work, or when wishing to enhance their position within their current SQL-based working environment.

This Advanced SQL training course, which has a duration of one week, builds upon the prior knowledge and understanding of SQL, as taught in the introduction to SQL course. Thus, this Advanced SQL course focusses on sharing new, complex SQL techniques, allowing the participants to understand the core innovative SQL concepts currently present in the database world. This course creates capable SQL users, confident in executing more complicated commands when viewing and managing tables and running complex queries. 

SQL techniques taught during the course include creating and managing tables, Data Manipulation Language (DML), enabling the production of Databases and Apps, and scaling emerging and dynamic technological networks. On completion of the training course, participants will have a thorough understanding of these techniques, and will have obtained the skills necessary to effectively manage a relational database.

COURSE OBJECTIVES

During this course you will learn how to do:

Data Manipulation Language (DML)

Enabling the production of Databases and Apps

Scaling emerging and dynamic technological networks.

DURATION

5 days

WHO SHOULD ATTEND

This course is for individuals who are familiar with SQL and would like to improve their skills and expand on their knowledge.

COURSE CONTENT

Stored Procedure Basics

• Pros and cons of stored procedures
• Creating stored procedures
• Three ways to execute
• System stored procedures

Variables

• Declaring variables
• SET versus SELECT
• Tricks with variables and rowsets
• So-called global variables

Parameters and Return Values

• Passing parameters
• Default values and WHERE clauses
• Output parameters
• Using RETURN

Avoiding Scalar Functions

• What are scalar functions?
• Some examples
• Disadvantages of scalar functions
• Three advantages

Testing Conditions

• IF/ELSE statement
• Using CASE where possible

Looping

• Syntax of WHILE
• Breaking out of a loop
• Basic transactions
• Beginning a transaction
• Committing/rolling back
• Deleting and updating
• Using DELETE and UPDATE
• Sys.Objects
• Dropping objects

Creating Tables

• Creating Tables in SQL
• Inserting data
• Inserting single rows
• Inserting multiple rows

Temporary Tables and Table Variables

• Using temporary tables
• Creating table variables
• Pros and cons of each approach

Table Valued Functions

• In-line table-valued functions
• Multi- Statement table-valued functions
• Limitations of user-defined functions

Derived Tables and CTEs

• Using derived tables
• Common Table Expressions (CTEs)
• Recursive CTEs

Subqueries

• The concept of a subquery
• Using ALL, ANY, and IN
• Correlated subqueries
• Using EXISTS

Cursors

• Syntax of fetching rows
• When to use (and when not to)

Error-Handling

• Using TRY/CATCH
• System error functions
• Custom error messages
• The obsolete @@ error function

Debugging

• Version differences
• The Visual Studio debugger
• The SQL Server debugger
• Debugging (breakpoints, etc.)

GENERAL NOTES

• This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
• Training manuals and additional reference materials are provided to the participants.
• Upon successful completion of this course, participants will be issued with a certificate.