Course Description
This SQL Fundamentals for DB2 online training course is designed for the database professional who wants to get more "bang for the buck" out of DB2. This DB2 course presents basic and intermediate SQL syntax in an easy-to-understand format, demystifying even some of the more complex features. It will enable you to return to work much better prepared to avail yourself of the power of the DB2 engine.
Once you have completed SQL Fundamentals for DB2 the next course in this series is Advanced SQL for DB2. The Advanced SQL for DB2 course will enhance your knowledge by presenting advanced SQL syntax formats and discussing the most complex new features. Click here to learn more about Advance SQL for DB2.
In This Course You Will Learn:
- Use relational database concepts and terminology fluently, particularly that of the DB2 family of database management systems
- Identify the various types and components of Structured Query Language (SQL)
- Code simple to complex SELECT, INSERT, UPDATE, and DELETE statements
- Distinguish between DB2’s various built-in data types and use scalar functions to transform data types
- Code simple inner join statements and understand the concepts of an outer join
- Perform aggregate processing with column functions and the GROUP BY and HAVING clauses
- Distinguish between and code both correlated and non-correlated sub-queries
- Combine result sets with the UNION and UNION ALL set operators and understand the related concepts of intersection and difference
- Code CASE expressions and outer joins as appropriate alternatives to UNION ALL
Prerequisites
There are no DB2 or SQL specific prerequisites for this course. However, any prior experience with DB2 or other relational database management systems should prove valuable.
Proficiency with Windows, Notepad (or another Windows-based text editor), the mouse, and the PC in general is assumed. For a Linux/UNIX audience, familiarity with equivalent tools and concepts is assumed.
Outline
Note: Workshop environment, labs, solutions and course quiz are now available in the Downloads section of this page.
Module 01: Introduction to Relational Databases
- Relational database concepts
- The DB2 family
- Structured Query Language (SQL)
- Workshop: Familiarization
Module 01 A: Familiarization Workshop
Module 02: Simple SELECT Statements
- Simple SELECT statements
- Column projection: The SELECT clause
- Row selection: The WHERE clause
- Sequencing the result: The ORDER BY clause
- Workshop: Data retrieval
Module 03: Simple Modification Statements
- Adding rows: The INSERT statement
- Changing column values: The UPDATE statement
- Removing rows: The DELETE statement
- Views and modification statements
- SQL return codes
- Workshop: Data modification
Module 04: Data Types
- Numeric, string, and chronological data types
- Special registers
- Nullable columns
Module 05: Scalar Functions
- Data-type conversion with scalar functions
- Scalar functions to change nullity
- Numeric, string, and chronological scalar functions
- Miscellaneous scalar functions
- Workshop: Customizing result sets
Module 06: Joining Tables
- Types of joins
- Joins for referential integrity
- Self-referencing joins
- Joining three or more tables
- Exceptions and outer joins
- Workshop: Inner joins
Module 07: Aggregate Processing
- Column functions
- The GROUP BY clause
- The HAVING clause
- Workshop: Column functions and grouping
Module 08: Subqueries
- Syntax elements
- Noncorrelated and correlated subqueries
- Subquery predicates
- Modification statements with subqueries
- Workshop: Subqueries
Module 09: Set Operations
- Union
- Intersection
- Difference
- Workshop: Combining result sets
Module 10: CASE Expressions
- Searched WHEN clause
- Simple WHEN clause
- More examples of CASE expressions
Module 11: Outer Joins
- Inner joins-Review
- Exception rows
- Outer joins-The old way
- Left, right, and full outer joins
- Filtering rows in outer joins
- Workshop: Alternatives to UNION ALL