rank_by_year_and_name

SOURCE '0_create_keyspace.cql';

// Store cycling race information by year and race name using a COMPOSITE PARTITION KEY

// START-drop_rank_by_year_and_name
DROP TABLE IF EXISTS cycling.rank_by_year_and_name;
// END-drop_rank_by_year_and_name

// START-compositepk
CREATE TABLE cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);
// END-compositepk

// START-insert_rows_into_rank_by_year_and_name
INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Adam PHELAN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Thomas LEBAS', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Daniel MARTIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Johan Esteban CHAVES', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Ilnur ZAKARIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Carlos BETANCUR', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);
// END-insert_rows_into_rank_by_year_and_name

// START-select_all_from_rank_by_year_and_name
SELECT *
FROM cycling.rank_by_year_and_name;
// END-select_all_from_rank_by_year_and_name

// START-select_from_rank_by_year_and_name_with_race_year_and_name
SELECT *
FROM cycling.rank_by_year_and_name
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu';
// END-select_from_rank_by_year_and_name_with_race_year_and_name

// START-create_index_race_year
CREATE INDEX ryear ON
cycling.rank_by_year_and_name (race_year);
// END-create_index_race_year

// START-select_from_rank_by_year_and_name_with_year
SELECT *
FROM cycling.rank_by_year_and_name
WHERE race_year = 2015;
// END-select_from_rank_by_year_and_name_with_year

// START-update_rank_by_year_and_name_simple
UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'John SMITH'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 2;
// END-update_rank_by_year_and_name_simple

// START-update_rank_by_year_and_name_in_clause
UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'Jane DOE'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank IN (2, 3, 4);
// END-update_rank_by_year_and_name_in_clause

// START-update_rank_by_year_and_name_if_clause
UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'John SMITH'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 3
IF EXISTS;
// END-update_rank_by_year_and_name_if_clause

// START-delete_from_rank_by_year_and_name_column
DELETE cyclist_name
FROM cycling.rank_by_year_and_name
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 3;
// END-delete_from_rank_by_year_and_name_column

// START-delete_from_rank_by_year_and_name_row
DELETE
FROM cycling.rank_by_year_and_name
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 3;
// END-delete_from_rank_by_year_and_name_row

// START-delete_from_rank_by_year_and_name_multiple_rows
DELETE
FROM cycling.rank_by_year_and_name
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu';
// END-delete_from_rank_by_year_and_name_multiple_rows

// START-delete_from_rank_by_year_and_name_if_clause
DELETE
FROM cycling.rank_by_year_and_name
WHERE race_year = 2014
  AND race_name = '4th Tour of Beijing'
  AND rank = 3
IF EXISTS;
// END-delete_from_rank_by_year_and_name_if_clause

// START-truncate_rank_by_year_and_name
TRUNCATE cycling.rank_by_year_and_name;
// END-truncate_rank_by_year_and_name

// Repopulate the table
INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Adam PHELAN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Thomas LEBAS', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Daniel MARTIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Johan Esteban CHAVES', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Ilnur ZAKARIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Carlos BETANCUR', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);