17 Building Queries with Query Builder

Query Builder's graphical user interface enables database developers to build SQL queries without the need for manual SQL coding. Using Query Builder, you can search and filter database objects, select objects and columns, create relationships between objects, view formatted query results, and save queries with little or no SQL knowledge.

This section contains the following topics:

• About Query Builder

• Using the Object Selection Pane

• Selecting Objects

• Specifying Query Conditions

• Creating Relationships Between Objects

• Working with Saved Queries

• Viewing Generated SQL

• Viewing Query Results

About Query Builder

The Query Builder page is divided into three sections:

• Object Selection pane displays on the left side of the page and contains a list objects from which you can build queries. Only objects in the current schema display.

• Design pane displays to the right of the Object Selection pane and above the Conditions, SQL, Results, and Saved SQL tabs. When you select an object from the Object Selection pane, it appears in the Design pane.

• Output pane displays below the Design pane. Once you select objects and columns, you can create conditions, view the generated SQL, or view query results.

Description of the illustration qry_bldr.gif

Topics in this section include:

• Accessing Query Builder

• Understanding the Query Building Process

Accessing Query Builder

To access Query Builder:

1. On the Database Home Page, click the SQL icon.

2. To view Query Builder you can either:

   • Click the Query Builder icon.

   • Click the down arrow on the right side of the icon to view a drop down menu. Then, select the appropriate menu option.

   
   Description of the illustration qry_bldr_menu.gif
   
   

   Note:

   For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.

Understanding the Query Building Process

To build a a query in Query Builder, you perform the following steps:

1. Select objects from the Object Selection pane. See "Using the Object Selection Pane" .

2. Add objects to the Design pane and select columns. See "Selecting Objects".

3. Optional: Establish relationships between objects. See "Creating Relationships Between Objects".

4. Optional: Create query conditions. See "Specifying Query Conditions".

5. Execute the query and view results. See "Viewing Query Results".

See Also:

"Viewing Generated SQL" and "Working with Saved Queries"

Using the Object Selection Pane

The Object Selection pane displays on the left side of the Query Builder page and lists tables, views, and materialized views within the current schema.

Topics in this section include:

• Searching and Filtering Objects

• Hiding the Object Selection Pane

Searching and Filtering Objects

Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema.

To search or filter objects:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. In the search field at the top of the pane, enter a case insensitive query.

4. To view all tables or views within the currently selected schema, leave the search field blank.

Description of the illustration qry_bldr_search.gif

Hiding the Object Selection Pane

You can hide the Object Selection pane by selecting the Hide Table or Views control. By hiding the Object Selection pane, you can increase the size of the Design and Result panes.

The Hide Table or Views control displays on the right side of the Object Selection pane. If the Object list appears, selecting this control hides it. Similarly, if the Object list is hidden, selecting this control causes the pane to reappear.

Selecting Objects

The Design pane displays to the right of the Object Selection pane. When you select an object from the Object Selection pane, it appears in the Design pane. You use the Object Selection pane to select objects (that is, tables, views, and materialized views) and the Design pane to identify how those selected objects will be used in a query.

Topics in this section include:

• About Supported Column Types

• Adding an Object to the Design Pane

• Removing or Hiding Objects in the Design Pane

See Also:

"Creating Relationships Between Objects"

About Supported Column Types

Columns of all types available in Oracle Database 10g Release (10.2) display as objects in the Design pane. Note the following column restrictions:

• You may only select a maximum of 60 columns for each query.

• The following column types are not selectable and cannot be included in a generated query:

  • BLOB

  • NCLOB

  • RAW

  • LONG

  • LONG RAW

  • XMLType

  • Any other nonscalar column types

Adding an Object to the Design Pane

You add an object to the Design pane by selecting it from the Object Selection pane.

To add an object to the Design pane:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane. Note that a graphical representation of the datatype displays to the right of the column name.

   
   Description of the illustration qry_bldr_add.gif
   
   

4. Select the columns to be included in your query by clicking the check box to the left of the column name.

   When you select a column you are indicating it will be used in the query. As you select a column, it appears on the Conditions tab. Note that the Show check box on the Conditions tab controls whether a column is included in query results. Be default, this check box is selected.

   To select the first twenty columns, click the small icon in the upper left corner of the object and then select Check All.

5. To execute the query and view results, click Run.

   Tip:

   You can also execute a query by pressing CTRL + ENTER.

   The Results pane displays the query results.

See Also:

"Specifying Query Conditions"

Resizing the Design and Results Panes

As you select objects, you can resize Design and Results panes by selecting the grey horizontal rule in the center of the page. Moving the rule up, shrinks the Design pane. Moving the rule down expands the Design pane.

Removing or Hiding Objects in the Design Pane

You remove or hide objects in the Design pane by selecting controls at the top of the object. To remove an object, select the Remove icon in the upper right corner. To temporarily hide the columns within an object, click the Show/Hide Columns icon.

Description of the illustration qry_bldr_remove_ico.gif

Specifying Query Conditions

Conditions enable you to filter and identify the data you want to work with. As you select columns within an object, you can specify conditions on the Conditions tab. You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.

To specify query conditions:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

