Advanced SQL (Cross Platform)

Duration: 3 Days

Method: Instructor Led, Hands-on Workshops

Price: $1875.00

Course Code: SQ1010

View Related Video

Audience

Programmers, Analysts, Database Administrators, and IT Managers who are exposed to, or are required to perform the activities associated with data stored in a relational database such as Db2, Oracle, SQL Server or PostgreSQL.

Description

Get ready to go deep with Structured Query Language (SQL) and become a power user. Whether you are being asked to solve complicated problems with SQL or you need to read and understand complex SQL that is already written, this course is for you! Come prepared to master Structured Query Language (SQL) across multiple database platforms in this fast paced and hands-on course. We begin with an in-depth discussion of joins and move quickly to more complex operations involving set processing, grouping, subqueries, table expressions, recursion and more. Each topic is reinforced with hands-on workshops.Examples and workshops are provided for the following platforms with discussion of the relevant differences: Db2 for z/OS, Db2 for Linux, Unix & Windows, Oracle, SQL Server or PostgreSQL.

Prerequisites

SQ1000 or equivalent experience

Topics

I. Complex Joins
- Inner Joins
- LEFT and RIGHT Outer Joins
- Full Outer Joins
- “Anti” Joins
- Self Joins
II. Multiple Query Blocks
- Non-correlated Subqueries
- Correlated Subqueries
- Scalar Fullselects
III. Table Expressions and Views
- Views
- Nested Table Expressions
- Common Table Expressions
IV. Set Operations
- UNION
- INTERSECT
- EXCEPT / MINUS
V. Advanced Grouping
- GROUP BY Clause
- HAVING Clause
- ROLLUP
- CUBE
VI. Solving Complex Problems with SQL
- Relational Difference
- Quota Queries
- Relational Division
- Recursion
- Complex CASE Statements
- Table Pivoting
VII. Managing Transactions and Units of Work
- Data Modification Statements
- COMMIT, ROLLBACK and SAVEPOINT
- When to COMMIT?
- Checkpoint and Restart Considerations
- Constraint Violations
VIII. Temporary Data
- Global Temporary Tables
- Materialized Query Tables & Materialized Views