Day 4: Cassandra Lab Practice – Insert & Select Queries

✅ Day 4: Insert and Select Queries in Cassandra

🎯 Objective:

Learn how to insert and retrieve data using CQL’s INSERT and SELECT statements, and understand the rules of querying in Cassandra.

---

🧩 1. INSERT Data into Tables

Syntax:

INSERT INTO keyspace.table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

✅ Example – employee table:

INSERT INTO test_lab.employee (emp_id, name, department, joined_on)
VALUES (uuid(), 'John Doe', 'HR', toTimestamp(now()));

📝 Notes:

• Use uuid() to auto-generate a unique ID
• Use toTimestamp(now()) for the current timestamp

---

🔍 2. SELECT Data from Tables

➤ Select all rows:

SELECT * FROM test_lab.employee;

➤ Select specific columns:

SELECT name, department FROM test_lab.employee;

---

⚠️ 3. Query Rules in Cassandra

• WHERE clause must use partition key
• You can filter clustering columns after partition key
• If filtering non-key columns, use ALLOW FILTERING (use cautiously)

---

📘 4. Practice: Query with WHERE

➤ Valid Query (on partition key):

SELECT * FROM test_lab.attendance WHERE class_id = '10A';

➤ Valid Query (with clustering column):

SELECT * FROM test_lab.attendance 
WHERE class_id = '10A' AND date = '2024-10-01';

➤ Invalid Without Partition Key:

-- This will throw an error
SELECT * FROM test_lab.attendance WHERE date = '2024-10-01';

➤ Use ALLOW FILTERING:

SELECT * FROM test_lab.attendance 
WHERE status = 'Present' ALLOW FILTERING;

⚠️ Use ALLOW FILTERING only for small datasets or for testing.

---

🔢 5. LIMIT and ORDER BY

➤ Limit results:

SELECT * FROM test_lab.employee LIMIT 3;

➤ Order by clustering key (in reverse):

SELECT * FROM test_lab.attendance 
WHERE class_id = '10A' 
ORDER BY date DESC;

---

🧪 Day 4 Lab Tasks

1. Insert 3–5 rows into each table:

   • employee, attendance, exam_scores

2. Run different SELECT queries:

   • Fetch by partition key
   • Use LIMIT and ORDER BY
   • Try ALLOW FILTERING carefully
3. Observe errors when querying without partition key

---

✅ Checklist

Task	Done
Inserted multiple rows into tables	✅
Queried with valid WHERE conditions	✅
Practiced LIMIT, ORDER BY clauses	✅
Observed effect of ALLOW FILTERING	✅