The Query Builder window can be started in three different ways:
from the Database Explorer, select a table then use the Build Query menu item
from the Run SQL window, click on the Query icon or Build Query menu item
in the Database Explorer, select a view, right-click then select View as Query
The Query Builder has these major features:
allows you to build a query using the GUI paradigm – a graphical display of tables. This is particularly useful for building joins between tables and visualise a query easily
most aspects of a query can be specified – column order, column functions, column aliases, constants, summary queries (group by), row order, where clauses.
AQT can also reverse-engineer a query. It can take an existing SQL statement and turn it into a graphical query. This can be SQL statements generated by AQT, or some existing SQL you have. You can amend this code or reformat the SQL using the many options AQT provides for the format of the generated SQL.
AQT can also build and reverse-engineer View definitions.
There are limitations to the SQL that AQT can build or reverse-engineer.
Joins between tables can be quickly defined by dragging the mouse between the related columns in the Query Builder.
When you have a “known” relationship between your tables (eg. it is defined to your database as a foreign key relationship) AQT can automatically build a join between these tables with a single click.
There are many cases when you have a relationship between your tables, but this is not defined to your database. In this case AQT allows you to save the definition of the relationship as a user-defined relationship. The details of the relationship are permanently saved (to disk); you can then build a join between these tables with a single click.
Starting the Query Builder
The Query Builder window has the following tabs:
GUI – to show a graphical display of your query tables and joins.
Tables / Joins – to specify further information about the query tables and joins.
Columns – to specify further information about the columns in the query, and for building complex query-columns.
Where – to build the WHERE statement for the query
Options – to specify the many options you have for the way the SQL is generated.
In addition, at the bottom of the window is a text box that displays the query SQL. This text will change as the query is built or amended.
The toolbar gives access to frequently-used functions. You can customize the toolbar to change its appearance or add / remove buttons. These functions are also available from the Menu bar and shortcut keys.
Some functions that need to be described are as follows:
Refresh. Resets the query to the way it was when you entered this window. All changes you may have made will be lost.
Refresh Table Info. For performance reasons, AQT caches in memory the information about the tables in your query. This includes the columns, primary keys and foreign keys of the tables in your query. This reduces the number of times AQT has to query the system tables, which can sometimes be a slow process. However there is a disadvantage to this – if you change a table (eg. add some columns, or add a foreign key) then the Query Builder will not show this change. It will continue to use the old definition. If this happens, click on the Refresh Table Info icon. The information on the tables will be refreshed from the system tables.
Assistant. This switches the Query Assistant on or off.