Database Exploration - Activity 01

Welcome to your first database activity! This template helps you learn the fundamentals of databases, SQL queries, and data management using a realistic contact manager system.

🎯 Learning Objectives

By completing this activity, you will:

• Understand what databases are and why they're essential
• Create your first database schema with tables and constraints
• Master basic SQL operations (CREATE, INSERT, SELECT, UPDATE, DELETE)
• Write queries to search and analyze data
• Work with multiple database systems (SQLite, MySQL, PostgreSQL)

🚀 Getting Started (See Results in 30 Seconds!)

IMPORTANT: This template includes WORKING CODE! You can see results immediately:

Option One: SQLite Browser (Easiest - No Setup Required)

1. Download SQLite Browser (if you don't have it):

   • Visit: https://sqlitebrowser.org/
   • Download for your operating system
   • Install (takes 2 minutes)

2. Open the database files:

   • Launch DB Browser for SQLite
   • Click "New Database" -> Save as contacts.db
   • Click "Execute SQL" tab
   • Open sqlite/schema.sql -> Click "Execute" ▶️
   • Open sample-data.sql -> Click "Execute" ▶️
   • Click "Browse Data" tab -> See 20 contacts immediately!

3. Run the queries:

   • Open sqlite/queries.sql
   • Copy Query 1 (View All Contacts)
   • Paste in "Execute SQL" tab -> Click "Execute" ▶️
   • You just ran your first database query! 🎉

Option 2: Docker (One Command Setup)

# Make sure you're in the activity folder, then:
docker-compose up -d

# Access MySQL:
docker exec -it w4-mysql mysql -u root -plearning123 contacts_db

# Access PostgreSQL:
docker exec -it w4-postgres psql -U postgres contacts_db

Option 3: Online (Supabase - Cloud PostgreSQL)

1. Visit https://supabase.com and create free account
2. Create new project (takes 2 minutes)
3. Open SQL Editor in dashboard
4. Copy/paste schema.sql and sample-data.sql
5. Run queries immediately!

🎯 What's Already Working

70% of the code is implemented for you:

• ✅ Complete database schema with 14 fields (fully working)
• ✅ Sample data: 20 realistic contacts across 4 categories (loaded)
• ✅ Query 1: View all contacts (working)
• ✅ Query 2: Find favorite contacts (working)
• ✅ Query 3: Search by category (working)
• ✅ Query 4: Search by company (working)
• ✅ Query 5: Partial name search with LIKE (working)
• ✅ Query 6: Find contacts needing follow-up (working)
• ✅ Query 7: Count contacts by category (working)
• ⚠️ Query 8: Find complete contact information (TODO for you)
• ⚠️ Query 9: Find duplicate company contacts (TODO for you)
• ⚠️ Query 10: Advanced contact analysis (TODO for you)

📝 Your Learning Tasks

1. First, test the 7 working queries to understand the pattern
2. Then complete the 3 TODO queries following the examples
3. Finally, try the bonus challenges to extend your skills

📋 Tasks to Complete

TODO 1: Find Contacts with Complete Information (Medium)

Location: sqlite/queries.sql line 132 Your Task: Write a query to find contacts that have BOTH email AND phone

Success Criteria:

• Must show: full_name (first + last), email, phone, category
• Only include contacts where BOTH email AND phone are NOT NULL
• Order by category, then last name
• Should return 15-17 contacts

Hints:

• Use WHERE with AND operator
• Check for NOT NULL using: email IS NOT NULL
• Combine names: first_name || ' ' || last_name AS full_name

Why This Matters: In real applications, you need contacts with complete information for effective communication.

TODO 2: Find Duplicate Company Contacts (Hard)

Location: sqlite/queries.sql line 154 Your Task: Find companies where you have 2+ contacts

Success Criteria:

• Must show: company name and count of contacts
• Only include companies with 2 or more contacts
• Order by contact count (highest first)
• Should return 2 companies (Google with 3, others with 2)

Hints:

• Use GROUP BY company
• Use HAVING to filter groups: HAVING COUNT(*) >= 2
• HAVING is like WHERE but for grouped data

Why This Matters: Identifying multiple contacts at the same organization helps with networking and relationship management.

TODO 3: Advanced Contact Analysis (Challenge)

Location: sqlite/queries.sql line 174 Your Task: Create a comprehensive contact statistics query

Success Criteria:

• Calculate total contacts
• Calculate percentage with email (should be ~90%)
• Calculate percentage marked as favorite (should be ~50%)
• Use aggregate functions: COUNT(), ROUND()
• Display percentages with 1 decimal place

Hints:

• Use SELECT without FROM for calculations
• Calculate percentage: ROUND((count_subset * 100.0) / count_total, 1)
• Cast to float: Use 100.0 instead of 100 for accurate division

Expected Output: Single row like:

total_contacts | email_percentage | favorite_percentage
20             | 90.0             | 50.0

Why This Matters: Data analysis helps you understand your network health and prioritize relationship building.

🛠 Database Reference

Schema Structure (14 Fields)

Sample Data Summary

• Total Contacts: 20
• Family: 4 contacts (parents, siblings, cousins)
• Work: 8 contacts (Google, Microsoft, Amazon, Meta, etc.)
• Friends: 5 contacts (college friends, hobby groups)
• Clients: 3 contacts (business relationships)
• Favorites: 10 contacts (marked important)

Key SQL Commands Used

-- View data
SELECT column1, column2 FROM table WHERE condition;

-- Filter results
WHERE category = 'Work'
WHERE email IS NOT NULL

-- Sort results
ORDER BY company, last_name;

-- Group and count
GROUP BY category
HAVING COUNT(*) >= 2

-- Combine columns
first_name || ' ' || last_name AS full_name

🧪 Testing Your Work

Manual Testing Checklist

• All 7 working queries execute without errors
• TODO 8 returns 15-17 contacts with complete info
• TODO 9 returns 2-3 companies with multiple contacts
• TODO 10 returns single row with percentages
• Bonus queries (if attempted) produce reasonable results
• No syntax errors in your SQL code

Debugging Tips

1. "No such table" error:

   • Make sure you ran schema.sql first
   • Check you're in the correct database

2. "No such column" error:

   • Check column name spelling (case-sensitive!)
   • Use .schema contacts to see all columns

3. Query returns 0 rows:

   • Verify sample data loaded: SELECT COUNT(*) FROM contacts;
   • Check WHERE conditions aren't too restrictive

4. Syntax error:

   • Check for missing commas, semicolons
   • Ensure parentheses are balanced
   • Verify quotes are closed

Expected Results

🎓 Extension Challenges

Ready for more? Try these bonus features:

Beginner Extensions

• Find Gmail users: Write query to find contacts with @gmail.com
• Recent contacts: Show contacts added in last 7 days
• Missing data: Find contacts without phone numbers

Advanced Extensions

• Update queries: Modify data (UPDATE, DELETE)
• Backup system: Export contacts to CSV format
• Data validation: Add more CHECK constraints

Creative Extensions

• Search functionality: Build a contact search tool
• Birthday tracker: Add birthday field and find upcoming birthdays
• Tag system: Add tags column for flexible categorization

📚 Additional Resources

Database Learning

• SQLite Tutorial
• SQL Basics on W3Schools
• Database Design Principles

Tools & Software

• DB Browser for SQLite - Free, visual database tool
• SQLiteStudio - Alternative SQLite manager
• DBeaver - Universal database tool

SQL Concepts

• Data Types: Understanding INTEGER, TEXT, DATE, BOOLEAN
• Constraints: NOT NULL, UNIQUE, CHECK, PRIMARY KEY
• Indexes: Speeding up searches on large datasets
• Normalization: Organizing data efficiently

🏆 Success Criteria

Your project is complete when:

• ✅ Schema created and sample data loaded successfully
• ✅ All 7 working queries execute without errors
• ✅ TODO 8 completed (complete contact information)
• ✅ TODO 9 completed (duplicate companies)
• ✅ TODO 10 completed (contact analysis statistics)
• ✅ Code is clean with proper comments
• ✅ You understand the purpose of each query

🎉 Congratulations!

Once you complete this activity, you'll have:

• Created your first functional database
• Written 10+ SQL queries
• Mastered basic data filtering and analysis
• Gained confidence working with databases

What You've Learned

• Database structure (tables, columns, data types)
• CRUD operations (Create, Read, Update, Delete)
• SQL query syntax (SELECT, WHERE, ORDER BY, GROUP BY)
• Data relationships and constraints
• Real-world database applications

This foundation will serve you well in the next activities where we'll explore multi-table relationships, advanced queries, and building full applications with databases!

Need Help?

• Review the working queries (1-7) to understand patterns
• Check the hints in each TODO section
• Use the debugging checklist above
• Test queries one step at a time

Ready to Submit? Complete this activity and submit through the Activity Submission Form

Happy querying! 🗄️✨