For an instructor lead, in-depth look at learning SQL click below.
Building a vehicle maintenance log system requires a solid grasp of SQL (Structured Query Language). We’ll go through the steps to create this system, and introduce some SQL essentials along the way.
Getting Started
Let’s assume that we are working with a database that includes three tables: Vehicles, MaintenanceTasks, and MaintenanceLog. The Vehicles and MaintenanceTasks tables store information about your vehicles and what maintenance tasks they need. The MaintenanceLog table keeps track of all maintenance activities.
Designing the Database Schema
Initially, we will create three tables: Vehicles, MaintenanceTasks, and MaintenanceLog. We will use an SQL CREATE statement to build these tables.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Vehicles( vehicle_id INT PRIMARY KEY, model VARCHAR(100), make VARCHAR(100), year INT ); |
This Vehicles table holds basic information about each vehicle. Each vehicle will have a unique ID, model, make, and year.
|
1 2 3 4 5 6 |
CREATE TABLE MaintenanceTasks( task_id INT PRIMARY KEY, description VARCHAR(255) ); |
The MaintenanceTasks table lists all possible maintenance tasks with a unique ID and description.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE MaintenanceLog( log_id INT PRIMARY KEY, vehicle_id INT, task_id INT, maintenance_date DATE, FOREIGN KEY (vehicle_id) REFERENCES Vehicles(vehicle_id), FOREIGN KEY (task_id) REFERENCES MaintenanceTasks(task_id) ); |
The MaintenanceLog table will include a log_id as the primary key, the referenced vehicle_id and task_id along with the maintenance_date to track when each task was performed on each vehicle.
Using SELECT to Query Data
With the SQL SELECT statement, you can cleanly and conveniently pull data from your tables. Let’s find which tasks were performed on a vehicle with vehicle_id = 1:
|
1 2 3 |
SELECT task_id FROM MaintenanceLog WHERE vehicle_id = 1; |
INSERT INTO
You can add new records to your maintenance log with the INSERT INTO statement. For instance, to log that the vehicle with an ID of 2 underwent task 3 on 06/01/2022, the SQL code would look like this:
|
1 2 3 4 |
INSERT INTO MaintenanceLog (vehicle_id, task_id, maintenance_date) VALUES (2, 3, '2022-06-01'); |
By being proficient at SQL, we can create a vehicle maintenance log system that’s both robust and flexible. Whether confirming vehicle information or inputting new maintenance tasks, SQL gives us a sturdy foundation on which to build. It’s an essential skill for any data analyst or backend developer.