4. Select the columns to be included in your query by clicking the box to the left of the column name.

   When you select a column, you are indicating you want to include it in your query. As you select each column, it appears as a separate row in the Conditions view. Table 17-1 describes the attributes available on the Conditions tab.

   Table 17-1 Conditions Tab

   Condition Attribute	Description
   Up and Down Arrows	Controls the order that the columns to be displayed in the resulting query. Click the arrow buttons to move columns up and down. See Also: "Viewing Query Results"
   Column	Displays the column name.
   Alias	Specify an optional column alias. An alias is an alternative column name. Aliases are used to make a column name more descriptive, to shorten the column name, or prevent possible ambiguous references.
   Condition	Specify a condition for the column. The condition you enter modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. Consider the following examples: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15
   Sort Type	Select a sort type. Options include: Ascending (Asc) Descending (Desc)
   Sort Order	Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display.
   Show	Select this check box to include the column in your query results. You do not need to select Show if you need to add a column to the query for filtering only. For example, suppose you wish to create following query: SELECT ename FROM emp WHERE deptno = 10 To create this query in Query Builder: From the Object list, select EMP. In the Design Pane, select ename and deptno. For the deptno column, in Condition enter =10 and uncheck the Show check box.
   Function	Select an argument function. Available functions include: NUMBER columns - COUNT, COUNT DISTINCT, AVG, MAXIMUM,. MINIMUM, SUM VARCHAR2, CHAR columns - COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER DATE, TIMESTAMP columns - COUNT, COUNT DISTINCT
   Group By	Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output.
   Delete	Deselect the column, excluding it from the query.

   
   

   As you select columns and define conditions, Query Builder writes the SQL for you.

5. To view the underlying SQL, click the SQL tab.

Creating Relationships Between Objects

You can create relationships between objects by creating a join. A join identifies a relationship between two or more tables, views, or materialized views.

Topics in this section include:

• About Join Conditions

• Joining Objects Manually

• Joining Objects Automatically

About Join Conditions

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition. A join condition determines how the rows from one object will combine with the rows from another object.

Query Builder supports inner, outer, left, and right joins. An inner join (also called a simple join) returns the rows that satisfy the join condition. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

See Also:

Oracle Database SQL Reference for information about join conditions

Joining Objects Manually

You can create a join manually by selecting the Join column in the Design pane.

To join two objects manually:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. From the Object Selection pane, select the objects you want to join.

   The objects display in the Design pane.

4. Identify the columns you want to join.

   You create a join by selecting the Join column adjacent to the column name. The Join column displays to the right of the datatype, beneath the Remove icon. When your cursor is in the appropriate position, the following help tip displays:

   Click here to select column for join
   
   

5. Select the appropriate Join column for the first object.

   When selected, the Join column displays as a dark gray. To deselect a Join column, simply select is again or press ESC.

6. Select the appropriate Join column for the second object.

   Tip:

   You can also join two objects by dragging and dropping. Select a column in the first table and then drag and drop it onto a column in another table.
   
   Description of the illustration qry_bldr_join.gif
   
   

   When joined, a green line connects the two columns.

7. Select the columns to be included in your query. You can view the SQL statement resulting from the join by positioning the cursor over the green line.

8. Click Run to execute the query.

   The Results pane displays the query results.

Joining Objects Automatically

When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.

To join objects automatically:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. From the Object Selection pane, select an object.

   The object displays in the Design pane.

4. Click the small icon in the upper left corner of the object. Depending upon the selected object, the icon label displays as Table Actions or View Actions.

   The Actions window appears. Use the Actions window to select all columns within the current object or objects related to the current object.

5. In the Actions window, select the appropriate options:

   • Check All - Select this option to select the first twenty columns in the current object.

   • Add Parent - Displays tables that are referenced as a foreign key to the current object.

   • Add Child - Displays tables that reference the current object in a foreign key.

   If using Add Parent or Add child, the selected object appears and a green line connects the foreign key columns.

6. Select additional columns to be included in your query.

   You can view the SQL statement resulting from the join by positioning the cursor over the green line.

7. Click Run to execute the query.

   The Results pane displays the query results.

Working with Saved Queries

As you create new queries, you can save them by clicking the Save button in the Design pane. Once you save a query, you can access it later in the Saved SQL view.

Topics in this section include:

• Saving a Query

• Editing a Saved Query

• Deleting a Saved Query

Saving a Query

To save a query:

1. Build a query:

   1. On the Database Home Page, click the SQL icon.

   2. Click the Query Builder icon.

   3. Select objects from the Object Selection pane.

   4. Add objects to the Design pane and select columns.

   5. Execute the query.

2. Click Save.

3. Enter a name and description and click Save.

   The saved query displays in the Saved SQL view.

Description of the illustration qry_bldr_save.gif

Note that Query Builder does not support duplicate query names. If you open an existing query, keep the existing name, and save it again, Query Builder over-writes the existing query. If you change the name of an existing query and save it again,Query Builder saves the query again under the new name.

Editing a Saved Query

Once you save a query, you can access it in the Saved SQL view.

To edit a Saved SQL query:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. Select the Saved SQL tab.

4. To filter the display, you can:

   • Make a selection from the Owner list and click Go.

   • Enter a search query in the Name field and click Go.

5. To edit a query, select the appropriate name.

   The saved query appears. The selected objects display in the Design pane and the Conditions view appears.

Deleting a Saved Query

To delete a Saved SQL query:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. Select the Saved SQL tab.

4. Select the queries to be deleted and click Delete Checked.

Viewing Generated SQL

The SQL view presents a read-only, formatted representation of the SQL generated by Query Builder. You can copy the SQL code that appears in the SQL View for use in other tools such as SQL Command Processor or Application Builder.

See Also:

"Using SQL Commands"

To access the SQL view:

1. On the Database Home Page, click the SQL icon.

2. Click the Query Builder icon.

   Query Builder appears.

3. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

4. Select the columns to be included in your query.

5. Click the SQL tab.

   The SQL code generated by Query Builder appears.

Viewing Query Results

Once you select objects and determine what columns to include in your query, you execute a query by:

• Clicking the Run button (or pressing CTRL + ENTER)

• Selecting the Results tab

The Results view appears, displaying formatted query results.

Description of the illustration qry_bldr_results.gif