Cloud Infrastructure Inventory Database

Build a PostgreSQL database to track servers, services, and incidents.

Introduction

30 Second Summary

When something breaks in a cloud environment, the first question is always the same: what's running, who owns it, and when was it last patched? DevOps teams answer these questions by querying databases.

In this project, you will build a PostgreSQL database that models a cloud infrastructure fleet, load it with realistic server and incident data, and write progressively complex SQL queries to extract operational intelligence from it.

What You'll Build

You can open a terminal, connect to your infrastructure database, and run SQL queries that instantly answer the same questions a DevOps engineer answers daily: what's broken, who owns it, and how long has it been like this?

By the end of this project, you'll have:

  • A working SQL toolkit that queries your fleet to find which servers are running, stopped, or overdue for patching.
  • Multi-table JOIN queries that connect data across tables to instantly reveal which team owns an unpatched server.
  • Infrastructure health metrics built with GROUP BY and COUNT that summarize incident frequency and patch compliance per team.
  • Secret Mission: Write security audit queries that calculate patch compliance percentages by team and flag active security risks.

Are there any prerequisites?

None. This guide walks you through installing PostgreSQL and connecting to it from scratch. You just need a Linux (Ubuntu) terminal and a willingness to type some SQL.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Install PostgreSQL and Connect

Every SQL query needs a database engine running behind it. PostgreSQL is the database you will encounter most in DevOps and Cloud roles, powering services like AWS RDS, Aurora, and Kubernetes-deployed databases.

In this step, you will install PostgreSQL on your Ubuntu machine, verify the service is running, and create the project database you will use throughout this project.

In this step, get ready to:
  • Install PostgreSQL and verify the service is running.
  • Connect to PostgreSQL and create the project database.
Install PostgreSQL and verify the installation
  • Install PostgreSQL and its command-line client by running this command:
sudo apt update && sudo apt install postgresql postgresql-client -y

What does this command do?

  • sudo apt update refreshes your package list so Ubuntu knows about the latest available software versions.
  • sudo apt install postgresql postgresql-client -y installs both the PostgreSQL database server and psql, the command-line client you will use to run SQL commands.
  • The -y flag automatically confirms the installation prompt so you do not have to type "yes" manually.
  • Verify PostgreSQL installed successfully by running:
psql --version

✔️ I see a version number

You should see output like psql (PostgreSQL) 18.4 (your exact version number may differ). PostgreSQL is installed and ready.

ⓧ Command not found

If you see "command not found", the client package did not install correctly.

  • Run the install command again:
sudo apt install postgresql-client -y
  • Then retry psql --version.

Why PostgreSQL over other databases?

PostgreSQL is what powers AWS RDS for PostgreSQL, Aurora PostgreSQL, and most Kubernetes-deployed databases. Learning PostgreSQL first gives you maximum transferability to production environments.

SQLite is simpler but lacks multi-user and server features relevant to DevOps. MySQL is popular but less common in cloud-native infrastructure tooling.

  • Confirm the PostgreSQL service started automatically by running:
sudo systemctl status postgresql

You should see "active (exited)" in the output. This confirms the service started successfully.

Why does it say "exited" instead of "running"?

On Ubuntu, the postgresql.service unit is a wrapper that kicks off the real database process. Once it finishes launching the cluster, it exits. The actual PostgreSQL server continues running in the background.

Seeing "active (exited)" means your database is ready to accept connections.

Seeing "inactive" or "failed"?

  • Try restarting the service manually:
sudo systemctl restart postgresql
  • Then check the status again with sudo systemctl status postgresql.
  • If it still fails, check the logs for details:
journalctl -u postgresql --no-pager -n 20

Help me troubleshoot a PostgreSQL service failure

Connect to PostgreSQL and create your project database

On Ubuntu, installing PostgreSQL creates a system user called postgres with automatic peer authentication. You connect through this user to run SQL commands.

Before you run the next command, what do you think you will see when you connect successfully?

  • Connect to the PostgreSQL prompt by running:
sudo -u postgres psql

You should see the postgres=# prompt. This means you are connected to the PostgreSQL server and ready to run SQL commands.

What does this command do?

  • sudo -u postgres runs the following command as the postgres system user, which has permission to access the database.
  • psql is the interactive terminal for PostgreSQL. It is where you type SQL queries and database commands.
  • Create your project database by running this SQL command at the postgres=# prompt:
CREATE DATABASE infra_inventory;

