db.sql

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    owner_id INTEGER,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);

INSERT INTO person (id, first_name, last_name, age)
    VALUES (0, "Zed", "Shaw", 37);

INSERT INTO pet (id, owner_id, name, breed, age, dead)
    VALUES (0, 0, "Fluffy", "Unicorn", 1000, 0);

INSERT INTO pet (id, owner_id, name, breed, age, dead)
    VALUES (1, 0, "Gigantor", "Robot", 1, 1);

SQL.md

Exercises

• What is the schema below trying to model?
• Write a query that lists all the pets owned by Zed.
• Write a query that finds all pets older than 10 years.
• Write a query that shows what the most popular breed of pet is.
• How would you ensure this query performed well?
• Insert yourself and your pets (or imaginary pets) into the database.
• Sadly, Fluffy the Unicorn just died, write a query that updates the data to show this.
• How would you change this schema so that a pet can be owned by multiple people?
• What are some potential design flaws with this schema?
• How would you model this data using a NoSQL database, such as MongoDB?