SELECT

Once database is created, the user can query the information with SELECT statement.
The basic syntax for the SELECT statement is:

SELECT attribute1, attribute2, ... FROM table_name

SELECT with LAST, SINCE, or BETWEEN Clause:

The user can use LAST clause to query the last numbers of rows in a table by using the following syntax:

SELECT attribute1, attribute2, ... FROM table_name LAST number

For time-bounded sliding table, the user can query the last number of rows within a specified time window as follows:

SELECT attribute1, attribute2, ... FROM sliding_table_name LAST time_window

In addition, the user can query rows starting from a specified time in a time-bounded sliding table by SINCE clause. The syntax is:

SELECT attribute1, attribute2, ... FROM sliding_table_name SINCE date_time

Further, the user can also query data within a specified time frame by using the BETWEEN clause as follows:

SELECT attribute1, attribute2, ... FROM sliding_table_name BETWEEN start_date_time AND end_date_time

SELECT with FILTER BY SAMPLE Clause:

BigObject supports table sampling within FILTER BY clause. This is particularly useful to have a quick glance at a big table. The sampling syntax is as follows:

SELECT attribute1, attribute2, ... FROM table_name FILTER BY SAMPLE sample_number

The sample_number specifies the sampling ratio if it is a real number between 0.0 and 1.0. If it is an integer, it denotes the sample size of the table.

SELECT with WHERE Clause:

The user can specify the query with conditional filter using WHERE clause within SELECT statement.
The general syntax for this is following:

SELECT attribute1, attribute2, ... FROM table_name WHERE where_condition

The user can do query with string pattern match methods, such as LIKE or REGEXP (regular expression) in where clause as follows:

SELECT attribute FROM table_name WHERE attribute LIKE 'string_pattern'

SELECT attribute FROM table_name WHERE attribute REGEXP 'string_pattern'

SELECT with GROUP BY:

The user can take a step further in query by performing an analysis on a particular group of attribute(s).

The general syntax of such SELECT with GROUP BY statement is following:

SELECT attribute1, attribute2, ...., aggregate function()  FROM table_name GROUP BY attribute1, attribute2, ...

See aggregate functions for the list of aggregate functions that BigObject supports.

SELECT with HAVING Clause:

The user can use HAVING clause in the SELECT statement to specifiy filter condition for aggregate functions.

For example, the following query will return product brands with more than 2000 qtys:

SELECT Product.brand, SUM(qty)  FROM table_name GROUP BY Proudct.brand HAVING SUM(qty) > 2000

SELECT with ORDER BY Clause:

The user can arrange the query result with an ORDER BY clause. The default ORDER BY clause will arrange data shown in ascending order. The user can use the word DESC in the ORDER BY clause if he would like to arrange the data in descending order.
Below is the general syntax of SELECT with ORDER BY clause:

SELECT atrribute1, atrribute2, ... FROM table_name ORDER BY attribute [ASC | DESC]

SELECT with LIMIT Clause:

Sometimes, the query result may be too long, the user can specify number of record shown with a LIMIT clause.
The general syntax for such clause are following:

SELECT attribute1, attribute2, ... FROM table_name LIMIT [offset], row_count

SELECT attribute1, attribute2,...FROM table_name LIMIT row_count OFFSET [offset]

Note

If offset is omitted, the command will be executed from row 1.

The user can combine various select clauses in a select statement. The general syntax of a select statement is as follows:

SELECT attribute1, attribute2, ... FROM table_name
  [LAST clause]
  [FILTER BY clause]
  [WHERE clause]
  [GROUP BY clause]
  [HAVING clause]
  [ORDER BY clause]
  [LIMIT clause]

Case Examples:

After the database is completed, the staff of ABC company is curious to know which gender has greater overall purchase power in term of revenue generated from the total sales. He may issue following command to do a quick search:

SELECT Customer.gender, SUM(total_price) FROM sales GROUP BY Customer.gender

A table will be shown as following:

+-----------------+------------------+
| Customer.gender | SUM(total_price) |
+-----------------+------------------+
| Female          |       2782667.56 |
| Male            |       2687349.27 |
+-----------------+------------------+

If the ABC company staff wants to look for members whose age is over 60 to do some senior merchandise promotion, he can issue following command:

SELECT name, gender, age from Customer WHERE age > 60 LIMIT 10

He will get a long list of qualified people like this:

+------------------+--------+-----+
| name             | gender | age |
+------------------+--------+-----+
| Debra Fox        | Female |  77 |
| Philip Parker    | Male   |  63 |
| Helen Woods      | Female |  68 |
| Paul Johnston    | Male   |  65 |
| Lawrence Ruiz    | Male   |  74 |
| Lillian Harrison | Female |  63 |
| Julie Jordan     | Female |  62 |
| Denise Cruz      | Female |  64 |
| Doris Grant      | Female |  63 |
| Jesse Bryant     | Male   |  71 |
+------------------+--------+-----+

