Practice and reinforce the concepts from Lesson 1
Transform your understanding of databases from theory to practice! In this hands-on activity, you'll create a personal contacts database that organizes information about your friends, family, or professional connections. Experience firsthand how databases make storing and retrieving information efficient and powerful.
A personal contacts database that includes:
Let's create a table to store contact information. Copy and paste this SQL code:
-- Create the contacts table with all necessary fields
CREATE TABLE contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT,
nickname TEXT,
phone_number TEXT,
email TEXT,
birthday DATE,
category TEXT CHECK(category IN ('Family', 'Friend', 'Work', 'Other')),
favorite_color TEXT,
notes TEXT,
date_added DATE DEFAULT CURRENT_DATE
);
Now let's add some real contacts! Start with 3-5 people you know:
-- Example: Add your contacts (replace with real information)
INSERT INTO contacts (first_name, last_name, nickname, phone_number, email, birthday, category, favorite_color, notes)
VALUES
('Sarah', 'Johnson', 'SJ', '555-0101', 'sarah.j@email.com', '1995-03-15', 'Friend', 'Blue', 'Met in college, loves hiking'),
('Mike', 'Chen', 'Mikey', '555-0102', 'mchen@email.com', '1992-07-22', 'Work', 'Green', 'Great programmer, coffee enthusiast'),
('Emma', 'Davis', 'Em', '555-0103', 'emma.davis@email.com', '1998-11-30', 'Family', 'Purple', 'Cousin, studying medicine');
-- Add at least 2 more of your own contacts below:
-- INSERT INTO contacts (...) VALUES (...);
-- See all your contacts
SELECT * FROM contacts;
-- Find all family members
SELECT first_name, last_name, phone_number
FROM contacts
WHERE category = 'Family';
-- Find all friends
SELECT first_name, nickname, email
FROM contacts
WHERE category = 'Friend';
-- Find contacts with birthdays this month
SELECT first_name, nickname, birthday
FROM contacts
WHERE strftime('%m', birthday) = strftime('%m', 'now');
-- Find upcoming birthdays (next 30 days)
SELECT first_name, birthday,
CAST((julianday(strftime('%Y', 'now') || '-' || strftime('%m-%d', birthday)) - julianday('now')) AS INTEGER) as days_until
FROM contacts
WHERE days_until BETWEEN 0 AND 30
ORDER BY days_until;
-- Search by name (partial match)
SELECT * FROM contacts
WHERE first_name LIKE '%ar%' OR last_name LIKE '%ar%';
-- Find contacts by favorite color
SELECT first_name, favorite_color
FROM contacts
WHERE favorite_color = 'Blue';
-- Update a phone number
UPDATE contacts
SET phone_number = '555-9999'
WHERE first_name = 'Sarah' AND last_name = 'Johnson';
-- Add a note to a contact
UPDATE contacts
SET notes = notes || ' | Allergic to peanuts'
WHERE id = 2;
-- Count contacts by category
SELECT category, COUNT(*) as total
FROM contacts
GROUP BY category;
-- Find contacts added in the last week
SELECT first_name, date_added
FROM contacts
WHERE date_added >= date('now', '-7 days');
Write a query to show:
-- Your solution here:
Create a query that finds all contacts who:
-- Your solution here:
Add these new fields to your contacts table:
last_contacted - Date you last spoke with themsocial_media - Their social media handleaddress - Their home address-- Your solution here:
Efficiency: How is this database better than keeping contacts in a text file or spreadsheet?
Schema Design: What other fields would make your contacts database more useful?
Real-World Connection: Think of an app you use daily. What database tables might it have?
Create a second table to track how your contacts know each other:
CREATE TABLE relationships (
id INTEGER PRIMARY KEY,
contact1_id INTEGER,
contact2_id INTEGER,
relationship_type TEXT,
FOREIGN KEY (contact1_id) REFERENCES contacts(id),
FOREIGN KEY (contact2_id) REFERENCES contacts(id)
);
Add functionality to track events with your contacts:
CREATE TABLE events (
id INTEGER PRIMARY KEY,
event_name TEXT,
event_date DATE,
location TEXT
);
CREATE TABLE event_attendees (
event_id INTEGER,
contact_id INTEGER,
FOREIGN KEY (event_id) REFERENCES events(id),
FOREIGN KEY (contact_id) REFERENCES contacts(id)
);
Congratulations! You've built a functional database application. You've learned:
This foundation will help you understand how every app and website you use manages its data!