173|SQL – Shelter Shenanigans

[Sophie_Dong]

Time to Complete: 10 Mins
Rating: Beginner
Solutions:

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

[Liam_Smith]

SELECT p.Name, p.Age_Years, p.Impound_Date, p.Adoption_Fees
FROM Animals AS p
WHERE p.Breed = “Labrador Retriever”
ORDER BY p.Age_Years, p.Age_Months

SELECT a.Name, a.Breed, a.Age_Years, a.LocationID, a.Vaccination_Status
FROM Animals As a
WHERE (a.LocationID = “Central”) AND ( a.Vaccination_Status = “Not vaccinated” OR “Partically vaccinated”)
ORDER BY a.Inpound_Date DESC;

SELECT e.Role, e.LocationID
FROM Employee AS e
WHERE e.F_Name = “Chloe” AND e.L_Name = “Martin”

[Coleman_Halls]

Q1
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

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

Q3
SELECT Location_Name, Role, F_Name, L_Name, EmployeeID
FROM Employee
INNER JOIN Location ON Employee.LocationID = Location.LocationID
WHERE F_Name = ‘Chloe’ AND L_Name = ‘Martin’

[Anela_Zamarron]

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

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 (a.vaccination_status = ‘Not vaccinated’ or a.vaccination_status = ‘Partially vaccinated’)
and (l.location_name = ‘Central’)
Order by a.impound_date desc;

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

[Mackay_Roberts]

Time Required: 20 minutes
Difficulty: Easy
Q1
SELECT a.breed, 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;
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 locationid = “Central” AND (a.vaccination_status = “Not Vaccinated” OR a.vaccination_status = “Partially Vaccinated”)
ORDER BY impound_date DESC;
Q3
SELECT e.F_Name, e.L_Name, e.LocationID, 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’;

[Kaden_Westerlind]

Time to complete: 10 minutes
Difficulty: Easy
Solutions:

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, l.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 = ‘Not Vaccinated’ OR a.vaccination_status = ‘Partially Vaccinated’)
   ORDER BY impound_date Desc;
3. SELECT e.f_name, e.l_name, e.role, l.location_name
   FROM Employee e
   INNER JOIN location l ON l.locationID = e.LocationID
   WHERE e.f_name = ‘Chloe’ AND e.l_name = ‘Martin’;

[Meg_Nielsen]

Time to Complete: 20 minutes
Difficulty: Beginner

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

2. SELECT Name, Breed, Age_Years, Age_Months, LocationID, Vaccination_Status
   FROM Animals
   WHERE (Vaccination_Status = “Not vaccinated” OR Vaccination_Status = “Partially vaccinated”) AND LocationID = 2
   ORDER BY 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 e.LocationID = l.LocationID
   WHERE F_Name = “Chloe” and L_Name = “Martin”

[Jake_Lewis]

1. Select Names, age_years, age_months, impound_date, adoption_fee
   From animals
   Where breed = “Labrador Retriever”
   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”

[Elizabeth_Bowers]

Time: 20 minutes
Difficult: Easy
My answers

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

Query 2: SELECT a.Name, a.Age_Years, a.LocationID, a.Age_Months, a.Vaccination_Status
FROM Animals a
WHERE (a.LocationID = 2) AND
(a.Vaccination_Status = “Not vaccinated” OR a.Vaccination_Status = “Partially vaccinated”)
ORDER By a.Impound_Date;

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

[Katelyn_Pittman]

Time to Complete:10
Rating: easy
Comments: fun!

Question 1:
SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
Order by Age_Years DESC, Age_Months DESC

Question 2:
SELECT a.Name, a.Breed, a.Age_Years, a.Age_Months, l.LocationID, a.Vacinnation_Status
FROM Animals a
INNER JOIN Location L ON a.PetID=l.LocationID
WHERE a.Vaccination_Status LIKE “Not vaccinated” OR “Partially Vaccinated” AND l.Location_Name LIKE “Central”
ORDER BY a.Impound_Date DESC

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

[Ava_Egan]

Time to Complete: 20 minutes
Difficulty: Intermediate
Solution:

