Wednesday, April 24, 2024
Home Products Services Downloads Online Store Support Company Careers

Benefits of using SQL-Builder  

Chau Tran 
May, 2005

Summary: This 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 . 

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

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

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

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.

© NQL Software Inc. 2005- 404-834-3458