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.

Download Free Query Xtractor


Query Xtractor Features Summary

FeatureDescription
Visual SQL Query Builderdesign SQL queries instead of typing them
SQL Query Generatorgenerate SQL SELECT queries from the visual SQL builder
Simulated SQL Queriesgenerate equivalent database-specific SQL from any query
Emulated SQL Queriesalternative to native implementation or formula-based queries
Generic User Interfacesame visual SQL builder, formulas and functions for any database
Crosstab PIVOT Queriesemulated and native PIVOT SQL queries, to generate crosstabs
UNPIVOT Queriesemulated and native UNPIVOT queries, to generate property-value rows
Transpose Queriesemulated SQL queries to switch rows with columns
Sort with Nullsemulated and native ORDER BY clause with NULLs first or last
Grouping Setsemulated or native GROUPING SETS, with CUBE and ROLLUP
Drill-Down on Relationshipsautomatic drill-down queries for FK-based relationships
Drill-Down on Aggregatesautomatic display of all rows aggregated by a GROUP BY query
Nested Aggregate Queriesauto-generation of subqueries with simple external aggregates
Extending Aggregatesgrouping, list and median emulation, count with sum, aggregate fixes
Window Functionsfriendly interface for native ranking and analytic functions, and the OVER clauses
Statistical Functionsemulation or native variance/deviation/correlation functions
Regression Functionemulation or native linear regression functions
Summary Aggregatesclient-side summaries at the bottom of any grid result
Search Highlightsclient-side highlights of specific search patterns in the grid result columns
Display Formatclient-side format strings for different data types in the grid result columns
Data Type Functionscommon interface with database-specific string, numeric or date/time functions
Conversion Functionscommon interface with database-specific CAST and other convert functions
Math Functionscommon interface with database-specific formulas, math and trigonometric functions
Quoted Aliasesautomatic generation and quoting of all internal query identifiers
Parameterized Queriesinstant switched view to queries with generated parameters, to prevent SQL injection
Alternative Query Viewsinstant 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 Reusereuse of any grid column as repeating expression in the generated SQL query
More Reliable Queriesauto-fixes for known math and NULLs problems, data type checking etc
Fluent Expressionsobject-oriented user interface, with each formula extending the previous expression
Extended Joinsexclude joins added to the typical inner left/right joins and cross joins
Intersect and Exceptemulated or native INTERSECT and EXCEPT clauses, added to the UNION clause
Lookup Fieldsrelationship-based generated joins to replace identifiers with descriptions
Chain Joinsautomatic generation of all joins for relationships bypassing multiple tables
Duplicate Valuesquickly count and show unique values or duplicates
Reusable Subqueriesreuse any query in other new queries
Automatic Paginationautomatic generation of SQL queries to get subsets of continuous rows
Skip and Keepautomatic generation of database-specific SQL to skip some rows and/or keep only the next few rows
Constant Table Queriessimple creation of UNION or VALUE queries, to generate constant-based results
Collapsible Result Rowsgroup together the same consecutive sorted values in the same column, to create reports
Export Query Resultscopy or export the SQL query result as text, images, PDFs etc
Demo Querieslearn from hundreds of database-dependent and independent auto-generated queries
All and Some/Anyemulated or native ALL and SOME/ANY aggregate clauses

Visual Query Builder

query-builder-10

Design grids with top diagram area to select table/view/subquery columns from specific shapes:

  1. Field: start with fields, constants or global function calls. Each grid column becomes a reusable query expression.
  2. Lookup: alternative display field for any single foreign key. Like department and manager names instead of numeric IDs!
  3. Formula: fluent contextual transformations of the top selection. We divide here the salary by 10 and convert it to an integer.
  4. Where: intuitive filter, with string matching patterns, comparisons, list checks.
  5. Sort: sort order index, with NULLs first or last – available for any platform, natively supported or emulated!
  6. Format: spreadsheet-like display format, after the query execution.

Instant SQL Query Generator

query-builder-sql

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

query-results-mode-10

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

query-group-drill-down

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

query-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

query-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

query-pivot-mariadb

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

query-grouping-sets-10

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

query-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

query-window-functions-10

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.

ImportantData 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.

Download Data Xtractor