Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

SQL for iSeries 400 Programmers

Course:   ISQL
Duration:   3 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/ISQL.html
 
Course Summary

AS/400 and iSeries users who wish to access data and use the facilities provided by SQL. Programmers who need a solid understanding of the SQL language, and how to use the language effectively and efficiently.

Topics Covered In This Course

INTRODUCTION TO RELATIONAL DATABASE SYSTEMS

  • The origin and history of IBM relational products
  • Terminology pertinent to relational environments
  • Overview of DB2/400 components
  • How data is physically stored in a relational DBMS
  • The logical organization of the database
  • How the system retrieves data from the database
  • Data relationships
  • SQL syntax rules
  • Using STRSQL, and how to build and execute interactive SQL queries

USING SQL FOR BASIC DATA RETRIEVAL

  • Syntax of SELECT statements
  • Tailoring a Select List to choose specific columns
  • Building expressions
  • Using the WHERE clause to choose specific rows
  • How to eliminate duplicate data
  • Coping with NULL values during data retrieval
  • Using DISTINCT, ANY and ALL
  • Rules for logical comparison of data
  • Boolean comparison, using NOT, AND and OR
  • Rules for arithmetic comparison of data
  • Using IN and NOT IN to build include lists
  • Using BETWEEN and NOT BETWEEN to define ranges
  • Pattern-matching and building search arguments, using LIKE, NOT LIKE, and wildcard values
  • Sorting output, using the ORDER BY clause
  • The EXISTS and NOT EXISTS predicates
  • SRTSQL and OPS NAV
  • SQL language features to avoid!

USING SQL FOR ADVANCED DATA RETRIEVAL

  • Using Aggregate Functions, including AVG, MIN, MAX, COUNT (*), COUNT (DISTINCT), and SUM
  • Using Scalar Functions, including data-type conversion, character string manipulation, and date-time functions
  • GROUP BY and HAVING clauses
  • UNION, INTERSECT and EXCEPT keywords
  • Joining tables properly and efficiently
  • Uses for Sub-queries
  • Uses for Views

USING SQL FOR DATA MODIFICATION

  • The INSERT statement, including examination of statement parameters and referential integrity implications
  • The UPDATE statement, including examination of statement parameters and referential integrity implications
  • The DELETE statement, including examination of statement parameters and referential integrity implications
  • The COMMIT and ROLLBACK statements and database recovery
What You Can Expect
Recommended Prerequisites

Working familiarity with the iSeries 400 environment and some general knowledge about relational databases is desirable. Users or programmers who will use the Query/400 environment are the target audience.

Training Style

Instructor-led lectures, demonstrations, and progressive hands-on workshops.

Related Courses
Code Course Title Duration Level
RPGSQL
SQL – Using Embedded SQL in RPG IV
2 Days
II
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.