Level: Introductory Pat Moffatt (pmoffatt@ca.ibm.com), Information Management Program Manager, IBM Academic Initiative, IBM
Bruce Creighton (bcreight@ca.ibm.com), Skills Segment Planner, IBM
Jessica Cao , Training Tools Developer, IBM
31 Aug 2006 This tutorial series introduces readers to the Aroma database to apply Structured Query Language (SQL) and XML Query (XQuery) knowledge to solve
typical business questions. Also included are detailed instructions for installing DB2 and the Aroma database. This tutorial series has seven parts.
About this series
This tutorial series teaches basic to advanced SQL and basic XQuery topics and shows how to express commonly asked business questions as database queries by using SQL queries or XQueries. Developers and database administrators can use this tutorial
to enhance their database query skills. Academic Initiative members can use this tutorial series as a part of their database curriculum.
All the examples in this document are based on Aroma, a sample database that contains sales data for coffee and tea products sold in stores across the United States. Each example consists of three parts:
- A business question, expressed in everyday language
- One or more example queries, expressed in SQL or XQuery
- A table of results returned from the database
Tutorials in this series
The following tutorials are in this seven-part series:
SQL and XQuery tutorial for IBM DB2, Part 1: Introduction to Aroma and SQL
This tutorial introduces readers to the Aroma database to apply Structured Query Language (SQL) and XML Query (XQuery) knowledge to solve business questions. Also included are detailed instructions for installing DB2 and the Aroma database. This tutorial is Part 1 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 2: Basic queries
Through a series of simple examples, this tutorial illustrates how to retrieve data from an IBM DB2 database with standard SQL SELECT statements. This tutorial describes how to retrieve rows from a relational database table, retrieve specific columns, retrieve specific rows, reform logical operations on retrieved data, and use wildcard characters in search conditions. This tutorial is Part 2 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 3: SQL joins and unions
This tutorial describes two ways to combine data from different tables in an IBM DB2 database: by joining the tables with inner or outer joins and by using the UNION, EXCEPT, and INTERSECT operators, which take the intermediate result set from one query expression and combine it with the result set from another query expression. This tutorial is Part 3 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 4: Data analysis
This tutorial describes how to write queries that require basic data analysis. Many of the queries contain sequential calculations, or calculations that operate on an ordered set of rows--queries frequently encountered during business analysis. On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result. This tutorial is Part 4 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 5: Data comparison
This tutorial describes queries that compare data in an IBM DB2 database. This is accomplished by using either CASE expressions or subqueries. This tutorial is Part 5 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 6: Data Definition Language and Data Control Language
This tutorial expands on Data Manipulation Language (DML) and introduces Data Definition Language (DDL) and Data Control Language (DCL). It also discusses SQL statements other than SELECT. This tutorial is Part 6 of the SQL & XQuery tutorial for IBM DB2 series.
SQL and XQuery tutorial for IBM DB2, Part 7: Introduction to XML and XQuery
This tutorial introduces pureXML™ and XQuery, starting from a basic overview of the characteristics and advantages of the XML data type, and then compares it with standard relational tables. Users are then asked to write XQueries to retrieve XML elements, filter data based on XML values, transform XML output, and use various clauses to select data more precisely. The tutorial ends with a section on mixing XQuery and SQL. This tutorial is Part 7 of the SQL & XQuery tutorial for IBM DB2 series.
Resources
About the authors  | 
| | Pat Moffatt is the Information Management Program Manager for the IBM Academic Initiative. Through the Academic Initiative program, she ensures that appropriate Information Management resources are made available to help faculty integrate Information Management software into their curriculum. To learn more about this program, visit www.ibm.com/university/data. |
| 
| | Bruce Creighton is a Skills Segment Planner in the Information Management Education Planning and Development department. In this role, he plans investment in educational content and balances the investment between areas where IBM can attain revenue and those where the requirement for skills development are important enough to provide free education. |
| 
| | Jessica Cao is an Arts and Science and Computer Science student at McMaster University. She expects to complete her combined honours degree in April 2009. Jessica is working in IBM Toronto lab's DB2 Information Management Skills Channel Planning and Enablement Program to take advantage of her interest in programming, editing, and writing. |
Rate this content
| 
