Q) Buyer Event Analytics
CREATE KEYSPACE BuyerAnalytics
WITH replication = {‘class’: ‘SimpleStrategy’, ‘replication_factor’: 1};
USE BuyerAnalytics;
CREATE TABLE BuyerEvents (
event_id UUID PRIMARY KEY,
user_id UUID,
roduct_id UUID,
event_type TEXT,
event_timestamp TIMESTAMP,
price DECIMAL
);
INSERT INTO BuyerEvents (event_id, user_id, product_id, event_type, event_timestamp, price)
VALUES (uuid(), uuid(), uuid(), ‘purchase’, toTimestamp(now()), 99.99); // 49.99
1. Retrieve All Buyer Events
SELECT * FROM BuyerEvents;
2. Retrieve All Purchase Events
SELECT * FROM BuyerEvents WHERE event_type = ‘purchase’ ALLOW FILTERING;
3. Get Total Sales for a Product
SELECT SUM(price) FROM BuyerEvents WHERE product_id = uuid() ALLOW FILTERING;
4. Get Buyer Purchase History
SELECT * FROM BuyerEvents WHERE user_id = uuid() ALLOW FILTERING;
Q) Social Media Analysis
CREATE KEYSPACE SocialMedia
WITH replication = {‘class’: ‘SimpleStrategy’, ‘replication_factor’: 1};
1. Table for User Posts
USE SocialMedia;
CREATE TABLE Posts (
post_id UUID PRIMARY KEY,
user_id UUID,
content TEXT,
post_time TIMESTAMP,
likes INT,
shares INT
);
CREATE TABLE Comments (
comment_id UUID PRIMARY KEY,
post_id UUID,
user_id UUID,
comment_text TEXT,
comment_time TIMESTAMP
);
CREATE TABLE PostInteractions (
interaction_id UUID PRIMARY KEY,
post_id UUID,
user_id UUID,
interaction_type TEXT, — ‘like’ or ‘share’
interaction_time TIMESTAMP
);
Insert Sample Posts INSERT INTO Posts (post_id, user_id, content, post_time, likes, shares)
VALUES (uuid(), uuid(), ‘Hello, this is my first post!’, toTimestamp(now()), 10, 2);
Insert Sample Comments
INSERT INTO Comments (comment_id, post_id, user_id, comment_text, comment_time)
VALUES (uuid(), uuid(), uuid(), ‘Great post!’, toTimestamp(now()));
Insert Sample Interactions (Likes and Shares)
INSERT INTO PostInteractions (interaction_id, post_id, user_id, interaction_type, interaction_time)
VALUES (uuid(), uuid(), uuid(), ‘like’, toTimestamp(now()));
INSERT INTO PostInteractions (interaction_id, post_id, user_id, interaction_type, interaction_time)
VALUES (uuid(), uuid(), uuid(), ‘share’, toTimestamp(now()));
1. Retrieve All Posts
SELECT * FROM Posts;
2. Retrieve All Comments for a Specific Post
SELECT * FROM Comments WHERE post_id = uuid() ALLOW FILTERING;
3. Get the Total Number of Likes for a Specific Post
SELECT likes FROM Posts WHERE post_id = uuid();
4. Get All User Interactions on a Post
SELECT * FROM PostInteractions WHERE post_id = uuid() ALLOW FILTERING;
Q) Arrays
Insert at least three documents
use ArrayFunctions;
db.Arrays.insertMany([
{
name: “Alice”,
hobbies: [“reading”, “cycling”, “hiking”],
scores: [85, 92, 88],
bio: “Alice loves reading books, hiking trails, and cycling in the countryside.”
},
{
name: “Bob”,
hobbies: [“painting”, “cycling”, “chess”],
scores: [72, 81, 95],
bio: “Bob is passionate about art and strategy games.”
},
{
name: “Charlie”,
hobbies: [“gaming”, “reading”, “swimming”],
scores: [89, 90, 77],
bio: “Charlie enjoys video games, especially strategy and RPGs.”
}
]);
b) Retrieve all documents
db.Arrays.find().pretty();
c) Find documents where hobbies include “cycling”
db.Arrays.find({ hobbies: “cycling” });
d) Find documents where scores include any value greater than 90
db.Arrays.find({ scores: { $elemMatch: { $gt: 90 } } });
e) Add a new hobby “jogging” to Bob
db.Arrays.updateOne(
{ name: “Bob” },
{ $push: { hobbies: “jogging” } }
);
f) Add the score 93 to Alice only if it doesn’t already exist
db.Arrays.updateOne(
{ name: “Alice” },
{ $addToSet: { scores: 93 } }
);
g) Remove the last hobby from Charlie
db.Arrays.updateOne(
{ name: “Charlie” },
{ $pop: { hobbies: 1 } } // use -1 to remove first
);
h) Update the first score of Alice to 95
db.Arrays.updateOne(
{ name: “Alice” },
{ $set: { “scores.0”: 95 } }
);
i) Count the number of documents in the collection
db.Arrays.countDocuments({});
j) Use $split to convert bio into array of words
db.Arrays.aggregate([
{
$project: {
name: 1,
bioWords: { $split: [“$bio”, ” “] }
}
}
]);
k) Sort by average score (descending)
db.Arrays.aggregate([
{
$project: {
name: 1,
avgScore: { $avg: “$scores” }
}
},
{
$sort: { avgScore: -1 }
}
]);
l) Limit to top 2 users based on number of hobbies
db.Arrays.aggregate([
{
$project: {
name: 1,
hobbyCount: { $size: “$hobbies” }
}
},
{ $sort: { hobbyCount: -1 } },
{ $limit: 2 }
]);
Q) Movie Maker
use MovieMaker
db.Movies.insertMany ([
{ title: “Pulp Fiction”, director: “Quentin Tarantino”, year: 1994, actors: [“John Travolta”, “Samuel L. Jackson”, “Uma Thurman”] },
{ title: “Inglourious Basterds”, director: “Quentin Tarantino”, year: 2009, actors: [“Brad Pitt”, “Christoph Waltz”, “Diane Kruger”] },
{ title: “Fight Club”, director: “David Fincher”, year: 1999, actors: [“Brad Pitt”, “Edward Norton”, “Helena Bonham Carter”] },
{ title: “The Hobbit: An Unexpected Journey”, director: “Peter Jackson”, year: 2012, actors: [“Martin Freeman”, “Ian McKellen”, “Richard Armitage”] },
{ title: “The Hobbit: The Desolation of Smaug”, director: “Peter Jackson”, year: 2013, actors: [“Martin Freeman”, “Ian McKellen”, “Benedict Cumberbatch”] }
])
a) Retrieve all documents
db.Movies.find().pretty()
b) Retrieve all documents with Directornset to “Quentin Tarantino”
db.Movies.find({ director: “Quentin Tarantino” }).pretty()
c) Retrieve all documents where actors include “Brad Pitt”
db.Movies.find({ actors: “Brad Pitt” }).pretty()
d) Retrieve all movies released before the year 2000 or after 2010
db.Movies.find({ $or: [{ year: { $lt: 2000 } }, { year: { $gt: 2010 } }] }).pretty()
e) Add a synopsis to “The Hobbit: An Unexpected Journey”
db.Movies.updateOne(
{ title: “The Hobbit: An Unexpected Journey” },
{ $set: { synopsis: “A reluctant hobbit, Bilbo Baggins, sets out to the Lonely Mountain with a spirited group of dwarves to reclaim their mountain home – and the gold within it – from the dragon Smaug.” } }
)
f) Add a synopsis to “The Hobbit: The Desolation of Smaug”
db.Movies.updateOne(
{ title: “The Hobbit: The Desolation of Smaug” },
{ $set: { synopsis: “The dwarves, along with Bilbo Baggins and Gandalf the Grey, continue their quest to reclaim Erebor, their homeland, from Smaug. Bilbo Baggins is in possession of a mysterious and magical ring.” } }
)
// 1. Create the “StudentMaster” database and the “Student” collection
use StudentMaster;
// 2. Insert 10 records
db.Student.insertMany([
{ _id: 1, StudentRollNo: 101, StudentName: “Ajay Rathod”, Grade: “VI”, Hobbies: [“Cricket”, “Reading”], DOJ: ISODate(“2022-06-15”) },
{ _id: 9, StudentRollNo: 109, StudentName: “Madhav Pandey”, Grade: “VII”, Hobbies: [“Chess”], DOJ: ISODate(“2021-12-05”) },
{ _id: 10, StudentRollNo: 110, StudentName: “Pooja Rani”, Grade: “V”, Hobbies: [“Dancing”, “Reading”], DOJ: ISODate(“2022-05-12”) }
]);
// 3. Find the document where StudentName is “Ajay Rathod”
db.Student.find({ StudentName: “Ajay Rathod” });
// 4. Find all documents without the _id field
db.Student.find({}, { _id: 0 });
// 5. Retrieve only Student Name and Grade
db.Student.find({}, { StudentName: 1, Grade: 1, _id: 0 });
// 6. Retrieve Student Name and Grade of the student whose _id is 1
db.Student.find({ _id: 1 }, { StudentName: 1, Grade: 1, _id: 0 });
// 7. Add a new field “Address” in Student Collection
db.Student.updateMany({}, { $set: { Address: “Not Provided” } });
// 8. Find documents where the Grade is ‘VII’
db.Student.find({ Grade: “VII” });
// 9. Find documents where the Grade is NOT ‘VII’
db.Student.find({ Grade: { $ne: “VII” } });
// 10. Find documents where Hobbies include either ‘Chess’ or ‘Dancing’
db.Student.find({ Hobbies: { $in: [“Chess”, “Dancing”] } });
// 11. Find documents where Hobbies include neither ‘Chess’ nor ‘Dancing’
db.Student.find({ Hobbies: { $nin: [“Chess”, “Dancing”] } });
// 12. Find documents where the Student Name begins with ‘M’
db.Student.find({ StudentName: /^M/ });
