SQL Lab 3

use block3;

select * from animals;
select * from species;
select * from races;

-- List all the Affenpinscher (Race Name, Animal Name)
select races.name as "Race Name", animals.name as "Animal Name"
from races inner join animals on races.id = animals.race_id
where races.name = 'Affenpinscher';

-- List of animals (Name, DOB, Race Name, Race Description) who does not have the word pound in their race description.
select animals.name as "Animal Name", animals.dob as "DOB", races.name as "Race Name",  races.description as "Race Description"
from races inner join animals on races.id = animals.race_id
WHERE races.description not like "%pound%";

-- List of animals (Name, DOB, Race Name, Race Description) who does not have a race description.
select animals.name as "Animal Name", animals.dob as "DOB", races.name as "Race Name",  races.description as "Race Description"
from races inner join animals on races.id = animals.race_id
WHERE races.description is null; # no null race description

-- List all the Cats and Turtle with their sex, species, latin name and race if exist. Order by animal type and by race.
select animals.sex, species.current_name, species.latin_name, races.name as "Race Name"
from species inner join animals on species.id = animals.species_id inner join races on races.id = animals.race_id
WHERE species.current_name = "cat" or species.current_name = "turtle"
order by animals.sex and races.id;

-- List of the female cat without a race and was born before july 2010. (Name, DOB and Race Name)
select animals.name as "Animal Name", animals.dob, races.name as "Race Name"
from species inner join animals on species.id = animals.species_id inner join races on races.id = animals.race_id
WHERE species.current_name = "cat" and animals.sex = "F" and dob < "2010";


-- List all the female Chausie Cats . Order by age. (Race Name, Species Current name, DOB, Sex)
select races.name as "Race Name", species.current_name, animals.dob, animals.sex
from species inner join animals on species.id = animals.species_id inner join races on races.id = animals.race_id
WHERE species.current_name = "cat" and animals.sex = "f" and races.name = 'Chausie' # no female cat with name chausie
order by animals.dob;

-- List all the Cats who have any parents. (Species Current name, Name)
select species.current_name, animals.name
from species inner join animals on species.id = animals.species_id
WHERE species.current_name = "cat" and (animals.mother_id is not null or animals.father_id is not null);


select species.current_name, animals.name, animals.sex, animals.dob
from species inner join animals on species.id = animals.species_id
WHERE animals.father_id = "22";  # ????


select name, sex, dob from animals
where name = "Bouli";

SELECT species.current_name, animals.name, animals.sex, animals.dob
FROM animals
JOIN species
ON animals.father_id = animals.id
where animals.name = "Bouli";

select *
from animals
where name like "%Bouli%";


SELECT * FROM animals
WHERE MATCH (name, father_id)
AGAINST ('Bouli');    #add fulltext to animals first to run;


-- List of Bouli’s kid(s) (Name, Sex and DOB)
select t2.name,t2.dob,t2.sex from animals t1 join animals t2 on t2.father_id = t1.id where t1.name = "Bouli";




-- List of animals who has a father, a mother and a race. We must know the parent’s race if exist
select animals.name as "Animal Name", races.name as "Race Name", species.current_name, (select name from races where races.id = animals.race_id and races.id is not null) as fatherrace
from species inner join animals on species.id = animals.species_id inner join races on races.id = animals.race_id
WHERE animals.mother_id is not null or animals.father_id is not null or animals.race_id is not null;



UPDATE animals SET mother_id = 13, father_id = 20 WHERE id = 18;
UPDATE animals SET mother_id = 13, father_id = 25 WHERE id = 22;


select animals.name as "Animal Name", races.name as "Race Name", species.current_name, animals.father_id, animals.mother_id
from species inner join animals on species.id = animals.species_id inner join races on races.id = animals.race_id;


select t2.name,t2.dob,t2.sex from animals t1 join animals t2 on t2.father_id = t1.id where t1.name = "Bouli";


select species.current_name,t2.id as "Child id", t2.name "Child Name",t2.father_id,t2.mother_id,t2.sex
from animals t1 join animals t2 on t2.father_id = t1.id join species;


select t2.name,t2.dob,t2.sex from animals t1 join animals t2 on t2.father_id = t1.id where t1.name = "Bouli";


select species.current_name,t2.id as "Child id", t2.name "Child Name",t2.father_id,t2.mother_id,t2.sex
from animals t1 join animals t2 on t2.father_id = t1.id join species;

select * from animals as t1 join animals as t2 join animals as t3 on (t2.father_id = t1.id) = t3.id; #parent and child

select * from animals as t1 join animals as t2 join animals as t3;

select * from animals where name = "rox";

select * from animals t1 join animals t2 on (t1.father_id = t2.id); # child and parent

select * from animals t1 inner join  animals t2 on t2.father_id = t1.id inner join animals t3 on t2.id = t3.father_id; # grandfather , father and child

select * from animals t1 inner join  animals t2 on t2.father_id = t1.id inner join animals t3 on t2.id = t3.father_id;

select * from animals t1 inner join  animals t2 on t2.mother_id = t1.id inner join animals t3 on t2.id = t3.mother_id; # grandmother, mother and child

select * from animals t1 inner join  animals t4 on t4.mother_id = t1.id inner join animals t5 on t4.id = t5.mother_id; # grandmother, mother and child

use block3;

select * from animals t1 inner join  animals t2 on t2.father_id = t1.id inner join animals t3 on t2.id = t3.father_id
union
select * from animals t1 inner join  animals t4 on t4.mother_id = t1.id inner join animals t5 on t4.id = t5.mother_id; #grandparents, parent and child

-- Are there any Grand-Parents in our BD? (Species Current Name, Child ID, Child Name, Father ID, Father Name, Mother ID Mather Name, Grandma Name From Mom, Grandma ID From Mom, Grandpa Name From Mom, Grandpa ID From Mom, Grandma Name From Dad, Grandma ID From Dad, Grandpa Name From Dad, Grandpa ID From Dad)
select * from animals t1 inner join  animals t2 on t2.father_id = t1.id inner join animals t3 on t2.id = t3.father_id join species
union
select * from animals t1 inner join  animals t4 on t4.mother_id = t1.id inner join animals t5 on t4.id = t5.mother_id join species; #grandparents, parent and child


select * from animals child join animals mom on child.mother_id = mom.id
join animals dad on child.father_id = dad.id
join animals mom_mom on mom.mother_id = mom_mom.id
join animals mom_dad on mom.father_id = mom_dad.id
join animals dad_mom on dad.mother_id = dad_mom.id
join animals dad_dad on dad.father_id = dad_dad.id;