Python and SQL for Manufacturing: Hands-On Training¶

Demo Notebook 3: Visualizing Data with Python¶

Python SQLite Matplotlib Jupyter License: MIT Author GitHub

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

Notebook Scope: Query data from the EMP/DEPT database and turn it into charts — bar, pie, line, scatter, histogram, and horizontal bar — using Matplotlib.

We'll continue using company.db created in Demo Notebook 1.

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

  • Pull data from SQLite directly into Python lists
  • Draw a bar chart
  • Draw a pie chart
  • Draw a line chart
  • Draw a scatter plot
  • Draw a histogram
  • Draw a horizontal bar chart

Prerequisite: Complete day1_emp_dept.ipynb first so company.db exists.

Step 1 - Import libraries and connect¶

In [17]:
import sqlite3
import matplotlib.pyplot as plt

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

print('Connected to company.db')

Connected to company.db

Step 2 - Pull data from SQL into Python lists¶

Charts need plain Python lists. We query the data and unpack each column into its own list using a simple for loop (or a list comprehension — see the alternate approach below).

We want: average salary per department, joined with the department name.

In [18]:
rows = cursor.execute('''
    SELECT d.dname, AVG(e.sal)
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    GROUP  BY d.dname
    ORDER  BY d.dname
''').fetchall()

dept_names   = []
avg_salaries = []

for row in rows:
    dept_names.append(row[0])
    avg_salaries.append(row[1])

print('Departments:', dept_names)
print('Avg Salaries:', avg_salaries)

Departments: ['ACCOUNTING', 'RESEARCH', 'SALES']
Avg Salaries: [2916.6666666666665, 2195.0, 1566.6666666666667]
Alternate approach - Unpack into two separate lists¶
dept_names   = [row[0] for row in rows]
avg_salaries = [row[1] for row in rows]

Step 3 - Bar chart: Average salary per department¶

A bar chart is the simplest way to compare one number across several categories.

In [19]:
plt.figure(figsize=(7, 4))
plt.bar(dept_names, avg_salaries, color='steelblue')
plt.title('Average Salary by Department')
plt.xlabel('Department')
plt.ylabel('Average Salary')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 4 - Pie chart: Headcount by job¶

A pie chart shows proportions - what fraction of the workforce each job represents.

In [20]:
rows = cursor.execute(
    'SELECT job, COUNT(*) FROM emp GROUP BY job ORDER BY job'
).fetchall()

jobs   = []
counts = []

for row in rows:
    jobs.append(row[0])
    counts.append(row[1])

print(jobs)
print(counts)

['ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN']
[2, 4, 3, 1, 4]
In [21]:
plt.figure(figsize=(6, 6))
# autopct='%1.0f%%' adds percentage labels to the pie slices
# startangle=90 rotates the pie chart so the first slice starts at the top
plt.pie(counts, labels=jobs, autopct='%1.0f%%', startangle=90)
plt.title('Employee Headcount by Job')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 5 - Line chart: Salary sorted lowest to highest¶

A line chart shows a trend across an ordered sequence. Here we rank employees by salary to see the distribution shape.

In [22]:
rows = cursor.execute(
    'SELECT ename, sal FROM emp ORDER BY sal ASC'
).fetchall()

names    = []
salaries = []

for row in rows:
    names.append(row[0])
    salaries.append(row[1])

# Alternate approach
# names    = [row[0] for row in rows]
# salaries = [row[1] for row in rows]
print(names)
print(salaries)

['SMITH', 'JAMES', 'ADAMS', 'WARD', 'MARTIN', 'MILLER', 'TURNER', 'ALLEN', 'CLARK', 'BLAKE', 'JONES', 'SCOTT', 'FORD', 'KING']
[900.0, 950.0, 1100.0, 1250.0, 1250.0, 1300.0, 1500.0, 1600.0, 2450.0, 2850.0, 2975.0, 3000.0, 3000.0, 5000.0]
In [23]:
plt.figure(figsize=(10, 4))
plt.plot(names, salaries, marker='o', color='darkorange')
plt.title('Employee Salaries (Sorted Lowest to Highest)')
plt.xlabel('Employee')
plt.ylabel('Salary')
plt.xticks(rotation=45, ha='right')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 6 - Scatter plot: Employee number vs Salary¶

