Assignment no.
Name WNiranjan Shantinath Digraje
Roll No : C_26
Assignment : Create and manage NoSQL Databases with Cassandra
INSERT ROWS:
INSERT INTO EMP TABLE (EMP ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (1, AMIT DESAI', 'KOLHAPUR', 'PUNE, 2.5);
INSERT INTO EMP TABLE (EMP ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (2, 'RAJ RANE', 'KOLHAPUR', 'MUMBAI', 2);
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE)
) VALUES (3, 'RUTURAJ PATIL', 'PUNE', 'KOLHAPUR', 1);
INSERT INTO EMP TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (4, ‘KARAN MANE', 'KOLHAPUR', 'SANGLI’, 1.2);
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (5, 'RAM KALE', 'KOLHAPUR', 'SANGLI', 2.1);
UPDATE ROWS:
UPDATE EMP_TABLE SET CITY='SANGLI', DESTINATION='SATARA',
EXPERIENCE=3 WHERE EMP_ID=5;
UPDATE EMP_TABLE SET CITY='SANGLI’ WHERE EMP_ID=2;
UPDATE ROWS WITH UPSERT:
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (1, 'AMIT DESAI', 'VITA', 'SANGLI', 2);
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE)
VALUES (2, 'RAJ RANE', 'SANGLI', 'PUNE', 4);
RETRIEVE DATA FROM TABLE:
SELECT EMP_ID, NAME, CITY FROM EMP_TABLE;
SELECT * FROM EMP_TABLE WHERE EMP_ID = 5;
ALTER TABLE ADD COLUMNS ((EMAIL SET<TEXT>, EXPERTISE LIST<TEXT>,
PREV_JOBS MAP<TEXT, INT>):
ALTER TABLE EMP_TABLE ADD email set<text>;
ALTER TABLE EMP_TABLE ADD expertise list<text>;
ALTER TABLE EMP_TABLE ADD prev_jobs map<text, int>;
INSERT NEW ROWS
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE,
email, expertise, prev_jobs) VALUES (6, 'SURAJ PATIL', 'MUMBAI', 'DELHI', 4.5,
{'suraj@example.com'}, ['Engineering', 'Management'], {'Software Engineer': 3,
'Project Manager': 2});
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE,
email, expertise, prev_jobs) VALUES (7, 'AJIT PATIL', 'KADEGAON', 'SANGLI', 4,
{'ajit@example.com'}, ['Engineering', 'Programmer'], {'Software Developer': 1, 'Project
Manager': 3});
INSERT INTO EMP_TABLE (EMP_ID, NAME, CITY, DESTINATION, EXPERIENCE,
email, expertise, prev_jobs) VALUES (8, 'RAJ KAPOOR', 'NASHIK', 'MUMBAI', 5,
{'raj@example.com'}, ['Development'], {'Developer': 5});
DELETE ROWS AND VALUES:
Delete an Entire Row
DELETE FROM EMP_TABLE WHERE EMP_ID = 5;
Delete Specific Columns
DELETE email, expertise FROM EMP_TABLE WHERE EMP_ID = 5;
create table product(
... id uuid,
... name text,
... price float,
... quan int,
... primary key(id));
CREATE TABLE PRODUCT (ID UUID, NAME TEXT, PRICE FLOAT, QUAN INT,
PRIMARY KEY(ID));
PERFORM FOLLOWING OPERATIONS ON CREATED TABLE:
INSERT ROWS:
INSERT INTO PRODUCT (ID, NAME, PRICE, QUAN) VALUES(UUID(), 'SIMARAN
PATIL', 20000, 4);
INSERT INTO PRODUCT (ID, NAME, PRICE, QUAN) VALUES(UUID(), 'OM
RAJMANE', 500.14, 5);
INSERT INTO PRODUCT (ID, NAME, PRICE, QUAN) VALUES(UUID(), 'MITU
VARDHAN', 14.44, 2);
INSERT INTO PRODUCT (ID, NAME, PRICE, QUAN) VALUES(UUID(), 'MANISHA
WAGH', 60.30, 3);
ALTER TABLE PRODUCT ADD (INV_DATE TIMESTAMP, AVAILABLE
BOOLEAN);
ALTER TABLE PRODUCT ADD INV_DATE timestamp;
ALTER TABLE PRODUCT ADD AVAILABLE boolean;
INSERT NEW ROWS:
INSERT INTO PRODUCT(ID, NAME, PRICE, QUAN, AVAILABLE, INV_DATE)
VALUES(UUID(), 'PRATIKSHA KADAM', 690, 4,true, toTimestamp(now()));
INSERT INTO PRODUCT(ID, NAME, PRICE, QUAN, AVAILABLE, INV_DATE)
VALUES(UUID(), 'BABU PATIL', 14.99, 4,false, toTimestamp(now()));
INSERT INTO PRODUCT(ID, NAME, PRICE, QUAN, AVAILABLE, INV_DATE)
VALUES(UUID(), 'OM RAHANE', 14.99, 7,true, toTimestamp(now()));
Problem Statement 2:
CREATE KEYSPACE : USER_PROFILE
use user_profile;
CREATE TABLE USERS (USER_ID UUID PRIMARY KEY, FIRST_NAME
TEXT, LAST_NAME TEXT, EMAIL TEXT, AGE INT, CREATED_AT
TIMESTAMP); INSERT A FEW RECORDS INTO THE USERS TABLE:
INSERT INTO USERS(USER_ID, FIRST_NAME, LAST_NAME, EMAIL, AGE,
CREATED_AT) VALUES(UUID(), 'MARUTI', 'MANE', 'maruti@gamil.com', 50,
toTimestamp(now()));
INSERT INTO USERS(USER_ID, FIRST_NAME, LAST_NAME, EMAIL, AGE,
CREATED_AT) VALUES(UUID(), 'KALPANA', 'RAUT', 'kalpana@gamil.com', 35,
toTimestamp(now()));
INSERT INTO USERS(USER_ID, FIRST_NAME, LAST_NAME, EMAIL, AGE,
CREATED_AT) VALUES(UUID(), 'RAJESH', 'VARDHAN', rajesh@gamil.com', 28,
toTimestamp(now()));
WRITE A CQL QUERY TO RETRIEVE ALL USERS FROM THE USERS TABLE.
SELECT * FROM USERS;
WRITE A QUERY TO FETCH A USER BY EMAIL.
SELECT USER_ID, FIRST_NAME, LAST_NAME FROM USERS WHERE EMAIL =
'maruti@gamil.com' ALLOW FILTERING;
UPDATE THE AGE OF A USER WITH A SPECIFIC EMAIL.
SELECT user_id FROM users WHERE email = 'maruti@gamil.com';
UPDATE USERS SET AGE = 25 WHERE USER_ID = ecbacf34-5d25-4b25-
8cf95b6c2bf420fb;
DELETE A USER FROM THE TABLE USING THE USER_ID.
DELETE FROM USERS WHERE USER_ID = ecbacf34-5d25-4b25-8cf9-
5b6c2bf420fb;
SELECT * FROM users;
