Demo Queries

What Demo Queries Do

Within any connected database, you may add, run, customize, delete or regenerate sets of built-in demo queries.

1. Demo queries help you quickly understand how our visual SQL query builder works, before building your own custom queries.
2. Demo queries help you look at valid generated SQL queries in your own database context, for some typical use cases and operations.
3. Demo queries can be changed, customized, and cloned. After being deleted, they can be regenerated any time you want. This gives you a live playground to play with our visual SQL.
4. Demo queries are usually database-independent, in the sense they can successfully run against any supported database, regardless of your database tables, as most of them do not depend on tour tables.
5. Some demo queries are specifically generated just for one of our demo databases, such as Chinook, when we recognize you connected to such as sample. This helps you run table-based queries, similar to the ones you will have in real life.
6. Demo queries are always guaranteed to run with any database and never require a paid subscription. You may always run them for free for SQLite, SQL CE or Firebird databases, but also for SQL Server and Oracle, MySQL and PostgreSQL, DB2 and any other supported open-source or commercial database.

How to Generate Demo Queries

Demo queries are always installed under the Queries > Demo Queries folder, in Data Xtractor, Query Xtractor or Visual Xtractor. Right-click on any tree node and click on Add Demo Queries menu command. Or click on the add demo queries link within the tree control. This will open the following popup:

Here you can select or unselect demo queries not already installed or that have been deleted or renamed. You cannot install demo queries already present. By default, all missing queries are selected, so you can just click Add and everything missing will be (re)generated.

You may or may not see some of the queries, as described in the following chapter. For instance, the Chinook Tutorial set will be installed only when a Chinook connected database is detected. Inline Charts and Charts are not installed on Query Xtractor, as they require the data visualization module of Visual Xtractor.

The previous image shows there are in total 263 demo queries you may generate and use. Only four of them – the ones selected on screen – are missing and can be generated here. You get an information on all demo queries here: their query names, descriptions, category and set. When already generated, an icon shows you what type of query is that. To install other uninstalled queries, unselect those and manually select – with CTRL and/or SHIFT click – the ones you want.

Sets of Demo Queries

All demo queries are subject to frequent changes. We may add more queries or sets, remove some or change any demos with no further notice. Here are the demo query sets today:

• Basic – database-independent queries with basic SQL functionality. Many queries use on-the-fly constant data generated by the SELECT UNION subqueries from FruitsU. They can be installed in Data Xtractor, Query Xtractor or Visual Xtractor.
• Inline Charts – database-independent queries with inline charts. Most queries use the same FruitsU subquery from the Basic set. They can be installed in Data Xtractor or Visual Xtractor.
• Charts – database-independent queries that generate a separate chart. Most queries use the same FruitsU subquery from the Basic set. They can be installed in Data Xtractor or Visual Xtractor.
• Chinook Tutorial – installed only on one of our Chinook sample databases, when we recognize tables with the same names. The queries use data stored in these tables, as in most real-life scenarios.They can be installed in Data Xtractor, Query Xtractor or Visual Xtractor.

How to Use a Demo Query

Just double-click on a demo query tree node. The query will be loaded on the right pane and run in results mode. Here is the Annual Sales of Selected Countries demo queries, opened in results mode, installed on a Microsoft SQL Server Chinook database sample, under the Queries > Demo Queries > Chinook Tutorial > Crosstab Pivot Queries folder:

The top SQL toolbar button will show you the generated SQL. Make your unpopulated empty chart smaller, to see more of the SQL syntax.

Click on the top Design toolbar button, to see the visual SQL query builder and the diagram view. You may later show and hide each individually, with the Builder and Diagram top toolbar buttons. Minimize the chart, as you don’t need it in design mode.

You can frequently hide the diagram view as well, once you selected your shapes and checked the shape items. Click on the Chart’s checkbox to completely hide the chart as well. Hide also the Sort and Format builders, they will appear now disabled. They are still on screen, because you have custom data there. Run the new query (still in design mode).

This is a rather complex crosstab/pivot query, that can be either be fully or partially emulated with our built-in powerful engine. Switch to SQL to see the query itself. The PIVOT values are automatically found with an internal query, which was generated as well:

-- Internal query used to get the pivot values:

SELECT invoice_.[BillingCountry] AS [Country]
FROM [dbo].[Invoice] AS invoice_
WHERE invoice_.[BillingCountry] IN ('USA', 'United Kingdom', 'Canada')
GROUP BY invoice_.[BillingCountry]
ORDER BY 1 DESC

-- 3 pivot values: USA, United Kingdom, Canada

With the native Microsoft SQL Server PIVOT, your query will look like:

WITH t__1 AS (
SELECT DATEPART(YEAR, invoice_.[InvoiceDate]) AS [Year],
   invoice_.[BillingCountry] AS [Country],
   invoice_.[Total] AS [Sales]
FROM [dbo].[Invoice] AS invoice_
WHERE invoice_.[BillingCountry] IN ('USA', 'United Kingdom', 'Canada'))

SELECT [Year], [USA], [United Kingdom], [Canada]
FROM t__1
PIVOT (
   SUM([Sales])
   FOR [Country] IN ([USA], [United Kingdom], [Canada])
) AS t__2
ORDER BY [Year] DESC

But you may also emulate this query and get a completely different view. Check the Emulate Pivot/Unpivot option, to get an equivalent powerful generated SQL query, as in all other relational databases with no built-in PIVOT support:

SELECT DATEPART(YEAR, invoice_.[InvoiceDate]) AS [Year],
   SUM(CASE WHEN invoice_.[BillingCountry] = 'USA' 
      THEN invoice_.[Total] END) AS [USA],
   SUM(CASE WHEN invoice_.[BillingCountry] = 'United Kingdom' 
      THEN invoice_.[Total] END) AS [United Kingdom],
   SUM(CASE WHEN invoice_.[BillingCountry] = 'Canada' 
      THEN invoice_.[Total] END) AS [Canada]
FROM [dbo].[Invoice] AS invoice_
WHERE invoice_.[BillingCountry] IN ('USA', 'United Kingdom', 'Canada')
GROUP BY DATEPART(YEAR, invoice_.[InvoiceDate])

To see just the end-result data and nothing else, click on the Results top toolbar button. The results view will hide the visual SQL query builder with the unnecessary design columns. And you can also further drill-down on each row to see more of your contextual aggregate data. The image below shows you all granular entries whose data has been combined for the year 2011:

You can now follow a similar approach with your own designed queries…