G550 SQL Coding Workshop - Topical Outline

SQL Coding Workshop - Topical Outline

Introduction - Overview of DB2

Computer Exercise: Course setup

The Sample Database

Relational Operations: Select, Project, Join

Overview of DB2 SQL - the SELECT statement: WHERE clause

Basic SPUFI usage

Computer Exercise: A First Exploration of SQL

SPUFI Processing and menu options

SELECT DISTINCT

SELECT with multiple conditions

Computed values, NULL, and LIKE in SELECT statements

CASE Expressions

Computer Exercise: SELECT with Complex Conditions

SPUFI Defaults

Autocommit and explicit Commit and Rollback

FETCH FIRST "n" ROWS ONLY

Built-in Column Functions

Aggregation: GROUP BY and HAVING

Result set sequencing: ORDER BY

Computer Exercise: Data Aggregates

Special Registers

Dates, Times, and Timestamps

Date Operations

Built-in Scalar Functions

Computer Exercise: Special Registers and Scalar Functions

Joins

Name Specification

Joins with Aggregates

Outer Joins

Classic join syntax

Computer Exercise: Joins

Subqueries

Outer Joins

UNION

EXCEPT

INTERSECT

Computer Exercise: Subqueries and UNION

CREATE, DROP, and ALTER

Primary and Foreign Keys

UPDATE, DELETE, and INSERT

Computer Exercise: Creating and Changing Tables

Defining and Using Views

DB2 Security

Table Security Features

GRANT / REVOKE

DB2 Catalog Tables: SYSTABLES, SYSCOLUMNS, SYSTABAUTH, SYSVIEWS, SYSDUMMY1

Computer Exercise: Querying the Catalog Tables

Batch SQL

DB2 Query Performance

EXPLAIN

PLAN_TABLE

Performance Techniques

Index Usage

Computer Exercise: Batch SQL

G550 / 3 Days

Materials version: V3.x

Course Objectives
Course Description
Home page