Advanced SQL Training

Advanced SQL Course Overview

(Structured Query Language) is the world’s most widely used database language, utilised 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 builds upon the prior knowledge and understanding of SQL. It focuses on sharing new, complex SQL techniques, allowing the delegates 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, delegates will have a thorough understanding of these techniques, and will have obtained the skills necessary to effectively manage a relational database.

Advanced SQL Course Outline

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.)

This course is for anybody looking to improve their SQL knowledge to a high level.

Who is this SQL Training course for?

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

Prerequisites

It is required that delegates have a prior understanding of SQL, and so it is necessary for delegates to have attended the Introduction to SQL training course before enrolling on this Advanced SQL course.

Advanced SQL skills can help learners progress their careers in database design and management, and data analysis.