A scatter plot shows the relationship between two numeric columns. Here we check whether earlier hires (lower empno) tend to earn more.

In [24]:
rows = cursor.execute(
    'SELECT empno, sal FROM emp ORDER BY empno'
).fetchall()

empnos   = []
salaries = []

for row in rows:
    empnos.append(row[0])
    salaries.append(row[1])

print(empnos)
print(salaries)

[7369, 7499, 7521, 7566, 7654, 7698, 7782, 7788, 7839, 7844, 7876, 7900, 7902, 7934]
[900.0, 1600.0, 1250.0, 2975.0, 1250.0, 2850.0, 2450.0, 3000.0, 5000.0, 1500.0, 1100.0, 950.0, 3000.0, 1300.0]
In [25]:
plt.figure(figsize=(7, 4))
plt.scatter(empnos, salaries, color='seagreen', s=80)
plt.title('Employee Number vs Salary')
plt.xlabel('Employee Number (empno)')
plt.ylabel('Salary')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 7 - Histogram: Salary distribution¶

A histogram groups numeric values into bins and shows how many items fall in each bin. Here it reveals whether salaries cluster around a midpoint or spread widely.

In [26]:
rows = cursor.execute(
    'SELECT sal FROM emp ORDER BY sal'
).fetchall()

all_salaries = []

for row in rows:
    all_salaries.append(row[0])

print('Salaries:', all_salaries)

Salaries: [900.0, 950.0, 1100.0, 1250.0, 1250.0, 1300.0, 1500.0, 1600.0, 2450.0, 2850.0, 2975.0, 3000.0, 3000.0, 5000.0]
In [27]:
plt.figure(figsize=(7, 4))
# bins=6 splits the salary range into 6 equal-width buckets
plt.hist(all_salaries, bins=6, color='mediumpurple', edgecolor='white')
plt.title('Salary Distribution (Histogram)')
plt.xlabel('Salary')
plt.ylabel('Number of Employees')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 8 - Horizontal bar chart: Total salary bill per department¶

A horizontal bar chart works well when category labels are long. Here we compare total salary expenditure across departments - each bar grows to the right, making the labels easy to read.

In [28]:
rows = cursor.execute('''
    SELECT d.dname, SUM(e.sal)
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    GROUP  BY d.dname
    ORDER  BY SUM(e.sal) DESC
''').fetchall()

dept_names   = []
total_sal    = []

for row in rows:
    dept_names.append(row[0])
    total_sal.append(row[1])

print('Departments:', dept_names)
print('Total Salaries:', total_sal)

Departments: ['RESEARCH', 'SALES', 'ACCOUNTING']
Total Salaries: [10975.0, 9400.0, 8750.0]
In [29]:
plt.figure(figsize=(7, 4))
# barh draws bars horizontally; y-axis holds categories, x-axis holds values
plt.barh(dept_names, total_sal, color='tomato')
plt.title('Total Salary Bill by Department')
plt.xlabel('Total Salary')
plt.ylabel('Department')
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 9 - Close the connection¶

In [30]:
conn.close()
print('Done!')

Done!

Summary¶

Chart type When to use it
Bar chart Compare one value across categories
Pie chart Show proportions of a whole
Line chart Show trend across an ordered sequence
Scatter plot Show relationship between two numeric columns
Histogram Show the frequency distribution of a numeric column
Horizontal bar chart Compare values when category labels are long

Next: day4_capstone.ipynb — put SQL + Python + charts together into one mini-report.

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