For an instructor lead, in-depth look at learning SQL click below.
In today’s blog, we are building a basic expense tracker using SQL. This application will provide insights into your expenditure by keeping track of your income and expenditure. We will be using SQL (Structured Query Language), a standard language for managing data held in a relational database management system. Let’s view necessary steps together.
Designing the Database
The first step in any application development is setting up a database that stores the application data. In this case, we’ll create a simple database named ‘Expenses’.
|
1 2 3 |
CREATE DATABASE Expenses; |
Creating Tables
In the Expenses database, we will create two tables – ‘Income’ and ‘Expenditure’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE Expenses; CREATE TABLE Income( id INT PRIMARY KEY, amount DECIMAL(10,2), date DATE, description VARCHAR(100) ); CREATE TABLE Expenditure( id INT PRIMARY KEY, amount DECIMAL(10,2), date DATE, description VARCHAR(100) ); |
Inserting Data
Now that we have our tables set up, let’s insert some data into these tables.
|
1 2 3 4 5 6 7 |
INSERT INTO Income(id, amount, date, description) VALUES(1, 1000.00, '2022-01-01', 'January Salary'); INSERT INTO Expenditure(id, amount, date, description) VALUES(1, 200.00, '2022-01-02', 'Groceries'); |
Manipulating and Retrieving Data
Updating Records
If you made an error while entering data or just need to update an existing record, you can use the UPDATE statement.
|
1 2 3 4 5 |
UPDATE Income SET amount = 1050.00 WHERE id = 1; |
Fetching Records
You can retrieve and analyze your data using the SELECT statement. For example, to fetch all incomes:
|
1 2 3 |
SELECT * FROM Income; |
Calculating Total Income and Expenditure
SQL is great for calculations as well. Let’s find out total income and expenditure.
|
1 2 3 4 |
SELECT SUM(amount) AS TotalIncome FROM Income; SELECT SUM(amount) AS TotalExpenditure FROM Expenditure; |
Conclusion
And there you have it! Your very own expense tracker using SQL. Beyond what was shown today, SQL offers more powerful features like JOINS, Nested Queries, Triggers, and more. Continue exploring this versatile language to expand the functionality of your new Expense Tracker. Happy coding!