Introduction

ZCQL is Catalyst's own query language that is similar to MySQL or PostGres. Zoho Catalyst supports ZCQL query language to perform data manipulations in the Catalyst Data Store.

You can perform the following operations on the tables in your Data Store using ZCQL:

• Retrieval: Obtaining records from existing tables
• Insertion: Inserting records into the existing columns of an existing table
• Updating: Updating the value of existing columns in an existing table
• Deletion: Deleting records from an existing table

You can execute a variety of DML queries using ZCQL to obtain or manipulate data based on specific criteria by using various clauses and statements such as the SQL Join clauses, Groupby statements, OrderBy statements, and the WHERE condition. ZCQL also supports several built-in SQL numeric functions that help you execute arithmetic operations easily.

You can implement ZCQL queries in your Catalyst application's source code, such as in the functions or the client component. For example, you pass a ZCQL query in a function's body to retrieve data from a table in the Data Store and process it further.

The Catalyst console also provides a ZCQL query execution window, where you can execute queries and view the responses. This allows you to test ZCQL queries easily, before implementing them in your application.

SDK and API documentation

Catalyst offers ZCQL in the Java, Node.js, and Web SDK packages, and as an API. For code samples on executing ZCQL queries in these programming environments, refer to these help pages:

• Execute ZCQL Query - Java SDK
• Execute ZCQL Query - Node.js SDK
• Execute ZCQL Query - Web SDK
• Execute ZCQL Query - API

You can refer to Catalyst Tutorials to get an idea of ZCQL's implementation in the function or client code of the applications.

Benefits

• Can perform data retrieval, insertion, updating, and deletion operations in the Catalyst Data Store
• Similarity to MySQL ensures ease of use and there is no separate learning curve
• Can test the execution of the queries in the console before implementing them in the application
• Can pass a ZCQL query in an API's body using the SDKs
• Can perform arithmetic and numerical operations on the result set using the ZCQL built-in functions

General Syntax of SELECT

ZCQL supports data retrieval query operations using the SELECT command. This command lets you select a column or a set of columns from a base table and view the data records within the command's scope.

The general syntax for a basic ZCQL data retrieval operation is as follows:

SELECT {COLUMNS} FROM {BASE_TABLE_NAME} [JOIN_CLAUSE] [WHERE {WHERE_CONDITION}] 
	[GROUP BY {GROUP_BY_COLUMN}] [ORDER BY {ORDER_BY_COLUMN}] LIMIT [{OFFSET}],{VALUE}

The base table is the table you execute the query on.

We will discuss each ZCQL operation in detail with an example database and sample queries that you can execute on it.

Example Database:

Imagine you're developing a ticket booking application where users can view the movie listings and showtimes for various theaters in a city, and can book movie tickets using the application. Let's create a table named 'Movies' that contains the showtime listings of the movies played in various theaters.

Sample records from the Movies table are given below:

Basic SELECT

The SELECT statement is used to select the columns from a base table and display its records. You should mention the names of the columns that are to be displayed in the result set.

The syntax for using a basic SELECT statement is:

SELECT column_name(s)
FROM base_table_name

Example:

To select the MovieID and MovieName columns from the Movies table, execute the following query:

SELECT MovieID, MovieName from Movies

It will generate the following output:

SELECT *

You can fetch records from all the columns of a base table, instead of selecting records from a particular column or columns, by using a '*' following the SELECT statement. The '*' denotes all the columns of the base table.

The syntax for selecting all the columns from a base table is:

SELECT * FROM base_table_name

Example:

To display the records from all the columns of the Movies table, execute the following query:

SELECT * FROM Movies

This will display records from all the columns in the Movies table.

Before we discuss the various clauses and statements available for the SELECT operation, let's discuss the INSERT, UPDATE, and DELETE operations.

General Syntax of INSERT

You can insert a record in a table by passing the values for each column of that table for that record.

