171|SQL – Shelter Shenanigans Sequel

BYU Student Author: @Millie_K_B
Reviewers: @Sterling_Lane, @jimmy_han
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
This challenge is correlated with 173|SQL – Shelter Shenanigans and 174|SQL – Super Shelter Shenanigans. It is not necessary to complete either challenge before completing this one.

You’re still waiting to hear back from that dream accounting job, so you spend your days after graduation volunteering at a local animal shelter. While you’re there, you decide to keep your tech skills sharp and ask your manager if there’s anything you could do to help improve the shelter’s operations. As it turns out, she has a few questions that you think you could answer using SQL.

  1. Your boss is considering limiting the intake of rabbits into the shelter as she thinks there are too many that aren’t getting adopted.
    a. How many rabbits are there at each location? (Note: the only two breeds that are rabbits are the Holland Lops and the Netherland Dwarfs.)
    b. Which rabbit has been here the longest?
    2.The South location is running a promotion on adoption fees where all adoption fees are now $75!
    a. How many animals does this promotion affect, or in other words, how many animals had an adoption fee of over $75?
    b. How much will this promotion cost the location in waived fees, assuming that every pet in the shelter will get adopted?
  2. For this chain of shelters, protocol states that each location needs to have at least one caretaker for every five animals and one vet tech for every fifteen animals housed there.
    a. Are there any locations that are failing these requirements?
    b. If so, present solutions in the challenge replies. The shelter could hire more people, reassign employees, adjust where animals are housed, or something else.

She provides you with the database, the diagram below explaining the relationship between tables, and a Diet Coke. Good luck!

Instructions

  1. Import all the sheets of data into whichever program you choose to practice SQL. If you choose to use Microsoft Access, you can use the database file provided and skip step 2, as the relationships have already been created.
  2. Verify that the relationships between the various Excel sheets are correct. If no relationships are identified, create the relationships yourself.
  3. Create queries that will answer the questions above. For most, if not all, of these questions, it will be easiest to find the answer by creating multiple separate queries. For your solutions, show the SQL query and the resulting table or record.

Data Files

Solution

Time to complete: 30 minutes
Rating: Intermediate
This felt like a great way to see the practicalities of using SQL to verify if company policies are being followed correctly. Rather than using multiple queries, I decided to write some subqueries to answer question 3 in one go. It turned out to be tougher than I originally thought to go that route, but it ended up working out.

My findings from question 3 led me to believe that a vet tech should be reassigned from the East location to the South location and the Central location needs to hire at least three more caretakers to be fully adherent to policy. Thanks for the challenge, Millie!

Question 3 Output:

Code for Question 3 Output

SELECT Location_Name, Role, COUNT(EmployeeID) AS TotalEmployees, TotalAnimals, IIf(
    Role = 'Caretaker' AND TotalAnimals / 5 < COUNT(EmployeeID), 'Good',
    IIf(
      Role = 'Caretaker' AND TotalAnimals / 5 >= COUNT(EmployeeID), 'Bad',
      IIf(
        Role = 'Vet Tech' AND TotalAnimals / 15 < COUNT(EmployeeID), 'Good',
        'Bad'
      )
    )
  ) AS Status
FROM (Location AS l INNER JOIN Employee AS e ON l.LocationID = e.LocationID) INNER JOIN (SELECT LocationID, COUNT(Pet_ID) AS TotalAnimals
FROM Animals
GROUP BY LocationID)  AS a ON l.LocationID = a.LocationID
WHERE Role IN ("Caretaker", "Vet Tech")
GROUP BY Location_Name, Role, TotalAnimals;