calendar

Using clustering columns to display data.

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop-table[]
DROP TABLE IF EXISTS cycling.calendar;
// end::drop-table[]

// NOT A QUERY, JUST A TABLE FOR QUERIES
// CREATE TABLE WITH LIST FOR UPDATE
// The SELECT statements that use this table can be found below

// tag::listColumn[]
CREATE TABLE IF NOT EXISTS cycling.calendar (
  race_id int,
  race_name text,
  race_start_date timestamp,
  race_end_date timestamp,
  PRIMARY KEY (
    race_id, race_start_date, race_end_date
  )
) WITH CLUSTERING ORDER BY (
  race_start_date DESC, race_end_date DESC
);
// end::listColumn[]

// tag::insert-complex-string[]
INSERT INTO cycling.calendar 
  (race_id, race_start_date, race_end_date, race_name) 
   VALUES
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);
// end::insert-complex-string[]

// tag::insert-data[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2015-05-09', '2015-05-31'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2015-06-07', '2015-06-14'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2015-06-13', '2015-06-21'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2015-07-04', '2015-07-26'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2014-05-08', '2014-05-30'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2014-06-06', '2014-06-13'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2014-06-12', '2014-06-20'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2014-07-03', '2014-07-25'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2013-05-07', '2014-05-29'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2013-06-05', '2013-06-12'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2013-06-11', '2013-06-19'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2013-07-02', '2013-07-24'
);
// end::insert-data[]

CAPTURE 'select_all_from_calendar_allow_filtering.results';
// tag::select_start_date[]
SELECT * 
FROM cycling.calendar 
WHERE race_start_date = '2015-06-13' 
ALLOW FILTERING;
// end::select_start_date[]
CAPTURE OFF;

// tag::select_with_in[]
SELECT * 
FROM cycling.calendar 
WHERE race_id IN (101, 102, 103); 
// end::select_with_in[]

CAPTURE 'select_all_from_calendar_with_date_range.results';
// tag::select_with_range[]
SELECT * 
FROM cycling.calendar 
WHERE race_id = 101
  AND race_start_date >= '2014-05-27' 
  AND race_start_date < '2017-06-16';
// end::select_with_range[]
CAPTURE OFF;

// tag::paging_off[]
PAGING OFF;
// end::paging_off[]

CAPTURE 'select_all_from_calendar_with_in_and_order.results';
// tag::select_with_in_and_order[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date DESC;
// end::select_with_in_and_order[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_allow_filtering.results';
// tag::select_with_allow_filtering[]
SELECT * 
FROM cycling.calendar 
WHERE race_name IN ('Giro d''Italia','Tour de Suisse') 
ALLOW FILTERING;
// end::select_with_allow_filtering[]

CAPTURE 'select_all_from_calendar_with_in_and_asc_order.results';
// tag::select_with_in_and_asc_order[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date ASC;
// end::select_with_in_and_asc_order[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_two_ins.results';
// tag::select_with_two_ins[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
  AND (race_start_date, race_end_date)
  IN (('2015-05-09', '2015-05-31'), ('2015-05-06', '2015-05-31'));
// end::select_with_two_ins[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_in_and_range.results';
// tag::select_with_in_and_range[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
  AND (race_start_date, race_end_date) >= ('2015-05-09', '2015-05-24');
// end::select_with_in_and_range[]
CAPTURE OFF;

// tag::insert_with_TTL[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  200, 'placeholder', '2015-05-27', '2015-05-27'
)
USING TTL 200;
// end::insert_with_TTL[]

CAPTURE 'select_ttl_from_calendar.results';
// tag::select_with_TTL[]
SELECT TTL(race_name)
FROM cycling.calendar
WHERE race_id = 200;
// end::select_with_TTL[]
CAPTURE OFF;

// tag::update_with_TTL[]
UPDATE cycling.calendar 
USING TTL 300 
SET race_name = 'Tour de France - Stage 12' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';
// end::update_with_TTL[]

CAPTURE 'select_ttl_after_update_from_calendar.results';
SELECT TTL(race_name)
FROM cycling.calendar
WHERE race_id = 200;
CAPTURE OFF;

// tag::update_TTL_0[]
UPDATE cycling.calendar 
USING TTL 0
SET race_name = 'Tour de France - Stage 12' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';
// end::update_TTL_0[]

// tag::delete-200[]
DELETE
FROM cycling.calendar
WHERE race_id = 200;
// end::delete-200[]

// tag::insert_with_escape_characters[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, 'Women''s Tour of New Zealand', '2015-02-18', '2015-02-22'
);
// end::insert_with_escape_characters[]

// tag::delete-201[]
DELETE
FROM cycling.calendar
WHERE race_id = 201;
// end::delete-201[]

// tag::insert_with_dollar_characters[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, $$Women's Tour of New Zealand$$, '2015-02-18', '2015-02-22'
);
// end::insert_with_dollar_characters[]

DELETE
FROM cycling.calendar
WHERE race_id = 201;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com