The general syntax of a basic ZCQL data insertion operation is as follows:

INSERT INTO {BASE_TABLE_NAME} VALUES (VALUE1, VALUE2,..)

You must pass the values in the same order as the columns are present in the table.

Example:

To insert a record in the Movies table, execute the following query:

INSERT INTO Movies VALUES (2060,'Mission: Impossible – Fallout', '2018-7-27', '16:00:00', 053)

Partial Insert

You can also pass values only for specific columns in a table by specifying the column names which the values need to be inserted for. The syntax for a partial insert is as follows:

INSERT INTO {BASE_TABLE_NAME} (COLUMN_NAME1, COLUMN_NAME2,..) VALUES (VALUE1, VALUE2,..)

To insert a record and pass values for the MovieID and TheaterID columns alone in the Movies table, execute the following query:

INSERT INTO Movies (MovieID, TheaterID) VALUES (2061, 052)

General Syntax of UPDATE

The UPDATE command enables you to update specific column values of existing records of a table in the Data Store. You can update the values of columns based on certain conditions. These conditions can be specified using the WHERE clause, which we will discuss later on in detail.

The general syntax of a basic ZCQL data updation operation is as follows:

UPDATE {BASE_TABLE_NAME} SET {COLUMN_NAME}={VALUE}[WHERE {WHERE_CONDITION}]

Example:

To update the value of a specific movie name referred to by its MovieID in the Movies table, execute the following query:

UPDATE Movies SET MovieName='The Equalizer 2'WHERE MovieID=2056

General Syntax of DELETE

The DELETE command enables you to delete one or more records from a table permanently. You can indicate the records to be deleted using the WHERE clause, which is explained in the next section, and specify the conditions.

The general syntax of a basic DELETE statement is as follows:

DELETE FROM {BASE_TABLE_NAME}[WHERE {WHERE_CONDITION}]

Example:

To delete the record of a specific show date in a particular theater from the Movies table, you can execute this query:

DELETE FROM Movies WHERE MovieID=2059 AND ShowDate='2018-07-14' AND TheaterID=053

WHERE Clause

The WHERE clause is used to filter the data records on the basis of a specified condition or a set of conditions in the search queries. When the WHERE condition is used in a query, the data records are verified based on the specified conditions, and the commands are executed only on the records that satisfy the conditions.

For example, the syntax for using the WHERE condition in a SELECT statement is:

SELECT column_name(s)
	FROM base_table_name
	WHERE condition
	

The WHERE condition can be used with the UPDATE and DELETE statements as well.

Operators Supported by the WHERE Clause

You can use the following operators in the WHERE conditions in ZCQL queries:

Example:

To view a list of the movies that are screened on days other than July 13, 2018 from the Movies table, you can specify the date condition in the query using the WHERE clause in the following way:

SELECT MovieName, ShowDate FROM Movies WHERE ShowDate= '2018-07-14'

This will generate the following output:

Multiple WHERE Conditions

You can specify a maximum of five WHERE conditions in a single query. When multiple WHERE conditions are specified, you can use either an AND or an OR operator to link the conditions together. The functionalities of the AND and OR operators are:

• AND: Produces only the data records that satisfy both of the conditions that are associated with the AND operator.
• OR: Produces the data records that satisfy either of the conditions that are associated with the OR operator.

For example, the syntax for using multiple WHERE conditions in a SELECT statement is:

SELECT column_name(s)
FROM base_table_name
WHERE condition_1 AND|OR condition_2.. AND|OR condition_5

Example:

To view a list of the show dates and show times from the Movies table for either 'The First Purge' or 'Skyscraper' movies that are screened on July 14, execute the following query:

SELECT MovieName, ShowDate, ShowTime FROM Movies WHERE MovieName='The First Purge' OR MovieName='Skyscraper' AND ShowDate='2018-07-14'

There is only record that matches the above conditions. The query will therefore generate the following result:

LIKE Statement

