About this workshop
This practical workshop is open for developers with or without SQL experience.
Theoretical explications are alternated with practical exercises using Access Client Solutions on an example database and eventually also on your own IBM i environment, this allowing you to use the data you are familiar with for exercises.
You will learn how to run SQL scripts via Access Client Solutions as well as best practices for setting options and connection properties.
The first exercises cover the SQL basics, allowing you to familiarize with the Db2 for i SQL syntax.
Then the exercises gradually build up to cover the complete syntax of the SELECT statement including commonly used SQL functions, joining, grouping, sub selects and Common Table Expressions.
Examples are also provided for the INSERT, UPDATE and DELETE statements and for creating tables, views and indexes.
After a chapter about best practices for SQL performance, you learn how to use the database part of Access Client Solutions, as well as the functions in the SQL Performance Center allowing to check and analyze the SQL performance and the Index Advice.
Skills to be learned
- The SQL basics and commonly used SQL functions
- Get familiar with the Db2 for i SQL syntax
- Translate a data-related question into an SQL SELECT statement.
- Use of joining, grouping, sub selects and Common Table Expressions
- Build and test complex SQL SELECT statements
- Use of the INSERT, UPDATE and DELETE statements
- Db2 for i best practices for creating tables, views and indexes
- Change connection parameters and run SQL scripts via Access Client Solutions
- Learn how to use the database part of Access Client Solutions
- Analyze SQL performance and use the SQL performance center
This workshop covers the following topics
- Part 1: Introduction
- Part 2: IBM i Access Client Solutions
- Part 3: SQL basic syntax
- Part 4: CL commands and Database Metadata
- Part 5: SELECT statement (+ exercises)
- SELECT statement – Basics
- WHERE and ORDER BY clause
- Single-row functions
- Joining multiple tables
- Grouping functions
- Sub selects and Common Table Expressions
- Part 6: Some advanced SQL functions
- Part 7: SQL performance and best practices
- Part 8: INSERT, UPDATE and DELETE rows
- Part 9: Create tables, views and indexes
- Part 10: More SQL possibilities
- Part 11: References
- Handouts and presentation containing screenshots and detailed examples
- .sql file allowing to create an example database for the exercises
- .sql files with examples, exercises and solutions
- Any developers
- Recent version of Access Client Solutions installed on your PC
- Connection available to your IBM i environment
- 3 days