/*QUESTION 1 List the species of animals in our zoo? (No duplicates) */

SELECT UNIQUE ASPECIES
FROM ANIMAL;

/*QUESTION  2 How many barred cages are in the zoo? */

 SELECT COUNT(CTYPE)
FROM CAGE
WHERE CTYPE = 'BARRED';

/*QUESTION 3 What type of food do the elephants eat? */
  SELECT UNIQUE FDESC 
  FROM RATION 
  WHERE ASPECIES ='ELEPHANT';
 
/*QUESTION 4 How much hay does each elephant eat per week? */
 SELECT ASPECIES, RATION.FDESC,SUM( RHOWMUCH*RHOWOFTEN) AMOUNT,FOOD.FUNITS
  FROM RATION, FOOD
  WHERE RATION.FDESC ='HAY'
  AND ASPECIES ='ELEPHANT'
  AND FOOD.FDESC =RATION.FDESC
  GROUP BY ASPECIES, RATION.FDESC,FUNITS;

/*QUESTION 5 List each type of food and the species of animal to which it is served. (No duplicates) */
SELECT UNIQUE FDESC, ASPECIES
FROM RATION;

/*QUESTION 6 Name the Lion that eats the most meat? */
SELECT ANAME, FDESC,RHOWMUCH "MAX AMOUNT PER SERVING"
  FROM RATION
  WHERE RHOWMUCH=
   (SELECT MAX(RHOWMUCH)
   FROM RATION
   WHERE FDESC ='MEAT'
AND ASPECIES = 'LION')
  AND FDESC ='MEAT'
AND ASPECIES = 'LION';

/*QUESTION 7 List each cage type and the species which occupy cages of that type. */

SELECT UNIQUE CTYPE, ASPECIES
FROM ANIMAL, CAGE
WHERE CAGE.CLOC = ANIMAL.CLOC;

/*QUESTION 8 How many female animals in the zoo? */

SELECT COUNT(*)
FROM ANIMAL
WHERE AGENDER = 'F';

/*QUESTION 9 How much grain is eaten per week? */

SELECT RATION.FDESC, SUM(RHOWMUCH*RHOWOFTEN) AMOUNT,FOOD.FUNITS
  FROM RATION, FOOD
  WHERE RATION.FDESC ='GRAIN'
  AND FOOD.FDESC =RATION.FDESC
  GROUP BY  RATION.FDESC, RHOWMUCH*RHOWOFTEN, FUNITS;

/*QUESTION 10 What type of cage are elephants kept in ? */ 
 
SELECT UNIQUE CTYPE
FROM CAGE, ANIMAL
WHERE CAGE.CLOC = ANIMAL.CLOC
AND ASPECIES = 'ELEPHANT';