How Does English Query Work?


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.

Translating English Commands

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 

SQL Clauses and Their English Query Equivalents

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
SELECT

Show or list , as in the following:

 List the customers 
<column list>

Specify each column name in a with clause, separated by and :

 Show customers with email and phone. 
FROM

Include the table name as the noun in your sentence :

 List customers. 
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? 
avg()

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? 
TOP

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 


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net