# 174|SQL – Super Shelter Shenanigans

**URL:** <https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436>\
**Category:** Challenges\
**Tags:** beginner, managerial, sql, database, data, query\
**Created:** [April 9, 2024, 11:31pm UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436 "2024-04-09T23:31:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Millie\_K\_B](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/millie_k_b/32/5493_2.png) [@Millie\_K\_B](https://www.techhub.training/u/Millie_K_B)\
**Post date:** [April 9, 2024, 11:31pm UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436/1 "2024-04-09T23:31:29Z")

</div>

_BYU Student Author_: @Millie_K_B  
_Reviewers_: @Jacob_Dutton, @Andrew_Wilson  
_Estimated Time to Solve:_ 20 Minutes

We provide the solution to this challenge using:

- SQL

Need a program? Click [here](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138).

**Overview**  
This challenge is correlated with [173|SQL – Shelter Shenanigans](https://www.techhub.training/t/173-sql-shelter-shenanigans/2434) and [171|SQL – Shelter Shenanigans Sequel](https://www.techhub.training/t/171-sql-shelter-shenanigans-sequel/2399). It is not necessary to complete either challenge before completing this one.

While waiting for a call back from your dream accounting job, you keep your commitment to go forth and serve by volunteering at a local animal shelter. You spent some time cleaning kennels until your boss realized that you’re a tech savvy accountant. She quickly reassigned you to help the receptionists at all three locations answer questions that they receive.

1. A family came into the Central branch wanting to know which pet has been there the longest; in other words, they want the pet with the earliest impound date.  
a. The query should return that pet’s name, breed, impound date, and adoption fee. Order by impound date and show only the first result.  
b. The family will adopt the pet if the adoption fee is less than $150. Will they adopt the pet?
2. A young girl called wanting to adopt an adult cat. She is willing to travel to any of the three locations for the cat as long as the cat is up to date on their vaccinations.  
a. The query should return the names, breeds, age in years, vaccination status, and locations of all cats over 2 years old who are fully vaccinated. Order by age in years.  
b. Note: The cat breeds are Domestic Shorthair, Siamese, Scottish Fold, Ragdoll, Bengal, and Maine Co  
c. Which locations, if any, have a fully vaccinated adult cat?
3. Someone walked into the East location looking for a caretaker named Carter, who had told them about a Great Dane named Cooper who was available for adoption. The receptionist didn’t recognize either of those names and asked you to help direct the client to the correct location.  
a. The query should return the location name, address, city, state, and zip code for the location that Carter the caretaker works at, or that Cooper the Great Dane is housed at.

She provides you with the database, the diagram below explaining the relationship between tables, and a can of Sprite. Good luck!

 ![Challenge173_Shelter_Shenanigans_Image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/8/86748f8df314ba7cc7536b1ba0447290140def1d.png)

**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 your solutions, show the SQL query and the resulting table or record.

**Data Files**

- [Challenge174\_Data.xlsx](https://byu.box.com/shared/static/yv6nqs1bpzuwx6hz1d0b06macy2gvrw2)
- [Challenge174\_Data.accdb](https://byu.box.com/shared/static/e42qcildzvyvsrabzl1706v3mpes50ld)
- [Challenge74\_Data\_Dictionary](https://byu.box.com/shared/static/84k62vgdzf6gv9itufh3k1vo1263k397)

> **Suggestions and Hints**
>
> - For the first query, remember to use WHERE to only select animals at the Central location.
> - Using SELECT TOP 1 will give you the first queried result.
> - Pay attention to how you group your WHERE statement for the second query.
> - For the third query, you do not need to join all three tables. Pick whether to join on the Animals table or the Employee table.

**Solution**

[Challenge174\_Solution.accdb](https://byu.box.com/shared/static/pjhenaq7mi853lwd8uw9ftxg11tjbvmr)  
_Solution Video:_ [Challenge 174|SQL – Super Shelter Shenanigans](https://youtu.be/xGErsmgqeZI)

---

<div class="post-metadata">

**Author:** ![Rachel\_Hansen](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/rachel_hansen/32/7245_2.png) [@Rachel\_Hansen](https://www.techhub.training/u/Rachel_Hansen)\
**Post date:** [December 12, 2024, 5:36pm UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436/2 "2024-12-12T17:36:27Z")

</div>

Time to Complete: 10 min,  
Rating: Beginner,  
Comments: This was a fun challenge and I enjoyed it. It was basic, but it got me back into the groove of sql again.  
Solution:

1. Yes they will adopt the pet.
2. Locations Central and East have adult cats who are fully vaccinated.
3. Carter and Cooper the Great Dane are located at the South location.

---

<div class="post-metadata">

**Author:** ![Kambrie\_Earl](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/kambrie_earl/32/7506_2.png) [@Kambrie\_Earl](https://www.techhub.training/u/Kambrie_Earl)\
**Post date:** [February 27, 2025, 4:08am UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436/3 "2025-02-27T04:08:53Z")

</div>

Time to complete: 10 minutes  
1:  
SELECT TOP 1 Name, Breed, Impound\_Date, Adoption\_Fee  
FROM Animals  
ORDER BY Animals.Impound\_Date DESC;  
2:  
SELECT a.Name, a.Breed, a.Age\_Years, a.Vaccination\_Status, l.Location\_Name  
FROM Animals a  
INNER JOIN Location l ON a.LocationID = l.LocationID  
WHERE a.Age\_Years \> 2 AND a.Vaccination\_Status = “Up to date” AND a.Breed IN (“Domestic Shorthair”, “Siamese”, “Scottish Fold”, “Ragdoll”, “Bengal”, “Maine Co”);

---

<div class="post-metadata">

**Author:** ![Josh\_Tanner](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/josh_tanner/32/7436_2.png) [@Josh\_Tanner](https://www.techhub.training/u/Josh_Tanner)\
**Post date:** [March 11, 2025, 11:40pm UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436/4 "2025-03-11T23:40:18Z")

</div>

Time to complete: 10 minutes  
1:  
SELECT TOP 1 Name, Breed, Impound\_Date, Adoption\_Fee  
FROM Animals  
ORDER BY Animals.Impound\_Date DESC;  
2:  
SELECT a.Name, a.Breed, a.Age\_Years, a.Vaccination\_Status, l.Location\_Name  
FROM Animals a  
INNER JOIN Location l ON a.LocationID = l.LocationID  
WHERE a.Age\_Years \> 2 AND a.Vaccination\_Status = “Up to date” AND a.Breed IN (“Domestic Shorthair”, “Siamese”, “Scottish Fold”, “Ragdoll”, “Bengal”, “Maine Co”);  
3)  
SELECT l.Location\_Name, l.address, l.City, l.State, l.Zip\_Code  
FROM (Location AS l INNER JOIN Employee AS e ON l.LocationID = e.LocationID)  
INNER JOIN Animals AS a ON l.LocationID = a.LocationID  
WHERE ( e.F\_Name = “Carter” AND e.Role = “Caretaker”) OR (a.Breed = “Great Dane” AND a.Name = “Cooper”);

---

<div class="post-metadata">

**Author:** ![Jane\_Cai](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/jane_cai/32/7278_2.png) [@Jane\_Cai](https://www.techhub.training/u/Jane_Cai)\
**Post date:** [March 13, 2025, 2:51am UTC](https://www.techhub.training/t/174-sql-super-shelter-shenanigans/2436/5 "2025-03-13T02:51:32Z")

</div>

Time to complete: 15 minutes  
Rating: Beginner  
Solution:  
Q1:  
SELECT TOP 1 a.Name, a.Breed, a.Impound\_Date, a.Adoption\_Fee  
FROM Animals AS a  
ORDER BY a.Impound\_Date;

The family can adopt either pet

Q2:  
SELECT a.Name, a.Breed, a.Age\_Years, a.Vaccination\_Status, l.Location\_Name  
FROM Animals AS a  
INNER JOIN Location AS l ON a.LocationID=l.LocationID  
WHERE a.Age\_Years \> 2 AND a.Vaccination\_Status LIKE “up to date” AND a.Breed IN (“Domestic Shorthair”, “Siamese”, “Scottish Fold”, “Ragdoll”, “Bengal”, “Maine Co”)  
ORDER BY a.Age\_Years;

The East location has adult cats who are fully vaccinated and over 2 years

Q3:  
SELECT l.Location\_Name, l.Address, l.City, l.State, l.Zip\_Code

FROM Location AS l

INNER JOIN Animals AS a ON l.LocationID=a.LocationID

WHERE a.Name LIKE “Cooper” AND a.Breed LIKE “Great Dane”;