DB SQL HW 1-6

-- HW 1
select animal_name AS 'ID', animal_age AS 'Age', arrival_date AS 'Arrival Date'
from animal
where animal_gender = 'F'
order by 1 asc;

-- HW 2
select animal_id AS 'ID', animal_name AS 'Animal Name'
from animal
where arrival_date = '2014-01-02';

-- HW 3
select animal_id AS 'ID', animal_name AS 'animal name'
from animal join breed
	on animal.breed_id = breed.breed_id
where animal_gender = 'M' AND animal_id = 10
order by animal_id asc;

-- HW 4
select concat(vol_first, ' ', vol_last) AS 'Coco Volunteer'
from animal join volunteer
	on animal.vol_id = volunteer.vol_id
where animal_name = 'Coco';

-- HW 5
select animal_name AS 'Name', breed_name AS 'Breed', (male_lo_weight + female_lo_weight) / 2 AS 'Average Low Weight', (male_hi_weight + female_hi_weight) / 2 AS 'Average High Weight'
from animal join breed
	on animal.breed_id = breed.breed_id
order by 4 desc;

-- HW 6 requires a 3 way join with the wishlist
select concat(adopter_first, ' ', adopter_last, ' wishes for a ', breed_name, ' age ', animal_age) AS 'Wish list'
from adopter join wishlist -- we joined the wishlist THIS IS A THREE-WAY JOINwishlist
	on adopter.adopter_id = wishlist.adopter_id
join breed -- we join breed with the the breed that they want too
	on breed.breed_id = wishlist.desired_breed
join animal
	on animal.breed_id = wishlist.wish_id
where breed_type = 'cat'
order by adopter_first asc;