Zoo Assignment

The purpose of this assignment to understand simple SQL statements and answer questions based on the Zoo database schema.

Assignment Requirements

Run the Zoo database's "FULL_DATABASE_with_DROP" script to create the tables and insert data for these questions.

Questions

1. List the all animal name whose name start with letter A.

2. Select all the employee information from the employee who has id 4213.

3. List the team names that are alphabetically between fuschia and yellow.

4. List all the animal's whose names start with "B".

5. List all the product ids, product names, product_suggested price and product type. Order by product name.

6. List the names of those corporate sponsors whose donations are between 10,000 and 25,000.

7. Display all the different cities that employees live in. Make sure there are no duplicates.

8. List the employee id, first name, last name that are certified keeper. Use in clasue.

9. List the first name, last name, phone number and department name of volunteers who work in 'Information Systems', 'Finance', and 'Public Relations'. Order your results by Department name.

10. List the ID, first name, last name, phone number and social security number of the employee(s) who have been with the company the longest. Use a subquery.

11. List the Product ID, Name and suggested price for those products whose suggested price is less than the average suggested price.

12. List the first name, last name, and salary for the highest paid employee. Use a subquery.

13. Display employee's first and last names if they received a research bonus.

14. List the product id, product name and product suggested price for that product which has the highest suggested price. Use subquery.

15. List the name of animal, its gender and breed name for the oldest animal in the zoo.

16. How many employees belong to the research department?

17. How many employees are there in each zoo?

18. Find the number of payments made by each credit card type.

19. Display the transaction date and the sum of the total transaction amount grouped by the transaction date with the store id = '1401' having the sum of the transcation total amount greater than 20.

20. List the product name, the max stock, the amount in inventory, and the reorder point of those products whose current inventory amount is within 7 items of the reorder point.

21. List the animal name and the breed name whose height is more than 31.

22. List the names of the sponsor companies that donated in July of 2008. Also list the amount donated, but limit your results with those sponsors who donated more than 2000.

23. List the receipt line id, selling price, product name, transaction date, store city, employee last name, zoo name. Order the output by employee last name.

24. List all the food item descriptions and corresponding food categorie descriptions even if the food category isn't associated with a food item, and food item does not have a category. Limit your results by selecting only the food categories that being with A or B OR only the item descriptions that being with 'Tasty' or 'Organic'.

25. List employee id who are from the same city and whose salary is greater than 90,000.

26. For each animal, list their name, gender and their prescription dosage and the date taken. All animals should be included in the results. For those animals that do not have a prescription, leave the appropriate fields blank. Order by animal name.

27. What is the sum total amount paid with Check?

28. List the first and Last names of the vets who work at a hospital that starts with "Wo".

29. List all animal breed names that came from Africa.

30. List only the product id of all the "manufactured" products with even product id. [Hint: Use MOD function].