Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

SQL and XQuery tutorial for IBM DB2, Part 2: Basic queries

The fundamentals of SQL queries

Pat Moffatt (pmoffatt@ca.ibm.com), Information Management Program Manager, IBM Academic Initiative, IBM
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 (bcreight@ca.ibm.com), Skills Segment Planner, IBM
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, Training Tools Developer, IBM
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.

Summary:  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.

View more content in this series

Date:  03 Aug 2006
Level:  Introductory PDF:  A4 and Letter (97 KB | 25 pages)Get Adobe® Reader®

Activity:  39943 views
Comments:  

Using the SELECT statement to retrieve data

Question

What regions, districts, and markets are defined in the Aroma database?

Solution

The first step is to identify the table that contains the rows and columns that can answer this question. In Part 1, there is a diagram of the tables in the AROMADB. Looking at the lower left-hand section of the diagram, there is a table called Market, which contains columns titled mktkey, hq_city, hq_state, district, and region. Listing the contents of this table will answer our question and can be done with an SQL SELECT statement.

Example query

		SELECT * FROM aroma.market;


Result
MktkeyHq_cityHq_stateDistrictRegion
1AtlantaGAAtlantaSouth
2MiamiFLAtlantaSouth
3New OrleansLANew OrleansSouth
4HoustonTXNew OrleansSouth
5New YorkNYNew YorkNorth
6PhiladelphiaPANew YorkNorth
7BostonMABostonNorth
8HartfordCTBostonNorth
9ChicagoILChicagoCentral
10DetroitMIChicagoCentral
11MinneapolisMNMinneapolisCentral
12MilwaukeeWIMinneapolisCentral
14San JoseCASan FranciscoWest
15San FranciscoCASan FranciscoWest
16OaklandCASan FranciscoWest
17Los AngelesCALos AngelesWest
19PhoenixAZLos AngelesWest

Retrieving data: SELECT statement

You use SELECT statements to retrieve columns and rows of data from database tables; to perform arithmetic operations on the data; and to group, order, or group and order the data. In most cases, a SELECT statement contains a simple query expression that begins with the SELECT keyword and is followed by one or more clauses or subclauses.

The most basic SELECT statement contains two keywords, SELECT and FROM:

		SELECT column name(s)
		FROM table name(s)

column name(s)Column names or SQL expressions are separated by commas. An asterisk (*) can also be used to list all column names that occur in the list of table name(s)
table name(s)Table names can be a list of tables or a SELECT statement. The table names are separated by commas. Referenced table(s) must contain the column name(s) that are referenced after the SELECT keyword. In this example, the FROM statement refers to AROMA.MARKET. More will be discussed about this naming convention later in Part 3, Table Names and Schemas. In this case, the FROM clause refers to the qualified table name. The first part is the schema and the second part is the table name. Schemas are useful to group like tables and other DB2 objects. In the AROMADB database, all the tables were set up with AROMA as the schema.

Alternate select statement

		SELECT mktkey, hq_city, hq_state, district, region FROM aroma.market

The above query produces the same result as SELECT * FROM aroma.market; however, rather than using the asterisk (*) to list all columns, the column names are identified.

arrow Important: SELECT and FROM (and all other words shown in uppercase) are reserved SQL keywords. These words must be used exactly as defined by the SQL standard. We use the uppercase format in this document to make the keywords easy to see; SQL is not case sensitive, so keywords can be written in uppercase or lowercase.

Usage notes

The semicolon (;) at the end of each example in this guide is not a mandatory part of SQL syntax. By convention, the SQL language ignores extra spaces, tabs, and end-of-line indicators. The semicolon, then, is an end-of-statement marker and not necessary if you execute only a single statement. For situations where you create a scripted series of SQL statements, the semicolon provides a definite end to each statement. Depending on the interactive SQL tool you use to enter queries, you may or may not need to specify such a marker. When using the Command Line Processor to execute multiple SQL statements, a semicolon is required to indicated the end of each statement.

3 of 15 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=151358
TutorialTitle=SQL and XQuery tutorial for IBM DB2, Part 2: Basic queries
publish-date=08032006
author1-email=pmoffatt@ca.ibm.com
author1-email-cc=
author2-email=bcreight@ca.ibm.com
author2-email-cc=
author3-email=jcao@ca.ibm.com
author3-email-cc=