Menagerie database

 CREATE DATABASE menagerie;

USE menagerie;

CREATE TABLE pet (

    name    VARCHAR(20),

    owner   VARCHAR(20),

    species VARCHAR(20),

    sex     CHAR(1),

    birth   DATE,

    death   DATE

);

CREATE TABLE event (

    name    VARCHAR(20),

    date    DATE,

    type    VARCHAR(15),

    remark  VARCHAR(255)

);

INSERT INTO pet (name, owner, species, sex, birth, death) VALUES 

('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),

('Claws', 'Gwen', 'cat', 'm', '1994-03-17', NULL),

('Buffy', 'Harold', 'dog', 'f', '1989-05-13', NULL),

('Fang', 'Benny', 'dog', 'm', '1990-08-27', NULL),

('Bowser', 'Diane', 'dog', 'm', '1979-08-31', '1995-07-29'),

('Chirpy', 'Gwen', 'bird', 'f', '1998-09-11', NULL),

('Whistler', 'Gwen', 'bird', NULL, '1997-12-09', NULL),

('Slim', 'Benny', 'snake', 'm', '1996-04-29', NULL),

('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);

INSERT INTO event (name, date, type, remark) VALUES 

('Fluffy', '1995-05-15', 'litter', '4 kittens, 3 female, 1 male'),

('Buffy', '1993-06-23', 'litter', '5 puppies, 2 female, 3 male'),

('Buffy', '1994-06-19', 'litter', '3 puppies, 3 female'),

('Chirpy', '1999-03-21', 'vet', 'needed beak straightened'),

('Slim', '1997-08-03', 'vet', 'broken rib'),

('Bowser', '1991-10-12', 'kennel', NULL),

('Fang', '1991-10-12', 'kennel', NULL),

('Fang', '1998-08-28', 'birthday', 'Gave him a new chew toy'),

('Claws', '1998-03-17', 'birthday', 'Gave him a new flea collar');

1. GROUP BY & HAVING

• Question 1: Write a query to find the total number of pets owned by each owner.

• Question 2: Find all types of species that have more than 2 pets in the database.

• Question 3: List the owners who own more than 2 pets, along with the total count of pets they own.

• Question 4: Calculate the average age of pets for each species, but only include species where the average age is greater than 30 years

2. Equijoins

• Question 5: Write a query to display each pet's name, their species, and the date of any event they participated in.

• Question 6: Find the names of all pets, their owners, and the specific type (type) of events where the remark contains the word "birthday".

• Question 7: List all pets (name and species) who have had a "vet" event, along with the date and the specific remark recorded by the vet.

3. Natural Joins

• Question 8: Using a NATURAL JOIN, retrieve a list of all pets who have recorded events, showing the pet's name, birth date, event date, and remarks.

• Question 9: Write a query using a natural join to find all female pets (sex = 'f') that have experienced an event, displaying their name, species, and the event type.

4. Subqueries

• Question 10: Write a query to find all pets that are younger than the average age of all pets in the database.

• Question 11: Find the names and species of all pets that have never participated in any event 

• Question 12: List the names of all pets that have had an event on the exact same date that 'Fluffy' had an event.