Skip to main content

Case Study: Learn SQL with Query Xtractor

It’s very possible that our tools offer the best way to learn SQL (Statement Query Language), the de-facto language of all relational databases! You may know nothing about SQL, but you’ll still be able to easily connect to a supported database. Let our tools collect the required amount of information about the schema, generate relationship items and other artifacts.

Then you can simply start looking at the generated SQL in so many queries and places. Beside the custom queries you may build and design from scratch, we transparently implement so many other inner mechanisms with SQL, that it is hard to not improve your own SQL skills just by this simple exposure!

Generated SQL Query

Your query could be in Results or Design mode, but in both cases you may click on the big SQL toolbar button and instantly get the SQL generated for your query and your specific database platform.

sql-mode.sql

You never write SQL, but each incremental design change in the visual builder may automatically trigger a re-generation of the SQL query. You see immediately how your action was translated into SQL.

Table-Based Queries

We browse tables with simple table-based queries you may not change (of course, you can always create a customizable clone). Just switch to SQL from any table-based result and check how we automatically populate the SELECT and FROM clauses, and eventually ORDER BY (for tables with primary keys) and Skip-and-Keep (LIMIT, OFFSET, TOP etc – when paging is on):

table-based-query

Page Queries

Any query using tables is by default created with paging on. This is to prevent collecting by mistake the millions of rows of a very large table you might encounter. And our paging mechanism is implemented with plain SQL as well, and it is transparent. Initial query may add those elements for page 1, selecting a limited amount of rows. LIMIT 1000 in previous query sets such a limit for page 1 of a table-based query.

When you navigate to another page of results and switch to SQL mode, you’ll see the query generated for that page. Intermediate pages may also skip first an amount of rows. Paging is usually very different from one platform to another, and may get very complex for old platforms, such as Oracle or SQL Server. Following Oracle query may be just a simple SELECT DNAME FROM dept, but you may get some very complex SQL for an intermediate page:

oracle-paging

Paging is basically a Skip-and-Keep operation (you skip m rows and you keep next n), that you may implement separately with Skip and Keep fields. This can be also further combined with paging, resulting in pretty complex queries with very simple interfaces.

Nested Subqueries or CTEs

Previous Oracle query uses subqueries listed one by one in a WITH clause. This is called CTE (Common Table Expression) and is supported by many platforms. But you can always show all subqueries as nested subqueries, with on single button click:

oracle-paging-nested

You may go even further and replace all subquery definitions by their alias names, when you want to focus on the outer query alone and remove complexity. Of curse, such query is not executable, but it helps you greatly better understand what you do by offering so many possible query views.

Drill-Down Queries

Whenever you navigate through relationships, we transparently generate one parameterized query per relationship and volatile specific query instances for each row you look at. To list all subordinates of the president KING, we list all employees whose MGR field value is 7839 (KING’s employee id):

drill-down-query

Generated Demo Queries

For each newly connected database, we automatically generate a variety of data models and demo queries. These generic queries do not depend on your specific data and look the same regardless on the database platform. But their generated SQL is usually very different from one platform to another.

Start by running these queries as they are, at look at the generated SQL queries. Then clone them into customizable queries in design mode, and gradually adjust them. You learn by practicing incremental changes and observing how they translate into the new SQL, the end result, and eventually the errors they may throw.

demo-query-sql

Simulated Queries

Every query may be simulated for any other supported platform, with a simple mouse click. You do not have to have those databases created, or their platforms installed. You may simply check the SQL of your query for Oracle, SQL Server, PostgreSQL, MySQL, SQLite etc etc without having your current database translated into one of those platforms as well! Without actually having Oracle, SQL Server or anything else installed!

And look how different the previous generated SQL query may look in Microsoft Access:

demo-query-access-sql

 

Leave a Reply