For an instructor lead, in-depth look at learning SQL click below.
With the aid of the SQL language, we can create a system to manage and track the registration and attendance of volunteers at various events. In this blog post, we will illustrate how to create such a system using SQL code.
Step 1: Designing the Database
To begin with, we will need to design and create our database. This will hold tables related to events, volunteers, registration, and attendance. Here are the SQL statements to create these tables:
|
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 28 29 30 |
CREATE TABLE Events( event_id INT PRIMARY KEY, event_name VARCHAR(100), event_date DATE ); CREATE TABLE Volunteers( volunteer_id INT PRIMARY KEY, volunteer_name VARCHAR(100), volunteer_email VARCHAR(100) ); CREATE TABLE Registrations( registration_id INT PRIMARY KEY, event_id INT, volunteer_id INT, date_registered DATE, FOREIGN KEY (event_id) REFERENCES Events(event_id), FOREIGN KEY (volunteer_id) REFERENCES Volunteers(volunteer_id) ); CREATE TABLE Attendance( attendance_id INT PRIMARY KEY, registration_id INT, attended BOOLEAN, date_attended DATE, FOREIGN KEY (registration_id) REFERENCES Registrations(registration_id) ); |
Step 2: Adding Data to the Tables
We will now populate our tables with sample data. The following SQL statements insert records into the previously created tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
INSERT INTO Events(event_id, event_name, event_date) VALUES (1,'Clean-Up Drive','2020-05-30'), (2,'Tree Planting','2020-06-15'), (3,'Charity Run','2020-07-01'); INSERT INTO Volunteers(volunteer_id, volunteer_name, volunteer_email) VALUES (1,'John Doe',<a href="mailto:'John.Doe@example.com'" >'John.Doe@example.com'</a>), (2,'Jane Smith', <a href="mailto:'Jane.Smith@example.com'" >'Jane.Smith@example.com'</a>), (3,'Bob Martin',<a href="mailto:'Bob.Martin@example.com'" >'Bob.Martin@example.com'</a>); INSERT INTO Registrations(registration_id, event_id, volunteer_id, date_registered) VALUES (1,1,1,'2020-05-15'), (2,2,1,'2020-05-25'), (3,2,2,'2020-05-26'); INSERT INTO Attendance(attendance_id, registration_id, attended, date_attended) VALUES (1,1,true,'2020-05-30'), (2,2,true,'2020-06-15'), (3,3,false,'2020-06-15'); |
Step 3: Querying the Database
The final step is to use SQL SELECT statements to extract and analyze our data. Here’s a simple example:
|
1 2 3 4 5 6 7 |
SELECT e.event_name, v.volunteer_name, a.attended FROM Events e JOIN Registrations r ON e.event_id = r.event_id JOIN Volunteers v ON r.volunteer_id = v.volunteer_id JOIN Attendance a ON a.registration_id = r.registration_id; |
This query returns a list of events, together with the names of registered volunteers and their attendance status. It exemplifies the kind of insights that can be captured using SQL.
Conclusion
This example only scratches the surface of what SQL can do. By mastering SQL, you can create more sophisticated systems to handle even more complex data management tasks.