Python and SQL for Manufacturing: Hands-On Training

Demo Notebook 2: Querying Data with SQL

---

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

Notebook Scope: Learn how to query and filter data using SELECT, WHERE, ORDER BY, GROUP BY, JOIN, and subqueries against the classic EMP and DEPT tables.

---

We'll use the company.db database created in Demo Notebook 1.

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

• Pick specific columns with SELECT
• Filter rows with WHERE
• Sort results with ORDER BY
• Summarise data with GROUP BY and aggregate functions
• Combine tables with JOIN
• Use a basic subquery

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

---

Step 1 — Connect to the database

import sqlite3

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

print('Connected to company.db')

Step 2 — SELECT: pick specific columns

Instead of SELECT * (all columns), you can name exactly the columns you want.

print('Employee names and their jobs:')
for row in cursor.execute('SELECT ename, job FROM emp'):
    print(row)

Step 3 — WHERE: filter rows

WHERE lets you keep only the rows that match a condition.

# Employees who are MANAGERS
print('Managers:')
for row in cursor.execute("SELECT ename, sal FROM emp WHERE job = 'MANAGER'"):
    print(row)

# Employees earning more than 2000
print('High earners (sal > 2000):')
for row in cursor.execute('SELECT ename, job, sal FROM emp WHERE sal > 2000'):
    print(row)

# Combine two conditions with AND
print('Salespeople earning more than 1400:')
for row in cursor.execute("SELECT ename, sal FROM emp WHERE job = 'SALESMAN' AND sal > 1400"):
    print(row)

Step 4 — ORDER BY: sort the results

ASC = smallest first (default). DESC = largest first.

# Sort by salary, lowest first
print('All employees sorted by salary (lowest first):')
for row in cursor.execute('SELECT ename, sal FROM emp ORDER BY sal ASC'):
    print(row)

# Sort by salary, highest first
print('All employees sorted by salary (highest first):')
for row in cursor.execute('SELECT ename, sal FROM emp ORDER BY sal DESC'):
    print(row)

Step 5 — Aggregate functions: COUNT, SUM, AVG, MIN, MAX

These functions collapse many rows into a single summary number.

count = cursor.execute('SELECT COUNT(*) FROM emp').fetchone()[0]
print(f'Total employees: {count}')

total_salary = cursor.execute('SELECT SUM(sal) FROM emp').fetchone()[0]
print(f'Total salary bill: {total_salary}')

avg_salary = cursor.execute('SELECT AVG(sal) FROM emp').fetchone()[0]
print(f'Average salary: {avg_salary:.2f}')

row = cursor.execute('SELECT MIN(sal), MAX(sal) FROM emp').fetchone()
print(f'Lowest salary: {row[0]}   Highest salary: {row[1]}')

Step 6 — GROUP BY: summarise per group

GROUP BY splits the rows into groups and applies an aggregate to each group separately.

# How many employees are in each job?
print('Headcount by job:')
for row in cursor.execute('SELECT job, COUNT(*) AS headcount FROM emp GROUP BY job ORDER BY headcount ASC'):
    print(row)

# Average salary per department
print('Average salary per department:')
for row in cursor.execute('SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno ORDER BY deptno'):
    print(f'Dept {row[0]}: {row[1]:.2f}')

Step 7 — HAVING: filter groups

WHERE filters individual rows before grouping.
HAVING filters groups after the aggregation is done.

# Only show departments with more than 3 employees
print('Departments with more than 3 employees:')
for row in cursor.execute(
    'SELECT deptno, COUNT(*) AS headcount FROM emp GROUP BY deptno HAVING headcount > 3'
):
    print(row)

Step 8 — JOIN: combine EMP and DEPT

Right now the EMP table only stores the department number (e.g. 30).
A JOIN lets us pull in the department name and location from the DEPT table.

We match rows where emp.deptno = dept.deptno.

print('Employee  |  Job       |  Department  |  Location')
print('-' * 55)
for row in cursor.execute('''
    SELECT e.ename, e.job, d.dname, d.loc
    FROM   emp  AS e
    JOIN   dept AS d ON e.deptno = d.deptno
    ORDER  BY d.dname, e.ename
'''):
    print(f'{row[0]:<10}  {row[1]:<11}  {row[2]:<13}  {row[3]}')

Step 9 — Subquery: use one query inside another

A subquery runs first and its result is used by the outer query.

Example: Find all employees who earn more than the average salary.

print('Employees earning above the company average:')
for row in cursor.execute('''
    SELECT ename, sal
    FROM   emp
    WHERE  sal > (SELECT AVG(sal) FROM emp)
    ORDER  BY sal DESC
'''):
    print(f'{row[0]}: {row[1]}')

Step 10 — Close the connection

conn.close()
print('Done!')

---

Summary

What you did	SQL used
Pick columns	SELECT col1, col2 FROM table
Filter rows	WHERE condition
Sort results	ORDER BY col ASC / DESC
Count / total / average	COUNT(*), SUM(col), AVG(col)
Summarise by group	GROUP BY col
Filter groups	HAVING condition
Combine two tables	JOIN ... ON ...
Nest a query	WHERE col > (SELECT ...)

Previous: day1_emp_dept.ipynb — CREATE, INSERT, UPDATE, DELETE.

---

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}