SQL Lab 7

select dob from animals;
SELECT name, dob FROM animals WHERE MONTH(dob) = 6;

 # 1. Select all the animals born in June (assuming you don't now the month number of June)     
SELECT *
FROM animals
WHERE MONTHNAME(dob) = 'june';

# 2. Select all the animals born in the first 8 weeks of the year
SELECT name, dob, WEEK(dob) AS week, WEEKOFYEAR(dob) AS week2, YEARWEEK(dob) AS week_year
FROM animals
where WEEKOFYEAR(dob) <= 8;

# 3. Display the day (in numbers) and month of birth (in words) of all the turtles and cats born before 2007 (two columns).
SELECT name, dob, DATE_FORMAT(dob, '%d')AS day_in_no, DATE_FORMAT(dob, '%M')AS month_in_words
from animals
where (species_id = 2 or species_id = 3) and (dob < "2007");

# 4. Display the day (in numbers) and month of birth (in words) of all the turtles and cats born before 2007 (one column).
SELECT name, dob, DATE_FORMAT(dob, '%d of %M')AS format_date
from animals
where (species_id = 2 or species_id = 3) and (dob < "2007");

# 5. Select all the animals born in April, but not April 24, sorted by decreasing birth time (hours, minutes, seconds) and display their date of birth as in the exemple below:
# Format : January 8, at 6h30 PM, in 2010 after J.C.
SELECT name, dob, DATE_FORMAT(dob,'%M %d, at %r in %Y') as df
FROM animals
having MONTHNAME(dob) = 'april' and df not like 'April 24%' order by df;

# 6. Display all the animal ages in seconds, minutes, hours, days, months, years (I want a column for each)
SELECT name, dob, TIMESTAMPDIFF(SECOND, dob, NOW()) "seconds",
  TIMESTAMPDIFF(MINUTE, dob, NOW()) "minutes",
  TIMESTAMPDIFF(HOUR, dob, NOW()) "hours",
  TIMESTAMPDIFF(day, dob, NOW()) "days",
  TIMESTAMPDIFF(MONTH, dob, NOW()) "months",
  TIMESTAMPDIFF(YEAR, dob, NOW()) "years"
FROM animals;

# 7. Which animals have their birthdays in a month with an even number of days.
select name, dob, DAY(LAST_DAY(dob)) as DaysInMonth
from animals
having (DaysInMonth % 2 = 0);

# 8. Assuming that cats and dogs should not live more than 4 years. . . which of them should be dead by now. (#sorryNoSoSorry)
select name, dob, species_id, TIMESTAMPDIFF(YEAR, dob, NOW()) "age"
from animals
having (species_id = 1 or species_id = 2) and (age > 4);

# 1. Moka was supposed to be born on February 27, 2008. Calculate how many days late she was born.
SELECT name, dob, DATEDIFF(date(dob), '2008-02-27') as days_late
from animals
where name like "moka";

# 2. Display the date that each parrot will celebrate their 25th birthday.
SELECT name, dob, ADDDATE(dob, INTERVAL 25 year) AS "25_bday"
from animals
where (species_id = 4);

# 3. Select all the animals born within a month that has exactly 29 days.
select name, dob, DAY(LAST_DAY(dob)) as DaysInMonth
from animals
having (DaysInMonth = 29);

# 4. After twelve weeks, a kitten is weaned (with some exceptions of course).
# Display the date from which a cats may be adopted (passed or future date).
SELECT name, dob, ADDDATE(dob, INTERVAL 12 week) AS "may_be_adopted"
from animals
where (species_id = 2);

# 5. After Rouquine, Zira, Bouli and Balou are part of the same scope.
# Calculate how long, in minutes, Balou was born before Zira.
SELECT name, dob, TIMESTAMPDIFF(Minute, (select dob from animals where name = 'balou'), (select dob from animals where name = 'zira')) as balou_born_before_zira
from animals
WHERE name IN ('Zira', 'Balou');

# 6. Display the age of each animal in numbers
SELECT name, dob, CURDATE(),
       TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age
       FROM animals;

# 7. Display the animals born in the same year, Display in this format:
# "... was born this YYYY, in MM, on DD (name of day), at HH:MM:SS"
SELECT name, dob, DATE_FORMAT(dob,'was born this %Y, in %m, on %a, at %T') as df
FROM animals
group by dob;

# 1. Rouquine, Zira, Bouli and Balou are part of the same scope.
# Calculate how long in minutes elapsed between the first born and last born.
SELECT name, dob, TIMESTAMPDIFF(Minute, (select dob from animals where name = 'balou'), (select dob from animals where name = 'zira')) as balou_born_before_zira
from animals
WHERE name IN ('Zira', 'Balou');

# 2. Calculate how many animals are born during a month in which the molds are the most consumables (that is to say the months ending in "ber" [September, October, November and December]).
select dob, DATE_FORMAT(dob,'%M') as df, count(id)
from animals
group by df
having df like '%ber';

/* 3. For dogs and cats, display the date of birth of litters of at least two individuals (DD / MM / YYYY),
and the number of individuals for each of these litters.
By Litter I mean 'Animals Born on the same date'.
Attention, it is possible that a range of cats was born the same day a litter of dogs.*/
select date(dob), count(*)
from animals
where (species_id = 1 or species_id = 2) and ((dob) = (dob))
group by date(dob);

# 4. Calculate the average dogs that were born each year between 2006 and 2010 (knowing that we had at least one birth every year).
select date(dob), count(*)
from animals
where species_id = 1 and (dob BETWEEN '2005-12-31' AND '2008-12-31')
group by year(dob);

# 5. Display the date in ISO format of the fifth anniversary of an animals having a father or a mother.
SELECT DATE_FORMAT(dob, '%Y-%m-%d') AS iso_date_formatted, ADDDATE(DATE_FORMAT(dob, '%Y-%m-%d'), INTERVAL 5 year) AS "fifth anniversary"
FROM animals
where father_id is not null or mother_id is not null
ORDER BY id DESC;