17 Building Queries with Query Builder

Accessing Query Builder

To access Query Builder:

1. Log in to Oracle HTML DB.

2. Click the SQL Workshop icon on the Workspace home page.

   The SQL Workshop home page appears.

3. 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 pull-down menu. Then, select the appropriate menu option.

   Figure 17-2 Query Builder Pull-down Menu

   
   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".

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. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

2. Select a schema from the Schema list on the right side of the page.

   Only objects in the current schema display. Note that the values available in the Schema list depend upon your resource privileges.

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.

Figure 17-3 Search Field on the Query Builder Home Page

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.

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 selectable and will not be included in a generated query:

  • BLOB

  • CLOB

  • NCLOB

  • RAW

  • LONG

  • LONGRAW

  • XML TYPE

  • 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. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

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

   Figure 17-4 Object Added to the Design Pane

   
   Description of the illustration qry_bldr_add.gif
   
   

3. 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.

4. 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.

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.

Figure 17-5 Object Controls

Description of the illustration qry_bldr_remove_ico.gif

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.

Joining Objects Manually

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

To join two objects manually:

1. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

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

   The objects display in the Design pane.

3. 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
   
   

4. 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.

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

   
   

   Figure 17-6 Two Joined Columns

   
   Description of the illustration qry_bldr_join.gif
   
   

   When joined, a green line connects the two columns.

6. 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.

7. 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. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

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

   The object displays in the Design pane.

3. 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.

4. 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.

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

6. Click Run to execute the query.

   The Results pane displays the query results.

Saving a Query

To save a query:

1. Build a query:

   1. Navigate to Query Builder

   2. Select objects from the Object Selection pane.

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

   4. Execute the query.

2. Click Save.

3. Enter a name and description and click Save.

   The saved query displays in the Saved SQL view.

Figure 17-7 Saved SQL Query

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. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

2. Select the Saved SQL tab.

3. 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.

4. 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. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

2. Select the Saved SQL tab.

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