1. SELECT Name, Age_Years, Impound_Date, Adoption_Fee
   FROM Animals
   WHERE Breed = “Labrador Retriever”
   ORDER BY Age_Years DESC, Age_Months DESC;
2. SELECT a.Name, 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;
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”;

[Logan_Christensen]

Time to Complete: 20 minutes
Difficulty: Easy
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 Name, Breed, Age_Years, Age_Months, LocationID, Vaccination_Status
FROM Animals
WHERE LocationID = 2
AND (Vaccination_Status = “Not vaccinated” OR Vaccination_Status = “Partially vaccinated”)
ORDER BY Impound_Date DESC;
3: SELECT e.F_name, e.L_name, l.Location_Name, e.role
FROM Employee AS e
INNER JOIN Location AS l ON e.LocationID = l.LocationID
WHERE F_name = “Chloe” AND L_name = “Martin”;

[Tyler_Brickey]

Time to complete: 15 minues
Challenge Level: Easy
Comments: None

Q1:
SELECT Names, Age_Years, Age_Months, 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.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

Q3.
SELECT Location.LocationID, 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”

[Steven_Ramirez1]

Time to complete: 22 minutes

Difficulty: Medium

1

SELECT Animals.[Name], Animals.[Age_Years], Animals.[Age_Months], Animals.[Impound_Date], Animals.[Adoption_Fee]

FROM Animals

WHERE Animals.[Breed] = ‘Labrador Retriever’

ORDER BY Animals.[Age_Years] DESC;

2

SELECT Animals.[Name], Animals.[Age_Years], Animals.[Age_Months], Animals.[Breed], Animals.[Impound_Date], Animals.[Vaccination_Status]

FROM Animals INNER JOIN Location ON Animals.[LocationID] = Location.[LocationID]

WHERE Location.[Location_Name] = ‘Central’ AND Animals.[Vaccination_Status] <> ‘Up to date’

ORDER BY Animals.[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’;

[Cannon_Harmer]

Time: 25 min
Rating: Moderate
Solution:

1. SELECT Name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
   FROM Animals
   ORDER BY Age_Years, 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 IN (“Not vaccinated”, “Partially vaccinated”) and l.LocationID = 2
   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 LIKE “Chloe” and e.L_Name LIKE “Martin”;

[Thomas_Ricks]

Time 30 min
Level Intermediate
1.SELECT Animals.[Name], Animals.[Age_Years], Animals.[Age_Months], Animals.[Impound_Date], Animals.[Adoption_Fee]
FROM Animals
WHERE Animals.[Breed] = ‘Labrador Retriever’
ORDER BY Animals.[Age_Years] DESC;
2.SELECT Animals.[Name], Animals.[Age_Years], Animals.[Age_Months], Animals.[Breed], Animals.[Impound_Date], Animals.[Vaccination_Status]
FROM Animals INNER JOIN Location ON Animals.[LocationID] = Location.[LocationID]
WHERE Location.[Location_Name] = ‘Central’ AND Animals.[Vaccination_Status] <> ‘Up to date’
ORDER BY Animals.[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’;

[Ava_Neuenschwander]

Time to complete: 10
Difficulty: Beginner
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 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.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”;

[Charles_T_Garrard]

Time to Complete:30 Minutes
Difficulty: Simple

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

[Travis_Walker]

1, SELECT name, Age_Years, Age_Months, Impound_Date, Adoption_Fee
FROM Animals
WHERE Breed = ‘Golden Retriever’
order by Age_Years desc, Age_Months desc
2.SELECT Name, Breed, Age_Months, Age_Years, LocationID, Vaccination_Status
FROM Animals
ORDER BY Impound_Date
3.SELECT F_Name, L_Name, EmployeeID, e.LocationID, Location_Name, Role
From Employee e
Inner JOIN Location l on e.LocationID = l.LocationID
WHERE F_Name = ‘Chloe’ and L_Name = ‘Martin’

[Lily_Flint]

Time to Complete: 15 minutes
difficulty: Easy

SELECT a.name, a.Age_Years, 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;
2.
SELECT a.name, a.breed, 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 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’;