174|SQL – Super Shelter Shenanigans

Challenges
, , , , ,
1

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.

Overview
This challenge is correlated with 173|SQL – Shelter Shenanigans and 171|SQL – Shelter Shenanigans Sequel. 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
Challenge173_Shelter_Shenanigans_Image724×214 5.14 KB

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

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
Solution Video: Challenge 174|SQL – Super Shelter Shenanigans

2

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.
3

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”);

4

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”);

5

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”;