![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Before you start
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
This guide is designed to allow participants to learn the SQL language and XQuery. As with any learning, it is important to supplement it with hands-on exercises. This is facilitated by the table definitions and data.
For students using this as part of an academic class, obtain from your instructor the instructions to connect to the Aroma database and learn about any differences between the guide and your local set up.
This tutorial was written for DB2 Express-C 9 for UNIX®, Linux® and Windows® (formerly known as Viper).
This tutorial discusses queries that compare data in an IBM DB2 database. It begins by illustrating the problem that confronts the query writer: how to use SQL to return a spreadsheet or "cross-tab" report rather than a standard, vertically ordered result set that is hard to read. The problem is solved by using either CASE expressions or subqueries.
The CASE solution, presented first, is a simple and concise way of comparing similar groups of values. Next, several examples of FROM clause and select-list subqueries are presented. These subqueries have the added value of being able to both compare data from different groups and include calculations against the compared values, such as share percentages over given time periods.
This tutorial describes subqueries stated as conditions in the WHERE clause, which are useful for simpler comparison queries. The last section also describes the ALL, EXISTS, and SOME or ANY predicates, which can be used to express conditions on subquery results.
You need to connect to a database before you can use SQL statements to query or manipulate data. The CONNECT statement associates a database connection with a user name.
Find out from your instructor the database name that you will need to be connected to. For this series, the database name is aromadb.
To connect to the aromadb database, type the following command in the DB2 command line processor:
CONNECT TO aromadb USER userid USING password |
Replace the user ID and password with the user ID and password that you received from your instructor. If no user ID and password are required, simply use the following command:
CONNECT TO aromadb |
The following message tells you that you have made a successful connection:
Database Connection Information Database server = DB2/NT 9.0.0 SQL authorization ID = USERID Local database alias = AROMADB |
Once you are connected, you can start using the database.
Table of contents
- Before you start
- Comparing data with SQL
- Using CASE expressions
- Using subqueries in the FROM clause
- Performing calculations and comparisons
- Using subqueries in the SELECT list
- Using correlated subqueries
- Using cross references
- Calculating percentages of quarter and year
- Using subqueries in the WHERE clause
- Using comparison predicates
- Summary
- Download
- Resources
- About the authors
