Creating a SELECT statement

A SELECT statement retrieves data from a database and returns it in the form of a table. It can be embedded in an application or used interactively.

The SELECT statement is made up of several clauses including the SELECT clause, FROM clause, WHERE clause, ORDER BY clause, GROUP BY clause and HAVING clause. Refer to the related information for more details.

To create a SELECT statement using the SQL Builder:

1. Switch to the Data Definition view in the Data perspective.

2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

3. Right-click the Statements folder, and then click New > Select Statement on the pop-up menu.

4. Type a name for the statement, and then click OK. The SQL Builder opens.

5. Add one or more tables to the statement. Do the following for each table:
   1. Right-click in the Tables pane, and then click Add Table on the pop-up menu.

   2. Select the table to use, specify an alias if required, then click OK.

6. To create a join, drag the pointer from a column in one table in the Tables pane to a column in another table.

7. To eliminate all but one of each set of duplicate rows of the final result table, select the DISTINCT check box.

8. To specify the columns to use in your statement, click the Columns tab, and then do the following:
   1. Select one or more columns to use in your statement, or, to create an expression, start the Expression Builder wizard by clicking the first empty row, then the first cell under the Column heading in the Design pane.

   2. (Optional) Enter an Alias for your column or expression. There cannot be any spaces in the alias. When you execute your SQL statement, the Alias appears in the statement instead of the column or expression name.

   3. To show a column or expression in the result set when the statement is executed, select its Output check box. To use a column or expression in your statement, but not use it when you execute your statement, clear its Output check box. An example of when you may want to do this is if defining an ORDER BY clause on a column when you still want to show all columns in the result set: SELECT * FROM DEPT ORDER BY DEPT.NUMBER.

   4. Fill in the Sort Order and Sort Type fields to create an ORDER BY clause for the SELECT statement. Refer to the related task below on "Creating an ORDER BY clause" for more information about how to use these fields.

   5. To specify conditions for the SELECT statement, click the Conditions tab. The conditions specify which rows of the database table should be returned in the results.
      1. Click the first row, then click the cell under the Column heading

      2. Select the column that you want to include in your condition, or click Build Expression to build an expression to include in your condition.

      3. Click the adjoining cell in the Operator column, and then select an operator from the list.

      4. Click the adjoining cell under the Value heading and type a value. For a value, you can specify a column, build an expression, manually type in a specific value (such as 23), or a host variable.

      5. When you are finished, click outside of the row.

      6. Add further conditions if needed. Select from the And/Or column to say how the conditions should be evaluated.
      If you do not specify a condition, all rows of the target database table are returned in the results.

9. Use the Groups and Group Conditions tabs to define grouping for your statement. Refer to the related task below on "Creating a GROUP BY clause" for information on how to fill in the fields on these tabs.

10. When you have finished, select SQL > Execute to execute the statement.

The output is shown in the DB Output view.

To create a SELECT statement using the Create A New SQL Statement wizard:

1. Click File > New > Other.

2. In the New window, expand the Data folder, click SQL Statement, and then click Next.

3. In the SQL statement field of the wizard, click SELECT.

4. Fill in the remaining fields on the wizard pages, as necessary. To see information about a particular field, position the pointer in the field and then press F1.

SELECT statement
The SELECT statement retrieves data from a database and returns it in the form of a table. It can be embedded in an application or used interactively.

Creating joins
A join is a relational operation in a SELECT statement that lets you retrieve data from two or more tables based on matching column values. The data in the tables is linked into a single result.

Creating a GROUP BY clause
The GROUP BY clause contains a grouping expression that is used to define the grouping of the result set of the previous clause of the SELECT statement. The result of a GROUP BY clause is that a set of groups of rows is created. Each row in the result table represents the set of rows for which the grouping expression is equal.

Creating an ORDER BY clause in a SELECT statement
The ORDER BY clause specifies the order of the rows of a results table for an SQL statement.

 

Parent topic

UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related tasks

Editing an SQL statement
Executing an SQL statement