For an instructor lead, in-depth look at learning SQL click below.
veloping a Pet Adoption Management System Using SQL
Introduction
The SQL language offers a wide range of opportunities for managing databases. In this post, we’ll create a simple pet adoption management system using SQL. This will involve creating tables, inserting data, and querying the data. Let’s get started.
Creating The Tables
We will require three main tables: Pets, Adopters, and Adoptions. The Pets table will store information about the pets while the Adopters table will store information about the individuals or families adopting the pets. The Adoptions table will store records of all adoptions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--Pet Table CREATE TABLE Pets ( PetID INT PRIMARY KEY, Name VARCHAR(100), Species VARCHAR(100), Breed VARCHAR(100), Age INT ); --Adopter Table CREATE TABLE Adopters( AdopterID INT PRIMARY KEY, FullName VARCHAR(100), PhoneNumber VARCHAR(15), Email VARCHAR(100) ); --Adoption Table CREATE TABLE Adoptions ( AdoptionID INT PRIMARY KEY, PetID INT FOREIGN KEY REFERENCES Pets(PetID), AdopterID INT FOREIGN KEY REFERENCES Adopters(AdopterID), AdoptionDate DATE ); |
Populating the Tables
With our tables created, we can now add some data. Here’s how we can insert data into these tables:
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO Pets VALUES (1, 'Buddy', 'Dog', 'Beagle', 4),(2, 'Luna', 'Cat', 'Siamese', 3); INSERT INTO Adopters VALUES (1, 'John Doe', '123-456-7890', <a href="mailto:'john.doe@email.com'" >'john.doe@email.com'</a>), (2, 'Jane Doe', '987-654-3210', <a href="mailto:'jane.doe@email.com'" >'jane.doe@email.com'</a>); INSERT INTO Adoptions VALUES (1, 1, 1, GETDATE()),(2, 2, 2, GETDATE()); |
Creating Queries
Now that our database is populated, let’s run some queries to get useful information about our pet adoptions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--Get information on all the pets that were adopted SELECT Pets.Name, Adopters.FullName, Adoptions.AdoptionDate FROM Adoptions JOIN Pets ON Adoptions.PetID = Pets.PetID JOIN Adopters ON Adoptions.AdopterID = Adopters.AdopterID; --Get all adopters who have adopted more than one pet SELECT Adopters.FullName FROM Adopters JOIN Adoptions ON Adopters.AdopterID = Adoptions.AdopterID GROUP BY Adopters.FullName HAVING COUNT(Adoptions.AdopterID) > 1; |
Conclusion
Using SQL for a Pet Adoption Management System presents a straightforward and efficient way to manage pet-adopter relationships. By using SQL’s powerful querying and data management capabilities, we can easily keep track of the adoption process. To build on this, one could add additional tables or add more details to the existing ones, and create more complex queries to extract useful insights from the data.