The staff may further organize above query result with an order by age.

SELECT name, gender, age from Customer WHERE age > 60  ORDER BY age DESC LIMIT 10

The return will be arrange as following:

+-------------------+--------+-----+
| name              | gender | age |
+-------------------+--------+-----+
| Gregory Chavez    | Male   |  80 |
| Catherine Watkins | Female |  80 |
| Nicole Wright     | Female |  80 |
| Joyce Hanson      | Female |  80 |
| Cynthia Wright    | Female |  80 |
| Kimberly Rogers   | Female |  80 |
| Mark Lawrence     | Male   |  80 |
| Joshua Schmidt    | Male   |  80 |
| Willie Garrett    | Male   |  80 |
| Earl Coleman      | Male   |  80 |
+-------------------+--------+-----+

On a separate occasion, the staff of ABC company is curious to know how many products carry the name beginning with "Fan" among all its current clients. The staff can issue either of the following two commands:

SELECT name FROM Product WHERE name LIKE 'Fan%'

SELECT name FROM Product WHERE name REGEXP '^Fan'

The staff will receive following result:

+---------------------------------------------------------------+
| name                                                          |
+---------------------------------------------------------------+
| Fanta orange                                                  |
| Fanta Apple                                                   |
| Fanta Orange 6 x                                              |
| Fanta Laranja||Refrigerantes                                  |
| Fanta Laranja lata||Refrigerantes                             |
| Fanta Laranja diet||Refrigerantes | FANTA LIGHT LATA 12/350ML |
| Fanta Uva||Refrigerantes                                      |
| Fanta Uva Light lata||Refrigerantes | FANTA UVA LIGHT 350ML   |
| Fanta maça lata||Refrigerantes                                |
| Fanta Citrus lata||Refrigerantes                              |
+---------------------------------------------------------------+

Finally, as we can recall, the original sales table that ABC company staff created looks like following:

+----------+-------------+------------+--------------+---------------------+-----+-------------+
| order_id | Customer.id | Product.id | channel_name | Date                | qty | total_price |
+----------+-------------+------------+--------------+---------------------+-----+-------------+
| 1        | 3226        | 2557       | am/pm        | 2013-01-01 00:04:05 |   8 |       52.24 |
| 2        | 6691        | 2631       | am/pm        | 2013-01-01 00:11:27 |   4 |       39.72 |
| 2        | 6691        | 1833       | am/pm        | 2013-01-01 00:21:03 |   1 |         6.9 |
| 3        | 4138        | 1626       | am/pm        | 2013-01-01 00:30:22 |   5 |        42.1 |
| 3        | 4138        | 375        | am/pm        | 2013-01-01 00:35:44 |   6 |       67.26 |
| 3        | 4138        | 3336       | am/pm        | 2013-01-01 00:45:12 |   8 |       41.68 |
| 3        | 4138        | 736        | CVS          | 2013-01-01 00:55:34 |   6 |        56.4 |
| 4        | 1292        | 4434       | 7-11         | 2013-01-01 01:06:00 |   6 |       86.64 |
| 5        | 5596        | 4135       | 7-11         | 2013-01-01 01:08:42 |  10 |        50.1 |
| 5        | 5596        | 3528       | am/pm        | 2013-01-01 01:18:10 |   9 |       94.68 |
+----------+-------------+------------+--------------+---------------------+-----+-------------+

If he just wants to take a glance of the rows 2-7, he may use one of the following SELECT command with LIMIT clause:

SELECT * from sales LIMIT 1, 6

SELECT * from sales LIMIT 6 OFFSET 1

The following list will be returned:

+----------+-------------+------------+--------------+---------------------+-----+-------------+
| order_id | Customer.id | Product.id | channel_name | Date                | qty | total_price |
+----------+-------------+------------+--------------+---------------------+-----+-------------+
| 2        | 6691        | 2631       | am/pm        | 2013-01-01 00:11:27 |   4 |       39.72 |
| 2        | 6691        | 1833       | am/pm        | 2013-01-01 00:21:03 |   1 |         6.9 |
| 3        | 4138        | 1626       | am/pm        | 2013-01-01 00:30:22 |   5 |        42.1 |
| 3        | 4138        | 375        | am/pm        | 2013-01-01 00:35:44 |   6 |       67.26 |
| 3        | 4138        | 3336       | am/pm        | 2013-01-01 00:45:12 |   8 |       41.68 |
| 3        | 4138        | 736        | CVS          | 2013-01-01 00:55:34 |   6 |        56.4 |
+----------+-------------+------------+--------------+---------------------+-----+-------------+