173|SQL – Shelter Shenanigans

Challenges
, , , ,
61

Time: 15
Level: Easy
Fun challenge
1)
SELECT a.Name, a.Age_Years, a.Age_Months, a.Impound_Date, a.Adoption_Fee
FROM Animals as a
WHERE Breed = ‘Labrador Retriever’
Order by a.Age_Years desc, a.Age_months desc
;
2)
SELECT
a.name,
a.breed,
a.locationid,
a.vaccination_status
FROM Animals AS a
INNER JOIN location AS l
ON a.locationid = l.locationid
WHERE l.location_name = ‘Central’
AND (
a.vaccination_status = ‘Not vaccinated’
OR a.vaccination_status = ‘Partially vaccinated’
)
ORDER BY a.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’;

62

1 - SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_Months DESC;
2 - SELECT a.Name, a.Breed, a.Age_Years, a.Age_Months, loc.LocationID, a.Vaccination_Status
FROM Animals a INNER JOIN Location loc ON a.LocationID = loc.LocationID
WHERE (loc.Location_Name = ‘Central’) AND (a.Vaccination_Status = ‘Not Vaccinated’ OR a.Vaccination_Status = ‘Partially Vaccinated’)
ORDER BY a.Impound_Date;
3 - SELECT loc.Location_Name, e.Role
FROM Location loc
INNER JOIN Employee e ON loc.LocationID = e.LocationID
WHERE e.F_Name = ‘Chloe’ AND e.L_Name = ‘Martin’;

63

Time to Complete: 10 Minutes
Level: Beginner
Notes: Fun

  1. SELECT a.name, a.age_years, a.age_months, a.impound_date, a.adoption_fee
    FROM Animals AS a
    WHERE a.breed = “Labrador Retriever”
    ORDER BY a.age_years DESC;
  2. SELECT a.name, a.breed, a.age_years, a.age_months, a.locationID, a.vaccination_status
    FROM Animals AS a
    INNER JOIN Location AS l ON a.locationID = l.locationID
    WHERE l.location_name = “Central” AND (a.vaccination_status = “Not vaccinated” OR a.vaccination_status = “Partially vaccinated”)
    ORDER BY a.impound_date DESC;
  3. SELECT l.locationID, l.location_name, e.role
    FROM Location AS l
    INNER JOIN Employee AS e ON l.locationID = e.locationID
    WHERE e.f_name = “Chloe” AND e.l_name = “Martin”;
64

Time to complete: 20 min
Rating: Beginner
Solution:
1: SELECT a.Name, a.Age_Years, a.Age_Months, a.Impound_Date, a.Adoption_Fee
FROM Animals a
WHERE Breed = “Labrador Retriever”
ORDER BY a.Age_Years DESC, a.Age_Months DESC;
2: SELECT a.Name, a.Breed, a.Age_Years, A.Age_Months, a.LocationID, a.Vaccination_Status
FROM Animals a INNER JOIN Location l ON a.LocationID = l.LocationID
WHERE (a.Vaccination_Status = “Not vaccinated” OR a.Vaccination_Status = “Partially vaccinated”) AND a.LocationID = 2
ORDER BY a.Impound_Date DESC;
3: SELECT e.F_Name, e.L_Name, e.LocationID, l.Location_Name, e.Role
FROM Employee e INNER JOIN Location l ON l.LocationID = e.LocationID
WHERE e.F_Name = “Chloe” AND e.L_Name = “Martin”;

65

Time to complete: 15 minutes
Difficulty: Beginner
I got slightly different answers but do believe that my queries answered the questions adequately.
1:SELECT a.Name, a.Age_Years, a.Age_Months, a.Impound_Date, a.Adoption_Fee
FROM Animals a
Where Breed = “Labrador Retriever”
ORDER BY a.Age_Years DESC, a.Age_Months desc
2:SELECT a.Name, a.Breed, a.Age_Years, a.Age_Months, a.LocationID, a.Vaccination_Status
From Animals a
INNER JOIN Location l ON l.LocationID=a.LocationID
WHERE l.Location_Name = “Central” AND
a.Vaccination_Status IN (“Partially vaccinated”, “Not vaccinated”)
ORDER BY a.Impound_Date DESC
3. SELECT l.Location_Name, e.Role, e.F_Name, e.L_Name
FROM Employee e
INNER JOIN Location l ON l.LocationID=e.LocationID
WHERE F_Name= “Chloe” and L_Name = “Martin”

66

