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.
Description | Days | Price (ex vat) | ||
---|---|---|---|---|
Advanced SQL * |
2 | ZAR | USD | |
R 7,500 | $ 650 |
|
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