UD07 - DB2 for LUW - SQL Workshop - 2 Days

Course Description

This training course teaches the delegate how to write efficient SQL statements that can be used to read, manipulate and join DB2 tables. It is aimed at those who have little or no previous SQL experience.

Pre-requisites

A working knowledge of the DB2 host environment is advantageous but not essential.

Objectives

The aim of this course is to provide the delegate with the necessary skills to perform simple and complex queries using the DB2 for LUW workbench.

On completion of this course the student will be able to:

  • describe the main objects that make up the DB2 environment
  • describe the data types available when defining DB2 columns
  • describe the importance of an Index for certain queries
  • use the Command Line Processor, Command Window or Command Centre to run queries
  • write SELECT, UPDATE, DELETE and INSERT SQL statements
  • join tables together
  • use inner joins and outer joins
  • write non-correlated and correlated subqueries
  • use DB2 functions and the CASE statement
  • understand the issues that determine SQL performance
  • write efficient queries

Environment

The course applies to DB2 running in a Linux, Unix or Windows environment.

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

GETTING STARTED WITH DB2 FOR LUW
The Relational Model
Data Representatio
Accessing The Data - Structured Query Language
SQL Structure
The Structure Of DB2 Objects
Database Definition
Default Tablespaces
Automatic Storage Databases
Database Creation using IBM Data Studio
Tablespace Organisation
Data Placement – SMS or DMS?
Sms Tablespace Example
Dms Tablespace Example
Automatic Storage Tablespaces
Table Definition
Db2 Column Types
Null Values
Implicitly Hidden Columns
Row Change Timestamps
Row Change Timestamp Insertion
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables – Comparisons
Indexes
Index Definition Example

RUNNING SQL AND COMMANDS
Connecting To The Database
Running SQL Scripts from IBM Data Studio
The DB2 Command Window and Command Line Processor
Command Line Syntax
On-Line Help
Interactive / Non-Interactive Modes
Clp Option Flag
Clp Termination
The Update Command Options Command

DATA MANIPULATION LANGUAGE
Sql - Structured Query Language
Sql Query Results
The Select Statement
The 'As' Clause
Casting between Data Types
The Where Clause
Special Operators
Not Operand
In Operand
Like Operand
Between Operand
Statements Using Nulls
Column / Aggregate Functions
Using 'Distinct'
Group By Clause
Expressions / Functions in Group By
Additional Group By Features
Group By Rollup
The Grouping Function
Group By Cube
Group By Grouping Sets
Having Clause
Order By Clause
Fetch First 'n' Rows Only Clause
Scalar Functions
Function Examples
Special Registers
Current Date
Current Time
Current Timestamp
Current Timezone
User Keyword
Date, Time And Timestamp Functions
Variable Timestamp Precision
Variable Timestamp Precision – Current Timestamp
The Values Statement
The Update Statement
Update with Subselect
The Delete Statement
The Insert Statement
The Mass Insert Statement
The Merge Statement
Merge Statement Restrictions
Select from Insert
Select from Insert Example
Select from Update
Select from Delete
The Case Statement
Table Join
Inner Joins
Outer Joins
Outer Join Syntax
Join Examples
Joining More Than 2 Tables (using Newer Syntax)
Outer Join - Where Clause
Nested Table Expression
Union, Intersect and Except
Union
Using Union with Join
Union Example within a Where Clause
Union Example within an Insert or Update
Intersect and Except
Intersect and Except Examples
Subqueries
Subqueries Using In
Subqueries using Exists
Subqueries in Select Statements
Subqueries in Case Statements
Subqueries with Fetch First and Order By
Subqueries - The Order By Order Of Clause
Subqueries - using 'All'
Subqueries - using 'Any' or 'Some'
Common Table Expressions
Common Table Expression Example
Common Table Expressions – A Complex Example
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion

Course Enquiry