DB2 SQL Workshop for Experienced Users

Description

At Sixe Engineering we have been providing official IBM training around the world for over 15 years. Get the best training from our specialists worldwide.

Course details

Target Audience

This intermediate course is tailored for experienced SQL end users, application programmers, database administrators, and user support staff who seek advanced SQL knowledge.

Prerequisites

Participants should have prior experience in:

• Coding and executing basic SQL statements.

These skills can be acquired by attending the SQL Workshop (CE120) or through equivalent experience.

Instructors

The majority of IBM courses are conducted by our engineers, ensuring the highest quality of instruction. We complement training with proprietary materials and laboratories, drawing from our extensive experience in deployments, migrations, and course delivery over the years.

Added Value

Our courses are meticulously role-oriented. For instance, the technological proficiency requirements vary significantly between developer teams and those responsible for deploying and managing infrastructure. We consider the level of prior experience seriously, focusing not just on routine commands and tasks but on real-world problem-solving specific to each team. Our training imparts the necessary knowledge, competencies, and skills tailored to each project. Furthermore, our documentation is based on the latest version of each product.

Agenda and Course Syllabus

Introduction

• Purpose of SELECT Statement Clauses: Identify the purpose of various clauses in the SELECT statement.
• IBM DB2 Platforms: Describe key differences among IBM DB2 platforms.
• OLAP Features in DB2: Use OLAP features like GROUPING functions (CUBE and ROLLUP), and RANK, DENSE_RANK, and ROW_NUMBER functions.

Create Objects

• Table and View Creation: Code statements to create tables and views.
• Table Alteration: Alter tables, create indexes, and implement referential integrity (RI).
• Triggers and Constraints: Define triggers and check constraints, including INSTEAD OF triggers.
• Impact of RI: Identify impacts and advantages of referential integrity, especially delete rules and considerations for triggers and check constraints.

Joins

• Data Retrieval: Retrieve data from multiple tables using inner and outer joins.
• Outer Joins: Use LEFT, RIGHT, and FULL outer joins.
• ANTI JOINS: Utilize ANTI JOINS.
• Self-Joins: Join a table to itself.
• Unions and Intercepts: Use UNION, UNION ALL, EXCEPT, and INTERCEPT.

CASE, CAST, Summary Tables, and Materialized Query Tables

• CASE Expressions: Identify and code CASE expressions in SELECT lists and WHERE clauses.
• CAST Specifications: Recognize when to use CAST specifications.
• Summary and Temporary Tables: Identify advantages of using Summary (Materialized Query) Tables and Temporary tables.
• Materialized Query Tables (MQTs): Identify when and how to use MQTs and Temporary tables.

Using Subqueries

• Subqueries: Code subqueries using ALL, ANY/SOME, and EXISTS keywords.
• Correlated Subqueries: Code correlated subqueries.
• Subquery Selection: Choose the appropriate type of subquery for each case.

Scalar Functions

• Function Manipulation: Extend knowledge of scalar functions to manipulate arithmetic data, date values, and character data.
• Function Examples: Functions covered include SUBSTR, POSSTR, COALESCE/VALUE, DECIMAL, ROUND, DIGITS, CHAR, DATE, and TIME.

Table Expressions and Recursive SQL

• Table Expressions: Understand reasons for using table expressions and recursive SQL.
• Common Table Expressions: Use nested and common table expressions.
• Views vs. Table Expressions: Identify differences between views and table expressions.
• Recursive SQL: Code and control the depth of recursion in recursive SQL.

User-Defined Types/Functions and Performance

• User-Defined Elements: Describe concepts behind User-Defined Types (UDTs), User-Defined Functions (UDFs), and Stored Procedures.
• Query Optimization: Predict when queries will use indexes for better performance.
• Predicate Processing: Understand concepts of predicate processing.
• Index Structure: Learn introductory concepts about index structure and general best practices for performance optimization.

This comprehensive agenda ensures participants are well-equipped with advanced SQL skills tailored to their specific roles and project requirements.

Do you need to adapt this syllabus to your needs? Are you interested in other courses?  Ask us without obligation.

Locations for on-site delivery

• Austria: Vienna
• Belgium: Brussels, Ghent
• Denmark: Cophenhagen
• Estonia: Tallinn
• Finland: Helsinki
• France: Paris, Marseille, Lyon
• Germany: Berlin, Munich, Cologne, Hamburg
• Greece: Athens, Thessaloniki
• Italy: Rome
• Louxemburg: Louxembourg (city)
• Netherlands: Amsterdam
• Norway: Oslo
• Portugal: Lisbon, Braga, Porto, Coimbra
• Slovakia: Bratislava
• Slovenia: Bratislava
• Spain: Madrid, Sevilla, Valencia, Barcelona, Bilbao, Málaga
• Sweden: Stockholm
• Turkey: Ankara
• United Kingdom: London