Do not use SELECT * in any query.

Show all tables needed in a query either in the FROM clause with join statements in the WHERE clause or in the FROM clause using the INNER JOIN ON construction

Do not use any SQL features that were not covered in this course.

Do not use subqueries IF joins can be used to answer a question. However, some questions may require the use of subqueries.

Memphis Zoo Database

Pay close attention to the following notes. Notes:

Notes:

Animal species means elephant, giraffe, tiger, etc.

Animal name is a pet name like Rover, Dumbo, Lucky, etc. Every animal in the zoo has a pet name, but these names are not unique, even within a species..

Enclosure type can be cage, aquarium, pool, fenced field, etc.

Food type can be meat, vegetables, oats, etc.

Procedure type can be surgery, medical exam, dental exam, etc.

Format dates as MM/DD/YYYY.

Questions:

1. List the female wolves from Canada that are over four years old. List them in alphabetic order by their names.

2. Which female wolves from Canada are older than the oldest male foxes from the United States?

3. List, in alphabetic order, the companies that built cages that house animals from Brazil.

4. List the animals, whose names start with the letter A or end with the letter T, that live in aquariums made by companies located in California.

5. How many surgeries did each veterinarian perform on lions that are over 10 years old? Only include veterinarians who performed at least 5 such surgeries.

AnimalNum Name Species Gender CountryOf Age EnclosureNum Origin FoodNum ANIMAL EnclosureNum EnclType Size CompanyName ENCLOSURE Company Name City State YearFounded CONSTRUCTIONCOMPANY University Name Supervisor VetNum VetName VETERINARIAN VetNum Skill VETSKILLS ProcedureNum ProcType Date AnimalNum VetNum PROCEDURE University Name State DateFounded UNIVERSITY FoodNum FOOD FoodName FoodType SupplierName City State Phone SUPPLIER Food Num DELIVERY SupplierName Date Quantity Cost AnimalNum Name Species Gender CountryOf Age EnclosureNum Origin FoodNum ANIMAL EnclosureNum EnclType Size CompanyName ENCLOSURE Company Name City State YearFounded CONSTRUCTIONCOMPANY University Name Supervisor VetNum VetName VETERINARIAN VetNum Skill VETSKILLS ProcedureNum ProcType Date AnimalNum VetNum PROCEDURE University Name State DateFounded UNIVERSITY FoodNum FOOD FoodName FoodType SupplierName City State Phone SUPPLIER Food Num DELIVERY SupplierName Date Quantity Cost