English Query harnesses a natural language processor to interpret sentences and process them into SQL queries that are sent to SQL Server for processing. If you had a table named Customers that contained a list of all your customers and their addresses, telephone numbers, ages, and email, you could issue the following requests for information:
Show me all the customers. How many customers are there? Show customers with their address and phone and email.
English Query examines these queries and converts them into standard SQL queries:
select CustomerID from customers select count(distinct CustomerID) from customers select CustomerID, address, phone, email from customers
Notice that in these examples, English Query didn't get all the columns from the Customers table. Instead, it returned just the customer identifier (in this case, the CustomerID). You can define which column identifies a row in a table, but, by default, English Query assumes that the primary key should be used.
In the case of an OLAP cube, English Query converts your questions into MDX to query directly against the cube.
The English Query parser is fairly robust. With a little help, it can understand commands as vague as "Who rented the most movies last week?" Although the parser can understand a wide range of questions, users will quickly learn the most efficient way to get the answers they need. The following sentences all generate the same SQL query in English Query:
Show me a list of all of the customers in my database. Are there any customers? Give me a complete list of my customers, please. List all customers. Customers.
All of these sentences produce this SQL query:
Select CustomerID from Customers
If you already know SQL, you are familiar with the rules that enable you to get answers to your questions. Listed here is a table that shows you how to translate the components of a SQL SELECT statement into the appropriate English Query words or phrases that provides the same result.
|SQL Clause||Corresponding English Query Word or Phrase|
Show or list, as in the following:
List the customers
Specify each column name in a with clause, separated by and:
Show customers with email and phone.
Include the table name as the noun in your sentence:
|WHERE||A wide variety of verbs or prepositions can be used to specify the search criteria, depending on the type of relationship between the entities, such as have, in (existence), between (range), before, after (date/time). The definition of the relationships is the largest part of an English Query application.|
|count() or sum()||
Specify How many, as in:
How many customers are there? How many customers have not rented movies this month? How many movies are romances?
Specify the word average, as in:
What is the average age of people who rented movies directed by Stanley Kubrick?
|min() or max()||
Ask for smallest/largest, lowest/highest, earliest/latest, least/most, or oldest/youngest:
Which is the oldest store? What is the latest date that a person rented the movie 'Full Metal Jacket'?
|GROUP BY||Specify for each or per:
How many movies for each customer? What is the average number of rentals per customer?
Specify a number or percentage of records to return:
List the ten oldest stores List the 5 oldest stores List the 5 customers with the most rentals