For an instructor lead, in-depth look at learning SQL click below.
SQL is an excellent language for dealing with structured data sets. In this tutorial, we’ll be developing a basic property maintenance tracking system using this powerful language. Our system will manage properties, their maintenance tasks, and staff.
Database Structure
To begin, let’s define the basic structure of our database. We will need three tables: ‘Properties’, ‘Maintenance_Tasks’, and ‘Staff’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE Properties ( PropertyID INT PRIMARY KEY, PropertyName VARCHAR(255), PropertyLocation VARCHAR(255) ); CREATE TABLE Maintenance_Tasks ( TaskID INT PRIMARY KEY, TaskName VARCHAR(255), TaskDescription VARCHAR(255), PropertyID INT FOREIGN KEY REFERENCES Properties(PropertyID) ); CREATE TABLE Staff ( StaffID INT PRIMARY KEY, StaffName VARCHAR(255), TaskID INT FOREIGN KEY REFERENCES Maintenance_Tasks(TaskID) ); |
Inserting Data
Now we can insert some data into our tables. Here are some examples:
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO Properties (PropertyID, PropertyName, PropertyLocation) VALUES (1, 'Property A', 'Location A'); INSERT INTO Maintenance_Tasks (TaskID, TaskName, TaskDescription, PropertyID) VALUES (1, 'Task A', 'Description A', 1); INSERT INTO Staff (StaffID, StaffName, TaskID) VALUES (1, 'Staff A', 1); |
Queries
Now we can start fetching and manipulating the data. For instance, if we want to find all the tasks for a particular property, we can use the following SQL SELECT statement:
|
1 2 3 4 5 6 |
SELECT t.TaskName FROM Maintenance_Tasks t INNER JOIN Properties p ON t.PropertyID = p.PropertyID WHERE p.PropertyName = 'Property A'; |
Conclusion
This is just a basic example of what you can achieve with SQL. The possibilities are endless. Once you have mastered the basic commands, you can start to build more complicated queries to manage and analyze your data more effectively. Keep practicing and experimenting. Happy coding!