You should see CREATE DATABASE printed as confirmation.

What does this do?

This creates a brand new, empty database called infra_inventory. Think of a database as a separate container that holds all the tables you will create in the next steps. Keeping your project in its own database means it stays isolated from other data on the server.

  • Connect to your new database by running:
\c infra_inventory

You should see the message: "You are now connected to database "infra_inventory"". Your prompt also changes to infra_inventory=#.

Seeing "permission denied" or "role does not exist"?

  • Make sure you use sudo -u postgres psql and not just psql by itself. Running psql alone tries to connect as your current Linux user, which does not have a PostgreSQL role.
  • If you accidentally closed the terminal and need to reconnect, run sudo -u postgres psql again.

Help me troubleshoot a PostgreSQL connection error

  • Exit psql for now by running:
\q

You are back in your regular Ubuntu terminal.

Your database engine is running and your infra_inventory database is ready. Next up, you will design the table structure that models a real cloud infrastructure.

Design Your Infrastructure Schema

Your database engine is running and the infra_inventory database is ready.

Right now that database is an empty shell with no structure. Without tables defining what data looks like, PostgreSQL has nothing to store and you have nothing to query. In this step, you will design a schema with three interconnected tables that model real cloud infrastructure.

In this step, get ready to:
  • Create a services table to track your cloud applications.
  • Create servers and incidents tables with foreign key relationships.
  • Verify the schema is correctly set up with all relationships.
Connect to your database and create the services table

Each table you create defines a type of infrastructure asset. The first table, services, represents the cloud applications your team runs.

  • Connect to your infra_inventory database by running this command:
sudo -u postgres psql -d infra_inventory

You should see the infra_inventory=# prompt, confirming you are connected to the right database.

Seeing a 'database does not exist' error?

Make sure you created the database in the previous step. You can check by running sudo -u postgres psql and then \l to list all databases.

If infra_inventory is not listed, create it with CREATE DATABASE infra_inventory; and try connecting again.

  • Create the services table by running this SQL:
