SELECT

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

         SELECT column_name(s) FROM base_table_name
    

Example:

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

    

        
copy
        

            

        
    
    

         SELECT MovieID, MovieName from Movies
    

It will generate the following output:

For columns that have been named using integers, you need to add the backtick ` punctuation to diffrencitate between column name and value.

For example, a SELECT query on a table called Numbers with a column named 01 can be written in the following manner

    

        
copy
        

            

        
    
    

         SELECT `01` FROM Numbers
    

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:

    

        
copy
        

            

        
    
    

         SELECT * FROM base_table_name
    

Example:

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

    

        
copy
        

            

        
    
    

         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.