SQL for Smart Factories: Your First Manufacturing Database

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

What if your SQL practice database was not about customers, movies, employees or fictional sales?

What if you could use SQL to investigate what is happening inside a factory?

This is the idea behind this series: Industrial SQL for Smart Factories.

In this first article, we will discover a synthetic manufacturing environment built with PostgreSQL 17 and learn how relational database concepts can be applied to real manufacturing questions.

---

Why SQL for a Smart Factory?

A modern manufacturing environment produces data continuously.

Production orders are created, machines produce parts, quality inspections are performed, downtime events occur, maintenance interventions are recorded and energy consumption is measured.

The challenge is not simply to store this information.

The real challenge is to connect the data and extract useful information from it.

This is where SQL becomes extremely useful.

Industrial SQL means using SQL not only to retrieve records, but to investigate production performance, machine behavior, quality, maintenance and other industrial KPIs.

From Factory Operations to Database Tables

Let's imagine a fictional manufacturing company operating three production lines.

Each line contains several industrial machines. Products are manufactured through production orders, and the factory records production, quality, downtime, maintenance and energy information.

Instead of keeping everything in one enormous table, we separate the information into related entities.

The main entities

• Production lines — where manufacturing takes place.
• Machines — industrial equipment used for production.
• Products — products manufactured by the factory.
• Production orders — planned manufacturing operations.
• Production events — actual production activity.
• Downtime events — periods when machines are unavailable.
• Quality inspections — inspection and defect information.
• Maintenance events — preventive and corrective interventions.
• Energy consumption — machine energy measurements.

The Manufacturing Database

The complete Smart Factory database contains nine core tables. Together, they represent a simplified but realistic manufacturing information system.

Table	Purpose
production_lines	Manufacturing lines
machines	Industrial machines
products	Manufactured products
production_orders	Production planning and results
production_events	Production activity
downtime_events	Machine downtime
maintenance_events	Maintenance interventions
quality_inspections	Quality inspections
energy_consumption	Machine energy measurements

📊 The Dataset at a Glance

The complete dataset 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

That gives us more than 20,000 industrial observations to investigate.

Important: The manufacturing environment is synthetic. It is designed for education, SQL practice, demonstrations and industrial analytics exercises.

🔗 How the Data Fits Together

One of the most important concepts in industrial databases is that information is distributed across multiple related tables.

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

Products
      │
      └── Production Orders
              │
              ├── Production Events
              └── Quality Inspections

This structure is what allows us to ask much more interesting questions than simply:

How many rows are in the database?

Instead, we can ask:

Which production line is performing best?

Which machine generates the most downtime?

Which production line has the highest rejection rate?

Is machine performance changing over time?

Does maintenance appear to improve machine performance?

Why PostgreSQL?

PostgreSQL is an excellent database engine for this type of educational project because it provides a powerful relational SQL environment while remaining open source and widely used.

In this series, we use PostgreSQL 17.

You can work with PostgreSQL using tools such as:

• psql
• pgAdmin
• DBeaver
• other PostgreSQL-compatible database clients

Your First Industrial SQL Query

Let's start with a simple but meaningful question:

Which production line achieved the highest actual production?

We need information from two tables: production_orders and production_lines.

SET search_path TO smart_factory;

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;

What is happening here?

Let's break the query down.

1. Select the schema

SET search_path TO smart_factory;

This tells PostgreSQL to use the smart_factory schema by default.

2. Join production orders with production lines

JOIN production_lines pl
    ON pl.line_id = po.line_id

The production order contains the production line identifier. The JOIN allows us to retrieve the human-readable line information.

3. Aggregate production

SUM(po.actual_quantity)

We calculate the total actual production for each line.

4. Group the results

GROUP BY
    pl.line_code,
    pl.line_name

The aggregation is performed independently for each production line.

5. Rank the lines

ORDER BY actual_quantity DESC;

The line with the highest actual production appears first.

🧠 What Did We Just Learn?

This first query already introduces several fundamental SQL concepts:

• SELECT
• JOIN
• SUM()
• GROUP BY
• ORDER BY

More importantly, we used these SQL concepts to answer an actual manufacturing question.

This is the philosophy of this series:
Learn the SQL concept, apply it to manufacturing data, and interpret the result from an industrial perspective.

🔍 What We Will Investigate

This first article is only the beginning.

Throughout the Smart Factory SQL series, we will progressively investigate:

• Production performance
• Production fulfillment
• Reject rates
• Machine downtime
• Machine cycle time
• Machine performance degradation
• Quality problems
• Maintenance effectiveness
• Energy consumption
• Manufacturing trends
• Industrial KPIs
• SQL window functions
• Anomaly detection
• Root-cause analysis

🧪 Your First Challenge

Now it's your turn.

Challenge: Modify the previous query to find the production line with the highest rejected quantity.

Then try to answer a second question:

Which production line has the highest rejection rate?

You will need more than one column to answer the second question. We will explore this type of manufacturing KPI in the next articles.

Continue the Series

This article is the starting point of the Smart Factory SQL Series.

Next, we will go deeper into the database model and understand how a manufacturing process is translated into a relational PostgreSQL schema.

Next article: How to Design a Manufacturing Database with PostgreSQL

Want to Practice with the Complete Dataset?

Smart Factory Manufacturing Dataset v0.1

A complete synthetic manufacturing database for learning PostgreSQL, Industrial SQL and Smart Factory analytics.

Includes production, machines, downtime, quality, maintenance, energy data, PostgreSQL SQL scripts, CSV files and documentation.

$9

Get the Complete Dataset →

🔗 Related Resources

• Smart Factory SQL — GitHub Repository
• Smart Factory Manufacturing Dataset — Gumroad

---

Data note: The manufacturing environment used in this series is synthetic and intended for educational, training and demonstration purposes.