SQL Lab 5

use block_db_w3d3;

-- How many races exist in the animals table? (Display all of their name)
select count(race_id) as races_number, GROUP_CONCAT(name) from animals;

-- How many species exist in the races table? (Display all of their name)
select count(distinct species_id) as species, GROUP_CONCAT(distinct species.current_name) from races
inner join species on races.species_id = species.id;

-- How many Dogs have a father? (Display the children's name and the father's name)
select * from animals where species_id = 1 and father_id is not null;

select * from animals as t1 join animals as t2 join animals as t3 on (t2.father_id = t1.id) = t3.id where t2.father_id is not null and t2.species_id = 1;

-- What is the average race price of each specie?
SELECT AVG(price) AS "Average Price",
       id AS "Race ID"
    FROM races
GROUP BY id;

-- How many males and females exist in the animals table?
select count(*) as male_number
from animals
where sex = 'M'
union
select count(*) as female_number
from animals
where sex = 'F'
group by sex;

select name, COUNT(*)as tot,
  COUNT(case when animals.sex='M' then 1 end) as male,
  COUNT(case when animals.sex='F' then 1 end) as female
  from animals  group by name;
 
  SELECT
  SUM(CASE WHEN animals.sex='M' THEN 1 ELSE 0 END) as MaleCount,
  SUM(CASE WHEN animals.sex='F' THEN 1 ELSE 0 END) as FemaleCount,
  COUNT(*) as TotalCount
FROM animals;

-- Give the name of 6 random males and 4 random females.
(SELECT name,sex FROM animals where sex = 'M'
ORDER BY RAND() 
LIMIT 6)
union
(SELECT name,sex FROM animals where sex = 'F'
ORDER BY RAND() 
LIMIT 4);

-- How many animals have the same name length?
select length(name) as same_length, count(name) from animals where length(name);

-- Whice race doesn’t have any animal attached to it
select name from races where id not in (select race_id from animals where race_id is not null);

select * from animals where race_id is null;

-- Which specie has less than 5 males (order by latin_name alphabetically)
SELECT animals.sex, species.latin_name
  FROM animals
     INNER JOIN species
       ON species.id = animals.species_id
        GROUP BY latin_name
          HAVING COUNT(*) < 5;
         
-- What is the average animal age per species
SELECT species_id as "species_id", avg(YEAR(CURDATE()) - YEAR(dob)) AS "average age" FROM animals group by species_id;

-- How many males and females of each race do we have? Do a total count for the race (male and female) and for the species. Display the race name and the current species name.
  SELECT
  SUM(CASE WHEN animals.sex='M' THEN 1 ELSE 0 END) as MaleCount,
  SUM(CASE WHEN animals.sex='F' THEN 1 ELSE 0 END) as FemaleCount,
  COUNT(*) as TotalCount,
  races.name as "Race Name", species.current_name
FROM animals inner join races on races.id = animals.race_id inner join species on species.id = animals.species_id group by race_id;

-- What would be the cost per species and the total cost to adopte: Parlotte, Spoutnik, Caribou, Cartouche, Cali, Canaille, Yoda, Zambo and Lulla?
SELECT AVG(price) AS "Average Price per species",
       species_id AS "Species ID", (select SUM(price) as total_debt from species)
    FROM animals
    inner join species on species.id = animals.species_id where name = 'Parlotte' or name = 'Spoutnik' or name = 'Caribou' or name = 'Cartouche' or name = 'Cali' or name = 'Canaille' or name = 'Yoda' or name = 'Zambo' or  name = 'Lulla'
GROUP BY species_id;


SELECT
AVG(price) AS "Average Price",
       species_id AS "Species ID",
SUM(species.price) AS "price", animals.name
FROM animals
inner join species on species.id = animals.species_id
WHERE animals.name = 'Parlotte' or animals.name = 'Spoutnik' or animals.name = 'Caribou' or animals.name = 'Cartouche' or animals.name = 'Cali' or animals.name = 'Canaille' or animals.name = 'Yoda' or animals.name = 'Zambo' or  animals.name = 'Lulla'
GROUP BY animals.name
WITH ROLLUP;

