171|SQL – Shelter Shenanigans Sequel

Millie_K_B · April 9, 2024, 2:48am

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.

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?
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

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.
Verify that the relationships between the various Excel sheets are correct. If no relationships are identified, create the relationships yourself.
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

Challenge171_Solution.accdb
Solution Video: Challenge 171|SQL – Shelter Shenanigans Sequel

Sterling_Lane · April 11, 2024, 5:09am

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;

Cragun_Bastian · February 25, 2025, 9:36pm

Time: 45 minutes
Difficulty: Medium
Comments: This was a good application program that shows how it might be useful in real life.

SELECT Location.Location_Name, Count(Animals.PetID) AS Quantity, Animals.Impound_Date, Animals.Breed
FROM Animals
INNER JOIN Location ON Animals.LocationID=Location.LocationID
WHERE Animals.Breed= ‘Netherland Dwarf’ OR Animals.Breed= ‘Holland Lop’
GROUP BY Location.Location_Name, Animals.Impound_Date, Animals.Breed
ORDER BY Animals.Impound_Date;

Location_Name	Quantity	Impound_Date	Breed
South	1	01/05/23	Holland Lop
South	1	02/10/23	Holland Lop
Central	1	06/25/23	Netherland Dwarf
Central	1	07/30/23	Holland Lop

2A.SELECT Count(Animals.PetID) AS total_animals
FROM Animals
INNER JOIN Location ON Animals.LocationID=Location.LocationID
WHERE Location.Location_Name = ‘South’ AND Animals.Adoption_Fee >75;

TotalAnimals

TotalAnimals total_animals
13

2B. SELECT Sum(Animals.Adoption_Fee-75) AS promotion_cost
FROM Animals
INNER JOIN Location ON Animals.LocationID=Location.LocationID
WHERE Location.Location_Name = ‘South’ AND Animals.Adoption_Fee >75;

PromotionCost

PromotionCost promotion_cost
$495.00

SELECT Employee.Role, Count(Employee.EmployeeID) as employee_number, Location.Location_Name
FROM Employee
Left JOIN Location ON Employee.LocationID=Location.LocationID
GROUP BY Location.Location_Name, Employee.Role;

Role	employee_number	Location_Name
Caretaker	3	Central
Manager	1	Central
Receptionist	1	Central
Vet Tech	2	Central
Caretaker	4	East
Manager	2	East
Receptionist	1	East
Vet Tech	2	East
Caretaker	4	South
Manager	1	South
Receptionist	1	South
Vet Tech	1	South

-AND-

SELECT Count(Animals.PetID) as animal_number, Location.Location_Name

FROM Animals

Left JOIN Location ON Animals.LocationID=Location.LocationID

GROUP BY Location.Location_Name;

animal_number	Location_Name
27	Central
15	East
16	South

Therefore the south location needs another vet tech. However, the east location can make do with just one, so one of the vet techs in the east location can move to the south location. In addition, the Central location needs to hire 3 more caretakers to manage the number of animals there.

Lilia_Fisher · February 27, 2025, 2:48am

Time to complete: 30
Rating: Intermediate
I liked having to figure out when to GROUP BY and when to use equations.

For question 3, the Central location needs two more caretakers, and they can take one from the East and South.

1a. SELECT l.location_Name, COUNT(a.PetID) AS Total
FROM Animals AS a INNER JOIN Location AS l ON a.LocationID = l.LocationID
WHERE a.Breed = “Holland Lop” OR a.Breed = “Netherland Dwarf”
GROUP BY l.Location_Name;

1b. SELECT Breed, Name, Impound_Date
FROM Animals
WHERE Breed = “Holland Lops” OR Breed = “Netherland Dwarfs”
ORDER BY Impound_Date;

2a.SELECT COUNT(a.[PetID]) AS [NumberofPetsAffected]
FROM Location AS l INNER JOIN Animals AS a ON l.LocationID = a.LocationID
WHERE a.Adoption_Fee > 75 AND l.Location_Name = “South”;

