173|SQL – Shelter Shenanigans

[Madelyn_Davis]

Time to complete:15min

Rating: Easy

Q1

SELECT Name, Age_Years, Impound_Date, Adoption_Fee

FROM Animals

WHERE Breed = ‘Labrador Retriever’

ORDER BY Age_Years DESC, Age_Months DESC

Q2

SELECT animals.Name, animals.Breed, animals.Age_Years, 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 animals.Impound_Date DESC

Q3

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

[Adam_Pendleton]

Time to Complete:
Difficulty:
Solution:

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 Age_Years DESC, Age_Months DESC;
2. SELECT A.Name, A.Breed, A.Age_Years, A.Age_Months, A.LocationID, A.Vaccination_Status
   FROM Animals AS A RIGHT JOIN Location AS L ON A.LocatoinID = 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”;

[Benton_Worthen]

Time: 15 min
Difficulty: Easy

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_Months, a.Age_Years, 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”;

[Seager_Bogden]

Time to complete: 15 minutes
rating: easy

SELECT name, age_years, age_months, impound_date, adoption_fee
FROM animals
WHERE breed = “Labrador Retriever”
ORDER BY age_years DESC , age_months DESC;

SELECT breed, age_years, age_months, l.locationid, vaccination_status, location_name
from animals
Inner Join location as l on l.locationid = animals.locationid
where vaccination_status = “Not vaccinated” or “Partially vaccinated” and location_name = “Central”

order by impound_date desc

select *
from employee
where f_name = “Chloe”

[braydenlwhite]

Time to complete: 15 minutes
Rating: Beginner
Solutions:

1. SELECT Animals.Name, Animals.Age_Years, Animals.Age_Months, Animals.Impound_Date, Animals.Adoption_Fee
   FROM Animals INNER JOIN Location on Animals.LocationID = Location.LocationID
   WHERE Animals.Breed = “Labrador Retriever”
   ORDER BY Animals.Age_Years DESC, Animals.Age_Months DESC

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

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

[Aaron_Larson]

Time: 15
Rating: beginner
Solution:

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, a.Vaccination_Status, l.LocationID
   FROM Animals a
   INNER JOIN Location 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.Location_Name, e.Role
   FROM Location l
   INNER JOIN Employee e ON l.LocationID=e.LocationID
   WHERE e.F_Name=“Chloe” AND e.L_Name=“Martin”;

[Caleb_Spiers]

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

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

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

[Austin_Parise]

Time to complete: 15
Rating: Medium

1. SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_fee
   FROM Animals
   WHERE Breed = “Labrador Retriever”
   ORDER BY 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 i ON a.locationID = i.locationID
   WHERE i.location_name = “Central” AND (a.vaccination_status = “Not vaccinated” OR a.vaccination_status = “Partially Vaccinated”)
   ORDER BY a.impund_date DESC;

3. SELECT i.locationID, i.location_name, e.role
   FROM Location AS i
   INNER JOIN Employee AS e ON i.locationID = e.locationID
   WHERE e.f_name= “Chloe” AND e.l_name = “Martin”;

[Landon_Wells]

SELECT Breed, Age_Years, Age_Months, Name, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_Months DESC;

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

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

[Colby_Field]

Time to Complete: 20
Rating: Intermediate
Solutions:

1. SELECT a.Name, a.Age_Years, a.Age_Months, a.Impound_Date, a.Adoption_Fee
   FROM Animals AS a
   WHERE a.Breed IN(“Labrador”, “Retreiver”)
   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 e.Role, l.Location_Name
   FROM Employee AS e INNER JOIN Location AS l ON e.LocationID = l.LocationID
   WHERE e.F_Name = “Chloe” AND e.L_Name = “Martin”;

[Johnathan_Mulvey]

Time to Complete: 15
Rating: beginner
At first was a little challenging with the way the chart was provided. Had to take a step back and slow down.

1- SELECT Animals.Name, Animals.Age_Years, Animals.Age_Months, Animals.Impount_Date, Animals.Adoption_Fee
FROM Animals
WHERE Breed = ‘Labrador Retreiver’
ORDER BY Animals.Age_Years DESC, Animal.Age_Months DESC;

