Advanced SQL

Home / Courses / SQL Training / Advanced SQL

Key points about this course

Duration: 2 Days*

Pre-requisites: - Introduction to SQL

Dates & Prices Enquire

Learn how to Create Tables in SQL.
Understand The SQL Server debugger.
Learn the Common Table Expressions (CTEs).

Available delivery methods for this course

Classroom

Onsite

Online

Live Virtual

Course Information

Overview

Advanced SQL Course Overview

SQL (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, which has a duration of two days, 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 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.

Outline

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

Prerequisites

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.

FAQs

What is SQL?

SQL (Structured Query Language) is the world’s most widely used database language, used by a large variety of database systems for creating queries and manipulating data. To find out more about SQL, take a look at our SQL Topic page.

What is included in the Advanced SQL training course?

On this SQL training course with The Knowledge Academy, you will be provided with: A courseware book, Manual, Certificate, and Refreshments

What time should I arrive at the venue?

Please arrive at the venue for 8.45am for a 9:00 am start.

What level of understanding of SQL should I have before attending this Advanced SQL course?

It would useful for you to have some previous programming or database experience. The Introduction to SQL training course provided by The Knowledge Academy supplies candidates with this experience and as such, is considered a prerequisite for this Advanced SQL course.