SQL Server training -  Advanced SQL

SQL Training

Advanced SQL (2 days)

£725 + VAT for most venues (£850 + VAT for London)

If you already know how to write SELECT statements in SQL, maybe it's time to find out how to program in SQL! Learn how to create stored procedures (including passing parameters to them), write your own user-defined functions, work with temporary tables and table variables and much more.

Course details

  • Locations for Advanced SQL coursesLocations:Manchester, London
  • Next Advanced SQL course locationNext course:Thu 21 September 2017
  • Advanced SQL course timesTime:09.30-16.30

Course Contents

Stored procedures

  • Training topicCreating stored procedures
  • Training topicExecuting stored procedures
  • Training topicSystem stored procedures

Variables

  • Training topicDeclaring variables
  • Training topicSET versus SELECT
  • Training topicTricks with variables
  • Training topicSo-called global variables

Parameters and return values

  • Training topicPassing parameters
  • Training topicDefault values / WHERE clauses
  • Training topicOutput parameters
  • Training topicUsing RETURN

Scalar functions

  • Training topicWhat they are
  • Training topicWriting user-defined functions
  • Training topicWorked examples
  • Training topicPros and cons of scalar functions

Testing conditions

  • Training topicIF / ELSE statement
  • Training topicUsing CASE where possible

Looping

  • Training topicSyntax of WHILE
  • Training topicBreaking out of a loop

Error-handling

  • Training topicUsing TRY / CATCH
  • Training topicSystem error functions
  • Training topicCustom error messages

Deleting using SQL

  • Training topicWays to drop tables
  • Training topicDELETE versus TRUNCATE

Updating data in SQL

  • Training topicThe UPDATE statement
  • Training topicUpdating using joins

Inserting data

  • Training topicMaking tables (SELECT INTO)
  • Training topicAppending data (INSERT INTO)
  • Training topicInserting individual rows

Creating tables

  • Training topicCreating tables in SQL
  • Training topicPrimary keys and indexes
  • Training topicAdding relationships

Transactions

  • Training topicBeginning a transaction
  • Training topicCommitting / rolling back

Temporary tables and table variables

  • Training topicScope (local versus global)
  • Training topicUsing temporary tables
  • Training topicCreating table variables
  • Training topicPros and cons of each approach

Table-valued functions

  • Training topicIn-line table-valued functions
  • Training topicMult-statement table-valued functions

Derived tables and CTEs

  • Training topicUsing derived tables
  • Training topicCommon Table Expressions (CTEs)
  • Training topicMultiple CTEs in a single query

Covered if time allows If time

Cursors

  • Training topicSyntax of fetching rows
  • Training topicWhy not to use cursors!

Debugging

  • Training topicDebugging queries and procedures
  • Training topicSetting breakpoints

Covered if time allows If time

Dynamic SQL

  • Training topicBuilding up dynamic SQL
  • Training topicExecuting dynamic SQL
  • Training topicDisadvantages of dynamic SQL

Covered if time allows If time

Pivots

  • Training topicAssembling data for pivoting
  • Training topicUsing PIVOT
  • Training topicDynamic column headers

Covered if time allows If time

Triggers

  • Training topicInsert, update and delete triggers
  • Training topicUsing the generated tables (eg INSERTED)
  • Training topicUsing INSTEAD OF

Why Choose Us?

View full list of benefits View full list of benefits

Sample customer feedback

"Excellent course, well structured, relative and flows. (Have used Wise Owl before at a former company.)"

Andy Johnston (MW Hargreaves)

"I believe I have everything I need to get going on my own at work. Thanks!"

Scott Wadeson (Salford Royal NHS Foundation Trust)

"The content is fantastic and the small class sizes allow for more personal learning experience. Location and price are good."

Lisa Sorrell (University of Leeds)

"Trainer was extremely helpful, the overall presentation and timescale of the content was great. The trainer was very enthusiastic and was willing to help individuals quickly if problems arose."

Connel Watson (PricewaterhouseCoopers Services Ltd.)

"Very informative. Excellent venue and course material."

Sophie Jones (The University of Manchester)

There are lots more where these came from (or just refresh this page to change the quotes shown).

You should only attend this course if you have either attended our two-day introduction to SQL course or if you are comfortable creating queries using SQL.  The course shows how to write the dialect of SQL used by Microsoft (called T-SQL), using SQL Server Management Studio as a development environment.

Related Courses

Introduction to SQL

Basic

2 day course

Reporting Services RECOMMENDED

Introduction

2 day course

Introduction to SSIS

Introduction

2 day course

Other T-SQL programming training resources

SQL exercises

SQL exercises

Our advanced SQL exercises are based on simple, easy-to-understand databases, as you can see for yourself!

View SQL exercises

SQL videos

SQL videos

Although we firmly believe people learn best from people, we've also published a range of SQL tutorial videos online.

View SQL videos

SQL skills assessment

SQL skills assessment

You can see how well you know SQL relative to others by taking one of our skills assessment tests online.

View SQL skills assessment tests

SQL blogs

SQL blogs

Take a few minutes to see what blogs we've published on SQL on our website.

View SQL blogs

This page has 0 threads Add post