2b. SELECT SUM(a.Adoption_Fee - 75) AS [Waived Fees]
FROM Animals AS a INNER JOIN Location AS l ON a.LocationID = l.LocationID
WHERE a.Adoption_Fee > 75 AND l.Location_Name = “South”;

3. SELECT COUNT(a.PetID) / 5 AS Employees, COUNT(a.PetID) AS Animals, COUNT (a.PetID) / 15 AS VTechs, l.Location_Name
FROM Animals AS a INNER JOIN Location AS l ON a.LocationID = l.LocationID
GROUP BY l.Location_Name;

Harrison_Briggs · February 27, 2025, 4:11am

Time to Complete: 30 min
Difficulty: Intermediate
1.
a) SELECT l.Location_Name, COUNT(a.breed) AS NumberAtLocation
FROM Location l INNER JOIN Animals a ON l.locationID=a.LocationID
WHERE a.breed = “Holland Lop” OR a.breed = “Netherland Dwarf”
GROUP BY l.Location_Name;

2 in Central, 2 in South

b) SELECT a.PetID, a.Name, a.Impound_Date
FROM Animals a
WHERE Breed = “Netherland Dwarf” OR Breed = “Holland Lop”
ORDER BY a.Impound_Date

Zoe, PetID 2

a) SELECT COUNT(a.PetID)
FROM Animals a INNER JOIN Location l ON l.locationID=a.locationID
HAVING Adoption_fee > 75 AND l.location_name = “South”

13 Pets

b) SELECT SUM(a.Adoption_Fee-75) AS CostOfPromotion
FROM Animals a INNER JOIN location l ON l.locationID=a.LocationID
WHERE a.Adoption_Fee > 75 AND l.location_name = “South”

$495

SELECT Location_Name, Role, COUNT(EmployeeID) AS TotalEmployees, TotalAnimals
FROM (Location l INNER JOIN Employee e ON l.LocationID = e.LocationID) INNER JOIN (SELECT LocationID, COUNT(PetID) AS TotalAnimals
FROM Animals
GROUP BY LocationID) a ON l.LocationID = a.LocationID
WHERE Role = “Caretaker” OR Role = “Vet Tech”
GROUP BY Location_Name, Role, TotalAnimals;

Central has too few caretakers
South has too few Vet Techs

You can move a Vet Tech from East to South and a Caretaker from East to Central and resolve the pet:employee issues!

Comments: thank you for this problem!

Quinn_Twitchell · February 27, 2025, 12:10pm

If we want to keep with policy we should move one vet tech from the east to that south location and one caretaker from the east to the central location as well as hiring two more caretakers for the central location

Query to calculate Animal per Caretaker

SELECT
a.Location_Name,
a.Count_of_Animals,
c.Count_of_Caretakers,
IIF(c.Count_of_Caretakers > 0, a.Count_of_Animals / c.Count_of_Caretakers, NULL) AS Animal_Per_Caretaker
FROM
(SELECT l.Location_Name, COUNT(a.PetID) AS Count_of_Animals
FROM Animals AS a
INNER JOIN Location AS l ON a.LocationID = l.LocationID
GROUP BY l.Location_Name) AS a
INNER JOIN
(SELECT l.Location_Name, COUNT(e.EmployeeID) AS Count_of_Caretakers
FROM Employee AS e
INNER JOIN Location AS l ON e.LocationID = l.LocationID
WHERE e.Role = ‘Caretaker’
GROUP BY l.Location_Name) AS c
ON a.Location_Name = c.Location_Name;

Query to calculate Animal per Vet Tech

SELECT
a.Location_Name,
a.Count_of_Animals,
vt.Count_of_Vet_Tech,
IIF(vt.Count_of_Vet_Tech > 0, a.Count_of_Animals / vt.Count_of_Vet_Tech, NULL) AS Animal_Per_Caretaker
FROM
(SELECT l.Location_Name, COUNT(a.PetID) AS Count_of_Animals
FROM Animals AS a
INNER JOIN Location AS l ON a.LocationID = l.LocationID
GROUP BY l.Location_Name) AS a
INNER JOIN
(SELECT l.Location_Name, COUNT(e.EmployeeID) AS Count_of_Vet_Tech
FROM Employee AS e
INNER JOIN Location AS l ON e.LocationID = l.LocationID
WHERE e.Role = ‘Vet Tech’
GROUP BY l.Location_Name) AS vt
ON a.Location_Name = vt.Location_Name;