2- SELECT Animals.Name, Animals.Breed, Animals.Age_Years, Animals.Age_Months, Animal.LocationID, Animals.Vaccination_status
FROM Animals
INNER JOIN Location ON Animals.LocationID = Location.LocationID
WHERE Animals.Vaccination_Status IN (‘Not vaccinated’, ‘Partially vaccinated’) AND Location.LocationID = ‘Central’
ORDER BY Animals.Impound_Date DESC;

3-SELECT Location.Location_Name, Employee.Role
FROM Location
INNER JOIN Employee ON Location.LocationID = Employee.LocationID
WHERE Employee.F_Name = ‘Chloe Martin’

[Aliza_Amundsen]

Time to complete: 20 min
Rating: Beginner
This was a great project!

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, a.Age_Months DESC;

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 a.Vaccination_Status=“Not Vaccinated” OR a.Vaccination_Status=“Partially vaccinated”
ORDER BY a.Impound_Date DESC
HAVING l.Location_Name = “Central” ;

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

[Avalon_Porter]

Time: 20 min
Challenge: Easy
I enjoyed this.

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, a.Age_Months DESC;

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;

[Will_Snow]

Time: 15min
DIfficulty: easy
Q1

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;
   Q2
   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;
   Q3
   SELECT l.Location_Name, e.Role
   FROM Location AS l
   INNER JOIN Employee AS e ON l.LocationID = e.EmployeeID
   WHERE e.F_Name = “Chloe” AND e.L_Name = “Martin” ;

[Malachi_Mazey]

Time to Complete: 20 Mins
Rating: easy
Solution:

1.SELECT a.name, a.age_years, a.age_months, a.impound_date, a.adoption_fee
FROM Animals AS a INNER JOIN Location AS l on a.locationID = l.locationID
WHERE a.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, 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 Location AS l INNER JOIN Employee AS e ON l.locationID = e.locationID
WHERE e.F_name = “Chloe” AND e.L_name = “Martin”;

[Makayla_Larkin]

Time to complete: 10 minutes
Difficulty: easy
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, a.locationid, a.vaccination_status
FROM animals a
INNER JOIN location i ON a.locationid=i.locationid
WHERE (a.vaccination_status=“not vaccinated” OR a.vaccination_status=“partially vaccinated”)
AND l.location_name = “central”
ORDER BY impound_date DESC;
3.
SELECT l.locationid, l.location_name, e.role
FROM location l
INNER JOIN employee e ON l.locationid=e.locationid
WHERE e.f_name = “Chloe” AND e.l_name = “Martin”;

[George_Earnest]

Time to complete: 10 minutes
Difficulty: Easy

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, 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 l.location_name = “central”
   ORDER BY impound_date DESC;

3. SELECT l.locationid, l.location_name, e.role
   FROM location l
   INNER JOIN employee e ON l.locationID=e.locationID
   WHERE e.F_name = “Chloe” AND e.L_name = “Martin”;

[Bret_Thomas]

Time to Complete: 25 Mins
Rating: Beginner

1.SELECT a.name, a.age_years, a.age_months, a.impound_date, a.adoption_fee
FROM Animals AS a INNER JOIN Location AS l on a.locationID = l.locationID
WHERE a.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, 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 Location AS l INNER JOIN Employee AS e ON l.locationID = e.locationID
WHERE e.F_name = “Chloe” AND e.L_name = “Martin”;

[David_Fox]

Time to complete: 15 min
Difficulty: Intermediate
Solution:

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

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

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

[Adlee_Baird]

Time: 15
Level: easy
Solution: see below

Q1:
SELECT Name, Age_years, Impound_Date, Adoption_fees
FROM Animals
WHERE Breed = ‘Labrador Retriever’
ORDER BY Age_Years DESC, Age_months DESC;

Q2:
SELECT a.Name, a.Breed, 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 = ‘Not Vaccinated’ OR a.Vaccination_Status = ‘Partially vaccinated’)
ORDER BY a.Impound_date DESC;

Q3:
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’;