| Oracle® Database Application Express User's Guide Release 3.0 Part Number B32258-01 |
|
|
View PDF |
| Oracle® Database Application Express User's Guide Release 3.0 Part Number B32258-01 |
|
|
View PDF |
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:
See Also:
"Using the Query Finder"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.
Selecting a Schema
A schema is a logical container for database objects. To access objects in another schema, make a selection from the Schema list in the upper right side of the page.
Switching to Another SQL Workshop Component
You can navigate to another SQL Workshop component by selecting one of the following from the Component list located on the upper right side of the page:
Object Browser. See "Managing Database Objects with Object Browser".
SQL Commands. See "Using SQL Commands".
SQL Scripts. See "Using SQL Scripts".
Query Builder. See "Building Queries with Query Builder".
Topics in this section include:
To access Query Builder:
Log in to the Workspace home page.
Click SQL Workshop.
To view Query Builder you can either:
Click SQL Workshop and then Query Builder.
Click the down arrow on the right side of the SQL Workshop icon to view a drop down menu. Then select the Query Builder menu option.
Note:
For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.To build a a query in Query Builder, you perform the following steps:
Select objects from the Object Selection pane. See "Using the Object Selection Pane" .
Add objects to the Design pane and select columns. See "Selecting Objects".
Optional: Establish relationships between objects. See "Creating Relationships Between Objects".
Optional: Create query conditions. See "Specifying Query Conditions".
Execute the query and view results. See "Viewing Query Results".
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:
Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema.
To search or filter objects:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
In the search field at the top of the pane, enter a case insensitive query.
To view all tables or views within the currently selected schema, leave the search field blank.
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.
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:
See Also:
"Creating Relationships Between Objects"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
You add an object to the Design pane by selecting it from the Object Selection pane.
To add an object to the Design pane:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
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.
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 Table Actions icon in the upper left corner of the object. The Actions window appears. Select Check All.
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"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.
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:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
Select an object from the Object Selection pane.
The selected object appears in the Design Pane.
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 >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15 |
|
Sort Type |
Select a sort type. Options include:
|
|
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:
To create this query in Query Builder:
|
|
Function |
Select an argument function. Available functions include:
|
|
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.
To view the underlying SQL, click the SQL tab.
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:
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 Language Reference for information about join conditionsYou can create a join manually by selecting the Join column in the Design pane.
To join two objects manually:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
From the Object Selection pane, select the objects you want to join.
The objects display in the Design pane.
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
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.
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 green line connects the two columns.
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.
Click Run to execute the query.
The Results pane displays the query results.
When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.
To join objects automatically:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
From the Object Selection pane, select an object.
The object displays in the Design pane.
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.
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.
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.
Click Run to execute the query.
The Results pane displays the query results.
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:
To save a query:
Build a query:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
Select objects from the Object Selection pane.
Add objects to the Design pane and select columns.
Execute the query.
Click Save.
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.
Once you save a query, you can access it in the Saved SQL view.
To edit a Saved SQL query:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
Select the Saved SQL tab.
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.
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.
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:
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
Select an object from the Object Selection pane.
The selected object appears in the Design Pane.
Select the columns to be included in your query.
Click the SQL tab.
The SQL code generated by Query Builder appears.
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. To export the report as a comma-delimited file (.csv) file, click the Download link at the bottom of the page.
