For an instructor lead, in-depth look at learning SQL click below.
Health is wealth; keeping track of your fitness journey and progress makes you richer. Today, we will be diving into a basic tutorial that shows how you could use SQL to build your personal health and fitness tracker and goal achievement system. We’ll cover basics such as creating tables, inserting values, and generating reports.
Creating the Tables
First, let’s start by creating three tables: User, Workout, and Meal. The User table will hold/user information, the Workout table will keep track of the workout routine, and the Meal table will keep tabs on the caloric intake.
Creating User Table
|
1 2 3 4 5 6 7 8 |
CREATE TABLE User( UserID INT PRIMARY KEY, UserName VARCHAR(100), UserEmail VARCHAR(100), ); |
Creating Workout Table
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Workout( WorkoutID INT PRIMARY KEY, UserID INT, WorkoutType VARCHAR(50), Duration INT, WorkoutDate DATE, FOREIGN KEY (UserID) REFERENCES User(UserID) ); |
Creating Meal Table
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Meal( MealID INT PRIMARY KEY, UserID INT, MealName VARCHAR(50), Calories INT, MealDate DATE, FOREIGN KEY (UserID) REFERENCES User(UserID) ); |
Inserting Values
After designing our tables, it’s now time to populate them with some data. Let’s add data for one user, plus some Workout and Meal information.
Adding User Information
|
1 2 3 4 |
INSERT INTO Users(UserID, UserName, Email) VALUES (1,'John Doe',<a href="mailto:'john.doe@gmail.com'" >'john.doe@gmail.com'</a>); |
Adding Workout Data
|
1 2 3 4 |
INSERT INTO Workout(UserID, WorkoutType, Duration, WorkoutDate) VALUES (1,'Cardio', 30, '2021-01-01'); |
Adding Meal Data
|
1 2 3 4 |
INSERT INTO Meal(UserID, MealName, Calories, MealDate) VALUES (1,'Chicken Salad', 250, '2021-01-01'); |
Generating Reports
Now, with the data in place, we can generate reports to track the fitness progress. You could retrieve data from the database by linking these tables by ‘UserID’. This example demonstrates how to retrieve a user’s workout and meal details.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT u.UserName, w.WorkoutType, w.Duration, w.WorkoutDate, m.MealName, m.Calories, m.MealDate FROM User u JOIN Workout w ON u.UserID = w.UserID JOIN Meal m ON u.UserID = m.UserID WHERE u.UserID = 1 |
With the SQL skills you’ve learned from this guide, you can now track your fitness journey, remain consistent, and stay healthy!