Use query builder to generate queries

Creating SQL statements

You can create an SQL statement using either the SQL query builder or the SQL query wizard. Both tools support the creation of SELECT, UPDATE, INSERT and DELETE statements. The SQL query builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement you create is stored in a file with the extension .sqx.

To create an SQL statement using the SQL query builder:

  1. Switch to the Data perspective.

  2. Switch to the Data Definition view by selecting the Data Definition tab.

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

  4. Right-click the Statements folder.

  5. Click New > and the type of statement you wish to create.

    New

  6. Name

    Enter a name for your statement and click OK.

Your statement will be added to the Statements folder and will open in the SQL query builder. Complete the statement using the SQL query builder:

Name

SQL query builder

The SQL query wizard and SQL query builder both provide a visual interface for creating and executing SQL statements. You can create a simple query using the SQL query wizard, or you can use the SQL query builder that supports a wider range of statements. Statements generated by these tools are saved in a file with the extension .sqx.

The SQL query builder contains a number of panes and tabs. The options available depend on the type of statement you create. The following screen capture shows the basic layout of the SQL query builder:

SQL query builder

The builder is composed of three main sections:

SQL Source pane

The top pane contains the source code of the SQL statement. You can type your query directly in this pane, or use the features provided by the tool to build your query. Content assist is available from the pop-up menu of the SQL source pane. If you modify your query in this pane, the statement syntax will be checked and the interface will be updated when you switch the focus to another pane.

Tables pane

The Tables pane provides a visual representation of the tables or views used in your statement. In this pane you can add or remove a table, give a table an alias, and select or exclude columns from the table. When building a SELECT statement, you can also define joins between tables in this pane.

Design pane

The options in the design pane will vary depending on the type of statement that you are creating. When there is more than one set of options available, they will appear as notebook pages. For example, for a SELECT statement, some of the options include: selecting columns, creating conditions, creating groups, and creating group conditions.

Other helpful features

An expression builder wizard that guides you through the creation of complex expressions can be launched from anywhere in the SQL query builder where an expression can be entered.

The Outline view, which is to the right of the SQL query builder in the default Data perspective, shows the components of a WITH or FULLSELECT statement.

Building expressions

Using the Expression Builder wizard you can build complex expressions or subqueries. An expression specifies a value. It can be a simple value, consisting of only a constant or a column name, or it can be more complex.

Expression Builder

The types of expressions that can be built using the Expression Builder wizard are:

Using host variables in an SQL statement

Host variables can be used in an SQL statement to represent a value that will be substituted in at execution time. The host variable syntax is a symbol followed by a string. For example, :companyname. The symbol used depends on the what syntax your database vendor uses to denote variables.

You can use a host variable in your SQL statement in place of an expression. The Expression Builder can be used to create a host variable or you can simply type a host variable in place of an expression in the SQL query builder views.

host variable

When you execute your statement in the SQL builder you will be prompted to substitute a value of the appropriate type for the host variable.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCBCD 1.3 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free Mock Exam Engine