Cadestroyer5 · February 27, 2025, 5:46pm

Time: 30
Difficulty: Medium
Notes: You can have back your diet coke.
Solutions

SELECT l.Location_Name, a.Impound_Date, a.Breed, Count(a.LocationID) as Count_Locations

FROM Animals a

INNER JOIN Location l On a.LocationID = l.LocationID
WHERE a.Breed IN (‘Holland Lop’ , ‘Netherland Dwarf’)
GROUP BY l.Location_Name, a.Impound_Date, a.Breed;
There are two rabbits at both South and Central.
The Holland Lop at South has been here longest since January 2023

SELECT Count(a.PetID) as SaleCount
FROM Animals a
INNER JOIN Location l ON a.LocationID = l.LocationID
WHERE (a.Adoption_Fee > 75) AND (Location_Name = ‘South’);

2B.
SELECT Sum(a.Adoption_Fee - 75) as LostProceeds

FROM Animals a

INNER JOIN Location l ON a.LocationID = l.LocationID

WHERE (a.Adoption_Fee > 75) AND (Location_Name = ‘South’);

SELECT l.Location_Name, Count(a.PetID) AS AnimalCount
FROM Location l
LEFT JOIN Animals a ON l.LocationID = a.LocationID
GROUP BY l.Location_Name;

AND

SELECT l.Location_Name, Count(e.EmployeeID) AS EmployeeCount
FROM Location l
LEFT JOIN Employee e ON l.LocationID = e.LocationID
WHERE e.Role Like (“Caretaker”)
GROUP BY l.Location_Name;

The Central location is low on caretakers.They could hire more or reassign

Luke_Hall · February 27, 2025, 6:26pm

Time to Complete: 35 minutes
Difficulty: Intermediate
Query 1:
SELECT L.Location_Name, A.Impound_Date, A.Breed, Count(A.LocationID) as Count_Locations
FROM Animals AS A
INNER JOIN Location AS L On A.LocationID = L.LocationID
WHERE A.Breed IN (‘Holland Lop’ , ‘Netherland Dwarf’)
GROUP BY L.Location_Name, A.Impound_Date, A.Breed;
Query 2:
a).
SELECT Count(A.PetID) as SaleCount
FROM Animals AS A
INNER JOIN Location AS L ON A.LocationID = L.LocationID
WHERE A.Adoption_Fee > 75 AND Location_Name = “South”;
b).
SELECT Sum(A.Adoption_Fee - 75) AS LostProceeds
FROM Animals AS A
INNER JOIN Location AS L ON A.LocationID = L.LocationID
WHERE A.Adoption_Fee > 75 AND Location_Name = “South”;
Query 3:
a).
SELECT L.Location_Name, Count(A.PetID) AS AnimalCount
FROM Location AS L
LEFT JOIN Animals AS A ON L.LocationID = A.LocationID
GROUP BY L.Location_Name;
b).
SELECT L.Location_Name, Count(E.EmployeeID) AS EmployeeCount
FROM Location AS L
LEFT JOIN Employee AS E ON L.LocationID = E.LocationID
WHERE E.Role Like (“Caretaker”)
GROUP BY L.Location_Name;

Topic		Replies	Views
173\|SQL – Shelter Shenanigans Challenges beginner , managerial , sql , analytics , query-writing	69	335	March 13, 2025
174\|SQL – Super Shelter Shenanigans Challenges beginner , managerial , sql , database , data , query	4	70	March 13, 2025
175\|SQL&CHATGPT – Shelter Shenanigans Again Challenges intermediate , managerial , sql , chatgpt , database , data , query , generative-ai , tables	2	194	January 28, 2025
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	138	March 13, 2025
170\|SQL – Booking Barney’s Bulk Books Buyers Challenges beginner , intermediate , sql , data-analysis , bookkeeping	15	169	March 15, 2025

171|SQL – Shelter Shenanigans Sequel

Related topics