race_sponsors

Races and race sponsors in collection types.

Race sponsors set, list, and map collection

// source '0_create_keyspace.cql'; 
// above does not work when run from main prompt, need to be explicit

CREATE KEYSPACE IF NOT EXISTS cycling WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
USE cycling;

// create a simple table of races with sponsors in a set collection 

DROP TABLE IF EXISTS cycling.race_sponsors ;

// START-racesponsorsset
CREATE TABLE cycling.race_sponsors (
   race_name text PRIMARY KEY,
   sponsorship set<text>
    );
// END-racesponsorsset

// Insert data into table and set
INSERT INTO cycling.race_sponsors (race_name, sponsorship) VALUES ('Tour de France', {'LCL', 'Carrefour', 'Skoda', 'Vittel', 'Krys'});
INSERT INTO cycling.race_sponsors (race_name, sponsorship) VALUES ('Tour de Suisse', {'BMC', 'Tag Heuer', 'Assos'});
INSERT INTO cycling.race_sponsors (race_name, sponsorship) VALUES ('Criterium du Dauphine', {'LCL', 'Skoda'});
INSERT INTO cycling.race_sponsors (race_name, sponsorship) VALUES ('Giro d''Italia', {'Castrelli',
'Tag Heuer', 'Alfa Romeo', 'Unibet', 'Pinarello', 'Selle Italia',  'Rovagnati', 'Scrigno'});

// view populated table
SELECT * FROM race_sponsors;


// Delete all elements from a set for a race, delete Giro sponsors
// START-deleteset
DELETE sponsorship FROM cycling.race_sponsors 
WHERE race_name = 'Giro d''Italia';
// END-deleteset

// view table again, Giro sponsor column is now null
SELECT * FROM race_sponsors;

// Drop table for next example
DROP TABLE IF EXISTS race_sponsors ;

// Create table with partition keys for a list collection
// START-racesponsorlisttbl
CREATE TABLE cycling.race_sponsors (
   race_year int,
   race_name text,
   sponsorship list<text>,
   PRIMARY KEY (race_year, race_name)
    );
// END-racesponsorlisttbl

// Insert data into the table and list 
// START-insertsponsorlist
INSERT INTO cycling.race_sponsors (race_year, race_name, sponsorship) VALUES (2018, 'Tour de France', ['LCL', 'Carrefour', 'Skoda', 'Vittel', 'Krys']);
// END-insertsponsorlist

// view populated table
SELECT * FROM race_sponsors;

// Delete data from index position 2, which is sponsor Skoda because 0-based numbering
// START-deletelistelement
DELETE sponsorship[2] FROM cycling.race_sponsors WHERE race_year=2018 AND race_name='Tour de France' ;
// END-deletelistelement

// // view table again, Skoda sponsor column is gone
SELECT * FROM race_sponsors;