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:  

The six clauses of the SELECT statement

There are six clauses that can be used in an SQL statement. These six clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Clauses must be coded in a specific sequence. Let's briefly discuss each here. You will learn more about them as you encounter them later in the tutorial.


1. SELECTcolumn name(s)*
2. FROMtable or views
3. WHEREconditions or predicates are met
4. GROUP BYsubsets of rows
5. HAVINGa common condition as a group
6. ORDER BYa sorting method

Note: column name(s) are more correctly referred to as elements, because the SELECT statement displays both columns that exist in the table and columns that may be generated by SQL as a result of a query.

Example query

		SELECT perkey, sum(dollars)
		FROM aroma.sales
		WHERE perkey < 50
		GROUP BY perkey
		HAVING sum(dollars) > 8000
		ORDER BY perkey;

About the query

The SELECT clause is where you list the columns you're interested in. The SELECT displays what you put here. There are other items you can put in a SELECT that will be explained later. In the example, the perkey column, as well as the sum of the dollar column, are selected.

The FROM clause indicates the table you're getting your information from. You can list more than one table. The number of tables you could list is specific to your operating system. In the example, both columns are selected from the Sales table.

SELECT and FROM are required; the rest of these clauses are optional and serve to filter or limit, aggregate or combine, and control the sort.

The WHERE clause is where you indicate a condition. This helps you filter unwanted data from the results. WHERE gives you a subset of the rows in a table. In the example, only rows with a perkey value of less than 50 are selected.

GROUP BY allows you to group your data to achieve more meaningful results. Instead of getting a total sum of the dollar sales for all the rows selected, you can break down sales by perkey to get the daily sales total. This is done in the example by indicating GROUP BY perkey.

HAVING puts a condition on your groups. In the example, only those days that have a total dollar amount greater than 8,000 are returned.

ORDER BY orders your result rows. You can choose to order results by ASC (ascending) or DESC (descending) order. The default is ASC.

The SELECT statement is the most common usage of data manipulation language (DML). Other DML statements (UPDATE, INSERT, and DELETE) and the other two components of SQL (data definition language and control language) are discussed in Part 6 of this series.

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