The LIKE condition enables you to indicate records which contain a specific criteria and select all records that match that condition. For example, you can indicate a specific alphabet that the records you require to be fetched must begin with, or a particular value that they must contain.

For example, the syntax of the LIKE condition in an UPDATE statement is as follows:

UPDATE base_table_name 
	SET column_name(s)=value(s)
	WHERE condition 
	LIKE *value*

The '*' can be used either before or after, or both before and after, the value that you specify. The '*' is essentially a placeholder that indicates that any value can be replaced in its stead.

For example, to indicate that all records in a table that begin with the alphabet 'A' must be fetched, you can enter the LIKE value as 'A*'. This specifies that after the letter A, any values can follow.

Example:

To update the records where the movie names begin with the letter 'S' in the Movies table, execute the following query:

UPDATE Movies SET ShowDate='2018-07-17' WHERE MovieName like 'S*'

This will update the following record and set the value:

BETWEEN Statement

The BETWEEN condition enables you to filter records by indicating the starting and ending values in a particular column containing numerical values. For example, you can select all the records where the percentage values are between 70 and 80.

For example, the syntax of a BETWEEN condition in a DELETE statement is as follows:

DELETE FROM base_table_name
	WHERE condition
	BETWEEN Value1 AND Value2

Example:

To delete the records where the value of the MovieID is between 2056 and 2059 in the Movies table, execute the following query:

DELETE FROM Movies WHERE MovieID BETWEEN 2056 AND 2059

The following records from the table will be deleted:

Column to Column Comparison

You can compare two columns of the same table or in different tables by referring to the column names with their respective table names in the LHS and RHS of the comparison. The result of the comparison will return the rows that contain the same value for column1 and column2 in the table.

This comparison support in WHERE clause is applicable to the SELECT, INSERT, UPDATE and DELETE queries.

You can compare any two columns that are of the following data types : BOOLEAN,DOUBLE,DATE,DATETIME,ENCRYPTED,VARCHAR,TEXT,INT and BIGINT.

Select

The general syntax for column comparison within the same table is as follows:

SELECT * FROM tablename WHERE columnname1 = tablename.columnname2

Example

SELECT MovieName FROM Movies WHERE Actor = Movies.Producer

Update

UPDATE Movies SET self_produced = "Yes" WHERE Actor = Movies.Producer

Delete

DELETE FROM Movies WHERE Actor = Movies.Producer

Comparison of Columns in Different Tables

You can also compare columns of two different tables as shown in the syntax below :

SELECT tablename1.columnname1, tablename2.columnname1 FROM tablename2 LEFT JOIN tablename1 ON tablename2.columnname1 = tablename1.columnname2 WHERE tablename1.columnname1 = tablename2.columnname2

Example

SELECT Movies.name, Theatres.city from Theatres left join Movies on Theatres.ROWID = Movies.theatreID where Movies.distributionCity = theatre.city

Similarly, you can compare columns belonging to two different tables while executing the INSERT, UPDATE and DELETE queries.

JOIN Clause

The JOIN clause is used to combine rows from two or more tables in a SELECT query, based on a related column between them. The rows from all the tables are generated together with the common column merged.

Before we learn more about the JOIN clause, let's understand a few key terms related to joins:

1. Primary Key: A primary key is a unique identifier of a record in a table. A table can have one column as its primary key column, and the value for each record in this column must be unique, is mandatory, and cannot be duplicated.
2. Foreign Key: A foreign key column in one table uniquely identifies a row in another table or the same table. The foreign key of the second table refers to the primary key of the first table.
3. Parent Table: In the JOIN clause, the parent table is the base table that the join is performed based on.
4. Join Table: The join table is the secondary table or the child table which is being merged with the parent table in the JOIN clause.

In our example, the MovieID column is the primary key of the Movies table as it holds the unique identification number of a movie.

Example Database:

Let's create a table named 'Theaters' in the ticket booking application which specifies the locations of all the theaters that are registered with the application.

