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 WHERE clause to retrieve specific rows

Question

What products are sold without packaging?

Example query

		SELECT prod_name, pkg_type
		FROM aroma.product
		WHERE pkg_type = 'No pkg';
		

Result

Prod_NamePkg_Type
VeracruzanoNo pkg
Xalapa LapaNo pkg
ColombianoNo pkg
Expresso XONo pkg
La AntiguaNo pkg
Lotta LatteNo pkg
Cafe Au LaitNo pkg
NA LiteNo pkg
Aroma RomaNo pkg
Demitasse MsNo pkg
Darjeeling Number 1No pkg
Darjeeling SpecialNo pkg
Assam Grade ANo pkg
Assam Gold BlendNo pkg
Earl GreyNo pkg
English BreakfastNo pkg
Irish BreakfastNo pkg
Special TipsNo pkg
Gold TipsNo pkg
Breakfast BlendNo pkg
Ruby's AllspiceNo pkg
Coffee MugNo pkg
Travel MugNo pkg
Aroma t-shirtNo pkg
Aroma baseball capNo pkg

Retrieving specific rows: WHERE clause

By including a set of logical conditions in a query, you can retrieve a specific set of rows from a table. Logical conditions are declared in the WHERE clause. If a row satisfies the conditions, the query returns the row; if not, the row is discarded. Logical conditions are also called search conditions, predicates, constraints, or qualifications.

The WHERE clause

SELECT column name(s) FROM table name(s) [WHERE search_condition];

search_conditionThis condition evaluates to true or false.

The square brackets ([ ]) indicate that the WHERE clause is optional.

About the query

The example query retrieves and displays the names of products that are not prepacked or packaged. IBM DB2 9 evaluates the following condition for each row of the Product table and returns only those rows that satisfy the condition:

		pkg_type = 'No pkg'

Usage notes

A character literal is a character string enclosed within single quotes. To represent a single quote in a character literal, use two single quotes (''). For example:        'Scarlet O''Hara'

Character literals must be expressed as exactly stored in the database, in either uppercase or lowercase. For example, the following condition:

		class_type = 'Bulk_beans'

is false when the referenced column contains the following string:

		'BULK_beans'

Set functions are not allowed in the WHERE clause. For more information about set functions, refer to Part 3, Using the ORDER BY clause.

5 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=