QUERIES

Queries are the CJ blocks for accessing and manipulating databases.

Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Stream

The stream block is used to select data from a table. 

Here we select the whole Employees database and transfer it to the collection item.@Employees.

Query

The query block can store the result set of a stream. 

The query is treated as a variable, you can pass, set, and rename it.

Sort by

The block sort by is used to sort the query-set in ascending or descending order. 

The sort by sorts the records in ascending order by default. To sort the records in descending order, switch it to desc.

In the example below, we sort the database of employees by the emp.Salary (descending order), so the first variable in the result-set will have the biggest value. We get it and break out of the loop.

Where

The where block is used to filter records.

The where is used to extract only those records that fulfill a specified condition. 

Here we get all the Employees with a condition emp.Country is not India.

Group & aggregate

The group by block groups rows that have the same values into summary rows, like "find the total salary of employees considering their gender".

The group by is often used along with aggregate functions( count, min, max, sum, average) to group the result by one or more columns.

Below is the loop that creates groups considering the gender of an employee and aggregates the sum for each of these sums. After that, we add new rows to our item.@Employees collection.

Aggregate functions

The block allows you to choose an aggregate function and set the name to it.

The aggregate functions include:

Count - counts the number of items in a group.

Sum - sums up all values of the selected group.

Average - calculates the mean value for a selected column in a group.

Max/Min - returns the Maximum/Minimum value of a given group’s column.