SQL for iSeries 400 Programmers

Course:  ISQL
Duration:  3 Days
Level:  I
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.

« Hide The Details
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
  • Use SQL DDL (Data Definition) and DML (Data Modification) with STRSQL
  • Use the Operations Navigator interface
  • Use SQL statements to extract, display, and update data
  • Create and drop files (tables)
  • Use the SQL Query Manager
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.

« Hide The Details
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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com