Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| 1. Opening a Query Builder Window - Visually building a query begins by openning the Query Builder tool. The application toolbar includes a button to open the tool. When initially openning the tool, it will open a database connection to the database server currently selected in the server browser. Alternaitavely, you may right mouse-click on the server desired and select the "Query Builder" menu item to launch the tool. Select an object in the Schema Browser, then click the Query Builder Icon or right click the object in the Schema Browser and select Query Builder from the menu (Ctrl + Alt + Q) | |||||
| 2. Adding Tables To the Query - When the Query Builder is initially openned, the add table dialog will open, allowing you to choose tables you would like in your query. Having the Query Builder automatically display the Add Table dialog at startup is optional based on File->Options->General->Query Builder->[Show Add Table Dialog at startup]. You may also choose the "Add Table" menu item in the toolbar to add more tables at any time if necessary. The Add Table chooser allows you to automatically include columns in the query, or you may choose them individually from the table entity in the diagram by clicking on the check box left to the column. The table entity contains a list of columns within the table and one extra row to indicate all columns of the table. The primary key columns are bolded, and columns participating in indexes are in italics.
Frequently used tables can be added to the shortcuts toolbar when it is enabled in the applicaton menu under Windows->[Show Table Shortcuts Toolbar]. Right clicking the title bar on a table entity in the diagram pane allows adding a shortcut. Clicking on a toolbar shortcut for a table adds it to the diagram pane without the user having to browse for it. This allows you to quickly add frequently used tables to your queries without browsing for them. |
|
||||
| 3. Creating JOINs with drag and drop - JOINs are created by dragging Table Columns and dropping them off on Columns of other Tables in the Builder Diagram Pane. JOINs are indicated by connecting lines. JOINs can be removed by right clicking the line and selecting Remove. If the JOIN icon on the line is first selected, then right clicked, menu options allow selecting all rows of either/both tables in the JOIN. The JOIN icon changes to indicate all rows of a Table have been selected. Within the SQL Pane, the type of JOIN and its details are writen as changes are made out as SQL for review. |
|
||||
| 4. Adding Columns to the Query - Columns are added by checking their check box on the left-hand side of a Table Entity. When a column is checked, it appears in the Columns Pane. Aliases can be created and edited by entering text within the Alias column inside the Columns Pane. The SQL pane updates instantly to show the aliases in script form for review. Syntax highlighting and formatting within the SQL Pane is set within File->Options->SQL Editor and File->Options->Formatter. Insert Rows in the Columns Pane to use Expressions - Within the Columns Pane, rows can be inserted by selecting a row, then right clicking to Insert. Once a blank row is inserted, Expressions can be entered in the blank row. The order that these rows appear can be shifted by using the moving options presented in the right click menu for moving a selected row up or down. Expressions can be as in-depth as needed, and when executed, will have their results appear in the Query Results Pane. |
|
||||
| 5. Adding Filter criteria (Quick Criteria Mode) - The Filter icon within the Query Builder allows for filtering of Quote Identifiers, Fully Qualified Tables, Database Qualified Tables and Auto Joins on Foreign Keys. In Quick Criteria Mode, only Operators and Values can be added within the Columns Pane. Criteria values can be inserted and Operatars can be selected (LIKE, =, IN, IS NULL, IS NOT NULL, NOT IN, >, <, <>, >=, <=, BETWEEN) when Quick Criteria Mode for filters is enabled within the Windows menu. |
|
||||
| 6. Adding Filter criteria (Advanced Mode) - Unchecking "Quick Criteria Mode" from the Window Menu in the Query Builder Menu Bar enables the Advanced Mode, allowing more than just Operators and Values to be added to the Columns Pane. Advanced mode offers Criteria insertions, each 'Or ...' column in the Columns Pane defines additional criteria. Very complex filter criteria can be added to build out detailed queries. |
|||||
| 7. Saving Queries (SQL) - Queries can be saved by clicking the Save SQL icon (Ctrl + Alt + S) in the Query Builder Toolbar. This saves the query as SQL and can be edited further in Aqua Data Studio's SQL editor, either through inclusion in a mount in the Scripts Browser, or as part of a script directory tied to a database server connection within the Schema Browser.
Saving Query Builder files (.xqb files) - Saving a Query Builder file (Ctrl +S) saves files with the extension .xqb. If a saved Query Builder file appears in the Schema Browser or Scripts Browser, double clicking it will open it within the Query Builder. Query Builder files can be edited in the XML Editor to alter connection information. This can be very useful for sharing .xqb files with users who may not have the same names for their database connections, but do connect to the same database server and database. |
|||||
| 8. Executing Queries - Queries are executed by clicking the the Execute icon (Ctrl + E) or by right clicking within the Diagram Pane and selecting Execute from the menu. Executing a query within the Query Builder functions the same was as executing a query from within a Query Analyzer window. When a query is executed, its results appear in the Results Pane at the bottom of the Query Builder. Results can be viewed as Grid Results or Pivot Grid Results. Errors and messages generated during execution can be viewed in the Messages tab beneath the results pane. Charts can be generated from Grid Results and Pivot Grid Results by clicking on the Chart icon at the top left of the Results Pane once results have been generated. For an in-depth description of Aqua Data Studio's charting abilities, see the Charting in ADS and Working With Pivot Grids sections of the documentation |
|||||
|
9. Executing Queries for Editing - Executing edits is useful for altering table data, inserting rows and other information. Execute Edit is performed by (Ctrl + Alt + Enter) or by clicking the Execute Edit icon in the tool bar. The Table Data Editor appears when an Execute Edit is performed, allowing additional rows, data and other information to be added or deleted from databases through an Excel-like grid. Note: The Table Data Editor uses the primary key or any unique constraint to identify the row in the result which it will generate UPDATE statements for. If your resultset doesn’t have a primary key or unique constraint, you will be prompted to define a primary key in the primary key tab of the Table Data Editor. |
|||||
| 10. Saving Results to Excel/CSV/Insert - Once a query is built and executed, the results it generates can be right clicked and saved as Delimited Data, INSERT statements, XML Documents, HTML Documents or Excel Worksheets. Results can be viewed from within the Query Builder as Grid Results and Pivot Grid Results by clicking on the tabs which appear at the bottom of the Query Builder Window. Messages generated during query execution (including execution time, warnings and errors) are viewable underneath the Messages tab to the right of the Grid Results and Pivot Grid Results tabs. |
|||||
| 11. Group By Examples - The Query Builder also allows you to write GROUP BY queries. From the Menu, select Query->[Group By] to enable the [Group By] column in the columns grid list. This allows you to change GROUP BY options for each of the columns. You may select the "Group By" option on the columns you want to participate in the GROUP BY clause, and select an aggregate function (AVG, COUNT, COUNT_BIG, MAX, MIN, SUM) on the columns you want to select and aggregate on. With these options you may quickly create a GROUP BY query. | |||||
| 12. Index Assistant - The Index Assistant helps you create queries which use indexes efficiently. When the index assistant is enabled, a list of indexes which exist on all the selected tables will be presented. If an index is selected, the tool will color code the column criteria in all columns selected and enumerate the column index count to allow the user to quickly identify if the index will be used in the query. If the column index enumeration is not in numerical order, the index will not likely be used. |