Sample records from the Theaters table are given below:

The TheaterID column is the primary key of the Theaters table as it holds the unique identification numbers of the theaters. The TheaterID column in the Movies table is the foreign key to the TheaterID column in the Theaters table.

If you don't specify the table name with the column name in a query, then by default it is considered to be a column in the base table..

Types of Joins

There are two types of Joins that can be performed in ZCQL. They are:

1. INNER JOIN
   The INNER JOIN returns the records that have matching values in both the parent table and the join table. When you use an inner join on two tables, only the records whose values match for the specified columns in both the tables are produced as the output.
   The syntax for using an INNER JOIN is:
    

   SELECT column_name(s)
   			FROM parent_table_name
   			INNER JOIN join_table_name
   			ON parent_table_name.column_name = join_table_name.column_name

   
   Example:
   To view a list of the movie names, show dates, and show times from the Movies table along with the theater names from the Theaters table, execute the following query:
    

   SELECT Movies.MovieName, Theatres.TheaterName, Movies.ShowDate, Movies.ShowTime, FROM Movies INNER JOIN Theatres ON Movies.TheaterID = Theatres.TheaterID 

   
   This will generate the following output:

   MovieName	TheaterName	ShowDate	ShowTime
   Ant-Man and the Wasp	The Express Cinemas	2018-07-13	13:30:00
   Hotel Transylvania 3: Summer Vacation	Cosmos Theater	2018-07-13	14:20:00
   The First Purge	Cosmos Theater	2018-07-14	17:00:00
   The First Purge	FunTime Cinemas	2018-07-14	21:30:00

2. LEFT JOIN
   The LEFT JOIN returns all the records from the parent table and the matched records from the join table. When you use a left join on two tables, all records from the parent table are returned, even if they do not have matches from the join table. The result is displayed as 'NULL' for records that do not have a match in the join table.
   The syntax for using a LEFT JOIN is:
    

   SELECT column_name(s)
   			FROM parent_table_name
   			LEFT JOIN join_table_name 
   			ON parent_table_name.column_name = join_table_name.column_name

   
   Example:
   To view a list of theaters and their locations from the Theaters table along with the movies that are being screened in the theaters from the Movies table, execute the following query:
    

   SELECT Theaters.TheaterName, Theaters.Location, Movies.MovieName
   			FROM Theaters
   			LEFT JOIN Movies 
   			ON Theaters.TheaterID = Movies.TheaterID

   
   This will generate the following output:

   TheaterName	Location	MovieName
   The Express Cinemas	New York City	The First Purge
   ANC Cinemas	Rochester	NULL
   Cosmos Theater	Albany	Hotel Transylvania 3: Summer Vacation
   FunTime Cinemas	Buffalo	Skyscraper

   
   Since there are no matching records for ANC Cinemas in the Movies table, the result is displayed as 'NULL'.

Multiple Joins

You can combine a maximum of four joins in a single ZCQL query. This will display the results from four different tables, where one is linked to another using an INNER JOIN or a LEFT JOIN.

Example Database:

Let's create a table called 'Pricing' in the ticket booking application which displays the ticket price in dollars of a single ticket for a particular movie and theater. This table references TheaterID from the Theaters table and MovieID from the Movies table as the foreign keys.

Sample records from the Pricing table are given below:

You can execute a query to display the TheaterName from the Theaters table, MovieName, ShowDate, and ShowTime from the Movies table, and Price from the Pricing table, by executing the following query:

SELECT Theaters.TheaterName,Movies.MovieName, Movies.ShowDate, Movies.ShowTime, Pricing.Price
FROM Pricing
LEFT JOIN Movies 
ON Theaters.TheaterID = Movies.TheaterID
INNER JOIN Theaters
ON Movies.MovieID = Pricing.MovieID 

This will generate the following output:

GROUP BY Statement

