Introduction to Cassandra Database - CQL
Query for Creating a Keyspace using CQL
CREATE KEYSPACE Keyspace name WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};
Query for Viewing all KeySpaces
cqlsh:vit> describe keyspaces;
Output
cycling system_auth vit system_traces
system_schema system system_distributed
Query for Altering a Keyspace in Cassandra
cqlsh:university> alter keyspace university with replication={'class':'SimpleStrategy','replication_factor':2};
Query for Dropping a Keyspace by name vit
DROP KEYSPACE vit
Query for Creating a Table in Cassandra
cqlsh:university> create table employee(id int primary key,name text,address text,sal varint);
Query for Adding a Column to a Table by name phone of type int
cqlsh:university>alter table employee add phone int;
Query for Inserting Data into the employee Table
cqlsh:university> insert into employee (id,name,address,sal) values (1,'satish','vellore',10000);
Query for Selecting Data from a Table
cqlsh:university> select * from employee;
Output
id | address | name | sal
----+---------+--------+-------
1 | vellore | satish | 10000
2 | chennai | ram | 8999
Query for Selecting Data using Where Clause in CQL
cqlsh:university> select * from employee where id=2;
Output
id | address | name | sal
----+---------+------+------
2 | chennai | ram | 8999
Query for Select data using where clause from a column which is not a primary key
cqlsh:university> select * from employee where address='chennai' allow filtering;
Query demonstrating the use of And operator in the CQL query
cqlsh:university> select * from employee where address='chennai' and name='satish' allow filtering;
Query for updating data in the employee table using CQL
cqlsh:university> update employee set address='chennai' where id=1;
Query for Updating Multiple Columns in Single Query
cqlsh:university> update employee set address='us',name='ram' where id=1;
Query for Creating an Index on Salary Column in the employee table
cqlsh:university> create index sal_index on employee(sal);
Output
//you can query on a column defined with an index without using allow filtering
//as shown below
cqlsh:university> select * from employee where sal=10000;
id | address | name | sal
----+---------+--------+-------
1 | vellore | satish | 10000
Query for Dropping an Index using CQL
cqlsh:university> Drop index index_sal;
Query for viewing the index created on a table
cqlsh:university> SELECT index_sal FROM system.schema_columns WHERE keyspace_name='vit' AND columnfamily_name='employee';
Query for Deleting a row from the Employee table
cqlsh:university> delete from employee where id=2;
Output
cqlsh:university> select * from employee;
id | address | name | sal
----+---------+--------+-------
1 | vellore | satish | 10000
Queries for Deleting all rows from a table using CQL
cqlsh:university> truncate student;
Query for viewing all tables in CQL
cqlsh:university> describe tables;
Query for Dropping a Table
cqlsh:university> drop table employee;
Query for Performing left join between employee and department table
MariaDB [satish]> select eid,name,dname from employee left join department on dep_id=did;
Output
+-----+--------+-------+
| eid | name | dname |
+-----+--------+-------+
| 121 | Satish | SCOPE |
| 122 | Mathew | NULL |
+-----+--------+-------+
2 rows in set (0.040 sec)
Query for Performing Right Join between employee and the department table
MariaDB [satish]> select eid,name,dname from employee right join department on
-> dep_id=did;
Output
+------+--------+-------+
| eid | name | dname |
+------+--------+-------+
| 121 | Satish | SCOPE |
| NULL | NULL | SITE |
+------+--------+-------+
2 rows in set (0.001 sec)
Query for Performing Cross join between employee and the department table
MariaDB [satish]> select * from employee cross join department;;
Output
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
| eid | name | phone | salary | joindate | address | dep_id | did | dname |
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore | 424 | 424 | SCOPE |
| 122 | Mathew | 767766 | 3000 | NULL | vellore | NULL | 424 | SCOPE |
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore | 424 | 425 | SITE |
| 122 | Mathew | 767766 | 3000 | NULL | vellore | NULL | 425 | SITE |
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
4 rows in set (0.001 sec)
Query for Finding sum of salary for all employees
MariaDB [satish]> select sum(salary) from employee;
Output
+-------------+
| sum(salary) |
+-------------+
| 70000 |
+-------------+
1 row in set (0.060 sec)
Query for Finding average salary for all employees
MariaDB [satish]> select avg(salary) from employee;
Output
+-------------+
| avg(salary) |
+-------------+
| 17500.0000 |
+-------------+
1 row in set (0.000 sec)
Query for Finding the minimum salary from the employee table
MariaDB [satish]> select min(salary) from employee;
Output
+-------------+
| min(salary) |
+-------------+
| 3000 |
+-------------+
1 row in set (0.172 sec)
Query for Finding the maximum salary from the employee table
MariaDB [satish]> select max(salary) from employee
Output
+-------------+
| max(salary) |
+-------------+
| 50000 |
+-------------+
1 row in set (0.001 se)
Query for finding the count of employees in the table
MariaDB [satish]> select count(salary) from employee;
Output
+---------------+
| count(salary) |
+---------------+
| 4 |
+---------------+
1 row in set (0.001 sec)
Query for using between operator
MariaDB [satish]> select eid,name from employee where salary between 1000 and 10000;
Output
+-----+--------+
| eid | name |
+-----+--------+
| 121 | Satish |
| 122 | Mathew |
| 124 | Chris |
+-----+--------+
3 rows in set (0.060
Query for Finding the total salary for employees grouped by address
MariaDB [satish]> select address,sum(salary) from employee group by address;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
| vellore | 10000 |
+---------+-------------+
2 rows in set (0.018 sec)
Finding the sum of salary for employees grouped by address and filtering only sum of salary that is greater than 10000 rupees using the having clause
MariaDB [satish]> select address,sum(salary) from employee group by address having sum(salary)>10000;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
+---------+-------------+
1 row in set (0.100 sec)
Query for demonstrating the use of order by clause in ordering groups in the ascending order
MariaDB [satish]> select address,sum(salary) from employee group by address order by sum(salary);
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| vellore | 10000 |
| mumbai | 60000 |
+---------+-------------+
2 rows in set (0.001 sec)
Query for Ordering the groups in the descending order using order by clause
MariaDB [satish]> select address,sum(salary) from employee group by address order by sum(salary) desc;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
| vellore | 10000 |
+---------+-------------+
2 rows in set (0.001 sec)