Advanced SQL Training

Advanced SQL training is a hands-on training course for database administrators, business analysts or data scientists that need to extract, transform and load data into data stored for use in analytics or business intelligence applications.

Advanced SQL Training for Business Analysts and Database Admins

Advanced SQL, especially for stored procedures or functions, differ significantly between different relational database implementations. This course focuses on the syntax for MySQL or Postgresql depending on the needs of the students.  Indexing, constraints and SQL DDL such as create table, alter table are more uniformly implemented across the various database vendor implementations.


Advanced SQL Training Course Details
Description Days Price (ex vat)

Advanced SQL *

 2 ZAR USD
R 7,500 $ 650
  • Includes lunch, tea and training material.
  • Start time is 9:00am for 9:30am

Advanced SQL Training Course Contents

Stored procedures & functions

  • Pros and cons
  • Creating stored procedures
  • System stored procedures & functions

Variables

  • Declaring variables
  • Setting variables
  • Using variables

Parameters and return values

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

Scalar functions

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

Testing conditions

  • IF / ELSE statement
  • Using CASE where possible

Looping

  • Syntax of WHILE
  • Breaking out of a loop

Transactions

  • Beginning a transaction
  • Committing / rolling back

Deleting and updating

  • Using DELETE and UPDATE
  • Dropping objects

Creating tables

  • Creating tables in SQL
  • Primary keys and indexes
  • Setting constraints
  • Creating from existing data

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

Tempoaray tables and CTEs

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

Subqueries

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

Cursors

  • Syntax of fetching rows
  • When not to use

Error-handling

  • Exception handling
  • System error functions
  • Custom error messages


Dynamic SQL

  • Building up dynamic SQL
  • Executing dynamic SQL
  • Pros and cons

Pivots

  • Using the tablefuncs
  • crosstab
  • connectedby