iSeries (AS/400) Accesing DB2 with SQL

Introduction

This course is intended for development staff needing to view, create or modify data quickly and simply. Also, support staff who need to manipulate data within database files and technical users requiring a more direct interface into database files than that provided by Query/400. You will learn how to start and configure the basic SQL environment and use the basic commands to view and change selected data within a database.

Who should attend

Power users and Application development staff needing to view, create or modify test data quickly and simply. Application support staff who need to manipulate data within database files. Technical users of Query/400 requiring a more direct interface into database files.

Pre-requisites

There are no specific prerequisites but delegates should have basic keyboards skills.

Course content

• Interactive SQL
• Database Terminology such as:
  PF, LF, external definition, table, key, collection, schema, row, field
• Changing interactive SQL session attributes
• Entering, repeating and editing SQL statements
• Using Navigator Run SQL Script
  
  
• SELECT...FROM...WHEN
• Select all or some columns from a table (SELECT... FROM)
• Removing duplicates from result set - DISTINCT
• Create new columns using arithmetic and scalar functions such as : SUBSTRING, LEFT, RIGHT,
  CONCAT, TRIM, UCASE, LCASE, DECIMAL, INT, DIGITS
  
  
• GROUP BY... HAVING...Sub Select
• Use Column Functions to find summary values for result set such as: COUNT, MAX, MIN AVG SUM
• Summarise data from a table into a result set - GROUP BY
• Extract specific rows from a summarised result set - HAVING
• Select rows from a table based on a secondary select (sub-select)
• Use correlation names & correlated sub-select
• Making row selections with IN, ALL, ANY
  
  
• UNIONS and JOINS
• Understand the different types of joins that may be performed
• Join at least 2 tables with INNER, OUTER or EXCEPTION joins
• Understand the concepts of multi-table joins
• Understand UNION and know when to use it
  
  
• UPDATE... DELETE... INSERT
• Insert a single row into a table
• Insert multiple rows into a table
• Update selected rows in a table
• Delete selected rows from a table
• Use the CASE function to condition updates
  
  

A course manual with supporting documentation is provided. The course includes practical sessions to use SQL statements against database files on the iSeries.

Follow on Courses : Delegates should consider the Power RPG with SQL (ISSQE) course for RPG Developers

Schedule