Building a Small Project: SQL Database for Personal Finance

Hello, SQL enthusiasts!

Welcome to another practical and engaging project on SheSpeaksSQL. Today, we’re going to create a personal finance tracker using SQL. This hands-on project will help you apply your SQL skills to manage and analyze your finances effectively. By the end of this tutorial, you’ll have a functional database to track your income, expenses, and savings.

Step 1: Setting Up Your SQL Environment

Before we begin, ensure you have a SQL environment set up. You can use tools like SQLite, MySQL, or PostgreSQL. For this tutorial, we’ll use SQLite for its simplicity and ease of setup.

Install SQLite: Download and install SQLite from the official SQLite website.
Database Creation: Create a new database for your personal finance tracker.

sqlite3 personal_finance.db

Step 2: Creating the Database Schema

We need to create tables for tracking income, expenses, and savings. Here’s the schema:

CREATE TABLE Income (
    IncomeID INTEGER PRIMARY KEY,
    Date DATE,
    Source TEXT,
    Amount DECIMAL(10, 2)
);

CREATE TABLE Expenses (
    ExpenseID INTEGER PRIMARY KEY,
    Date DATE,
    Category TEXT,
    Description TEXT,
    Amount DECIMAL(10, 2)
);

CREATE TABLE Savings (
    SavingID INTEGER PRIMARY KEY,
    Date DATE,
    Description TEXT,
    Amount DECIMAL(10, 2)
);

Step 3: Inserting Sample Data

Let’s populate our tables with some sample data to work with.

INSERT INTO Income (Date, Source, Amount) VALUES
('2024-07-01', 'Salary', 3000.00),
('2024-07-15', 'Freelance Project', 500.00),
('2024-07-20', 'Investment', 200.00);

INSERT INTO Expenses (Date, Category, Description, Amount) VALUES
('2024-07-02', 'Rent', 'Monthly rent payment', 1000.00),
('2024-07-03', 'Groceries', 'Weekly groceries', 150.00),
('2024-07-10', 'Utilities', 'Electricity bill', 100.00),
('2024-07-15', 'Entertainment', 'Concert tickets', 75.00);

INSERT INTO Savings (Date, Description, Amount) VALUES
('2024-07-05', 'Savings Deposit', 500.00),
('2024-07-18', 'Emergency Fund', 300.00);

Step 4: Querying the Database

Now that we have our data, let’s write some queries to analyze our finances.

Total Income:

SELECT SUM(Amount) AS TotalIncome
FROM Income;

Total Expenses:

SELECT SUM(Amount) AS TotalExpenses
FROM Expenses;

Total Savings:

SELECT SUM(Amount) AS TotalSavings
FROM Savings;

Income vs. Expenses:

SELECT 
    (SELECT SUM(Amount) FROM Income) AS TotalIncome,
    (SELECT SUM(Amount) FROM Expenses) AS TotalExpenses,
    (SELECT SUM(Amount) FROM Income) - (SELECT SUM(Amount) FROM Expenses) AS NetSavings;

Step 5: Customizing and Sharing Your Project

This basic personal finance tracker is just the starting point. Customize it to suit your needs:

Categories: Add more categories to track different types of income and expenses.
Reports: Create detailed monthly or yearly financial reports.
Visualizations: Use tools like Tableau or Power BI to create visual representations of your data.

Downloadable Templates

To make it easier for you to get started, I’ve prepared downloadable templates for the database schema and sample data:

Engagement

I encourage you to customize this project and share your versions in the comments below. How have you adapted the tracker to fit your financial needs? What additional features have you added? Let’s learn and grow together!

Practice Exercise:

To help you build your personal finance tracker, I’ve prepared downloadable templates for the database schema and sample data:

•   Download income.csv
•   Download expenses.csv
•   Download savings.csv

Exercise Instructions:

1.  Load the dataset into your SQL environment.
2.  Create the tables using the provided schema.
3.  Insert the sample data into the tables.
4.  Write queries to analyze your finances.
5.  Customize the project to suit your needs and share your versions in the comments below!

Happy coding and happy tracking!

Lindsay

SheSpeaksSQL