Time to complete: 15 minutes
Difficulty: Beginner

  1. SELECT a.Name, a.Age_Years, a.Impound_Date, a.Adoption_Fee
    FROM Animals AS a
    WHERE Breed=“Labrador Retriever”
    ORDER BY Age_Years DESC , Age_Months DESC;

  2. SELECT a.Name, a.Breed, a.Age_Months, a.Age_Years, l.LocationID, a.Vaccination_Status
    FROM Animals AS a INNER JOIN Location AS l ON a.LocationID=l.LocationID
    WHERE l.Location_Name=“Central”
    AND a.Vaccination_Status IN (“Not vaccinated”, “Partially vaccinated”)
    ORDER BY a.Impound_Date DESC;

  3. SELECT e.F_Name, e.L_Name, e.Role, l.Location_Name
    FROM Employee AS e INNER JOIN Location AS l ON l.LocationID=e.LocationID
    WHERE e.F_Name = “Chloe” AND e.L_Name=“Martin”;

67

SELECT A.Name AS Name , A.Age_Years +A.Age_Months AS Age , A.Impound_Date, A.Adoption_Fee AS adoption_fees
FROM (Animals AS A
INNER JOIN Location AS L ON L.LocationID=A.LocationID)
INNER JOIN Employee AS E ON E.LocationID=L.LocationID
WHERE A.Breed=“Labrador Retriever”
GROUP BY A.Age_Years ,A.Age_Months ,A.Impound_Date,A.Name,A.Adoption_Fee
ORDER BY A.Age_Years ,A.Age_Months DESC

SELECT A.Name AS Name , A.Age_Years +A.Age_Months AS Age , A.Impound_Date AS ImpoundDate,A.Vaccination_Status AS VaccinationStatus ,
A.Breed AS Breed , L.Location_Name AS LocationName
FROM (Animals AS A
INNER JOIN Location AS L ON L.LocationID=A.LocationID)
WHERE L.LocationID =2
GROUP BY A.Name, A.Age_Years ,A.Age_Months ,A.Impound_Date, A.Vaccination_Status, A.Breed,L.Location_Name ,L.LocationID
HAVING A.Vaccination_Status =“Partially vaccinated” OR “Not vaccinated”
ORDER BY A.Impound_Date DESC
;

SELECT E.F_Name AS Name , L.Location_Name AS Location ,E.Role AS Role
FROM (Animals AS A
INNER JOIN Location AS L ON L.LocationID=A.LocationID)
INNER JOIN Employee AS E ON E.LocationID=L.LocationID
WHERE E.F_Name=“Chloe”

;

68

Time to complete: 15 minutes
Difficulty: Beginner
1.
SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed LIKE ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_Months DESC;

SELECT Name, Breed, Age_Years, Age_Months, Animals.LocationID, Vaccination_Status
FROM Animals INNER JOIN Location ON Animals.LocationID = Location.LocationID
WHERE (Location_Name = ‘Central’) AND (Vaccination_Status LIKE ‘Not vaccinated’ OR Vaccination_Status LIKE ‘Partially vaccinated’)
ORDER BY Impound_Date DESC;

SELECT l.Location_Name, e.Role
FROM Employee e
INNER JOIN Location l ON e.LocationID = l.LocationID
WHERE F_Name LIKE ‘Chloe’ AND L_Name LIKE ‘Martin’;

69

Time To Complete: 25
Difficulty: Beginner
Answers:
SELECT p.Name, p.Age_Years, p.Age_Months, p.Impound_Date, p.Adoption_Fee
FROM Animals AS p
WHERE Breed = “Labrador Retriever”
ORDER BY p.Age_Years DESC, p.Age_Months DESC;
2)
SELECT a.name, a.breed, a.age_years, a.age_months, l.location_name, a.vaccination_status
FROM Animals AS a INNER JOIN Location AS l ON a.locationID = l.locationID
WHERE (a.vaccination_status = “Not vaccinated” OR a.vaccination_status = “Partially vaccinated”) AND l.location_name = “Central”
ORDER BY a.impound_date DESC;
3)
SELECT l.Location_Name, e.Role
FROM Employee AS e
INNER JOIN Location AS l ON e.LocationID = l.LocationID
WHERE e.f_name = “Chloe” AND e.l_name = “Martin”;

70

Time to complete: 10 minutes
Rating: Beginner
Solution:
Q1
SELECT a.Name, a.Age_Years, a.Impound_Date, a.Adoption_Fee

FROM Animals AS a

WHERE a.Breed LIKE “Labrador Retriever”

ORDER BY a.Age_Years DESC, a.Age_Months DESC;

Q2
SELECT a.Name, a.Breed, a.Age_Years, a.LocationID, a.Vaccination_Status

FROM Animals AS a

WHERE a.Vaccination_Status LIKE “Not vaccinated” OR a.Vaccination_Status LIKE “Partially vaccinated”

ORDER BY a.Impound_Date DESC ;

Q3
SELECT e.F_Name, e.L_Name, l.Location_Name, e.Role

FROM Employee AS e

INNER JOIN Location AS l ON l.LocationID=e.LocationID

WHERE e.F_Name LIKE “Chloe” AND e.L_Name LIKE “Martin”;