SQL: Accessing Data, Database Design, Embedded SQL Programming

This class combines the content from the Accessing Data Using SQL, Database Design Using SQL, and RPG IV Programming with Embedded SQL classes into one comprehensive week of SQL training.

SQL is playing an ever-increasing IBM i role in enterprise database design, supplanting legacy DDS. This class shows you how to use industry-standard SQL Data Definition Language to build an IBM i database. In addition, it covers retrieving, analyzing, manipulating, and modifying your data using SQL Data Manipulation Language. The class will also cover embedding SQL statements into your RPG IV programs to combine the best of SQL and RPG IV.

Get a Price Quote

 

Recommended schedule: 4 day lab workshop


Who Should Attend
This class is for existing RPG IV programmers who want to understand SQL Data Definition Language, Data Manipulation Language, and embedded SQL programming concepts in an RPG IV environment.


Prerequisites
In order to successfully complete this course, students should have completed the following courses, or have equivalent knowledge/experience:

  • IBM i Concepts and Programming Tools
  • RPG IV and the Integrated Language Environment

What You Will Accomplish
At the end of the course, students will be able to:

  • Use interactive SQL to retrieve data from table
  • Summarize data with SQL column functions
  • Create complex queries which join files and multiple SELECT statements
  • Nest SELECT statements within other SELECT statements
  • Create and maintain a database using SQL statements
  • Incorporate SQL statements into RPG IV programs for simple record selection
  • Use SQL to retrieve sets of records into an RPG IV program
  • Use SQL to perform mass record updates in an RPG IV program
  • Build SQL queries dynamically (“on the fly”) in an RPG IV program

What You Will Receive
Each student receives:

  • Custom printed handout, containing class notes
  • Completion certificate

Course Outline
The class covers these topics:

  • SQL Overview
  • The SELECT Statement
  • Built-in Functions
  • Summarizing Data
  • Joining Files
  • Joining SELECT Statements
  • Using Subselects
  • INSERT, UPDATE, and DELETE Statements
  • Creating Collections
  • Creating Tables
  • Creating Views and Indexes
  • Managing Privileges
  • Introduction to SQL Catalog
  • Basic Elements of Embedded SQL
  • Free format RPG IV with Embedded SQL
  • Static vs Dynamic SQL
  • Using Host Variables, Structures
  • Compiling Embedded SQL
  • Error Handling
  • SQL Cursors and Fetch
  • Dynamic SQL
  • Introduction to Stored Procedures
  • Embedded SQL and the Integrated Language Environment

Hands-on Labs

  • The SELECT Statement
  • The CASE Statement
  • Built-in Functions
  • Summarizing Data
  • Joining Files
  • Joining SELECT Statements
  • Using Subselects
  • INSERT/UPDATE/DELETE Statements
  • Creating Collections, Tables, Views, Indexes
  • Using SELECT INTO
  • Using Static Embedded SQL
  • Using Serial Cursors
  • Single- and Multi-row Updates
  • Using Dynamic Embedded SQL
  • Using Positioned Update and Scrollable Cursors