/*QUESTION 1  What types of food do monkeys eat?*/

   SELECT UNIQUE FDESC
 	 FROM RATION
		  WHERE ASPECIES='MONKEY';

/*QUESTION 2 List each type of food and the species of animal to which it is served. */

  SELECT UNIQUE FDESC,ASPECIES
   FROM RATION ;

/*QUESTION 3 What cage type(s) do monkey's live in? */

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

/*QUESTION 4 List the name of each monkey and the amount of food they eat per week */

SELECT ANAME,RATION.FDESC,RHOWMUCH*RHOWOFTEN, FUNITS
FROM RATION, FOOD
WHERE ASPECIES='MONKEY'
AND RATION.FDESC = FOOD.FDESC;

 /*QUESTION 5 List the names of the animals that eat vegetables.*/

  SELECT ANAME
  FROM RATION
  WHERE FDESC='VEGETABLES';

/*QUESTION 6  How many animals share Sally's cage? */

  SELECT COUNT(ANAME)-1
  FROM ANIMAL
  WHERE CLOC = (SELECT CLOC
  FROM ANIMAL
  WHERE ANAME = 'SALLY');

/*QUESTION 7 How many monkey's are in the zoo? */

  SELECT COUNT(ASPECIES)
  FROM ANIMAL
  WHERE ASPECIES = 'MONKEY';


/*QUESTION 8 Which species has the most animals?*/

SELECT ASPECIES, COUNT(*) AMOUNT
  FROM ANIMAL
	GROUP BY ASPECIES
	HAVING COUNT(*) = 
	(SELECT MAX(COUNT(*))
 		FROM ANIMAL
		GROUP BY ASPECIES);

/*QUESTION 9 How many pounds of meat are served per week?  */

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


/*QUESTION 10 Which cage(s) are not occupied?*/

SELECT CLOC,CTYPE
FROM CAGE
WHERE CLOC NOT IN(SELECT CLOC FROM ANIMAL);