The GROUP BY statement is used to group the records fetched in a search query results by one or more columns. GROUP BY enables identical data to be grouped together and displayed sequentially. When there are duplicate values in a column, the GROUP BY statement displays the duplicates together. The records are then ordered based on other columns.

The GROUP BY statement is associated with the SELECT statement and is often used with ZCQL functions. It is used towards the end of the query and should therefore satisfy the JOIN statements and follow the WHERE conditions.

The basic syntax for using a GROUP BY statement along with the SELECT statement is as follows:

SELECT column_name(s)
	FROM parent_table_name
	GROUP BY column_name(s)

Example:

To view a list of theaters and their locations from the Theaters table, grouped by the location of the theaters, execute the following query:

SELECT TheaterName, Location 
	FROM Theaters
	GROUP BY Location

This will generate the following output:

ORDER BY Statement

The ORDER BY statement is used to sort the records fetched in a search query results in an ascending or a descending order, based on one or more columns. When the ORDER BY statement is used to sort textual data, it sorts the records in the alphabetical order.

Similar to the GROUP BY statement, the ORDER BY statement is associated with the SELECT statement and is often used after the GROUP BY statement. It is used towards the end of the query, after the JOIN clause statements or the WHERE conditions, if present, but before the LIMIT clause. The ORDER BY statement should then satisfy the JOIN statements and follow the WHERE conditions.

If there are duplicate values in a column, the ORDER BY statement will display the duplicates together. The records are then ordered based on other columns.

The basic syntax for using a GROUP BY statement with the SELECT statement is as follows:

SELECT column_name(s)
	FROM parent_table_name
	ORDER BY column_name(s) [ASC | DESC] 

Example:

To view a list of the ticket prices from the Pricing table in descending order for each movie from the Movies table execute the following query:

SELECT Movies.MovieName, Pricing.Price
	FROM Movies
	INNER JOIN Pricing
	ON Movies.MovieID = Pricing.MovieID
	ORDER BY Pricing.Price DESC

This will generate the following output:

LIMIT Clause

The LIMIT clause limits the number of data records that are displayed in the result set of a search query. It contains the following properties:

1. OFFSET: OFFSET defines the starting index of the result, i.e., the first record's position that will be displayed in the result.
2. VALUE: VALUE defines the number of records to be retrieved from the starting index, i.e., the OFFSET.

The LIMIT clause identifies the OFFSET index and then displays the number of records as specified in the VALUE from the OFFSET index. You can also just specify the value without specifying the offset, when the starting index is the first record.

The LIMIT clause is only used when a specific number of records is to be displayed. It is used towards the end of the query after all the other clauses and statements.

The basic syntax for using the LIMIT clause with a SELECT statement is:

SELECT column_name(s)
	FROM parent_table_name
	LIMIT OFFSET, VALUE

Example:

To view a list of the movies from the Movies table consisting of up to three records from the second starting index execute the following query:

SELECT *
	FROM Movies
	LIMIT 1,3

It will generate the following output:

ZCQL Functions

ZCQL supports some mathematical and analytical built-in functions that help you perform quick arithmetic operations on the data and manipulate the result set of a search query to meet your requirements. These functions are used in the SELECT statement for the columns that are generated in the output.

1. MIN()
   The MIN() function returns the minimum value of the selected column. You can use it to return the smallest value of a column that contains numerical data or the record that comes first in the alphabetical order.
   The basic syntax for using a MIN() function along with the SELECT statement is:
    

   SELECT MIN(column_name)
   			FROM base_table_name

   
   Example:
   To view the least expensive movie from the Pricing table, execute the following query:
    

   SELECT MIN(Price)
   			FROM Pricing

   
   This will generate the following output:

   MIN(Price)

   7.44

