For an instructor lead, in-depth look at learning SQL click below.
When managing properties, efficient data management and reporting is crucial. SQL (Structured Query Language) allows to create powerful data management systems. In this blog, I will guide you through designing a Property Inspection Management and Reporting System using SQL.
Database Structure
Firstly, it is crucial to understand the tables we would be working with. There are three core tables in our system: Properties, Inspections and Inspectors.
– Properties: Contains details of all properties.
– Inspections: Stores data related to the inspection.
– Inspectors: Keeps details for each property inspector.
Creating Our Tables
We will start by creating the three tables. Here are the SQL commands to create these tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Creating Properties Table CREATE TABLE Properties( Property_ID INT PRIMARY KEY, Address VARCHAR(255) NOT NULL, Owner_Name VARCHAR(255), Contact_Details VARCHAR(255) ); -- Creating Inspectors Table CREATE TABLE Inspectors( Inspector_ID INT PRIMARY KEY, Inspector_Name VARCHAR(255) NOT NULL, Inspector_Contact VARCHAR(255) ); -- Creating Inspections Table CREATE TABLE Inspections( Inspection_ID INT PRIMARY KEY, Property_ID INT, Inspector_ID INT, Inspection_Date DATE, Report NVARCHAR(MAX), FOREIGN KEY (Property_ID) REFERENCES Properties(Property_ID), FOREIGN KEY (Inspector_ID) REFERENCES Inspectors(Inspector_ID) ); |
Entering Data
Let’s learn to populate our tables with data using the INSERT command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Adding data to Properties table INSERT INTO Properties VALUES (1, '123 Street, City', 'John', '+1-202-555-0191'); INSERT INTO Properties VALUES (2, '456 Avenue, City', 'Jane', '+1-202-555-0151'); -- Adding data to Inspectors table INSERT INTO Inspectors VALUES (1, 'Inspector Nick', '+1-202-555-0141'); INSERT INTO Inspectors VALUES (2, 'Inspector Jenny', '+1-202-555-0111'); -- Adding data to Inspections table INSERT INTO Inspections VALUES (1, 1, 1, '2022-01-01', 'OK'); INSERT INTO Inspections VALUES (2, 2, 2, '2022-01-02', 'Needs repairs'); |
Queries for Reporting
Creating reports involves retrieving data from one or more tables and presenting it in a structured format. SQL’s SELECT command allows us do this. Let’s see some useful queries:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Get a list of properties inspected by a certain inspector SELECT P.* FROM Properties AS P JOIN Inspections AS I ON P.Property_ID = I.Property_ID WHERE I.Inspector_ID = 1; -- Count how many inspections an inspector has performed SELECT Inspector_ID, COUNT(Inspection_ID) as Inspections_Count FROM Inspections GROUP BY Inspector_ID; -- Get all inspection reports needing repairs SELECT * FROM Inspections WHERE Report = 'Needs repairs'; |
The above examples demonstrate how you can use SQL for creating a Property Inspection Management and Reporting System. The possibilities of what you can do with SQL are endless, and mastering it opens up a world of opportunities.