Cassandra Query Language vs Structured Query Language

Cassandra Query Language vs Structured Query Language

In order to connect with the database every database must provide the proper languages and interfaces. The languages of the Database are used for database creation and maintenance.

Various databases such as Oracle SQL, Oracle NoSQL, Hbase, MongoDB, etc are available in today's world.

The above mentioned databases have their specific language/ query language, which provide functionalities such as read, write, update, and store the data in database.

Databases are classified into two kinds according to database languages and interfaces

• NoSQL (Non-Relational Databases)
• SQL ( Relational Databases )

NoSQL:

NoSQL is a non-relational database management system that does not require a fixed schema, scales easily, and eliminates joins. NoSQL database is utilised with homogeneous data storage demands in distributed data store.

Some NoSQL databases are Cassandra, MongoDB, Hbase, Redis.

CQL:

CQL has many abbreviations such as,

• Cassandra Query Language used for Apache Cassandra.
• Contextual Query Language or common query language used for information retrieval.
• Classora Query Language, Classora is a knowledge-base.
• Chess Query Language, a query language for studying chess databases/ games which played previously used by chess engines.

SQL:

SQL is a relational database query language that helps in creating and manipulating databases. The word manipulation just mean applying operations such as insert, search, update, remove. SQL programming may be used efficiently to insert, search, update, and remove database records.

SQL is used in relational databases such as MS SQL Server, Oracle, Sybase, MySQL Database, and others.

Similarities and Differences:

	SQL	CQL
1	It mostly deals with structured data in the databases.	CQL has a clear advantage dealing with structured, semi structured and unstructured data.
2	It deals with data that is coming low velocity.	Deals with data that is coming in high velocity.
3	It can handle simple transactions and also complex transactions with the help of joins.	It only handles simple transactions in massive amount, but cannot handle complex transactions as it does not have any feature as joins.
4	It handles data in moderate volume.	Handles data in high volume.
5	Deployments are centralized.	Deployments are de-centralized.
6	Gives a great amount of read scalability.	Gives great amount of read and write scalability.
7	Transactions are written in one location which does not improve availability of the data.	Transactions is written at one location and copied in many other locations which improves availability of data.
8	It is deployed in vertical fashion.	It is deployed in horizontal fashion.

Language Differences:

Both the languages cannot be differentiated in terms of query language by each feature as it becomes complex and lengthy. One can differentiate by comparing the sub-languages categories used in database. Refer the below table for broad overview of differences between SQL and CQL.

Sub-Languages	SQL	CQL
Data Manipulation Language(DML)
Data Definition Language(DDL)
Data Retrieval Language(DRL)
Data Control Language(DCL)
Transaction Control Language(TCL)

Data Definition Language (DDL):

To define the database structure, schema and database objects, Data definition language statements are needed.

• CREATE –Used to build database objects.
• ALTER – Used to modify the database structure.
• DROP – Used in the database to remove objects.
• RENAME – Used for object renaming.
• TRUNCATE – This function is used to delete all records from a table.

SQL:

With and without the main key, you may build the table. The right half of the table will show if you add a table column.

CQL:

Without the primary key one cannot construct the table. In Cassandra, the primary key column will function as partition key which would be easy/ compound/ composite. If you create a column for a table, it will alphabetically arrange the column position.

Data Manipulation Language (DML):

For storing and modifying data, Data Manipulation Language statements are utilised.

For this sub-language SQL and CQL uses different operations.

SQL:

• INSERT- Thisfunction enters data into a table.
• UPDATE- This function changes existing data in a table.
• DELETE- This function erases records from a table.

 CQL:

• INSERT - If the row does not exist in a table, it inserts data into a table, and if there is a row, the row values are updated (works as an update).
• UPDATE – If there is a row, it updates current data in the table and inserts data in a table, if there is no row (works as an insert).
• DELETE — Deletes table records from a table. 

Data Retrieval Language (DRL):

SQL:

• In SQL complex transactions can be implemented with help of joins. You can select data for certain columns.
• With the use of WHERE, you may pick the data you want, even those columns that do not have the indexes in any of the columns in the table.
• One may use the WHERE clause to update/delete certain row values. However, for WHERE clause condition only the primary key column may be utilised.
• You may use logical operators to search for information such as NOT,AND, OR... 

CQL:

• In CQL it only supports simple transactions, as there is no specific joins feature present.
• One can select data from particular columns.
• Using the WHERE clause, you may pick the needed data from the primary key column of the table.
• If you wish to use a WHERE condition on a column that isn't a primary key, you'll require secondary indexes on that column.
• The data may only be queried using the AND operator. Logical operators such as OR and NOT  are not present. 

Data Control Language (DCL):

DCL statements are a programming language-similar syntax used to manage data access stored in a database. It is a structured query language in specific (SQL).

SQL:

• Grant - it allows users to utilise a database and objects.
• Revoke – If any user privileges on database objects have been given, the revoke command will remove them.

CQL does not support Data Control Language.

Transaction Control Language (TCL):

To handle transactions within the database, TCL commands are utilised. These are used to handle DML statements modifications. It also enables declarations in logical transactions to be grouped together.

SQL:

• COMMIT- COMMIT declaration is used to end your existing transaction and to make all transaction modifications permanent.
• SAVEPOINT- SAVEPOINT is a moment when the transaction may be reversed to a particular point without the full transaction being reversed.
• ROLLBACK-It restores the original database from the last COMMIT.

CQL does not support Transaction Control Language.

Conclusion:

CQL is truly a double-edged sword, due to the resemblance with SQL. One should take the time to comprehend the distinctions between the languages while enjoying the simple syntax of learning. Cassandra is provided much more than one requires on this tutorial. Here the theory part of Cassandra ends and the Cassandra query language is studied further.