BYU Student Author: @Millie_K_B
Reviewers: @Marta_Ellsworth, @James_Gerstner
Estimated Time to Solve: 25 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
This challenge is correlated with 174|SQL – Super Shelter Shenanigans and 171|SQL – Shelter Shenanigans Sequel. It is not necessary to complete either challenge before completing this one.
While waiting to hear back from your dream accounting job, you decide to volunteer at a local animal shelter. When the manager at your location finds out that you’re familiar with SQL, she decides to put you to work using the shelter’s database system to answer some questions:
- Someone walked in asking for a list of the available Labrador Retrievers at all three locations. Run a query that will return all adoptable Labrador Retrievers with their names, ages, impound dates, and adoption fees. Order by age in years, then age in months, descending.
- A regulator wants to know if the vet techs at the Central location are keeping up with vaccinations. Run a query that will return the names, breeds, ages, location IDs, and vaccination statuses of all pets with the vaccination status “Not vaccinated” or “Partially vaccinated” at the Central location. Sort by impound date, descending.
- An employee named Chloe Martin has put in their two weeks’ notice. Which location will need to hire a replacement, and for what role?
She provides you with the database, the below diagram explaining the relationship between tables, and a Dr. Pepper. 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 your solutions, show the SQL query and the resulting table or record.
Data Files
Suggestions and Hints
For query 2, you need to perform an INNER JOIN between the Animals table and the Location table. Join the two tables on LocationID, then use the WHERE clause to specify records where Location_Name = “Central.”
Likewise, in query 3, join the Location and Employee tables on LocationID, then SELECT Role and Location_Name WHERE F_Name = “Chloe” AND L_Name = “Martin.”
Solution
SELECT Names, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_Months DESC;
SELECT animals.name, animals.breed, animals.locationid, animals.vaccination_status
FROM Animals
INNER JOIN location ON animals.locationid = location.locationid
WHERE (location.location_name = ‘Central’) AND (animals.vaccination_status = ‘Not Vaccinated’ OR animals.vaccination_status = ‘Partially Vaccinated’)
ORDER BY Impound_date DESC;
SELECT Location.location_name, Employee.role
FROM Employee
INNER JOIN Location ON Employee.locationid = Location.locationid
WHERE Employee.f_name = ‘Chloe’ AND Employee.l_name = ‘Martin’;
“Time to complete: 15 mintues”
Difficulty: Beginner
This was great but it was hard to navigate the website and understand where to submit everything
SELECT Names, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_Months DESC;
SELECT animals.name, animals.breed, animals.locationid, animals.vaccination_status
FROM Animals
INNER JOIN location ON animals.locationid = location.locationid
WHERE (location.location_name = ‘Central’) AND (animals.vaccination_status = ‘Not Vaccinated’ OR animals.vaccination_status = ‘Partially Vaccinated’)
ORDER BY Impound_date DESC;
SELECT Location.location_name, Employee.role
FROM Employee
INNER JOIN Location ON Employee.locationid = Location.locationid
WHERE Employee.f_name = ‘Chloe’ AND Employee.l_name = ‘Martin’;
Time to complete: 25 minutes
difficulty beginner
1 -
SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’ AND Adoption_Status = ‘Available’
ORDER BY Age_Years DESC, Age_Months DESC;
2 -
SELECT animals.Name, animals.Breed, animals.LocationID, animals.Vaccination_Status
FROM Animals
INNER JOIN Location ON animals.LocationID = Location.LocationID
WHERE (Location.Location_Name = ‘Central’) AND (animals.Vaccination_Status = ‘Not Vaccinated’ OR animals.Vaccination_Status = ‘Partially Vaccinated’)
ORDER BY Impound_Date DESC;
3 -
SELECT Location.Location_Name, Employee.Role
FROM Employee
INNER JOIN Location ON Employee.LocationID = Location.LocationID
WHERE Employee.F_Name = ‘Chloe’ AND Employee.L_Name = ‘Martin’;
1 Like
Start with:
SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’ AND Adoption_Status = ‘Available’
ORDER BY Age_Years DESC, Age_Months DESC;
Then:
SELECT animals.Name, animals.Breed, animals.LocationID, animals.Vaccination_Status
FROM Animals
INNER JOIN Location ON animals.LocationID = Location.LocationID
WHERE (Location.Location_Name = ‘Central’) AND (animals.Vaccination_Status = ‘Not Vaccinated’ OR animals.Vaccination_Status = ‘Partially Vaccinated’)
ORDER BY Impound_Date DESC;
Finally:
SELECT Location.Location_Name, Employee.Role
FROM Employee
INNER JOIN Location ON Employee.LocationID = Location.LocationID
WHERE Employee.F_Name = ‘Chloe’ AND Employee.L_Name = ‘Martin’;
