Understanding & Using SQL


This intensive course has been designed to provide a comprehensive understanding of data manipulation using Structured Query Language (SQL). While it is intended for all personnel who are going to use SQL to report from and modify relational data in a DB2 for z/OS environment, it will still be relevant to users of other RDBMSs. Throughout the course there are extensive hands-on practical exercises. These now comprise almost 40% of the course timetable, and will enable attendees to practice their newly-gained skills.

Virtual Classroom Environment dates - click to book!

UK Start Times

15 April 2024 8 July 2024 25 September 2024

What is a 'Virtual Classroom Environment'?

 

What do I need?

  • webcam
  • headphones with microphone
  • sufficient bandwidth, at least 1.5 Mb/s in each direction.

What you will learn

On successful completion of this course you will be able to:

  • code SQL SELECT statements including those using built-in functions and techniques such as joins, nested table expressions, subqueries, unions and case expressions
  • code SQL INSERT, UPDATE, DELETE and MERGE statements to modify data
  • select the best options to use with SQL to achieve the required results
  • (for Db2 for z/OS users) use EXPLAIN to evaluate SQL access path and performance
  • (for Db2 for z/OS users) understand the factors influencing the Db2 optimizer and their effect on access path.

Who Should Attend

All of those requiring a thorough understanding of coding SQL data manipulation statements. The course is not suitable for those needing to embed the SQL in application programs.

Prerequisites

Attendees should have experience of a relational database environment.

Duration

3 days

Fee (per attendee)

£1600 (ex VAT)

 

This includes free online 24/7 access to course notes.

 

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

DSQL

Contents

Introduction to Structured Query Language

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer.

Introduction to DB2 Interactive (DB2I)

DB2I primary option menu; DB2I option D - DB2I defaults; DB2I option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; DB2I option 2 - DCLGEN (Declarations Generator); DCLGEN output; DB2I option 3 - Program Preparation; DB2I option 4 - Precompile; DB2I option 5 - Bind / Rebind / Free; DB2I option 6 - Run; DB2I option 7 - DB2 commands; DB2I option 8 - DB2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF; SPUFI report; QMF report.
.

Basic SELECT Statements

Supplied database structure; Supplied table formats; Specifying table names; Using view, alias or synonym; Specifying column names; Selecting all columns; Column sequence; Naming columns; Eliminating duplicate rows; Row sequence; Row restriction; Row restriction examples; Comparing columns from the same row; Multiple conditions; The BETWEEN operator; The IN operator; The LIKE operator; The SQL ESCAPE character; Searching for apostrophes; Arithmetic in SELECT statements; Arithmetic in the WHERE clause; Naming derived columns; Using literals in the SELECT list; Special registers: The concatenation operator; Date and time columns.

SQL Built-in Functions

Column functions; GROUP BY; HAVING; Rules for GROUP BY / HAVING; Scalar functions; Data conversion functions; Date manipulation functions; Numeric manipulation functions; String manipulation functions; Handling null values; User defined functions.

Joins & Nested Table Expressions

Inner JOIN; Correlation names; A JOIN of three tables; Joining a table to itself; A three-way join of two tables; Cartesian product; Outer join; Full outer join; Full outer join using COALESCE; Left / right outer joins; Nested table expressions - examples.

Unions, Exceptions, Intersections & CASE Expressions

UNION, INTERSECT and EXCEPT; UNION and UNION ALL; UNION with named derived result columns; INTERSECT and EXCEPT; CCASE expressions; CASE expression formats; CASE expressions in functions; CASE expressions in the WHERE clause.

Subqueries

Simple (non-correlated) subquery; Simple subquery with multiple rows; Subquery with multiple columns; Quantified predicates; Correlated subquery; Tests for existence with correlated subqueries; Performance consideration; NNull values and SQL.

Table Modification Statements

The INSERT statement; Inserting all columns, single row; Inserting a subset of columns, single row; Inserting multiple rows using SELECT; Inserting multiple rows using row set; Inserting using expressions; The UPDATE statement; Updating rows; Updating & case expressions; Updating and subselects; The DELETE statement; Deleting rows; The MERGE statement; Merging rows; Referential Integrity: & INSERT, & UPDATE, & DELETE; Referential Integrity review.

The DB2 Optimiser and Access Path Selection

DB2 optimiser; Access path selection; Access paths: index usage, direct row access, matching index access, non-matching index access, table(space) scan; Input to the optimiser; Filter factors; Filter factor and clustering; Filter factor and column cardinality; Filter factor and column correlation; Filter factor and column distribution; Influencing the optimiser; Influencing the optimiser: manually adjusting statistics, using optimisation hints, modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.

DB2 EXPLAIN

EXPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (DB2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (DB2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Practical exercises

Throughout the course there are hands-on exercises covering basic DML, built-in functions, joins and unions, subselects and subqueries, table modification commands and (optionally) DB2 EXPLAIN.


What the students say

Trainer was excellent. Was able to answer any questions I had and covered everything and explained it very well.

Analyst / Programmer

NHS BSA Prescription Pricing Division

© RSM Technology 2022