This lecture begins a mini-project to create a digital address book.It’s time to put everything we’ve learned together into a practical project. We are going to build a simple digital address book. This will reinforce the concepts of creating tables, inserting data, and selecting it.
Project Goal
Our goal is to create a single table that can store contact information for people. We want to store more than just a name and phone number. A good address book should also include an email address and a physical address.
Step 1: Designing the Table
First, we need to plan our table. What columns do we need? What data types should they be?
- A unique ID for each contact.
- First Name
- Last Name
- Email Address
- Phone Number
- Physical Address
Based on this, let’s choose our column names and data types:
ContactID:INTEGER PRIMARY KEY– Our unique identifier.FirstName:TEXT– The person’s first name.LastName:TEXT– The person’s last name.Email:TEXT– The email address.Phone:TEXT– The phone number.Address:TEXT– The full physical address.
Step 2: Creating the Table
Now, let’s write the SQL to create our `Contacts` table. This uses the same CREATE TABLE command we learned before, just with more columns.
CREATE TABLE Contacts (
ContactID INTEGER PRIMARY KEY,
FirstName TEXT,
LastName TEXT,
Email TEXT,
Phone TEXT,
Address TEXT
);Run this command in your SQLite tool. You should now have a new, empty table named Contacts.
Step 3: Inserting the Data
Next, let’s add a few contacts to our address book. We will use the INSERT INTO command for each person we want to add.
-- Add Jane Doe
INSERT INTO Contacts (FirstName, LastName, Email, Phone, Address)
VALUES ('Jane', 'Doe', 'jane.doe@email.com', '555-1234', '123 Main St, Anytown');
-- Add John Smith
INSERT INTO Contacts (FirstName, LastName, Email, Phone, Address)
VALUES ('John', 'Smith', 'john.smith@email.com', '555-5678', '456 Oak Ave, Sometown');
-- Add Emily Jones
INSERT INTO Contacts (FirstName, LastName, Email, Phone, Address)
VALUES ('Emily', 'Jones', 'emily.jones@email.com', '555-9999', '789 Pine Ln, Otherville');Run these three commands. We have now successfully populated our address book with three contacts.
In the Next Lecture…
Our data is in the table. In the second and final part of this mini-project, we will practice retrieving this data. We’ll write queries to find specific contacts, get a list of all emails, and more, putting our `SELECT` and `WHERE` skills to the test.
