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.

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:

    

        
copy
        

            

        
    
    

         SELECT MIN(column_name) FROM base_table_name 
    

Example:

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

    

        
copy
        

            

        
    
    

         SELECT MIN(Price) FROM Pricing 
    

This will generate the following output:

MIN(Price)
7.44

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:

    

        
copy
        

            

        
    
    

         SELECT MAX(column_name) FROM base_table_name 
    

Example:

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

    

        
copy
        

            

        
    
    

         SELECT MAX(Price) FROM Pricing 
    

This will generate the following output:

MAX(Price)
11.50

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

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

This will generate the following output:

COUNT(TheaterName)
2

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

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

This will generate the following output:

SUM(Price)
20.7

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 AVG () ZCQL function can be applied on the following data types:

• Date
• DateTime
• Boolean

The basic syntax for using a AVG() function with the SELECT statement is:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

         SELECT AVG(Price) As AverageTicketPrice FROM Pricing 
    

This will generate the following output:

AVG(Price)
9.19

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

         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

Use Multiple ZCQL Functions

You can use multiple ZCQL functions on the same column in a single ZCQL query statement.

Example Database:

The employee details of Zylker Technologies are being maintained in the Data Store in the Zylker_Employee_DB table. The table contains the following columns and rows:

ID	Name	Department	Salary
ZT-001	Amelia Burrows	Product Management	15000
ZT-2001	Bruce Wayne	Sr. Management	85000
ZT-239	Clark Kane	Media Relations	4500
ZT-4289	Michelle Mascarenhas	Finance	89000

Next, we apply multiple ZCQL functions together in the query, as shown below:

    

        
copy
        

            

        
    
    

         SELECT MIN (Salary), MAX (Salary), COUNT (Salary), SUM (Salary), AVG (Salary) FROM Zylker_Employee_DB
    

MAX(Salary)	SUM(Salary)	MIN(Salary)	AVG(Salary)	COUNT(Salary)
89000	193500	4500	48975	4