SQL Querying with IBM DB2 (2 days)
ISQ-75: Fixed price IBM DB2 training delivered on your site at any UK location.
COURSE OUTLINE |
pricing | more IBM DB2 training |
This SQL training course is for those who need to extract data specifically from IBM DB2 relational databases. While attendees gain much experience designing complex SQL queries using standard IBM DB2 functions, joins and subqueries, SQL action statements which facilitate data or database modification are not covered. The course is highly practical in nature and the focus throughout is on coding SQL (with IBM DB2 variances) by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are provided on a free USB pen drive to take away.
Suggested Prerequisites
No prior SQL, IBM DB2 or relational database experience is assumed.
An Overview of IBM DB2 Relational Databases
- The Role of the IBM DB2 Database Server
- Using a Client to interact with the IBM DB2 Server
- Some Available Clients for IBM DB2
- Databases, Tables, Rows and Columns
- Primary Keys and Foreign Keys Explained
- Introducing Data Types: Character, Numeric, Date and Time
Introducing SQL for IBM DB2
- Creating and Editing SQL
- About Statements, Batches and Scripts
- Executing and Parsing SQL Scripts
- SQL Syntax and The Rules of SQL
- About Keywords, Identifiers. Operators, Whitespace and Case
- About the Semi Colon
- SQL Conventions and Good Practice
Retrieving Data with IBM DB2 SQL: First Steps
- Introducing Queries: The SELECT Statement
- The Clauses of the SELECT Statement
- About Optional Clauses and Mandatory Clauses
- Using FROM to Specify the Source Table(s)
- Retrieving Entire Tables
- Retrieving Specific Columns
- The Importance of Clause Order
- How to Build Successful Queries
- Types of Output: About the Result Set
- Using Column Aliases to Rename Columns
- Performing Calculations
- Using Numeric and String Operators to Create Derived Output
- Ways of Limiting the Output
- Using ORDER BY to Sort the Output
- Ways of Working: Some Tips
Using WHERE to Filter Results
- Working with Comparison Operators (=, >= etc)
- Numeric and String Based Filtering
- Filtering Based on Calculations
- Eliminating Duplicate Results with DISTINCT
- Working with Execution Order
- Column Aliases: Where You Can and Cannot Use Them
- Extending Filters with AND and OR
- Solving AND/OR Difficulties with Brackets
- Excluding Results with NOT: Some Tips
- Range Filtering using BETWEEN and IN
- NULL and its Implications Explained
- Catering for NULL
- Matching Patterns with LIKE
Getting Results From Multiple Tables
- Qualifying Column Names
- Joins Explained
- The Different Types of Joins
- Creating an Inner Join: WHERE Syntax
- Creating an Inner Join: INNER JOIN Syntax
- Table Aliases: The Need
- Working with Self Joins
- Outer Joins: An Example
- How to Simplify Joins: An Approach
Using Standard IBM DB2 Functions
- How to Use Standard IBM DB2 Functions to Modify Results
- How to Find the Right Function
- Mathematical, String and Conversion Functions
- Functions for Modifying and Calculating Dates
- Formatting Numbers to Two Decimal Places
- Replacing NULL with a Specific Value
- Using Standard IBM DB2 Functions in WHERE
- Using CASE to Specify Output Conditions
- IBM DB2 Functions for Manipulating Dates
Grouping and Summarizing Results
- The difference Between Tabular and Scalar Results
- Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
- The Way Aggregate Functions Work
- Where to Use and Where Not to Use Aggregate Functions
- Using GROUP BY to Group Results
- The Need for HAVING: Filtering the Result Table
Working with Subqueries
- Subqueries Explained
- Where you can Use Subqueries
- How to Successfully Construct Subqueries
- Subqueries for Filtering
- Subqueries to Create Derived Columns
Working with Views
- Views Explained
- Advantages of Views
- How to use Views to Simplify your Work
- Creating Views
- Dropping Views
"A great couple of days."
MI Development Consultant
Aviva
(Norwich)