Visual Query BuilderThe Visual Query Builder helps you construct complex database queries without you having to know the syntax of SQL statements. A rich set of visual options are available to let you combine SQL clauses like JOINs, GROUP BY with properties like Indexes, Operators, Aliases, Sort Type, Sort Order and Criteria. Based on your selections, the Visual Query Builder will generate a complete SQL statement that can be executed. Features like Quick Criteria Mode, Quick Filtering, Index Assistant, flexible layouts, and drag and drop to include JOINs save you time and make the process of building queries more powerful and intuitive. |
|
Visual Query Builder functions include:
Connect to a Database Server and Create Complex Queries
Ctrl+Alt-Q invokes a Visual Query Builder window with a connection to a Database Server. The Add Table dialog allows you to include tables, columns and views of a specific database or schema. Once the table is added into the diagram, you can view the query in the SQL Pane. Execute SQL queries using Ctrl+E and view the results in different modes: Grid, Pivot Grid. Add Objects to Diagram Pane
The tables and views added with the Add Tables dialog appear in a Diagram Pane. More database objects can be easily added or removed from the Diagram Pane. To add columns to a query, place a check mark next to them in table entities. Bold text indicates a Primary key column. As Objects are manipulated, the SQL Pane generates the SQL automatically. Create JOINS with Drag and Drop
To create a JOIN in an SQL query, drag table columns from one table entity to another in the Diagram Pane. The SQL Pane displays the type of JOIN and its syntax instantly as changes are made. You can alter the JOIN type by double clicking on the Join line in the diagram and choosing the "Select All Rows" option. GROUP BY Clause
Quickly insert GROUP BY Clause to a query by selecting the columns and an aggregate function (AVG, COUNT, COUNT_BIG, MAX, MIN, SUM) from the list. Enable Group By in the Column Pane with Ctrl-G. Quick Criteria Mode and Quick Filter Results
You can also specify filters to your query from the Column Pane by entering operators (LIKE, =, IN, IS NULL, IS NOT NULL, NOT IN, >, <, <>, >=, <=, BETWEEN). You can enter an Operator directly or choose it from the available drop down. Then you can specify a Criteria for the Operator to get your Resultset. We can apply quick filter to Grid and Pivot Grid Results. Flexible Layouts
The Visual Query Builder allows you to expand each pane for a better view. Several options are available to customize the layout. Use Ctrl+Alt-F9 to show the Diagram Pane, Ctrl+Alt-F10 to show Columns Pane, Ctrl+Alt-F11 to show SQL Pane and Ctrl+Alt-F12 to show Results Pane. Execute Queries for Editing Tables
Ctrl+Alt+Enter invokes the query results in an editable, Excel-like grid. The Execute Edit feature is useful for altering, inserting, deleting, cloning and formatting data in the Table Data Editor. |
Enhanced Visual Query Building
To construct SQL queries without typing any SQL statements, use the GUI to select tables or views with column selection choices allowing All Columns, Indexed Columns, Leading Indexed Columns or None. With the rich set of options available in the Visual Query Builder, users can combine any of the SQL properties like JOINs, Indexes, Operators, Aliases, Sort Type, Sort Order, Criteria and make the process of writing queries more powerful and intuitive. SQL Pane with Syntax Highlighting
The SQL Pane automatically color codes SQL keywords, vendor keywords, data types, system functions, system tables, quoted strings, comments to make it easier to read and spot errors. Add Filters
The Visual Query Builder allows you to quickly add filters to your SQL queries when adding tables for JOIN operation. The available filters are Quote Identifiers, Fully Qualified Tables, Database Qualified Tables, and Auto Join on Foreign Key. Index Assistant
The Index Assistant helps you in optimizing SQL Queries by displaying the index sequence and prompting you to use indexes that will be more efficient. When an index is selected, the tool will color code the column criteria in all selected columns and enumerate the column index count. This allows you to quickly identify if the index will be used in the query. If the column index enumeration is not in numerical order, it is unlikely the index will be used in the query. Save Query Builder Files
Once a query is built it can be saved as a .XQB file for reuse in the Visual Query Builder. Video Tutorial
|