-- What's the median price of the species? (median = (max + min)/2)
select max(price) + min(price) / 2 as median from species;

-- Assuming the inflation rises 20% more every year, show the prices of each animal for the next 5 years. (based on specie's race) #?
select price + (price * 20.0 / 100.0)*1 as 'year1', price + (price * 20.0 / 100.0)*2 as 'year2', price + (price * 20.0 / 100.0)*3 as 'year3', price + (price * 20.0 / 100.0)*4 as 'year4',price + (price * 20.0 / 100.0)*5 as 'year5'  from species;

-- How much would it cost me to buy all the Dogs and all the Cats. Show the total per species and the overall total.
SELECT AVG(species.price) AS "Average Price", sum(species.price) as "sum",
       species.id AS "Species ID"
    FROM species,races where species.current_name = 'dog' or species.current_name = 'cat'
GROUP BY species.id
with rollup;


-- What is the average price of all the Dogs and the average price of all the Cats.
SELECT AVG(price) AS "Average Price", species.current_name,
       id AS "Species ID"
    FROM species where species.current_name = 'dog' or species.current_name = 'cat'
GROUP BY id;



-- Would it cost more to buy all the males or all the females animals based on their race price? (if the race price is null, or the animal doesn't have a race attached to it, use their specie's price)
  SELECT sum(races.price) as "Race price", sum(species.price) as "Species price",
  SUM(CASE WHEN animals.sex='M' THEN 1 ELSE 0 END) as MaleCount,
  SUM(CASE WHEN animals.sex='F' THEN 1 ELSE 0 END) as FemaleCount,
  COUNT(*) as TotalCount,
  races.name as "Race Name", species.current_name
FROM animals inner join races on races.id = animals.race_id inner join species on species.id = animals.species_id where animals.sex = 'M' or animals.sex = 'F' group by animals.sex with rollup;

SELECT
    animals.sex,
    sum(races.price) AS race_total_price, sum(species.price) AS species_total_price , sum(races.price + species.price) as total_price
FROM animals inner join races on races.id = animals.race_id inner join species on species.id = animals.species_id
GROUP BY
    animals.sex;


-- Show a sentence giving the price of the species, for each species ex: The Dogs cost: 200$...
SELECT concat('The ', species.current_name, ' cost: ', species.price), AVG(price) AS "Average Price",
       id AS "Species ID"
    FROM species
GROUP BY id;


-- Show the cats with the letter "a" as the second letter in their name.
SELECT name FROM animals WHERE name LIKE '_a%' and species_id = 2;


-- Show the names of parrots by replacing "a" with "@" and "e" with "3".
SELECT name, REPLACE(replace(name, 'a', '@'), 'e' , '3')  as "replace" from animals where species_id = 4;

-- Show the dogs with an even number of letters in their names.
-- select (CASE (length(name) % 2 = 0) WHEN 1 THEN "Even" END)as result from animals where species_id = 1;
 select name from animals where species_id = 1 AND (length(name) % 2 = 0);



-- Show the taxes you would pay on each animals and the total price. Assuming GST = 5% and HST = 9.9975% (Round to the tens) ex: Price: 10$ GST: 0.5$ HST: 0.99975$ Total:  11.5$
SELECT current_name, price,
  price + (price * 5.0 / 100.0) AS GST, price + (price * 9.9975 / 100.0) AS HST, price + ((price * 5.0 / 100.0) + ((price * 9.9975 / 100.0))) as tot
FROM species;




-- Give a nickname to all the animals with the following criteria: -max 10 characters -all lower cases -based on the reverse of their current name -starts their id -ends with their original name length -padding with "@
select name, lcase(name), reverse(name)  from animals;

select concat((id, lcase(reverse(name)), length(name))), name from animals;

-- select concat(id,(reverse(lcase(name))),length(name)) from animals;

select lpad(concat(id,(reverse(lcase(name))),length(name)), 10, '@') as 'nickname' from animals;