2. MAX()
   The MAX() function returns the maximum value of the selected column. You can use it to return the largest value of a column that contains numerical data or the record that comes last in the alphabetical order.
   The basic syntax for using a MAX() function with the SELECT statement is:
    

   SELECT MAX(column_name)
   			FROM base_table_name

   
   Example:
   To view the most expensive movie from the Pricing table, execute the following query:
    

   SELECT MAX(Price)
   			FROM Pricing

   
   This will generate the following output:

   MAX(Price)

   11.50

3. COUNT()
   The COUNT() function returns the value for the number of rows in the records that are returned for a particular column in the output. The COUNT function is also used with the SELECT statement.
   The basic syntax for using a COUNT() function with the SELECT statement is:
    

   SELECT COUNT(column_name)
   			FROM base_table_name

   
   Example:
   To view the number of theaters in New York City and Albany, from the Theaters table, execute the following query:
    

   SELECT COUNT(TheaterName)
   			FROM Theaters
   			WHERE Location='New York City' OR Location='Albany'

   
   This will generate the following output:

   COUNT(TheaterName)

   2

4. SUM()
   The SUM() function calculates and returns the total sum of a numeric column's records. This can only be used for a column that contains numerical data. The SUM() function can also be used with the SELECT statement for a singular column.
   The basic syntax for using a SUM() function with the SELECT statement is:
    

   SELECT SUM(column_name)
   			FROM base_table_name

   
   Example:
   To view the sum total of the ticket charges for 'The First Purge' and 'Hotel Transylvania 3: Summer Vacation' from the Pricing table, execute the following query:
    

   SELECT SUM(Price)
   			FROM Pricing
   			WHERE MovieID='2056' OR MovieID='2058'

   
   This will generate the following output:

   SUM(Price)

   20.7

5. AVG()
   The AVG() function calculates and returns the average value for a numeric column's records. This can only be used for a column that contains numerical data. The AVG() function is used along with the SELECT statement for a particular column alone.
   The basic syntax for using a AVG() function with the SELECT statement is:
    

   SELECT AVG(column_name)
   			FROM base_table_name
   			

   
   Example:
   To view the average ticket price for the movies in the Pricing table, execute the following query:
    

   SELECT AVG(Price) As AverageTicketPrice
   			FROM Pricing

   
   This will generate the following output:

   AVG(Price)

   9.19

6. DISTINCT
   The DISTINCT statement returns only the distinct record values for a column. If there are duplicate values in a column, this statement will not return the duplicates. The DISTINCT statement is used along with the SELECT statement in a query.
   The basic syntax for using a SELECT DISTINCT statement is:
    

   SELECT DISTINCT column_name(s)
   			FROM base_table_name

   
   Example:
   To view the distinct theaters the movies are being screened at, execute the following query:
    

   SELECT DISTINCT Theaters.TheaterName
   			FROM Movies
   			INNER JOIN Theaters
   			ON Movies.TheaterID=Theaters.TheaterID
   			

   
   This will generate the following output:

   TheaterName

   The Express Cinemas

   Cosmos Theater

   FunTime Cinemas

Testing ZCQL Queries

As discussed in the introduction, you can test ZCQL queries from the console before implementing it in your application's code. This enables you to test and verify the execution of queries before you begin building with it.

If your project's Data Store is populated, you can perform operations on it from the ZCQL console to retrieve, insert, update, or delete data from the Data Store's tables. You can view the output of the query executions on the same page. The ZCQL console also includes an autocomplete feature that helps you query in commands easily.

To execute a ZCQL query in the Catalyst console:

1. Navigate to ZCQL Console under Develop.
2. Type the query in the ZCQL query window.
   
   You can press the Control + Space keys to use the autocomplete feature. A dropdown will display the names of the tables in your project that you can select from.
   
   You can also view the names of the columns in a table after you enter the table name that you can select from. The dropdown will also list ZCQL commands for easy use.
   
   Note: You need not use the end-of-statement delimiter ';' at the end of a ZCQL query, while executing it in the ZCQL console.
3. Click Execute after you enter the query..

The output of your query's execution will be displayed below the query window.