Python and SQL for Manufacturing: Hands-On Training¶

Demo Notebook 4: Capstone — SQL + Python + Charts Mini-Report¶

Python SQLite Matplotlib Jupyter License: MIT Author GitHub

Author: Prakash Ukhalkar
Role: Assistant Professor (MCA) | Researcher in Data Science and Machine Learning

Notebook Scope: Bring together everything from Notebooks 1–3 — connect to SQLite, query with SQL, visualize with Matplotlib, and export a summary report to CSV.

This capstone notebook follows a real-world mini-pipeline:

SQLite DB  →  SQL Query  →  Python  →  Chart  →  CSV Report

By the end of this notebook you will be able to:

  • Query and summarise data across multiple tables
  • Identify the top earners per department
  • Build a combined summary chart
  • Export a report to a CSV file

Prerequisite: Complete day1_emp_dept.ipynb, day2_querying.ipynb, and day3_visualization.ipynb first.

Step 1 — Import libraries and connect¶

In [1]:
import sqlite3
import csv
import matplotlib.pyplot as plt

conn   = sqlite3.connect('company.db')
cursor = conn.cursor()

print('Connected to company.db')

Connected to company.db

Step 2 — Full employee roster (SQL + JOIN)¶

Pull every employee along with their department name and location — our master data view.

In [2]:
roster = cursor.execute('''
    SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    ORDER  BY d.dname, e.sal DESC
''').fetchall()

print(f'Total employees: {len(roster)}')
print()
print(f'{"Emp#":<6} {"Name":<8} {"Job":<11} {"Salary":>7}  {"Dept":<12} {"Location"}')
print('-' * 60)
for r in roster:
    print(f'{r[0]:<6} {r[1]:<8} {r[2]:<11} {r[3]:>7}  {r[4]:<12} {r[5]}')

Total employees: 14

Emp#   Name     Job          Salary  Dept         Location
------------------------------------------------------------
7839   KING     PRESIDENT    5000.0  ACCOUNTING   NEW YORK
7782   CLARK    MANAGER      2450.0  ACCOUNTING   NEW YORK
7934   MILLER   CLERK        1300.0  ACCOUNTING   NEW YORK
7788   SCOTT    ANALYST      3000.0  RESEARCH     DALLAS
7902   FORD     ANALYST      3000.0  RESEARCH     DALLAS
7566   JONES    MANAGER      2975.0  RESEARCH     DALLAS
7876   ADAMS    CLERK        1100.0  RESEARCH     DALLAS
7369   SMITH    CLERK         900.0  RESEARCH     DALLAS
7698   BLAKE    MANAGER      2850.0  SALES        CHICAGO
7499   ALLEN    SALESMAN     1600.0  SALES        CHICAGO
7844   TURNER   SALESMAN     1500.0  SALES        CHICAGO
7521   WARD     SALESMAN     1250.0  SALES        CHICAGO
7654   MARTIN   SALESMAN     1250.0  SALES        CHICAGO
7900   JAMES    CLERK         950.0  SALES        CHICAGO

Step 3 — Department summary (GROUP BY + aggregates)¶

For each department: headcount, total salary, and average salary.

In [3]:
summary = cursor.execute('''
    SELECT d.dname,
           COUNT(e.empno)  AS headcount,
           SUM(e.sal)      AS total_sal,
           AVG(e.sal)      AS avg_sal,
           MAX(e.sal)      AS top_sal
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    GROUP  BY d.dname
    ORDER  BY d.dname
''').fetchall()

print(f'{"Department":<14} {"Headcount":>10} {"Total Sal":>10} {"Avg Sal":>9} {"Top Sal":>9}')
print('-' * 58)
for r in summary:
    print(f'{r[0]:<14} {r[1]:>10} {r[2]:>10.0f} {r[3]:>9.2f} {r[4]:>9.0f}')

Department      Headcount  Total Sal   Avg Sal   Top Sal
----------------------------------------------------------
ACCOUNTING              3       8750   2916.67      5000
RESEARCH                5      10975   2195.00      3000
SALES                   6       9400   1566.67      2850

Step 4 — Top earner per department (Subquery)¶

For each department, find the employee with the highest salary.

In [4]:
top_earners = cursor.execute('''
    SELECT d.dname, e.ename, e.job, e.sal
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    WHERE  e.sal = (
        SELECT MAX(e2.sal)
        FROM   emp AS e2
        WHERE  e2.deptno = e.deptno
    )
    ORDER  BY d.dname
''').fetchall()

