Adding Filtering on Select

In this lesson, I will show you the use of primary key and partitions keys while selecting data from the Cassandra database. We have created some tables 'employee_by_car_make', 'employee_by_car_make_sorted' ,  and 'employee_by_car_model' on Create Cassandra Table lesson. In this lesson, I will insert some data into this table and perform queries. 

Show table details:

DESCRIBE TABLE employee_by_car_make;
DESCRIBE TABLE employee_by_car_make_sorted;
DESCRIBE TABLE employee_by_car_model;

 

Adding WHERE and ORDER BY:

We only can use PARTITION KEY for making WHERE condition and CLUSTERING KEY for ORDER BY.

Insert some data to employee_by_car_make (car_make is partition key and id is CLUSTERING KEY):

INSERT INTO employee_by_car_make (car_make, id, car_model) 
VALUES ( 'BMW', 1, 'Sports Car');

INSERT INTO employee_by_car_make (car_make, id, car_model) 
VALUES ( 'BMW', 2, 'Sports Car');

INSERT INTO employee_by_car_make (car_make, id, car_model) 
VALUES ( 'AUDI', 3, 'Truck');

INSERT INTO employee_by_car_make (car_make, id, car_model) 
VALUES ( 'AUDI', 5, 'Hatchback');

Select All Data:

SELECT * FROM employee_by_car_make

Add WHERE CLAUSE:

SELECT * FROM employee_by_car_make
WHERE car_make = 'BMW';

Adding Order By:

SELECT * FROM employee_by_car_make
WHERE car_make = 'BMW'
ORDER BY id;

 

Working with multiple PRIMARY KEY:

Insert some data into employee_by_car_model ( car_make, model is PARTITION KEY and id is CLUSTERING KEY)

INSERT INTO employee_by_car_model (car_make, model, id, age, car_model) 
VALUES ( 'BMW', 'HATCHBACK', 1, 20, 'Sports Car');

INSERT INTO employee_by_car_model (car_make, model, id) 
VALUES ( 'BMW', 'HATCHBACK', 2);

INSERT INTO employee_by_car_model (car_make, model, id, age) 
VALUES ( 'AUDI', 'HATCHBACK', 2, 30);

INSERT INTO employee_by_car_model (car_make, model, id, age, car_model) 
VALUES ( 'AUDI', 'TRUCK', 3, 80, 'Truck');

INSERT INTO employee_by_car_model (car_make, model, id, age, car_model) 
VALUES ( 'BMW', 'TRUCK', 3, 80, 'Truck');

INSERT INTO employee_by_car_model (car_make, model, id, age, car_model) 
VALUES ( 'AUDI', 'TIM', 5, 35, 'Hatchback');

 

Select all data from employee_by_car_model:

SELECT * FROM employee_by_car_model;

Adding WHERE clause:

SELECT * FROM employee_by_car_model 
WHERE car_make='BMW' AND model = 'HATCHBACK';

 Here important think is that we must have to add car_make and model on WHERE CLAUSE. If we use single one it will give errors because we used a combined partition key using car_make and model.

Adding ORDER BY:

SELECT * FROM employee_by_car_model 
WHERE car_make='BMW' AND model = 'HATCHBACK'
ORDER BY id;