SQL Advanced Functionality (Cross Platform)

Course:   SQLADB2C
Duration:   3 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/SQLADB2C.html
 
Course Summary

The course provides advanced relational data base concepts and using SQL (DDL, DML, and DCL (Overview) both for Mainframe, LUW (Linux and Windows) and SQL Server for the relevant version in use by the customer.

The course contains numerous practical exercises on all platforms to consolidate the theory sessions of the course. The exercises will use either SPUFI/ QMF, Data Studio, Control Centre or Query editor (Whichever is appropriate for the version employed by the customer).

Emphasis throughout the course is placed on using SQL efficiently.

The testing environment can be undertaken at the customers premises or using Verhoef Environment.

The course is fully documented. Each student is provided a manual.

Topics Covered In This Course

Complex Joins:

  • Inner Joins
  • LEFT and RIGHT Outer Joins
  • Full Outer Joins
  • ?Anti? Joins
  • Self Joins

Multiple Query Blocks:

  • Non-Correlated Subqueries
  • Correlated Subqueries
  • Scalar Fullselects

Table Expressions and Views:

  • Views
  • Nested Table Expressions
  • Common Table Expressions (CTE)

Set Operations:

  • Union
  • Intersect
  • EXCEPT/MINUS

Advanced Grouping:

  • GROUP BY Clause
  • HAVING Clause
  • ROLLUP
  • CUBE

Solving Complex Problems with SQL:

  • Relational Differences
  • Quota Queries
  • Relational Division
  • Recursion
  • Complex CASE Statements
  • Table Pivoting

Managing Transactions and Units of Work:

  • Data Modification Statements
  • COMMIT, ROLLBACK, and SAVEPOINT
  • When to COMMIT?
  • Checkpoint and Restart Considerations
  • Constraint Violations

Temporary Data:

  • Global Temporary Tables
  • Materialized Query Tables and Materialized Views

Access paths (Overview):

  • Index
  • EXPLAIN
  • VISUAL EXPLAIN
  • DB2EXPLN

Closing Discussion:

Differences between DB2 on z/OS, Linux and Microsoft SQL Server reviewed and discussed.

Who Should Take This Course

This course is for Users, Developers or Analysists who will be using Advanced SQL to access the data.

Recommended Prerequisites

Prospective delegates are expected to have attended the Fundamentals course

Training Style

Lecture and Hands on Workshops with the differences between DB2 for z/OS, Linux and Microsoft SQL Server reviewed and explained.

Related Courses
Code Course Title Duration Level
SQLDB2CP
SQL Fundamentals (Cross Platform)
2 Days
I
Details
DB2UDBI
DB2 for LUW (Linux, UNIX, Windows)
2 Days
I
Details
DB2LUW
DB2 LUW (Linux, UNIX, Windows) FOR PROGRAMMERS
4 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.