Advanced SQL

Course

In London and Manchester

£ 750 + VAT

*Indicative price

Original amount in GBP:

£ 750

Description

  • Typology

    Workshop

  • Level

    Advanced

  • Location

    At 2 venues

  • Duration

    2 Days

  • Start

    Different dates available

Description

If you are comfortable creating queries in SQL, maybe it is time to move on to learn more advanced programming topics? This two day course assumes that you know how to query data using SQL, and shows you how to progam in SQL Server. Learn how to write stored procedures; learn the differences between temporary tables, table variables, common table expressions, derived tables and the two types of table-valued function, and when to use each tool; find out what cursors are, and when you should avoid them; and learn about debugging and error trapping.
Suitable for: This Advanced SQL course is aimed primarily at people working in business and public sector organisations

Important information

Documents

  • Advanced SQL course outline.pdf

Facilities

Location

Starts

London
See map
Holland House 1-4 Bury Street, EC3A 5AW

Starts

Different dates availableNow taking bookings
Manchester (Lancashire)
See map
Barlow House, Minshull Street, M1 3DZ

Starts

Different dates availableNow taking bookings

To take into account

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.

Questions & Answers

Ask a question and other users will answer you

Who do you want to respond to your question/enquiry?

We will only publish your name and question

Subjects

  • Stored procedures
  • Variables
  • Parameters and return variables
  • Scalar functions
  • Testing conditions
  • Looping
  • Error Handling
  • Deleting using SQL
  • Updating data in SQL
  • Inserting Data
  • Creating Tables
  • Transactions
  • Temporary tables and table variables
  • Table-valued functions
  • Derived tables and CTEs
  • Debugging

Teachers and trainers (4)

Andrew Gould

Andrew Gould

Senior Trainer

Andy Brown

Andy Brown

Microsoft Trainer & Programmer

David Wakefield

David Wakefield

Director / Programmer / Trainer

Sam Lowrie

Sam Lowrie

Trainer

Course programme

Stored procedures
  • Creating stored procedures
  • Executing stored procedures
  • System stored procedures
Variables
  • Declaring variables
  • SET versus SELECT
  • Tricks with variables
  • So-called global variables
Parameters and return values
  • Passing parameters
  • Default values / WHERE clauses
  • Output parameters
  • Using RETURN
Scalar functions
  • What they are
  • Writing user-defined functions
  • Worked examples
  • Pros and cons of scalar functions
Testing conditions
  • IF / ELSE statement
  • Using CASE where possible
Looping
  • Syntax of WHILE
  • Breaking out of a loop
Error-handling
  • Using TRY / CATCH
  • System error functions
  • Custom error messages
Deleting using SQL
  • Ways to drop tables
  • DELETE versus TRUNCATE
Updating data in SQL
  • The UPDATE statement
  • Updating using joins
Inserting data
  • Making tables (SELECT INTO)
  • Appending data (INSERT INTO)
  • Inserting individual rows
Creating tables
  • Creating tables in SQL
  • Primary keys and indexes
  • Adding relationships
Transactions
  • Beginning a transaction
  • Committing / rolling back
Temporary tables and table variables
  • Scope (local versus global)
  • Using temporary tables
  • Creating table variables
  • Pros and cons of each approach
Table-valued functions
  • In-line table-valued functions
  • Mult-statement table-valued functions
Derived tables and CTEs
  • Using derived tables
  • Common Table Expressions (CTEs)
  • Multiple CTEs in a single query

If time

Cursors
  • Syntax of fetching rows
  • Why not to use cursors!
Debugging
  • Debugging queries and procedures
  • Setting breakpoints

If time

Dynamic SQL
  • Building up dynamic SQL
  • Executing dynamic SQL
  • Disadvantages of dynamic SQL

If time

Pivots
  • Assembling data for pivoting
  • Using PIVOT
  • Dynamic column headers

If time

Triggers
  • Insert, update and delete triggers
  • Using the generated tables (eg INSERTED)
  • Using INSTEAD OF

Additional information

£750 + VAT for most venues (£895 + VAT for London)

Advanced SQL

£ 750 + VAT

*Indicative price

Original amount in GBP:

£ 750