The basic and one of the most used methods in Advance Data Analytics is query through filter using Find statement.

The general syntax for Find statement is:

Find attribute FROM table_name

Attribute can be more than one separated by comma. An aggregate function can also be applied along with other attributes.

The user can store the result found into another table with the general syntax:

CREATE TABLE table_name AS (FIND statement)

Let's apply this in the ABC company examples.

While the database is created using the methods shown in Basic Data Management by the staff of ABC company, the staff may want to query some information in the database from time to time. For example, he may search the quantity bought for each product by gender from the database with following command:

FIND Product.name, Customer.gender, SUM(qty) FROM sales

The long list of result may looks like below:

In particular, the staff may further narrow down the list with specific instruction or quantifier. Below are some examples:

FIND 10% Product.name, Customer.gender, SUM(qty) FROM sales -- List 10% of the results

FIND TOP 10% Product.name, Customer.gender, SUM(qty) FROM sales -- List top 10% of the result based on SUM(qty)

FIND TOTAL 10% Product.name, Customer.gender, SUM(qty) FROM sales -- List and arrange the result from top and shows those whose SUM(qty) accounted for at least 10% of total quantity.

Note
There is a difference between FIND TOP 10% and FIND TOTAL 10%. TOP 10% returns the top 10% of the result, while TOTAL 10% acts as an aggregates function and returns the result that accounts for 10% of the total available quantity.

Conditional Filter and Auxiliary Clause:

The user may set more conditions to the attributes within the FIND statement to further narrow down the query. Below are some conditional filter examples:

FIND TOP 20% Product.brand, Customer.gender = 'Male' FROM sales

FIND TOP 20% Product.brand, Product.brandOwner = 'Pepsico', Customer.gender = 'Male', SUM(qty) > 100 FROM sales

The user can also place filter conditions within the auxiliary clause using {filter: conditions} in the end of the statement. Below are examples of Find statements with auxiliary clauses:

FIND 10% Product.brand, Customer.gender = 'Male', SUM(total_price) FROM sales {filter:Product.brandOwner = 'Pepsico'}

FIND 10% Product.brand, Customer.gender, SUM(total_price) FROM sales {filter:(Customer.gender = 'Male') AND (Product.brandOwner = 'Pepsico') }
Note
Use double quote to assign a filter string when the specified conditional string includes single quote. For example: FIND Product.brand="Lay's", channel_name FROM sales.

Sortby:

The user can specify how to arrange the result of the query in the FIND statement within auxiliary clause by using sortby phrase.

Example:

FIND TOP 10% Product.brand, SUM(total_price) FROM sales {sortby:SUM(total_price)}

Order:

Besides using top / bottom to arrange the order of the result shown, the user can also use order phrase (desc / asc) in the auxiliary clause to specify order.

Example:

FIND 10% Product.brand, sum(total_price) FROM sales {order:desc}