Creating Queries

In traditional database systems, queries are the most common way for users to make requests to the database engine. Queries are used to communicate with the database to get it to perform various data operations, such as to selecting a set of records, or to adding, modifying, or deleting data. Typically, queries are submitted to the engine in the form of "SQL statements"-- commands that are part of the "Structured Query Language."

Strata enables you to query your data in this tradition by using the Query Builder. Specifically, you can use Strata to create "select" queries to retrieve a set of records from one or more tables, and to combine these records into a new data set.

In general, the Query Builder make it easy for people not familiar with the Structured Query Language to develop queries that can be saved and run in the future, either manually or via the job scheduler.

To create a query, select New > Query from the File menu and the query builder will appear in a new tab.

As shown in the above screenshot, you may add tables to query in the top part of the Query Builder. To add tables, either drag them from the project panel or click the Add Tables button on the top left of the query builder.

In addition, you can relate (or join) multiple tables together that can be used in your query. To do this, select a field from the main (parent) table to the related field in the second (child) table. The parent table will drive the relationship of all child tables.

The default join operation is an "Inner Join", which will join only on matching records in both data sets. To select a "Left Outer Join", which contains all matching records as well as all non-matching records from the parent table, right-click on the black line linking the two tables and select "Left Outer Join."

You can also output the data set generated by the query to a new or existing table by using the Output Table box on the upper right of the page. Enter a new table name in the box or select Browse to find an existing table from your Project to overwrite.

To remove any duplicate records from the output results, click the checkbox next to the "Select Distinct" option box at the top right.

This section will discuss the bottom part of the query builder, which lets you define your query processes such as adding fields and sorting and filtering your results.

To begin, you need to add fields to your query. To do this, either double-click on the fields listed in the table above, or simply drag them from the table down to the Query Definition area. When you drag fields into the Query Definition area, they appear in a certain syntax that designates the table name and the field name. This is important to help distinguish field names if you are using multiple tables. The syntax is as follows:

So, if your table was named Web_logs and the selected field was called Referrer, the field would appear in the query definition as follows:

If you have multiple fields, you can change the order by selecting a field and dragging it up or down within the Query Definition section.

To remove a field, select the field and then click the delete button on your keyboard.

On the far left of each row is a checkbox. When the checkbox is checked the field will appear in your output table. If you do not want to have the field in your output table, remove the check from the box.

You may also create calculated fields within your queries. These calculated fields use formulas like normal calculated fields found within a data table. However, in queries, the calculated fields in the output table are "fixed" instead of dynamic. To create a calculation, develop your formula within the "Input Formula" area. For example, suppose you had the following field in the query definition:

Some of these log referrers might be uppercase and lowercase. To make your analysis clearer, you may want to turn every referrer into uppercase. To do this, you would modify the Input Formula using the UPPER() function:

When creating a query, you can also specify the names of your output fields. As a default, these are set to the original names of the fields. However, to edit them, simply double click on the Output Field section and change the name.

Once you have selected a group on a given field, you can select aggregate functions for other fields, such as minimum, maximum, or sum. You can also add a Count field or a Group ID field. A count will provide you with the total number of records per group. A Group ID will simply add a record number to each group.

NOTE 1: If you do not select a Group By function first, the aggregate functions, Count and Group ID will override the query and produce an output table with a single records. In these cases, because no group has been selected, the function assumes the entire table is a single group.

NOTE 2: If you have a field in the Query Definition and select the function Count or Group ID, these functions will override anything else (selections, formulas, etc.) in that row.

Within your query, you may specify the sort order of your output results. To do this, select the field you wish to sort on and the use the drop-down list to select "1. Ascending" or "1.Descending" from the list for your first order priority. To add a second sort priority, select "2.Ascending" or "2. Descending" from the list in a different field.

NOTE: The query builder will not allow you to select two sort orders that have the same sort priority. So, for instance, you may not select "1.Ascending" on two different fields.

You may also filter records in your query. To do, select the field you wish to filter and then type your filter criteria in the Criteria box (just a plain number or text field is needed; no formula is required). If you wish to add further filter criteria, you need to determine if the filter is an "AND" or an "OR" condition:

For example, if you want to filter where field1 = A AND field 3 = B, the query definition would be as follows:

Field	Criteria	Or	Or
field1	A
field2
field3	B
field4

If you want a filter where field1 = A OR field4 = C, the query definition would be as follows:

Field	Criteria	Or	Or
field1	A
field2
field3
field4		C

If you want a filter where field1 = A OR field 2 = B OR field 2 = C, the query definition would be as follows:

Field	Criteria	Or	Or
field1	A
field2		B	C
field3
field4

You can also create custom filters by creating logical formulas. For instance, you may specify criteria like:

In addition, you may also create more complicated boolean expressions by adding function names. To do this, you must add a colon (:) before the formula or the query will not run properly. This effectively passes the expression directly into the SQL Statement, so syntax needs to be correct. An example formula, where the field is called "fieldname", might look like this:

Once you have finished defining your query, you can save it by selecting the Save option from the File menu, or simply by clicking on the Save icon. A Save dialog will appear so that you can name your query and save it to the project panel.

In order to run your query, select Run Query/Script from the Tools menu. The query will run and a table will appear. The table is just another view of the query definition. Therefore, you can toggle between the Design View from the View menu and the Table View from the View menu (or by toggling the View icon on the top-right corner). Additionally, the query builder provides a SQL view, which shows the SQL syntax of the query. This view is read-only in that you cannot manually edit the SQL syntax to change the Query. However, you may copy the text to use the SQL syntax elsewhere, like into a script.