Query Xtractor
Visual SQL query builder, as productivity tool, to quickly design SELECT read-only queries, in a suggestive and intuitive manner, rather then write their SQL code from scratch. Instantly generates the vendor-specific SQL query and/or executes it directly against the connected relational database, and gets the results. Generic support for dozens of relational database platforms, including Oracle, SQL Server, PostgreSQL, MySQL, Azure, Amazon Redshift, SQLite and Firebird.
Query Xtractor Features Summary
| Feature | Description |
|---|---|
| Visual SQL Query Builder | design SQL queries instead of typing them |
| SQL Query Generator | generate SQL SELECT queries from the visual SQL builder |
| Simulated SQL Queries | generate equivalent database-specific SQL from any query |
| Emulated SQL Queries | alternative to native implementation or formula-based queries |
| Generic User Interface | same visual SQL builder, formulas and functions for any database |
| Crosstab PIVOT Queries | emulated and native PIVOT SQL queries, to generate crosstabs |
| UNPIVOT Queries | emulated and native UNPIVOT queries, to generate property-value rows |
| Transpose Queries | emulated SQL queries to switch rows with columns |
| Sort with Nulls | emulated and native ORDER BY clause with NULLs first or last |
| Grouping Sets | emulated or native GROUPING SETS, with CUBE and ROLLUP |
| Drill-Down on Relationships | automatic drill-down queries for FK-based relationships |
| Drill-Down on Aggregates | automatic display of all rows aggregated by a GROUP BY query |
| Nested Aggregate Queries | auto-generation of subqueries with simple external aggregates |
| Extending Aggregates | grouping, list and median emulation, count with sum, aggregate fixes |
| Window Functions | friendly interface for native ranking and analytic functions, and the OVER clauses |
| Statistical Functions | emulation or native variance/deviation/correlation functions |
| Regression Function | emulation or native linear regression functions |
| Summary Aggregates | client-side summaries at the bottom of any grid result |
| Search Highlights | client-side highlights of specific search patterns in the grid result columns |
| Display Format | client-side format strings for different data types in the grid result columns |
| Data Type Functions | common interface with database-specific string, numeric or date/time functions |
| Conversion Functions | common interface with database-specific CAST and other convert functions |
| Math Functions | common interface with database-specific formulas, math and trigonometric functions |
| Quoted Aliases | automatic generation and quoting of all internal query identifiers |
| Parameterized Queries | instant switched view to queries with generated parameters, to prevent SQL injection |
| Alternative Query Views | instant switch to SQL queries using lower case, alternative operators, a different layout, hiding subquery syntax, using WITH instead of nested subqueries, sort/group by position/alias etc |
| Query Expression Reuse | reuse of any grid column as repeating expression in the generated SQL query |
| More Reliable Queries | auto-fixes for known math and NULLs problems, data type checking etc |
| Fluent Expressions | object-oriented user interface, with each formula extending the previous expression |
| Extended Joins | exclude joins added to the typical inner left/right joins and cross joins |
| Intersect and Except | emulated or native INTERSECT and EXCEPT clauses, added to the UNION clause |
| Lookup Fields | relationship-based generated joins to replace identifiers with descriptions |
| Chain Joins | automatic generation of all joins for relationships bypassing multiple tables |
| Duplicate Values | quickly count and show unique values or duplicates |
| Reusable Subqueries | reuse any query in other new queries |
| Automatic Pagination | automatic generation of SQL queries to get subsets of continuous rows |
| Skip and Keep | automatic generation of database-specific SQL to skip some rows and/or keep only the next few rows |
| Constant Table Queries | simple creation of UNION or VALUE queries, to generate constant-based results |
| Collapsible Result Rows | group together the same consecutive sorted values in the same column, to create reports |
| Export Query Results | copy or export the SQL query result as text, images, PDFs etc |
| Demo Queries | learn from hundreds of database-dependent and independent auto-generated queries |
| All and Some/Any | emulated or native ALL and SOME/ANY aggregate clauses |
Visual Query Builder
Design grids with top diagram area to select table/view/subquery columns from specific shapes:
- Field: start with fields, constants or global function calls. Each grid column becomes a reusable query expression.
- Lookup: alternative display field for any single foreign key. Like department and manager names instead of numeric IDs!
- Formula: fluent contextual transformations of the top selection. We divide here the salary by 10 and convert it to an integer.
- Where: intuitive filter, with string matching patterns, comparisons, list checks.
- Sort: sort order index, with NULLs first or last – available for any platform, natively supported or emulated!
- Format: spreadsheet-like display format, after the query execution.
Instant SQL Query Generator
Can hide diagram area, the design grids (all or just specific builder rows), the result grid or the SQL area. Top SQL button will show you the instantly generated vendor-specific SQL. The query can be instantly simulated for another platform, without having anything from that platform installed!
Result Modes with Drill Down
You may get full instant results while you design your query, on almost each incremental change. The full page Results mode hides anything related to the builder and just executes the query, with result rows returned. Whenever you expose in your query fields involved in relationships (built-in, custom joins or chains), you make possible the in-depth result drill-down, and instantly discover related objects. Click the inner SQL button to visualize the query we transparently generate for each subform.
Group By with Instant Drill Down on All Aggregates
Powerful GROUP BY queries, with extended aggregates like List, Median, Grouping, with native support or emulated! Automatic drill-down to All Aggregates for any row. Show icons in query diagram area, to hide checkboxes and keep only selected columns from each shape.
Inclusive and Exclusive Joins
Our query joins try to hide the relative complexity and confusion related to SQL inner and outer joins. Cross joins are by default established between tables with no connector between. Default connector of an expanded relationship creates an inner join. Inclusive joins – black small circles when you click close to a connector’s end – include all rows from that table. Exclusive joins – empty small circles when you click close to a connector’s end – exclude all rows from that table. Reflexive manager-subordinate relationship here has inclusive joins on both sides. This leads to the inclusion of all rows from both tables. Without this join, the first three rows (with NULL on one side) would not be included in the result.
Generic Union Queries
Union builder grid makes possible concatenation of rows from the main query with other rows of constants or fields, using the UNION, INTERSECT or EXCEPT operators, with optional ALL key. All our database platforms support Intersect and Except, emulated or not! Values may be also used to generate a table of constants, using either VALUES (when supported) or UNION ALL – as in the FruitsU demo query on screen. No tables, but mandatory FROM clause? No worry, we transparently fill all these gaps for you.
Generic Pivot/Cross Tables
Instant pivot/crosstab queries, with vendor-specific native support or emulated (here is an emulated pivot table for MariaDB)! All you need is to turn out a GROUP BY column into a PIVOT column. Automatic or manual column header selections. Enjoy it with other goodies like lookups, format, sort, drill-down on aggregates etc.
Generic Grouping Sets
Instant queries with grouping sets, CUBE or ROLLUP, with vendor-specific native support or emulated (here is an emulated CUBE for MariaDB)! Make a choice from the “cube” button, and Grouping Flag/Fields aggregates can be automatically added. They identify which combination of columns is used for GROUP BY in current row.
Result Effects
One-two clicks is all it takes to further change the layout of your results. Hide Nulls and Hide/Merge Consecutive Duplicate Values buttons bring interesting instant effects on previous query result. You can also show Summary and Highlight builder grids, which remain visible as well in full Results mode. With Summary you can specify grand total-style aggregate functions calculated after the query execution. Highlight will look for cell values according to a simple criteria.
Generic Window Functions
A large number of generated demo queries demonstrate other capabilities of our productivity tool. When supported, you may design window function queries, with ranking and analytic functions, and three specific builders for the OVER clause.
Data Xtractor includes all the functionality of Query Xtractor, but you can also add data visualization – as regular and inline charts – and design SQL queries for many more database.
As a combination of Visual, Query and Model Xtractor, in Data Xtractor you may also create diagrams with Entity-Relationship models for your database.