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 introduces participants to the Aroma database, especially the retail group of tables, table definitions, sample data, and the relationship between tables.
These tables were created to show an environment where:
- There are few tables
- Column names use the analyst's vocabulary
- Columns are reflective of the natural dimension of the business
This is a typical, small data warehouse using a star schema. The other type of database is Online Transaction Processing (OLTP). OLTP databases contain transacations at the most granular level.
This tutorial also introduces the basics of SQL and suggests some typical business questions that IBM DB2 9 can be used to answer.
To use this tutorial to the fullest, you should have IBM DB2 9 installed. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications. You will also need to install the Aroma database, which can be downloaded from here.
To install DB2 9, follow these steps (default options should be used unless otherwise specified):
- Navigate to the folder containing the downloaded file - db2exc_viper_WIN_x86.zip (we will assume the file is downloaded to the C:\DownloadDirector folder).
- Unzip the file.
- Find setup.exe in the unzipped folder and double-click on it. The DB2 Setup Launchpad will appear.
- Within the DB2 Setup Launchpad, click on Install a Product from the panel on the left.
- Click on Install New under DB2 Express.
- In the installer, click Next to view the License Agreement. You will need to Accept the agreement to continue and click Next.
- Choose a Typical installation and click Next.
- Confirm that you plan to "Install DB2 Universal Database Express Edition on this computer and save my settings in a response file" and click Next.
- Verify the installation directory and click Next.
- In the "Configure DB2 instances" screen, click Next.
- In the "User Information" screen, set the Domain to None -- use local user account using the pull-down menu, use db2admin as the User name, and set a password.
(Optionally, you can select the LocalSystem account, but there are limitations introduced if this option is used. Click the Help button to learn more.)
- Make sure the checkbox Use the same user name and password for the remaining DB2 services is checked and click Next.
- Click Next to skip the "Prepare the DB2 tools catalog" screen.
- To simplify installation, uncheck Set up your DB2 server to send notifications and click Next.
- Verify that the Enable operating system security checkbox is checked. Leave the DB2 administrators group as the default value DB2ADMNS and leave DB2 users group as the default value DB2USERS. Click Next.
- Click Finish to start the DB2 product installation when you reach the "Start copying files and create response file" screen.
- When the installation is complete, a window with the message "Setup is complete" will appear. Click Finish to complete the installation procedure.
- A window entitled "DB2 First Steps" will now appear. Click Create profile. Your Web browser will open a page called DB2 First Steps. This tutorial contains many useful links to information on DB2, but it will not be used during this tutorial. Close your Web browser and continue with this tutorial.
- Ensure DB2 Express-C 9 is installed.
- Start the Command Window from the Start menu ==> all Programs ==> IBM DB2 ==> Command Line Tools ==> Command Window.
- Execute create_aroma.bat in the Command Window.
Note: Don't forget to move to the directory where the batch file is located using the
- Two files, create.txt and report.txt, will be created. Edit report.txt and compare to the expected results below:
XML and basic XQueries will be introduced in Part 7 of this series. To support XML, the aromadb database was created with the statement:
db2 create db aromadb using codeset utf-8 territory us
Note the specification of UTF-8 as the codeset for the database. The use of XML features is restricted to a database that is defined with codeset UTF-8 and has only one database partition. When creating your own databases in the future, don't forget to include this parameter or your database won't support XML features!