-- Create a table to track cloud services/applications
CREATE TABLE services (
    id SERIAL PRIMARY KEY,           -- Auto-incrementing unique ID
    name VARCHAR(100) NOT NULL,      -- Service name like 'payment-api'
    environment VARCHAR(20) NOT NULL, -- production, staging, etc.
    team VARCHAR(50) NOT NULL,       -- Team responsible for this service
    status VARCHAR(20) DEFAULT 'active', -- Current service health
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this SQL do?

  • SERIAL PRIMARY KEY creates an auto-incrementing ID column that uniquely identifies each row.
  • VARCHAR(100) NOT NULL means the column stores text up to 100 characters and cannot be left empty.
  • DEFAULT 'active' gives new rows the value active automatically if you do not specify one.
  • CURRENT_TIMESTAMP records the exact date and time each row was inserted.

You should see CREATE TABLE printed in the terminal, confirming the table was created.

Seeing a syntax error?

Make sure the entire statement including the closing ); was pasted. If you see a continuation prompt like infra_inventory(# it means PostgreSQL is waiting for the rest of the statement. Type ); and press Enter to close it.

help me fix a syntax error when creating a table in psql

Create the servers and incidents tables with foreign key relationships

A foreign key connects rows in one table to rows in another. Each server belongs to a service, and each incident belongs to a server.

  • Create the servers table by running this SQL:
-- Servers belong to services via the service_id foreign key
CREATE TABLE servers (
    id SERIAL PRIMARY KEY,
    hostname VARCHAR(100) NOT NULL,           -- e.g. 'prod-pay-01'
    ip_address VARCHAR(45) NOT NULL,          -- Internal IP address
    service_id INTEGER REFERENCES services(id), -- Links to services table
    instance_type VARCHAR(30) NOT NULL,       -- e.g. 't3.medium'
    region VARCHAR(30) NOT NULL,              -- e.g. 'us-east-1'
    last_patched DATE,                        -- When last patched (can be NULL)
    status VARCHAR(20) DEFAULT 'running',     -- running, stopped, etc.
    launched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does REFERENCES do?

The line service_id INTEGER REFERENCES services(id) creates a foreign key constraint. This means every value in service_id must match an existing id in the services table.

PostgreSQL will reject any insert that tries to link a server to a service that does not exist. This is how relational databases enforce data integrity.

You should see CREATE TABLE printed again.

  • Create the incidents table by running this SQL:
-- Incidents are tied to specific servers via server_id
CREATE TABLE incidents (
    id SERIAL PRIMARY KEY,
    server_id INTEGER REFERENCES servers(id), -- Links to servers table
    severity VARCHAR(10) NOT NULL,            -- low, medium, high, critical
    description TEXT NOT NULL,                -- What went wrong
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP                    -- NULL means still unresolved
);

Why is resolved_at nullable?

When resolved_at is NULL, it means the incident is still open. Later you will write queries using WHERE resolved_at IS NULL to find all unresolved incidents across your infrastructure.

You should see CREATE TABLE one more time.

✔️ Awesome, I've got everything!

All three tables are created. Let's verify the full schema next.

ⓧ I'd like to double check the full code

Here are all three CREATE TABLE statements together for reference. These are what you should have run in order.

CREATE TABLE services (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    environment VARCHAR(20) NOT NULL,
    team VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE servers (
    id SERIAL PRIMARY KEY,
    hostname VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    service_id INTEGER REFERENCES services(id),
    instance_type VARCHAR(30) NOT NULL,
    region VARCHAR(30) NOT NULL,
    last_patched DATE,
    status VARCHAR(20) DEFAULT 'running',
    launched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE incidents (
    id SERIAL PRIMARY KEY,
    server_id INTEGER REFERENCES servers(id),
    severity VARCHAR(10) NOT NULL,
    description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP
);
Verify your schema

Before moving on, you need to confirm that all three tables exist and that the foreign key relationships are wired correctly.

What do you expect to see when you list all tables in the database?

  • List all tables by running:
\dt

You should see three tables listed: incidents, servers, and services.

  • Inspect the servers table structure to confirm the foreign key by running:
\d servers

In the output, look for the Foreign-key constraints section at the bottom. It should show that service_id references services(id).

Don't see three tables?

If a table is missing, you may have gotten a syntax error when creating it. Run \dt to see which tables exist, then re-run the CREATE TABLE statement for any that are missing.

If you see a 'relation already exists' error, the table is already there and you can move on.

help me figure out why my tables are missing in psql

Your infrastructure schema is locked in. Next up, you will load realistic DevOps data into these tables and start querying your fleet.

Load Data and Query Your Fleet

Your schema is in place. Three tables with defined columns and relationships are ready to hold data.

Empty tables teach nothing. In this step, you will load realistic infrastructure data into your database and write your first queries to pull out exactly the information you need.

In this step, get ready to:
  • Insert services, servers, and incident records into your database.
  • Write filtering and sorting queries to interrogate your server fleet.
  • Discover a limitation of single-table queries that sets up the next step.
Insert services and servers data
  • Connect to your project database by running this command:
sudo -u postgres psql -d infra_inventory

You should see the infra_inventory=# prompt confirming you are connected to your database.

  • Insert five cloud services into the services table by running this SQL:
INSERT INTO services (name, environment, team, status) VALUES
('payment-api', 'production', 'payments', 'active'),
('user-auth', 'production', 'identity', 'active'),
('notification-service', 'production', 'comms', 'active'),
('analytics-pipeline', 'staging', 'data', 'active'),
('admin-dashboard', 'production', 'platform', 'degraded');

What does this SQL do?

Each row represents a cloud service your company runs. The team column records who owns it. The last service has a degraded status, meaning something is already wrong with it.

You should see INSERT 0 5 confirming all five rows were added.

  • Insert eight servers into the servers table by running this SQL:
INSERT INTO servers (hostname, ip_address, service_id, instance_type, region, last_patched, status) VALUES
('prod-pay-01', '10.0.1.10', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-pay-02', '10.0.1.11', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-auth-01', '10.0.2.10', 2, 't3.large', 'us-east-1', '2026-06-01', 'running'),
('prod-auth-02', '10.0.2.11', 2, 't3.large', 'us-west-2', '2026-05-20', 'running'),
('prod-notif-01', '10.0.3.10', 3, 't3.small', 'us-east-1', '2026-07-20', 'running'),
('stg-analytics-01', '10.1.4.10', 4, 't3.xlarge', 'us-west-2', '2026-04-10', 'running'),
('prod-admin-01', '10.0.5.10', 5, 't3.medium', 'us-east-1', '2026-03-15', 'stopped'),
('prod-admin-02', '10.0.5.11', 5, 't3.medium', 'us-east-1', '2026-06-28', 'running');

What does this data represent?

These servers model a realistic EC2-style fleet. Notice that service_id links each server back to the services table (a foreign key relationship).

The last_patched dates vary intentionally. Some servers were patched recently. Others have not been patched in months. One server (prod-admin-01) is stopped entirely.

You should see INSERT 0 8 confirming all eight rows were added.

Seeing an error about a foreign key violation?

This means you tried to insert servers before services. The service_id column references the services table, so services must exist first.

Run SELECT * FROM services; to check if your services are there. If the table is empty, scroll up and run the services INSERT first.

Help me fix a foreign key violation when inserting servers.

Insert incident data

Incidents record things that went wrong on specific servers. Each incident links to a server via its server_id foreign key.

  • Insert six incidents by running this SQL:
INSERT INTO incidents (server_id, severity, description, created_at, resolved_at) VALUES
(3, 'high', 'Authentication service timeout under load', '2026-07-10 09:15:00', '2026-07-10 10:30:00'),
(4, 'critical', 'Auth server unresponsive, full outage', '2026-07-18 03:22:00', '2026-07-18 04:45:00'),
(7, 'medium', 'Admin dashboard slow response times', '2026-07-05 14:00:00', '2026-07-05 16:30:00'),
(7, 'high', 'Admin dashboard completely down', '2026-07-12 08:00:00', NULL),
(6, 'low', 'Analytics pipeline delayed processing', '2026-07-20 11:00:00', '2026-07-20 12:00:00'),
(1, 'medium', 'Payment processing latency spike', '2026-07-22 16:45:00', '2026-07-22 17:15:00');

What is that NULL value?

The fourth incident has NULL for resolved_at. This means the incident is still open. Nobody has fixed it yet. You will query for unresolved incidents later in the project.

You should see INSERT 0 6 confirming all six incidents were recorded.

Write your first queries

Your database now holds 5 services, 8 servers, and 6 incidents. Time to ask it questions.

  • View all servers by running:
SELECT * FROM servers;

You should see all 8 rows with every column displayed. That is a lot of information at once.

  • Before you run the next query, predict how many rows you will get back. How many of your 8 servers have a status of running?
  • Filter to only running servers by running:
SELECT hostname, ip_address, status FROM servers WHERE status = 'running';

You should see 7 rows returned. The only server missing is prod-admin-01 because its status is stopped.

What did WHERE do?

The WHERE clause filters rows before they appear in your results. Only rows that match the condition (status = 'running') are returned. Everything else is excluded.

  • Sort servers by their patch date to see which ones are most overdue:
SELECT hostname, last_patched FROM servers ORDER BY last_patched ASC;

The oldest-patched server appears at the top. ASC means ascending order (earliest date first).

  • Now find servers that have not been patched since June 2026. Before you run this, think about what information you would need to alert the responsible team.
  • Run this query:
SELECT hostname, service_id, last_patched FROM servers WHERE last_patched < '2026-06-01';

You should see two servers: stg-analytics-01 with service_id 4, and prod-admin-01 with service_id 5.

Notice the problem. You can see which servers are overdue for patching. But service_id is just a number. Who owns these servers? Which team should you alert? You cannot tell from this query alone.

Why is this a problem?

The team name lives in the services table, not the servers table. Right now you would have to run a second query, find service_id 4 in the services table, and manually match it up. That does not scale.

This is exactly the gap that JOINs solve. You will fix this in the next step.

Save your work to reference files

Before moving on, save your INSERT statements and queries into files so you can reference them later.

  • Exit psql by typing:
\q
  • Open VS Code to your project folder.
  • Create a new file called seed_data.sql and paste the following content:
INSERT INTO services (name, environment, team, status) VALUES
('payment-api', 'production', 'payments', 'active'),
('user-auth', 'production', 'identity', 'active'),
('notification-service', 'production', 'comms', 'active'),
('analytics-pipeline', 'staging', 'data', 'active'),
('admin-dashboard', 'production', 'platform', 'degraded');

INSERT INTO servers (hostname, ip_address, service_id, instance_type, region, last_patched, status) VALUES
('prod-pay-01', '10.0.1.10', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-pay-02', '10.0.1.11', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-auth-01', '10.0.2.10', 2, 't3.large', 'us-east-1', '2026-06-01', 'running'),
('prod-auth-02', '10.0.2.11', 2, 't3.large', 'us-west-2', '2026-05-20', 'running'),
('prod-notif-01', '10.0.3.10', 3, 't3.small', 'us-east-1', '2026-07-20', 'running'),
('stg-analytics-01', '10.1.4.10', 4, 't3.xlarge', 'us-west-2', '2026-04-10', 'running'),
('prod-admin-01', '10.0.5.10', 5, 't3.medium', 'us-east-1', '2026-03-15', 'stopped'),
('prod-admin-02', '10.0.5.11', 5, 't3.medium', 'us-east-1', '2026-06-28', 'running');
  • Save seed_data.sql.
  • Create another new file called queries.sql and paste the following content:
-- Basic queries (Step 3)
SELECT * FROM servers;

SELECT hostname, ip_address, status FROM servers WHERE status = 'running';

SELECT hostname, last_patched FROM servers ORDER BY last_patched ASC;

SELECT hostname, service_id, last_patched FROM servers WHERE last_patched < '2026-06-01';
  • Save queries.sql.
  • Confirm both files exist by running this in your terminal:
ls *.sql

You should see queries.sql, schema.sql, and seed_data.sql listed.

✔️ Awesome, I've got everything!

Great. Double check you have saved both files.

ⓧ I'd like to double check the full code

Here is what seed_data.sql should contain (all three INSERT blocks):

INSERT INTO services (name, environment, team, status) VALUES
('payment-api', 'production', 'payments', 'active'),
('user-auth', 'production', 'identity', 'active'),
('notification-service', 'production', 'comms', 'active'),
('analytics-pipeline', 'staging', 'data', 'active'),
('admin-dashboard', 'production', 'platform', 'degraded');

INSERT INTO servers (hostname, ip_address, service_id, instance_type, region, last_patched, status) VALUES
('prod-pay-01', '10.0.1.10', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-pay-02', '10.0.1.11', 1, 't3.medium', 'us-east-1', '2026-07-15', 'running'),
('prod-auth-01', '10.0.2.10', 2, 't3.large', 'us-east-1', '2026-06-01', 'running'),
('prod-auth-02', '10.0.2.11', 2, 't3.large', 'us-west-2', '2026-05-20', 'running'),
('prod-notif-01', '10.0.3.10', 3, 't3.small', 'us-east-1', '2026-07-20', 'running'),
('stg-analytics-01', '10.1.4.10', 4, 't3.xlarge', 'us-west-2', '2026-04-10', 'running'),
('prod-admin-01', '10.0.5.10', 5, 't3.medium', 'us-east-1', '2026-03-15', 'stopped'),
('prod-admin-02', '10.0.5.11', 5, 't3.medium', 'us-east-1', '2026-06-28', 'running');

And here is queries.sql:

-- Basic queries (Step 3)
SELECT * FROM servers;

SELECT hostname, ip_address, status FROM servers WHERE status = 'running';

SELECT hostname, last_patched FROM servers ORDER BY last_patched ASC;

SELECT hostname, service_id, last_patched FROM servers WHERE last_patched < '2026-06-01';

Your database is loaded and you can filter, sort, and search your fleet with SQL. But you hit a wall: single-table queries cannot pull the team name from a different table. Next up, you will connect the dots with JOINs and turn those meaningless IDs into real answers.

Connect the Dots with JOINs

In the last step, you found servers overdue for patching. But the query only showed service_id as a number. You know stg-analytics-01 needs attention, but which team should you call?

JOINs solve this by connecting rows from different tables in a single query. Instead of mentally cross-referencing service_id = 4 back to the services table yourself, you let PostgreSQL do it for you.

In this step, get ready to:
  • Write a two-table JOIN that reveals which team owns each unpatched server.
  • JOIN incidents to servers to see what went wrong and where.
  • Write a three-table JOIN that gives you the full operational picture in one query.
Write your first JOIN to identify who owns at-risk servers

Your servers table has a service_id column, and your services table has the team name. A JOIN links these two tables together using that shared ID.

  • In your psql session connected to infra_inventory, run this query to connect servers to services and reveal the team name:
SELECT servers.hostname, services.name AS service, services.team, servers.last_patched
FROM servers
JOIN services ON servers.service_id = services.id
WHERE servers.last_patched < '2026-06-01';

What does this query do?

  • JOIN services ON servers.service_id = services.id tells PostgreSQL to match each server row with the service row that shares the same ID.
  • services.name AS service renames the column in your output so it reads as "service" instead of "name".
  • The WHERE clause is the same filter from Step 3, but now you see team names instead of meaningless numbers.

You should see two rows: stg-analytics-01 owned by the data team, and prod-admin-01 owned by the platform team. Now you know exactly who to notify.

Seeing zero rows or an error?

  • If you see "ERROR: relation does not exist", confirm you are connected to infra_inventory (your prompt should read infra_inventory=#). Reconnect with \c infra_inventory if needed.
  • If you see zero rows, double-check the date in your WHERE clause. It should be '2026-06-01' with single quotes around it.

Why isn't my JOIN returning results in psql?

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column between them. In this case, the foreign key relationship you defined in Step 2 (service_id REFERENCES services(id)) tells PostgreSQL exactly how the tables connect.

Think of it like a lookup. For each server row, PostgreSQL looks up the matching service row and stitches them together into one result.

JOIN incidents to see what went wrong and where

The incidents table has a server_id column but no hostname. A JOIN to the servers table surfaces the actual server name alongside each incident.

  • Run this query to see all incidents with the server hostname attached:
SELECT servers.hostname, incidents.severity, incidents.description, incidents.created_at
FROM incidents
JOIN servers ON incidents.server_id = servers.id
ORDER BY incidents.created_at DESC;

What does this query do?

  • JOIN servers ON incidents.server_id = servers.id links each incident to the server where it happened.
  • ORDER BY incidents.created_at DESC puts the most recent incidents first, so you see current problems at the top.

You should see 6 rows sorted by date, with the most recent incident (payment processing latency spike on prod-pay-01) at the top.

Rows not in the expected order?

  • If the oldest incident appears first, check that you wrote DESC (descending) not ASC (ascending) in the ORDER BY clause.
  • If you see fewer than 6 rows, make sure you do not have a leftover WHERE clause from a previous query. Copy the full query fresh.

Help me fix my incident JOIN query ordering.

Three-table JOIN for the full operational picture

You can chain multiple JOINs in a single query. This final query answers the question a DevOps team asks in every incident review meeting: "what broke, on which server, owned by which team?"

  • Before you run this next query, predict: how many rows do you expect back if you filter to only high and critical severity incidents?
  • Run this three-table JOIN to connect incidents, servers, and services together:
SELECT servers.hostname, services.name AS service, services.team,
       incidents.severity, incidents.description
FROM incidents
JOIN servers ON incidents.server_id = servers.id
JOIN services ON servers.service_id = services.id
WHERE incidents.severity IN ('high', 'critical');

What does this query do?

  • The first JOIN connects incidents to servers (so you get the hostname).
  • The second JOIN connects servers to services (so you get the team name and service name).
  • WHERE incidents.severity IN ('high', 'critical') filters to only the most urgent incidents, using IN to match against multiple values at once.

You should see 3 rows. Each row shows the hostname, service name, team, severity, and description all in one result set. You can instantly see which team owns each serious incident without switching between tables.

Seeing a syntax error on the multi-line query?

  • Make sure the comma after services.team on the first SELECT line is present. Missing commas between column names is the most common SQL syntax error.
  • Check that you have single quotes around 'high' and 'critical' in the IN clause. Double quotes will cause an error.

Help me fix my three-table JOIN syntax.

  • Save all three JOIN queries to queries.sql in VS Code, below the basic queries from Step 3. Add a -- JOINs (Step 4) comment above them.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file after this step:

-- Basic queries (Step 3)
SELECT * FROM servers;

SELECT hostname, ip_address, status FROM servers WHERE status = 'running';

SELECT hostname, last_patched FROM servers ORDER BY last_patched ASC;

SELECT hostname, service_id, last_patched FROM servers WHERE last_patched < '2026-06-01';

-- JOINs (Step 4)
SELECT servers.hostname, services.name AS service, services.team, servers.last_patched
FROM servers
JOIN services ON servers.service_id = services.id
WHERE servers.last_patched < '2026-06-01';

SELECT servers.hostname, incidents.severity, incidents.description, incidents.created_at
FROM incidents
JOIN servers ON incidents.server_id = servers.id
ORDER BY incidents.created_at DESC;

SELECT servers.hostname, services.name AS service, services.team,
       incidents.severity, incidents.description
FROM incidents
JOIN servers ON incidents.server_id = servers.id
JOIN services ON servers.service_id = services.id
WHERE incidents.severity IN ('high', 'critical');

You can now pull data from multiple related tables in a single query. That is the core skill behind every production database investigation. Next up, you will use GROUP BY and aggregate functions to generate infrastructure health summaries from your raw data.

Build Infrastructure Health Metrics

Your JOINs from Step 4 gave you detailed incident rows with full context. You can now see exactly which team owns a troubled server and what happened to it.

Leadership doesn't want individual rows. They want summaries: "How many incidents hit each service?" or "Which team owns the most servers?" GROUP BY with aggregate functions like COUNT lets you generate these dashboard-style metrics from raw data.

In this step, get ready to:
  • Count servers per service and incidents by severity level.
  • Filter aggregate results with HAVING to find problem services.
  • Identify unresolved incidents representing active operational risks.
Count servers per service

A DevOps team managing 8 servers needs to know how they're distributed across services. GROUP BY collapses multiple rows into groups and lets you run aggregate functions like COUNT on each group.

  • In your psql session connected to infra_inventory, count how many servers belong to each service by running this query:
SELECT services.name, COUNT(servers.id) AS server_count
FROM services
JOIN servers ON services.id = servers.service_id
GROUP BY services.name
ORDER BY server_count DESC;

What does this query do?

  • JOIN servers ON services.id = servers.service_id connects each server to its parent service.
  • GROUP BY services.name collapses all rows with the same service name into a single output row.
  • COUNT(servers.id) counts how many server rows landed in each group.
  • ORDER BY server_count DESC ranks the results so the service with the most servers appears first.

You should see payment-api and admin-dashboard at the top with 2 servers each, and the remaining services with 1 server each.

Seeing an error about the column name?

Make sure you typed services.name (not just name) in the SELECT and GROUP BY clauses. When you JOIN two tables that both have a name column, PostgreSQL needs you to specify which table you mean.

Why is my GROUP BY query failing?

Count incidents by severity

Knowing how many incidents fall into each severity bucket helps you understand your operational risk profile at a glance.

  • Count incidents by severity level by running:
SELECT severity, COUNT(*) AS incident_count
FROM incidents
GROUP BY severity
ORDER BY incident_count DESC;

What's the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row in the group, regardless of NULL values. COUNT(column) only counts rows where that specific column is not NULL.

Here we use COUNT(*) because we want to count all incidents, and no row in the incidents table has a NULL severity.

You should see a ranked list showing high and medium with 2 incidents each, and critical and low with 1 each.

Find the most problematic services using HAVING

You can filter individual rows with WHERE, but what about filtering groups? HAVING lets you keep only groups that meet a condition after aggregation.

  • Find which services have more than 1 incident by running this three-table JOIN with HAVING:
SELECT services.name, services.team, COUNT(incidents.id) AS incident_count
FROM services
JOIN servers ON services.id = servers.service_id
JOIN incidents ON servers.id = incidents.server_id
GROUP BY services.name, services.team
HAVING COUNT(incidents.id) > 1
ORDER BY incident_count DESC;

How does HAVING differ from WHERE?

WHERE filters individual rows before grouping happens. HAVING filters entire groups after GROUP BY has already collapsed rows.

HAVING COUNT(incidents.id) > 1 means: group all incidents by service, then throw away any service that only has 1 incident. Only the repeat offenders survive.

You should see a ranked list showing service names, their owning team, and how many incidents each has had. Only services with more than 1 incident appear.

Getting zero rows back?

Check that you used HAVING COUNT(incidents.id) > 1 (greater than 1), not > 2. With our seed data, two services have exactly 2 incidents each. Using > 2 would exclude them.

Why is my HAVING query returning empty results?

Find unresolved incidents

Summaries tell you about patterns over time. But right now, someone on the ops team needs to know: what is still broken? An incident with a NULL resolved_at timestamp means nobody has fixed it yet.

Before you run the next query, think about how many unresolved incidents you expect to find based on the data you loaded.

  • Find all currently unresolved incidents by running:
SELECT servers.hostname, services.name AS service, incidents.severity, incidents.description
FROM incidents
JOIN servers ON incidents.server_id = servers.id
JOIN services ON servers.service_id = services.id
WHERE incidents.resolved_at IS NULL;

What does IS NULL check?

In SQL, NULL means "no value exists." You cannot check for NULL with = NULL (that always returns false). You must use IS NULL instead.

Here, WHERE incidents.resolved_at IS NULL finds incidents that have never been marked as resolved. This is a common pattern for tracking open tickets, active alerts, or pending tasks.

You should see one row: prod-admin-01 from the admin-dashboard service, with severity high and description "Admin dashboard completely down." That is your one active operational risk right now.

Seeing more than one row?

If you ran the INSERT statements from Step 3 more than once, you may have duplicate incident records. Run SELECT COUNT(*) FROM incidents; to check. You should have exactly 6 rows. If you have more, you can reset with DELETE FROM incidents; and re-run the original INSERT from Step 3.

Help me fix duplicate rows in my incidents table.

  • Add a comment header and all four aggregation queries to your queries.sql file in VS Code:
-- Aggregations (Step 5)
SELECT services.name, COUNT(servers.id) AS server_count
FROM services
JOIN servers ON services.id = servers.service_id
GROUP BY services.name
ORDER BY server_count DESC;

SELECT severity, COUNT(*) AS incident_count
FROM incidents
GROUP BY severity
ORDER BY incident_count DESC;

SELECT services.name, services.team, COUNT(incidents.id) AS incident_count
FROM services
JOIN servers ON services.id = servers.service_id
JOIN incidents ON servers.id = incidents.server_id
GROUP BY services.name, services.team
HAVING COUNT(incidents.id) > 1
ORDER BY incident_count DESC;

SELECT servers.hostname, services.name AS service, incidents.severity, incidents.description
FROM incidents
JOIN servers ON incidents.server_id = servers.id
JOIN services ON servers.service_id = services.id
WHERE incidents.resolved_at IS NULL;
  • Save queries.sql.

✔️ Awesome, I've got everything!

Great. Make sure you saved queries.sql with the new aggregation section appended below your JOIN queries from Step 4.

ⓧ I'd like to double check the full code

Your queries.sql file should now have three sections, in this order:

  • The -- Basic queries (Step 3) section with four SELECT/WHERE/ORDER BY queries.
  • The -- JOINs (Step 4) section with three JOIN queries (two-table and three-table).
  • The -- Aggregations (Step 5) section with four queries: server count per service, incidents by severity, the HAVING query, and the unresolved incidents query.

Scroll through your file and confirm each section is present with its comment header.

You can now generate the kind of metrics that appear on infrastructure dashboards. You know which services are repeat offenders, which severity levels dominate, and what is still broken right now. Next up, you will tackle a Secret Mission that pushes your SQL further with compliance percentages and security risk calculations.

Secret mission

Write a DevSecOps Compliance Audit

DevSecOps teams need to prove compliance to leadership. Can you write queries that calculate patch compliance percentages by team and flag the highest-risk servers in your fleet? This mission introduces CASE expressions, date arithmetic, and percentage calculations.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • A local PostgreSQL database called infra_inventory with three tables (services, servers, incidents).
  • PostgreSQL server running as a system service on your machine.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep practicing queries.

  • Your database stays on your machine and costs nothing to keep.
  • Reconnect anytime by running:
sudo -u postgres psql -d infra_inventory
  • This is a great sandbox for practicing more complex queries, adding new tables (like a deployments table), or connecting to the database from a Python script.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your data stays intact on disk.

  • Stop the PostgreSQL service by running:
sudo systemctl stop postgresql
  • Your database files remain on disk. When you are ready to come back, start the service again by running:
sudo systemctl start postgresql
  • Then reconnect with sudo -u postgres psql -d infra_inventory and pick up where you left off.

Delete - I don't want to use this again

Remove the database and optionally uninstall PostgreSQL entirely.

  • Connect to PostgreSQL by running:
sudo -u postgres psql
  • Drop the project database by running:
DROP DATABASE infra_inventory;
  • Exit psql by typing \q and pressing Enter.
  • If you want to remove PostgreSQL from your machine entirely, run:
sudo apt remove postgresql postgresql-client -y
  • Delete any SQL files you saved locally (schema.sql, seed_data.sql, queries.sql, security_audit.sql) if you created them.

Nice Work!

Nice Work!

Nice work! You just built a cloud infrastructure inventory database from scratch and wrote the same SQL queries that DevOps engineers run every day to keep production systems healthy.

You learned how to:

  • Designed a relational schema with three interconnected tables and foreign key relationships modeling real cloud infrastructure.
  • Learned JOINs by hitting the limitation of single-table queries and solving it by connecting tables to answer "which team owns this broken server?"
  • Built infrastructure health metrics with GROUP BY, COUNT, and HAVING to generate dashboard-style summaries from raw operational data.
  • Secret Mission: Produced a DevSecOps compliance audit with percentage calculations and risk flags that identify the highest-risk teams.

Ready to quiz yourself?