print('Top earner per department:')
print(f'{"Department":<14} {"Employee":<8} {"Job":<11} {"Salary":>7}')
print('-' * 44)
for r in top_earners:
    print(f'{r[0]:<14} {r[1]:<8} {r[2]:<11} {r[3]:>7}')

Top earner per department:
Department     Employee Job          Salary
--------------------------------------------
ACCOUNTING     KING     PRESIDENT    5000.0
RESEARCH       SCOTT    ANALYST      3000.0
RESEARCH       FORD     ANALYST      3000.0
SALES          BLAKE    MANAGER      2850.0

Step 5 — Employees above the company average salary¶

A quick check on who earns above the mean.

In [5]:
above_avg = cursor.execute('''
    SELECT ename, job, sal
    FROM   emp
    WHERE  sal > (SELECT AVG(sal) FROM emp)
    ORDER  BY sal DESC
''').fetchall()

company_avg = cursor.execute('SELECT AVG(sal) FROM emp').fetchone()[0]
print(f'Company average salary: {company_avg:.2f}')
print()
print('Employees above average:')
for r in above_avg:
    print(f'  {r[0]:<8} {r[1]:<11} {r[2]}')

Company average salary: 2080.36

Employees above average:
  KING     PRESIDENT   5000.0
  SCOTT    ANALYST     3000.0
  FORD     ANALYST     3000.0
  JONES    MANAGER     2975.0
  BLAKE    MANAGER     2850.0
  CLARK    MANAGER     2450.0

Step 6 — Combined chart: Headcount and Average Salary side by side¶

Two subplots in one figure — a common pattern in management reports.

In [6]:
depts     = [r[0] for r in summary]
headcount = [r[1] for r in summary]
avg_sal   = [r[3] for r in summary]

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(11, 4))

# Left: headcount
ax1.bar(depts, headcount, color='steelblue')
ax1.set_title('Headcount by Department')
ax1.set_xlabel('Department')
ax1.set_ylabel('Number of Employees')

# Right: average salary
ax2.bar(depts, avg_sal, color='darkorange')
ax2.set_title('Average Salary by Department')
ax2.set_xlabel('Department')
ax2.set_ylabel('Average Salary')

plt.suptitle('Department Overview', fontsize=13, fontweight='bold')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 7 — Export the department summary to CSV¶

Saving results to a CSV file is useful for sharing reports or loading into Excel.

In [7]:
csv_file = 'department_summary.csv'

with open(csv_file, 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['Department', 'Headcount', 'Total Salary', 'Avg Salary', 'Top Salary'])
    for r in summary:
        writer.writerow([r[0], r[1], round(r[2], 2), round(r[3], 2), round(r[4], 2)])

print(f'Saved to {csv_file}')

Saved to department_summary.csv
In [8]:
# Read the file back to verify
with open(csv_file) as f:
    for line in f:
        print(line.rstrip())

Department,Headcount,Total Salary,Avg Salary,Top Salary
ACCOUNTING,3,8750.0,2916.67,5000.0
RESEARCH,5,10975.0,2195.0,3000.0
SALES,6,9400.0,1566.67,2850.0

Step 8 — Close the connection¶

In [9]:
conn.close()
print('Done! Report saved to department_summary.csv')

Done! Report saved to department_summary.csv

Summary — Full Pipeline¶

Step What happened
Connect sqlite3.connect('company.db')
Full roster SELECT ... JOIN dept
Department summary GROUP BY with COUNT, SUM, AVG, MAX
Top earner per dept Correlated subquery with MAX
Above-average filter WHERE sal > (SELECT AVG ...)
Combined chart plt.subplots(1, 2) with two bar charts
Export csv.writer to department_summary.csv

Previous: day3_visualization.ipynb — bar, pie, line, and scatter charts.

About This Material¶
Author Prakash Ukhalkar
Role Assistant Professor (MCA) · Researcher in Data Science and Machine Learning
GitHub @prakash-ukhalkar
Repository mfg-python-sql-training
License MIT

Provided for educational use in manufacturing analytics training programmes.
For questions, corrections, or contributions, open an issue or pull request on GitHub.

© 2026 Prakash Ukhalkar · Python and SQL for Manufacturing · MIT License