Toad Data Studio 1.1 - Installation Guide

Tutorial: Visually Build a Query

The Query Builder enables you to create a query without writing or editing SQL statements. Even if you are familiar with SQL, the graphical interface makes it easier to create relationships and visualize the query.

Note: This procedure does not cover all of the possible steps of visually building a query. It only covers the steps required to build a query for the scenario. See Build Queries Visually for more information.

To visually build a query based on this scenario

1. Connect to the database and select Tools | Query Builder | Query Builder.
2. Select the SH schema in the Object Explorer.

3. Drag the following tables from the Object Explorer to the Diagram pane: TIMES, SALES, CUSTOMERS, and COUNTRIES.

   Tips:

   • Select Query Builder | Arrange Tables (ALT+Q+A) to have Toad arrange the tables in the Diagram pane.
   • You can press CTRL and select a table, and then drag the table into the Editor pane. Click the list to create statements.
   • You can also press CTRL and select all of the tables in the Object Explorer, and then drag them into the Diagram pane. Toad automatically arranges the tables.
4. Add the following columns to the query by clicking each column:

   Table	Column
   COUNTRIES	COUNTRY_NAME
   TIMES	FISCAL_YEAR
   SALES	AMOUNT_SOLD

   Tips:

   • To view details for a table in the Diagram, click (F4). If a table is not selected, details for the last selected table display.
   • To remove a column from the query, click or drag column off grid.
5. Complete the following steps to group the data and sum the sales:
   1. Select the Group By field in the COUNTRY_NAME column, and then click +. The Group By field is in the Criteria pane, which is below the Diagram pane.
   2. Select the Group By field in the FISCAL_YEAR column, and then click +.

   3. Select the Aggregate Function field in the AMOUNT_SOLD column and select Sum. This sums all of the sales per fiscal year per country.

      Note: If you select a group by clause or aggregate function for a column, the rest of the columns in the query must also have a group by clause or aggregate function.

6. Complete the following steps to select the fiscal year range:
   1. Select the Where Condition field in the FISCAL_YEAR column and click . The Where Condition window displays.
   2. On the Form tab, select BETWEEN in the Operators field.
   3. Select 1998 in the first Constant field.

   4. Select 2001 in the second Constant field and click OK.

      Note: The SH schema does not have current data. The 1998-2001 years provide the best data for the purposes of this scenario.

7. Complete the following steps to select countries with less than $500,000 in sales:
   1. Select the Having Condition field in the AMOUNT_SOLD column and click . The Having Condition window displays.
   2. On the Form tab, select < in the Operators field.
   3. Select Sum in the Aggregate field.
   4. Enter 500000 in the Constant field and click OK.
8. Select Ascending in the Sort field of the COUNTRY_NAME and FISCAL_YEAR columns.

9. Click .

   The query results display in the Results tab. See View Result Sets for more information.

   

10. To save the query, right-click the Query Builder tab and select Save File.

    You can save a query and any results sets in a Toad editor file (.tef). This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

11. See About Designing Toad Data Reports for more information about creating a report from this data.

See Tutorial: Build a Subquery for a tutorial on creating a subquery.

See Add Subqueries for more information on how to add a subquery.