For an instructor lead, in-depth look at learning SQL click below.
In today’s connected world, the need for organizations to keep a comprehensive record of donations and contributors has exponentially increased. Therefore, a reliable and efficient system becomes indispensable. SQL, as a powerful language for managing and manipulating databases, provides a solid solution to this task. Here we’ll guide you through the process of designing a charity donation tracking system using SQL.
Database Structure
Before we dive into code examples, let’s define the structure of our database. Our system will primarily consist of two tables. The ‘Donor’ table will store details about each donor such as name and contact information, and the ‘Donation’ table will record details about each donation such as the donation amount, type, donation date and the donor who made the donation. Here is how we can set up these tables in SQL:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Donor( DonorID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(50), ContactNumber VARCHAR(15) ); |
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Donation( DonationID INT PRIMARY KEY, DonorID INT FOREIGN KEY REFERENCES Donor(DonorID), Amount DECIMAL(10,2), DonationType VARCHAR(50), DonationDate DATE ); |
Entering and Retrieving Data
After defining the structure of the database, we can now input some data into the tables. Suppose we want to insert a new donor’s data and a new donation record:
|
1 2 3 4 5 6 7 |
INSERT INTO Donor (DonorID, FirstName, LastName, Email, ContactNumber) VALUES (1, 'John', 'Doe', <a href="mailto:'john.doe@example.com'" >'john.doe@example.com'</a>, '1234567890'); INSERT INTO Donation (DonationID, DonorID, Amount, DonationType, DonationDate) VALUES (1, 1, 500.00, 'Monetary', '2019-10-01'); |
To retrieve data, we use the SELECT statement. If we want to get a list of all donors:
|
1 2 3 |
SELECT * FROM Donor; |
If we want to get a list of all donations:
|
1 2 3 |
SELECT * FROM Donation; |
Linking Donors and Donations
More often, we might want to track which donor made which donation. We do so by joining the Donor and Donation tables using the common DonorID field. Here is how we can achieve this:
|
1 2 3 4 5 |
SELECT Donor.FirstName, Donor.LastName, Donation.Amount, Donation.DonationType, Donation.DonationDate FROM Donor JOIN Donation ON Donor.DonorID = Donation.DonorID; |
Conclusion
SQL provides a broad range of capabilities for creating detailed and efficient tracking systems. With the addition of more tables and relationships, you can expand this basic system to cater to more specific needs of your charity organization. As you delve deeper into SQL, you’ll discover more advanced techniques such as creating views, stored procedures, and triggers that will further enhance the capabilities of your tracking system.