Profience

DB2 11 for z/OS SQL Queries Workshop

Why take this course?

After completing this course, you will be able to
  • execute SQL statements.
  • write standard Select statements.
  • use SQL functions in your queries.
  • code simple and correlated subselects.
  • code intersection and difference operations using the EXISTS predicate.
  • efficiently code UNION statements.
  • write efficient implicit and explicit Inner Joins between two or more tables.
  • code left, right and full outer joins.
  • code meaningful Cartesian joins and avoid invalid ones.
  • maintain DB2 data using Insert, Update and Delete statements.
This course introduces IBM's strategic, relational database product, providing a overview of DB2, an introduction to relational database concepts and a comprehensive introduction to the DML component of the SQL language. Working in an interactive environment, you will gain the experience you need to INSERT, UPDATE, DELETE and SELECT using standard functions, aggregate functions, sub-selects, unions and joins.

Hands-on exercises ensure an effective learning experience.

Who should attend?

End users, Business and Systems analysts and developers who need to interface with DB2 databases in an interactive environment using the SQL language, and/or anyone who needs to code both simple and complex SQL queries in a DB2 for z/OS UDB environment.

Any prerequisites?

No previous database experience is necessary. However, basic terminal/personal computer skills would be helpful.

Dates and Fees

3 Days   ? - $ 1,047 (CAD) ? Up to 3 - $ 2,097 Up to 6 - $ 3,144   ? Plus - $ 524
 
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

What Topics are covered?

Relational Database Concepts
  • The Relational Model
  • Structure
  • Keys
  • Integrity
  • Manipulation
  • Data Models
  • Mapping business data
  • Entity types
  • Relationships
  • Attributes
DB2 Components and Features
  • DB2 Objects
  • Database
  • Storage Group
  • Tablespace
  • Table
  • Column
  • Index
  • Indexspace
  • View
  • Synonym
  • Alias
  • Object Dependency
  • Column datatypes
  • Primary key
  • Foreign key
  • Referential integrity
The z/OS DB2 Environment
  • DB2 environment
  • The DB2 subsytem
  • DB2 attachments
  • DB2 connections
  • DB2 access
  • Application development tools
  • The DB2 catalog
  • Using the catalog
  • Catalog tables
  • Security
Introduction to SQL
  • What is SQL?
  • The base language
  • Executing an SQL statement
  • Simple SELECT statement
  • SPUFI Demo
Basic SQL Queries
  • The SELECT Statement
  • The SELECT Clause
  • Selecting Columns
  • Qualifying Column Names
  • Renaming Columns
  • Selecting All Columns
  • Selecting Literals
  • Arithmetic Expression
  • Character Expression
  • Date or Time Expression
  • CASE Expression
  • The DISTINCT Keyword
  • The FROM Clause
  • The WHERE Clause
  • Basic Predicate
  • NOT Keyword
  • Linking Predicates
  • The IN Predicate
  • The BETWEEN Predicate
  • The IS NULL Predicate
  • The IS NOT NULL Predicate
  • The LIKE Predicate
  • Special Registers
  • The ORDER BY Clause
SQL Functions
  • SQL Functions
  • Column Functions
  • The COUNT Function
  • The MIN and MAX Functions
  • The SUM Function
  • The AVG Function
  • The GROUP BY Clause
  • The HAVING Clause
  • Scalar Functions
  • Character Functions
  • SUBSTR
  • LEFT, RIGHT
  • LTRIM, RTRIM, STRIP
  • REPLACE
  • LOWER/LCASE, UPPER/UCASE
  • DIGITS
  • CHAR with Decimal Argument
  • Supported Date and Time Formats
  • CHAR with Date Argument
  • Number Functions
  • INTEGER
  • DECIMAL
  • ROUND, TRUNC
  • MOD, POWER, SQRT
  • RAND
  • Date Functions
  • Time Specific Functions
  • DAYS
  • DATE and TIME
  • Miscellaneous Functions
  • LENGTH
  • COALESCE/VALUE
  • NULLIF
Subselects
  • SELECT within SELECT
  • Subselect Returning a Single Value
  • Subselect Returning Multiple Values
  • Correlated Subselect
  • EXISTS/NOT EXISTS
  • Intersection
  • Difference
Union and Joins
  • UNION
  • UNION Considerations
  • UNION Example
  • CARTESIAN JOIN
  • INNER JOIN
  • Example
  • Using the Inner Join Operator
  • With Local Predicates
  • OUTER JOIN
  • Using the Left Outer Join Operator
  • Using the Right Outer Join Operator
  • Using the Full Outer Join Operator
  • With Local Predicates
Data Maintenance
  • INSERT
    • Single Row Insert
    • Mass Insert
  • UPDATE
    • Updating All Rows
    • Updating Selected Rows
  • DELETE
    • Deleting All Rows
    • Deleting Selected Rows

Competency Assessment and Certificate

At the request of the person booking, a technical competency assessment will be conducted with the student.
This assessment will be a combination of written and multiple choice questions as well as specific coding tests.
Upon a minimum pass rate of 85%, students will receive a Certificate of Competency instead of the standard certificate of course completion.
Please Contact us for more details or specific needs.