For an instructor lead, in-depth look at learning SQL click below.
In this tutorial, we’ll explore how to build a project task assignment and progress monitoring system using Structured Query Language (SQL). The SQL language is powerful and capable of streamlining a myriad of data tasks; among them is project management and progress tracking.
Database Design
Before we plunge into the SQL quagmire, it’s important to understand the database setup that we will work with. Our example will be simple and will encompass three main tables: Projects, Tasks, and Employees.
Projects Table
It will hold the details of the various projects. Here, we assume that each project has an ID and a name.
|
1 2 3 4 5 6 |
CREATE TABLE Projects ( ProjectID int PRIMARY KEY, ProjectName varchar(255) ); |
Tasks Table
This will contain the details of the tasks. These details will include an ID, a project ID that links to the Projects table, task name, assignment date, completion date, and task status.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Tasks ( TaskID int PRIMARY KEY, ProjectID int FOREIGN KEY REFERENCES Projects(ProjectID), TaskName varchar(255), AssignmentDate DATE, CompletionDate DATE, Status varchar(50) ); |
Employees Table
This table will hold the employee’s details. We are interested in the employee name and the task that they have been assigned.
|
1 2 3 4 5 6 7 |
CREATE TABLE Employees ( EmployeeID int PRIMARY KEY, EmployeeName varchar(255), TaskID int FOREIGN KEY REFERENCES Tasks(TaskID) ); |
Assigning Tasks
To assign tasks, we insert the task and the corresponding employee into the database.
|
1 2 3 4 5 6 7 |
INSERT INTO Tasks (TaskID, ProjectID, TaskName, AssignmentDate, CompletionDate, Status) VALUES (1, 1, 'Build wireframe', '2021-01-10', NULL, 'Not Started'); INSERT INTO Employees (EmployeeID, EmployeeName, TaskID) VALUES (1, 'John Doe', 1); |
Monitoring Task Progress
To monitor the progress of tasks, we simply query the Tasks table.
|
1 2 3 |
SELECT TaskID, TaskName, Status FROM Tasks; |
With this setup, your project management system can track the progress of tasks and make critical decisions based on real-time data with SQL.