ScyllaDB Training petclinic

CREATE KEYSPACE petclinic WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : '3'};

DESCRIBE KEYSPACE 

USE petclinic;

CREATE TABLE petclinic.pets (
pet_chip_id uuid,
pet_first_name text,
pet_last_name text,
pet_type text,
pet_breed text,
pet_birthday timestamp,
PRIMARY KEY (pet_chip_id));

DESCRIBE petclinic.pets;

INSERT INTO petclinic.pets ( pet_chip_id, pet_first_name, pet_last_name, pet_type, pet_breed, pet_birthday) VALUES (123e4567-e89b-12d3-a456-426655440001, 'Molly', 'Moore', 'Dog', 'Beagle', '2015-03-25');
INSERT INTO petclinic.pets ( pet_chip_id, pet_first_name, pet_last_name, pet_type, pet_breed, pet_birthday) VALUES (123e4567-e89b-12d3-a456-426655440000, 'Buddy', 'Bob', 'Dog', 'Akita', '2015-03-25');
INSERT INTO petclinic.pets ( pet_chip_id, pet_first_name, pet_last_name, pet_type, pet_breed, pet_birthday) VALUES (123e4567-e89b-12d3-a456-4266554400e5, 'Torty', 'Neagne', 'Turtle','River Turtle', '1975-05-14');

SELECT * FROM petclinic.pets;

UPDATE petclinic.pets set pet_breed = 'Sea Turtle' WHERE pet_chip_id = 123e4567-e89b-12d3-a456-4266554400e5

ALTER TABLE petclinic.pets ADD pet_gender text;


CREATE TABLE visits (
visit_number uuid PRIMARY KEY,
pet_chip_id uuid,
visit_date timestamp);

INSERT INTO visits (visit_number, pet_chip_id, visit_date) VALUES (1, 123e4567-e89b-12d3-a456-426655440001, '2016-09-05 09:34:26-0700');
INSERT INTO visits (visit_number, pet_chip_id, visit_date) VALUES (2, 123e4567-e89b-12d3-a456-426655440000, '2016-09-03 13:05:36-0700');
INSERT INTO visits (visit_number, pet_chip_id, visit_date) VALUES (3, 123e4567-e89b-12d3-a456-4266554400e5, '2016-10-15 12:36:19-0700');

SELECT * FROM visits WHERE visit_number=2;

SELECT pet_chip_id FROM visits WHERE visit_number=2;

CREATE TABLE pet_owners_by_name (
owner_first_name text,
owner_last_name text,
pet_chip_id uuid,
PRIMARY KEY (owner_first_name, owner_last_name));

INSERT INTO pet_owners_by_name (owner_first_name, owner_last_name, pet_chip_id) VALUES ('George', 'Washington', 123e4567-e89b-12d3-a456-426655440000);
INSERT INTO pet_owners_by_name (owner_first_name, owner_last_name, pet_chip_id) VALUES ('John', 'Adams', 123e4567-e89b-12d3-a456-426655440b23);
INSERT INTO pet_owners_by_name (owner_first_name, owner_last_name, pet_chip_id) VALUES ('John', 'Lilard', 123e4567-e89b-12d3-a456-426655440b54);

SELECT * FROM pet_owners_by_name;

SELECT pet_chip_id FROM pet_owners_by_name WHERE owner_first_name = 'John'; 

SELECT pet_chip_id FROM pet_owners_by_name WHERE owner_last_name = 'Adams'; 

SELECT pet_chip_id FROM pet_owners_by_name WHERE owner_last_name = 'Adams' ALLOW FILTERING; 

DROP TABLE petclinic.pet_owners_by_name;

CREATE TABLE petclinic.pet_owners_by_name (
owner_first_name text,
owner_last_name text,
pet_chip_id uuid,
PRIMARY KEY (owner_first_name, owner_last_name))
WITH CLUSTERING ORDER BY (owner_last_name ASC);

CREATE TABLE petclinic.veterinarian (
employee_id uuid, 
first_name text, 
last_name text, 
employment_start_date timestamp, 
employment_termination_date timestamp, 
certifications text,
certification_date timestamp,
PRIMARY KEY (employee_id));