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 block is used to select data from a table.
Here we select the whole Employees database and transfer it to the collection
query block can store the result set of a stream.
query is treated as a variable, you can pass, set, and rename it.
sort by is used to sort the query-set in ascending or descending order.
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 block is used to filter records.
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
group by block groups rows that have the same values into summary rows, like "find the total salary of employees considering their gender".
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
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.