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).
Using 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 from a relational database table
- Retrieve specific rows from a relational database table
- Perform logical operations on retrieved data
- Use wildcard characters in search conditions
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 "userid" 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.