Creating materialized view "movies_by_genre_country"
Finally, create materialized view movies_by_genre_country to be able to support the
following query:
SELECT * FROM movies_by_genre_country WHERE genre = 'Adventure' AND country = 'USA' AND year >=2010 ORDER BY year DESC;
✅ Create the materialized view:
Solution
CREATE MATERIALIZED VIEW IF NOT EXISTS
movies_by_genre_country AS
SELECT * FROM movies_by_genre
WHERE genre IS NOT NULL AND country IS NOT NULL
AND title IS NOT NULL AND year IS NOT NULL
PRIMARY KEY ((genre, country), year, title)
WITH CLUSTERING ORDER BY (year DESC, title ASC);
✅ Retrieve movies from the base table and materialized view:
Solution
SELECT * FROM movies_by_genre;
SELECT * FROM movies_by_genre_country;
✅ Delete the Alice in Wonderland movie from the base table:
Solution
DELETE FROM movies_by_genre
WHERE title = 'Alice in Wonderland' AND year = 2010
AND genre IN ('Fantasy','Adventure');
SELECT * FROM movies_by_genre;
SELECT * FROM movies_by_genre_country;