ZCQL Console

ZCQL

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:

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}
 
Note:
  • The base table is the table you execute the query on.
  • You need not use the end-of-statement delimiter (;) at the end of a ZCQL query, while executing it in the ZCQL console.

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:

MovieIDMovieNameShowDateShowTimeTheaterID
2056The First Purge2018-07-1313:00:00047
2057Ant-Man and the Wasp2018-07-1314:20:00052
2058Hotel Transylvania 3: Summer Vacation2018-07-1417:00:00052
2059Skyscraper2018-07-1421:30:00053
 

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:

MovieIDMovieName
2056The First Purge
2057Ant-Man and the Wasp
2058Hotel Transylvania 3: Summer Vacation
2059Skyscraper
 
Note: You can fetch a maximum of 20 columns and a maximum of 300 rows in one SELECT query. If you require more records to be fetched, you can use the LIMIT clause to iterate the query and specify the offset and value accordingly.
 

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
 
Note:SELECT * allows you to fetch a maximum of 300 rows in one query. If you require more records to be fetched, you can use the LIMIT clause to iterate the query and specify the offset and value accordingly.

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.

MovieIDMovieNameShowDateShowTimeTheaterID
2056The First Purge2018-07-1313:00:00047
2057Ant-Man and the Wasp2018-07-1314:20:00052
2058Hotel Transylvania 3: Summer Vacation2018-07-1417:00:00052
2059Skyscraper2018-07-1421:30:00053
 

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.

Note:
  • Ensure that you provide the right value for a column that matches the column's data type.
  • For columns where the IsUnique constraint is enabled, ensure that you don't enter values that are already present in other records. Similarly, for the columns where the IsMandatory validator is enabled, ensure that you do not leave the data value blank.
  • For a column where the data type is a Foreign Key, you must provide the ROWID of the parent table's record which it refers, as its value.

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)
 

 

Note: If the values to be inserted are String values, you must pass the values enclosed in single quotations as shown above.

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}]
 
Note:
  • Ensure that you provide the right value for a column that matches the column's data type.
  • For columns where the IsUnique constraint is enabled, ensure that you don't enter values that are already present in other records. Similarly, for the columns where the IsMandatory validator is enabled, ensure that you do not leave the data value blank.
  • For a column where the data type is a Foreign Key, you must provide the ROWID of the parent table's record which it refers, as its value.

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
 
Note: Similar to data insertion, if the values to be updated are String values, you must pass the values enclosed in single quotations.

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
 
Note: If you specify a string value in the WHERE condition, you must pass it enclosed in a single quotation.

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:

OperatorsDescription
=Equal to
ISTRUE if the operand is the same as the value
IS NULLTRUE if the operand is a null value
IS NOTTRUE if the operand is not the same as the value
IS NOT NULLTRUE if the operand is not a null value
=!Not equal to
LIKETRUE if the operand matches a pattern
NOT LIKETRUE if the operand does not match a pattern
BETWEENTRUE if the operand value is between the start and end values
INTRUE if the operand is equal to a list of expressions
NOT INTRUE if the operand is not equal to a list of expressions
>Greater than
>=Greater than or equal to
<Lesser than
<=Lesser than or equal to
 
Note: The LIKE, NOT LIKE, and BETWEEN operations are not supported for the SELECT statement currently. You can only execute them for the UPDATE and DELETE statements.

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 IS NOT'2018-07-13'
 

This will generate the following output:

MovieNameShowDate
Hotel Transylvania 3: Summer Vacation2018-07-14
Skyscraper2018-07-14
 

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:

MovieNameShowDateShowTime
Skyscraper2018-07-1421:30:00
 

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.

Note: The LIKE condition cannot be used with the SELECT statement currently. You can use it with the UPDATE and DELETE statements.

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:

MovieIDMovieNameShowDateShowTimeTheaterID
2059Skyscraper2018-07-1721:30:00053
 

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.

Note: The BETWEEN condition cannot be used with the SELECT statement currently. You can use it with the UPDATE and DELETE statements.

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
 
Note: You can use the BETWEEN statement only for selecting 'Int' or 'Double' values in the Catalyst Data Store table. You will not be able to use it for any other data types.

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:

MovieIDMovieName
2057Ant-Man and the Wasp2018-07-1314:20:00052
2058Hotel Transylvania 3: Summer Vacation2018-07-1417:00:00052
 

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.
Note: Joins between two tables are only possible if there is a relationship between them. One of the tables must refer to the other table's primary key column, so either the join table must have a foreign key column of the parent table, or the parent table must have a foreign key column of the join table.

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:

TheaterIDTheaterNameLocation
047The Express CinemasNew York City
048ANC CinemasRochester
052Cosmos TheaterAlbany
053FunTime CinemasBuffalo
 

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.

Note: When you use a JOIN clause, you must specify the column names and their table names for each column in the query as: 'table_name.column_name''
For example: 'Movies.MovieName', 'Theaters.Location'

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:
    MovieNameTheaterNameShowDateShowTime
    Ant-Man and the WaspThe Express Cinemas2018-07-1313:30:00
    Hotel Transylvania 3: Summer VacationCosmos Theater2018-07-1314:20:00
    The First PurgeCosmos Theater2018-07-1417:00:00
    The First PurgeFunTime Cinemas2018-07-1421: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:
    TheaterNameLocationMovieName
    The Express CinemasNew York CityThe First Purge
    ANC CinemasRochesterNULL
    Cosmos TheaterAlbanyHotel Transylvania 3: Summer Vacation
    FunTime CinemasBuffaloSkyscraper

    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:

TheaterIDMovieIDPrice
04720569.20
048NULLNULL
05220578.64
052205811.50
05320597.44
 

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:

TheaterNameMovieNameShowDateShowTimePrice
The Express CinemasThe First Purge2018-07-1313:00:009.20
ANC CinemasNULLNULLNULLNULL
Cosmos TheaterAnt-Man and the Wasp2018-07-1314:20:008.64
Cosmos TheaterHotel Transylvania 3: Summer Vacation2018-07-1417:00:0011.50
FunTime CinemasSkyscraper2018-07-1421:30:007.44
 

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:

TheaterNameLocation
Cosmos TheaterAlbany
FunTime CinemasBuffalo
The Express CinemasNew York City
ANC CinemasRochester
 

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.

Note: If the ORDER BY statement follows a GROUP BY statement, the ORDER BY statement takes precedence and the results are ordered first based on the ORDER BY statement, and then grouped together based on the GROUP BY statement.

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] 
 
Note: By default, the ORDER BY statement sorts the records in ascending order without using the keyword 'ASC'. To sort the records in a descending order, you will have to use the keyword '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:

MovieNamePrice
Hotel Transylvania 3: Summer Vacation11.50
The First Purge9.20
Ant-Man and the Wasp8.64
Skyscraper7.44
 

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:

MovieIDMovieNameShowDateShowTimeTheaterID
2057Ant-Man and the Wasp2018-07-1314:20:00052
2058Hotel Transylvania 3: Summer Vacation2018-07-1417:00:00052
2059Skyscraper2018-07-1421:30:00053
 

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.

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.
    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.

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

Share this post : FacebookTwitter

Still can't find what you're looking for?

Write to us: support@zohocatalyst.com