5.2 Using Query Builder

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.

Query Builder's graphical user interface enables database developers to build SQL queries without the need for manual SQL coding.

Tip:

SQL commands created in the Query Builder can be accessed from SQL Commands.

• Query Builder Home Page
  Learn about Query Builder home page.
• Accessing Query Builder
  Learn how to access Query Builder.
• Understanding the Query Building Process
  Learn how to build a query in Query Builder.
• Using the Object Selection Pane
  Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema as well as select objects.
• Using the Design Pane
  Learn about how to use the Design pane.
• Specifying Query Conditions
  As you select columns within an object, you can specify conditions on the Conditions tab.
• Creating Relationships Between Objects
  You can create relationships between objects by creating a join.
• Working with Saved Queries
  You can create queries and save the query for later use.
• 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 App Builder.
• Viewing Query Results
  Execute a query, once you select objects and determine what columns to include in your query.

See Also:

"Using SQL Commands"

5.2.1 Query Builder Home Page

Learn about Query Builder home page.

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 query_builder.png

See Also:

"Selecting a Schema"

5.2.2 Accessing Query Builder

Learn how to access Query Builder.

To access Query Builder:

1. On the Workspace home page, click SQL Workshop.
2. Click Utilities.
3. Click Query Builder.

The Query Builder home page appears.

Tip:

You can also access Query Builder from a code editor in many part of Oracle Application Express by clicking the Query Builder button when editing a SQL attribute.

5.2.3 Understanding the Query Building Process

Learn how to build a query in Query Builder.

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

1. Select objects from the Object Selection pane.
2. Add objects to the Design pane and select columns.
3. Optional: Establish relationships between objects.
4. Optional: Create query conditions.
5. Execute the query and view results.

See Also:

• "Viewing Generated SQL"

• "Working with Saved Queries"

5.2.4 Using the Object Selection Pane

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

• Searching and Filtering Objects
  Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema.
• Hiding and Restoring the Object Selection Pane
  You can hide the Object Selection pane by selecting the Collapse control.
• Selecting Objects
  Use the Object Selection pane to select objects.

5.2.4.1 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 Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. In the search field at the top of the Object Selection pane, enter a case insensitive query.
   
   Description of the illustration qb_search.png
3. To view all tables or views within the currently selected schema, leave the search field blank.

5.2.4.2 Hiding and Restoring the Object Selection Pane

You can hide the Object Selection pane by selecting the Collapse control.

The Collapse control displays on the right side of the Object Selection pane. If the Object Selection pane displays, selecting this control hides it. Similarly, if the Object Selection pane is hidden, selecting this control causes the pane restores it. Select the Left Splitter (click above or below the Collapse control to manually resize the Object Selection pane.

5.2.4.3 Selecting Objects

Use the Object Selection pane to select 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 are used in a query.

5.2.5 Using the Design Pane

Learn about how to use the Design pane.

Information on how to use how to use the Design pane includes the following topics:

• About Supported Column Types
  Columns of all types available in Oracle Database 10g Release (10.2) or later display as objects in the Design pane.
• Adding an Object to the Design Pane
  You add an object to the Design pane by selecting it from the Object Selection pane.
• Resizing the Design and Results Panes
  As you select objects, you can resize Design and Results panes by selecting the Bottom Splitter in the center of the page.
• 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.

5.2.5.1 About Supported Column Types

Columns of all types available in Oracle Database 10g Release (10.2) or later 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

5.2.5.2 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 Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select an object from the Object Selection pane.
   
   Description of the illustration query_builder_select.png

   The selected object appears in the Design Pane. The icon next to the column name represents the type of the column.

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 is included 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. By default, this check box is selected.

   To select the first twenty columns, click the Table Actions icon in the upper left corner of the object. The Actions window appears. Select Check All.

4. To execute the query and view results, click Run (Ctrl+Enter).

   The Results pane displays the query results.

See Also:

"Specifying Query Conditions"

5.2.5.3 Resizing the Design and Results Panes

As you select objects, you can resize Design and Results panes by selecting the Bottom Splitter in the center of the page.

The Bottom Splitter resembles a gray horizontal rule in the center of the page. Move the Bottom Splitter up and down to shrink and expand the Design pane.

5.2.5.4 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 query_builder_remove.png

5.2.6 Specifying Query Conditions

As you select columns within an object, you can specify conditions on the Conditions tab.

Conditions enable you to filter and identify the data you want to work with. 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 Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

3. 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 5-3 describes the attributes available on the Conditions tab.

   Table 5-3 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.
   Column	Displays the column name.
   Alias	Specify an optional column alias. An alias is an alternative column name. Aliases make a column name more descriptive, shorten the column name, or prevent possible ambiguous references.
   Object	Specifies table or view name.
   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. It is not necessary to select Show to add a column to the query for filtering only. For example, suppose you want 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 deselect the Show check box.
   Function	Select an argument function. Available functions are dependent on the column type and include: NUMBER columns - COUNT, COUNT DISTINCT, AVG, MAX, MIN, SUM VARCHAR2, CHAR columns - COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER DATE, TIMESTAMP columns - COUNT, COUNT DISTINCT, TO_CHAR YEAR, TO_CHAR QUARTER, TO_CHAR MONTH, TO_CHAR DAY, TO_CHAR DAY OF YEAR, TO_CHAR WEEK
   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.

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

See Also:

"Viewing Query Results"

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

• About Join Conditions
  A join condition determines how the rows from one object combine with the rows from another object.
• Joining Objects Manually
  You can create a join manually by selecting the Join column in the Design pane.
• Joining Objects Automatically
  Learn how to join objects automatically.

5.2.7.1 About Join Conditions

A join condition determines how the rows from one object combine with the rows from another object.

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition.

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 Language Reference" for information about join conditions

5.2.7.2 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 Workspace home page, click SQL Workshop and then 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 tooltip 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 it 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.

   When joined, a 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 line.
7. Click Run to execute the query.

   The Results pane displays the query results.

5.2.7.3 Joining Objects Automatically

Learn how to join 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 Workspace home page, click SQL Workshop and then 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 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.

5.2.8 Working with Saved Queries

You can create queries and save the query for later use.

As you create 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.

Tip:

The Saved SQL view only displays queries saved in Query Builder. In SQL Commands you can view queries saved in both SQL Commands and Query Builder.

• Saving a Query
  Learn how to save a query.
• Editing a Saved Query
  Once you save a query, you can access it in the Saved SQL view and edit.
• Deleting a Saved Query
  Learn how to delete a saved SQL query.

5.2.8.1 Saving a Query

Learn how to save a query.

To save a query:

1. Build a query:

   1. On the Workspace home page, click SQL Workshop and then Query Builder.

      Query Builder appears.

   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.

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.

5.2.8.2 Editing a Saved Query

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

To edit a Saved SQL query:

1. On the Workspace home page, click SQL Workshop and then 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.

5.2.8.3 Deleting a Saved Query

Learn how to delete a saved SQL query.

To delete a Saved SQL query:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select the Saved SQL tab.
3. Select the queries to be deleted and click Delete Checked.

5.2.9 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 App Builder.

To access the SQL view:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

3. Select the columns to be included in your query.
4. Click the SQL tab.

   The SQL code generated by Query Builder appears.

See Also:

"Using SQL Commands"

5.2.10 Viewing Query Results

Execute a query, once you select objects and determine what columns to include in your query.

Execute a query by:

• Clicking the Run button (or pressing Ctrl+Enter)

• Selecting the Results tab

The Results view appears, displaying formatted query results. To export the report as a comma-delimited file (.csv) file, click the Download link at the bottom of the page.