For an instructor lead, in-depth look at learning SQL click below.
If you’re looking to get organized or increase productivity, a personal project task tracking application can profoundly make a difference. In this post, we’re going to design such an application using SQL. SQL (Structured Query Language) is a standard language used for managing data in an RDBMS (Relational Database Management System) like MySql, Ms SQL Server, Oracle DB, etc. This application would essentially help you to track the status of your personal or professional tasks.
Setting up the Database
First, we start by setting up the database. The backbone of our task tracking application would be a SQL table named ‘task’ where we would store all the needed information about the tasks.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE task ( task_id INT PRIMARY KEY, task_name VARCHAR(255) NOT NULL, start_date DATE, end_date DATE, status VARCHAR(50) ); |
Here, we have fields task_id, task_name, start_date, end_date and status. ‘task_id’ would store a unique ID for each task. The ‘task_name’ field would store the name of the task. The ‘start_date’ and ‘end_date’ fields would store the date the task is expected to start and end. ‘status’ would store the progress status of each task.
Inserting Tasks
Once the ‘task’ table has been set up, we will insert some tasks. For inserting tasks into our table we would use the INSERT INTO command.
|
1 2 3 4 |
INSERT INTO task (task_id, task_name, start_date, end_date, status) VALUES (1, 'Write SQL blog post', '2022-03-01', '2022-03-31', 'Not Started'); |
This will insert a task into our table with task_id as 1, task_name as ‘Write SQL blog post’, a start_date from March 1, 2022, end_date as March 31, 2022, and the status as ‘Not Started’.
Updating Task Status
As we progress with our task, we will need to update the status of the task. To update any record in our table, we can use the UPDATE command.
|
1 2 3 4 5 |
UPDATE task SET status = 'In Progress' WHERE task_id = 1; |
This command will change the status of the task with the task_id 1 to ‘In Progress’.
Deleting a Task
We can remove a task that is no longer needed from our task list by using the DELETE command.
|
1 2 3 4 |
DELETE FROM task WHERE task_id = 1; |
The above command deletes the task with a task_id of 1 from the ‘task’ table.
Retrieving the tasks
Finally, to check the details of all tasks in the ‘task’ table, we use the SELECT command.
|
1 2 3 |
SELECT * FROM task; |
This command lists out all the tasks stored in the ‘task’ table.
Conclusion
And there you have it–your own personal project task tracking application in SQL. This only scratched the surface of what is possible. With SQL, you can create much complex and robust systems. Keep practicing and exploring!