SQL Advanced Functionality (Cross Platform)

Course: SQLADB2C

Duration: 3 Days

Level: II

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.

« Hide The Details

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.

« Hide The Details

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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]

Privacy Notice