Audience
Anyone who will be using SQL to extract data from Db2.
Prerequisites
The participants should have no prior knowledge of the SQL language.
Duration
2 days. Hands on.
This course is available on site only. Please call for details.
Course Objectives
This course will provide students with SQL skills needed to build simple or complex interactive queries against data which is stored in databases. The course includes an introduction to the structure and function of the database, and a comprehensive examination of the SQL language, including a progressive series of hands-on workshops to provide the student with experience in using the language. After taking this course the students should be able to utilise application programs to embed SQL statements within Db2.
Course Content
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