Benefits of using SQL-Builder
Chau Tran
NQLSoftware
May, 2005
Summary: This
White Paper explains the benefits of using SQL-Builder -
To save development time and to give the end user an
extremely fast, flexible and effortless way to query
relational databases.
Brief Background
Almost every software application using a database
provides a way for the user to query the database,
dialog base or submit a SQL Ad-hoc query. The Dialog
base is not flexible, and because of this, many dialogs
may have to be built. To enter even a simple SQL Ad-hoc
query, the required structure of SQL syntax is too
complicated for most end-users. SQL-Builder provides an
efficient means to resolve these problems.
SQL is not a language
for everyone.
The basic SQL query statement contains three clauses:
“select”, “from” and “where”. Each of the
tables or views that participate in the query have to be
included in the “from clause”. The “where
clause” contains “select criteria” and “table
joins” if more than one table participates in the
query. Joining tables is time consuming and sometimes
difficult. With this complexity, developers often spend
hours, days or weeks writing a SQL query. To provide a
more flexible, simpler and faster solution for the
end-user, the programmer and the Database Administrator,
NQLSoftware developed SQL-Builder as a tool to write SQL
queries faster and with a simpler syntax. This what we
refer to as NQL (Natural Query Language).
The basic NQL query
contains two clauses: “select” and “where”. The
“from clause” is omitted completely. The “where
clause” only contains “select criteria”, and no
“table joins” are needed. From the “select
clause” and “select criteria” in the “where
clause”, SQL-Builder uses a proprietary algorithm to
determine which tables participate in the query and
joins these tables appropriately, then builds the SQL
query.
The following are
examples to demonstrate and simplify these instructions.
Northwind, an example of MS-SQL database is being used .
Example:
NQL query:
show suppliers where customer city is London
SQL-Builder translates to
SQL as follows:
SQL query:
SELECT Suppliers.* FROM SUPPLIERS, PRODUCTS, [ORDER
DETAILS], ORDERS, CUSTOMERS WHERE ((Suppliers.SupplierID
= Products.SupplierID) and (Products.ProductID = [Order
Details].ProductID) and (Orders.OrderID = [Order
Details].OrderID) and (Customers.CustomerID =
Orders.CustomerID)) AND (Customers.City = 'London')
In the example above,
“customer city” is an “alias” of
“Customers.City” this makes the query closer to a
natural English sentence.
See Screenshot
1.
NQL query:
show employees who live in London
SQL query:
SELECT Employees.* FROM EMPLOYEES WHERE Employees.City =
'London'.
See Screenshot
2a.
NQL query:
show how many employees who live in London
SQL query:
SELECT COUNT(*) FROM EMPLOYEES WHERE Employees.CITY =
'London'
See Screenshot
2b.
NQL query is an easier
language statement to understand than the SQL query
statement isn't it?
Now you see how simple the NQL query is compared to the SQL query statement.
Dialog base with NQL:
Since the NQL query syntax is very simple, it needs only
the “select clause” and “select criteria”. If
your application has a dialog to build the select clause
and select criteria, this dialog can build any query.
The “Click Query” feature of SQL-Builder is an
example of such a dialog. There is no need to build many
query dialogs, as is the case when using SQL. The
Click Query of SQL-Builder is pick and click style.
Unlike other products, Click Query allows user to select
more than one search objects and filters on any object
attributes. With this feature and the mouse only, users
may issue and kind of query, simple or complex,
programmers may quickly create views or stored
procedures.
See Screenshot
3, 4
Ad-hoc query with NQL:
With the simplicity of the NQL syntax, natural,
English-like query, developers spend less time writing
queries. Using NQL, It now takes only seconds to write
queries, and virtually anyone can now write queries,
they can write queries with their own spoken languages.
The NQL Ad-hoc query or Natural Language Query may
be an option for your software application. Natural
Language Query of SQL-Builder features with
auto-complete, highlight the closet matched word while
user typing the query. Also users may save the query for
later use.
SQL-Builder
understands every word in the sentence and translates
accurately to SQL.
See Screenshot
1, 2
Relational Database
Keyword Query with NQL:
Keyword searches are popular in the field of document
searches. Are
you able to query your relational database using
Keywords? Yes, now you can. SQL-Builder uses a
proprietary algorithm to index your database to locate
the data. When search it locates the data and joins
tables appropriately to return the correct results.
SQL-Builder does not have any restrictions requiring all
keywords to be located in one data cell. Here is an
example; Suppose you are searching for Boston seafood
products. You simply select "products" from
the “search for” combo box and enter "seafood
Boston" in the
“with all the words” text box, then click the
Search button. In
the above scenario, “seafood” is in the
"categories" table; however, “boston “is
in "Suppliers" table.
In actuality, NONE of the keywords searched in
the above example need to be in the "products"
table which we search on.
See Screenshot
5, 6
Dictionary:
SQL-Builder features a
dictionary that allows users to define words,
define phrases, build their language, configure
output, and save queries. When a word or a phrase
defined it becomes a valid words, user may define new
words, new phrases base on the defined words,
recursively.
Data Location:
Users can quickly locate where the data resides. If you
know the keyword, say "Seafood", you may use
the Keyword Query, don't specify the search object and
search by keyword "Seafood", SQL-Builder will
find all data cells (table and column) that contain the
keyword "Seafood", for you. You may search by
table name or column name. Suppose you want to know what
tables store first name, you may use the WordBuilder of
SQL-Builder, enter "first%name" in the new
word edit box and click on the filter button,
SQL-Builder will get all tables and all columns where
the name is first%name ('%' is wild card).
Conclusion:
SQL-Builder increases productivity over the entire
enterprise by saving a significant amount of development
time for the programmer and Database Administrator and
gives the end-user a faster, more flexible method to
query SQL databases.
|