IB-Learning: practical, hands-on training for software developers, software testers, business analysts and project managers

Spotlight On

• Curriculums
  • Business Analysis
  • Business Skills
  • Mainframe
  • Relational Database Management Systems
  • Software Testing
  • Windows Desktop
  • WebSphere MQ
  • Project Management
  • IT Service Management
  
  
• Class Schedules
• Facility Rentals
• Special Offers!

DB2 SQL Queries Workshop - D205 ( 3 Days )

Course Outline

Abstract/Overview

This hands-on course provides the necessary knowledge to effectively utilize SQL in an interactive DB2 for z/OS UDB environment. It covers all relevant topics from an introduction to relational and DB2 concepts to an extensive discussion of both elementary and advanced SQL queries. Also included is a discussion of SQL for data maintenance. Hands-on exercises ensure an effective learning experience.

Audience - Who Should Attend?

The course is intended for End Users, Business and Systems Analysts and Developers who need to interface with DB2 databases in an interactive environment using the SQL language. This course is intended for anyone who needs to code both simple and complex SQL queries in a DB2 for z/OS UDB environment.

Prerequisite

No previous database experience is necessary, however, basic terminal/personal computer skills, basic TSO/ISPF skills, and general familiarity with the IBM mainframe environment are assumed.

Objective

• Gain a basic understanding of DB2 and the relational model.
• Become familiar with basic SQL Select statements.
• Understand how to execute SQL statements (using SPUFI).
• Gain an understanding SQL functions and their application.
• Learn when and how to code simple and correlated subselects.
• Understand how to code intersection and difference operations using the EXISTS predicate.
• Know when and how to efficiently code UNION statements.
• Understand the writing of efficient implicit and explicit Inner Joins between two or more tables.
• Know when and how to code left, right and full outer joins.
• Learn how to correctly code meaningful Cartesian joins and how to avoid invalid ones.
• Learn to write complex SQL statements involving combined subselects, unions, inner joins, outer joins, and/or nested table expressions.
• Gain a basic understanding of how to maintain DB2 data through Data Manipulation Language.

Content

Introduction to DB2

• What is a Relational Database?
• DB2 Objects
  • Database
  • Table
  • Columns, Rows, Data Types
  • Primary Key, Foreign Key

• The Base Language
  • DML, DDL, DCL
• Set Language
• SQL Environments Basic SQL Queries
• SELECT, FROM, WHERE
• Basic Predicates
• NOT, IN, BETWEEN, NULL, LIKE
• Special Registers
• The ORDER BY Clause
• Executing SQL Statements Using SPUFI  

• Column Functions
  • COUNT
  • MIN, MAX
  • SUM, AVG
  • GROUP BY Clause
• Scalar Functions
  • Character
  • Numeric
  • Date/Time
  • Miscellaneous
  • HAVING Clause

• Simple Subselect
  • Single Row
  • Multiple Row
• Correlated Subselect
  • with standard predicates
  • with EXISTS predicate

• Union, Union All
• Cartesian Joins
  • Avoiding Accidental Cartesian Joins
  • Useful Cartesian Joins
• Inner Joins
  • Implicit Inner Join
  • Explicit INNER JOIN Operator
  • Coding Local Predicates
• Outer Joins
  • Implicit OUTER JOIN
  • Explicit OUTER JOIN
  • Left, Right and Full Outer Joins
  • Coding Local Predicates

• Multi-Table Joins
• Table Expressions with Summary Tables
• Nested Subselects
• Subselects in the HAVING Clause

• INSERT
• UPDATE
• DELETE