SQL for Smart Factories: Your First Manufacturing Database

Learn SQL by exploring a real-world manufacturing scenario with PostgreSQL.

Imagine that you are a data analyst working inside a modern manufacturing company.

Every day, the factory generates thousands of records:

• production orders;
• machine events;
• downtime events;
• quality inspections;
• maintenance operations;
• energy measurements.

The production manager does not want to look at thousands of rows.

He wants answers.

Which production line is performing best?

Which machines are causing the most downtime?

Is production quality getting worse?

Does maintenance actually improve machine performance?

And most importantly:

Can we use SQL to find the answers?

This is where Industrial SQL becomes interesting.

Instead of learning SQL with artificial examples such as customers and orders, we are going to work with a manufacturing environment.

---

1. Why SQL matters in the Smart Factory

A Smart Factory produces data continuously.

Machines generate events.

Production systems record orders.

Quality systems record inspections.

Maintenance teams record interventions.

Energy monitoring systems record consumption.

The challenge is not simply collecting this data.

The challenge is turning the data into information that can support industrial decisions.

SQL is one of the most useful tools for doing this.

With SQL, we can move from:

"Machine M005 produced many parts."

to questions such as:

"How did M005's cycle time change between January and June?"

or:

"Did its quality performance deteriorate before a maintenance intervention?"

That is the difference between simply storing industrial data and actually analyzing it.

---

2. Meet the fictional factory

For this tutorial, we will use a synthetic manufacturing environment.

The company is called Atlas Components Manufacturing (ACM).

The factory contains:

• 3 production lines
• 12 industrial machines
• 5 products
• 1,143 production orders
• 17,167 production events
• 82 downtime events
• 8 maintenance interventions
• 1,143 quality inspections
• 2,172 energy measurements

The data covers a coherent six-month manufacturing scenario.

The objective is not to memorize these numbers.

The objective is to ask questions of the database.

---

3. From machines to production orders: understanding the data

The database is organized around several industrial entities.

At the center we have production orders.

A production order tells us what the factory planned to produce and what actually happened.

Machines generate production events.

Machines can also experience downtime.

Maintenance teams record interventions.

Quality inspections measure whether production meets requirements.

Energy measurements allow us to study machine consumption.

Conceptually:

Production Lines
       │
       ├── Machines
       │      │
       │      ├── Production Events
       │      ├── Downtime Events
       │      ├── Maintenance Events
       │      └── Energy Consumption
       │
       └── Production Orders
                │
                └── Quality Inspections

This is where database design becomes important.

The tables are not isolated CSV files.

They are related entities describing the same manufacturing system.

---

4. Setting up PostgreSQL

The exercises use PostgreSQL 17.

Once the database is loaded, we can select the industrial schema:

SET search_path TO smart_factory;

We can then inspect the available tables:

\dt

You should see tables such as:

production_lines
machines
products
production_orders
production_events
downtime_events
maintenance_events
quality_inspections
energy_consumption

This is already an important SQL lesson:

Before writing complicated queries, understand the structure of your database.

---

5. Your first manufacturing SQL query

Let's start with a simple business question:

How much production did each production line achieve?

We can answer it with:

SELECT
    pl.line_code,
    pl.line_name,
    SUM(po.actual_quantity) AS actual_quantity
FROM production_orders po
JOIN production_lines pl
    ON pl.line_id = po.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY actual_quantity DESC;

The important part is not simply the syntax.

Look at what the query does:

1. joins production orders with production lines;
2. groups the orders by production line;
3. calculates the total actual production;
4. sorts the result.

This is a very common pattern in industrial analytics:

JOIN
  ↓
GROUP BY
  ↓
AGGREGATE
  ↓
ORDER BY

---

6. Which production line performs best?

Now we can move from raw production volume to performance.

A line producing more units is not necessarily performing better.

We need additional indicators.

For example:

• planned quantity;
• actual quantity;
• rejected quantity;
• fulfillment rate;
• reject rate.

This is where SQL becomes much more interesting.

We are no longer asking:

"How many parts were produced?"

We are asking:

"How effectively did the factory produce them?"

That distinction is fundamental in manufacturing analytics.

---

7. What happens when machines stop?

Production performance is strongly influenced by machine availability.

Let's investigate downtime.

SELECT
    m.machine_code,
    m.machine_name,
    COUNT(d.downtime_id) AS events,
    ROUND(
        SUM(
            EXTRACT(
                EPOCH FROM (d.end_time - d.start_time)
            ) / 60.0
        )::numeric,
        2
    ) AS downtime_minutes
FROM machines m
LEFT JOIN downtime_events d
    ON d.machine_id = m.machine_id
GROUP BY
    m.machine_code,
    m.machine_name
ORDER BY downtime_minutes DESC;

This query introduces another important Industrial SQL concept:

time-based analysis.

Instead of simply counting downtime events, we calculate their duration.

A machine with 20 short stops may have a smaller production impact than a machine with only 5 very long stops.

Therefore:

Event count alone is not enough.

We need to understand the duration and context of each event.

---

8. From production data to industrial insight

At this point, we have several dimensions:

Production
    │
    ├── Machine performance
    │
    ├── Downtime
    │
    ├── Quality
    │
    ├── Maintenance
    │
    └── Energy

The real power of Industrial SQL appears when we connect these dimensions.

For example:

Does increasing cycle time coincide with increasing rejects?

Or:

Does downtime increase before a corrective maintenance intervention?

Or:

Does energy consumption increase while production performance decreases?

These are no longer basic SQL exercises.

They are industrial investigations.

---

9. Your first Smart Factory SQL challenge

Now it's your turn.

The production manager reports that one production area appears to be underperforming.

You have access to the complete manufacturing database.

Your mission is to investigate.

Challenge

Use SQL to:

1. identify the underperforming production line;
2. compare planned and actual production;
3. calculate reject rates;
4. identify the machines associated with the line;
5. analyze machine downtime;
6. investigate cycle-time evolution;
7. analyze quality inspections;
8. investigate energy consumption;
9. inspect maintenance history;
10. determine whether performance changed after maintenance.

Do not look for a predefined answer.

Let the data lead you to the conclusion.

---

10. What to explore next

This investigation is only the beginning.

Once you are comfortable with basic SQL, you can start using more advanced techniques:

• CASE
• CTEs
• window functions
• LAG()
• LEAD()
• rolling averages
• ranking
• time-series analysis
• anomaly detection
• machine-level KPIs

These techniques allow us to move from simple reporting toward industrial data analytics.

---

Ready to work with the complete dataset?

The Smart Factory Manufacturing Dataset v0.1 contains the complete synthetic manufacturing environment designed for hands-on Industrial SQL practice.

You get:

• PostgreSQL 17 database dump
• 9 CSV datasets
• Production data
• Machine data
• Downtime data
• Maintenance data
• Quality data
• Energy data
• Data Dictionary
• Database Schema
• Starter SQL queries
• Industrial investigation challenge

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

---

Smart Factory Manufacturing Dataset v0.1

PostgreSQL 17 • Industrial SQL • Industry 4.0 • Manufacturing Analytics