![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 describes two ways to combine data from different tables in an IBM DB2 database:
- By joining the tables
- By using the UNION, EXCEPT, and INTERSECT operators
The first part of this tutorial presents examples of inner and outer joins.
The second part illustrates how to combine data from different tables by using 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.
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
- Table names and schemas
- Using simple joins
- Using the ORDER BY clause
- Join of two tables
- A different way to join tables
- Self-joins
- Outer join of two tables
- Using the SUM, AVG, MAX, MIN, COUNT set sunctions
- Using the GROUP BY clause to group rows
- Using the GROUP BY clause to produce multiple groups
- OR versus UNION
- UNION versus UNION ALL
- INTERSECT operation
- EXCEPT operation
- Summary
- Download
- Resources
- About the authors
