For an instructor lead, in-depth look at learning SQL click below.
A Project Task Tracking application is essential for any organization to keep projects on track, ensuring tasks are completed in a timely manner and resources allocated effectively. This blog post will guide you on how to create this kind of application using SQL.
The Database design
An integral part of building the application is getting the database design right. This would typically involve two tables: Projects and Tasks.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), begin_date DATE, end_date DATE ); CREATE TABLE tasks ( task_id INT PRIMARY KEY, task_name VARCHAR(100), project_id INT, FOREIGN KEY (project_id) REFERENCES projects(project_id), status VARCHAR(20), start_date DATE, due_date DATE, completion_date DATE ); |
Filling data in the created tables
Let’s fill in some data to the tables we created. For the demonstration, we are using two projects: Project Alpha and Project Beta. Each project has its own set of tasks.
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO projects VALUES (1, 'Project Alpha', '2021-01-01', '2021-12-31'), (2, 'Project Beta', '2021-02-01', '2022-01-31'); INSERT INTO tasks VALUES (1, 'Requirement Gathering', 1, 'Completed', '2021-01-15', '2021-01-30', '2021-01-28'), (2, 'Design and Planning', 1, 'In progress', '2021-02-01', '2021-03-01', null), (3, 'Coding', 2, 'Not started', '2021-02-15', '2021-04-15', null); |
Fetching Project Tasks
To fetch all tasks associated with a project, you’d run the following SQL query:
|
1 2 3 4 5 6 |
SELECT tasks.task_name, tasks.status, tasks.start_date, tasks.due_date FROM tasks INNER JOIN projects ON tasks.project_id = projects.project_id WHERE projects.project_name = 'Project Alpha'; |
This above statement will display task details for ‘Project Alpha’. Similarly you can retrieve tasks for any project by just replacing project name in the WHERE clause.
And there you have it! A simple yet powerful project task tracking system – all created using SQL. Remember, this is a basic system and might need modifications based on the specific needs of your organization.