Python and SQL for Manufacturing: Hands-On Training¶

SQL Practice Notebook: EMP & DEPT Queries¶

Python SQLite Jupyter License: MIT Author GitHub

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

Notebook Scope: Practice SQL queries covering SELECT, WHERE, ORDER BY, operators, aggregate functions, GROUP BY, HAVING, JOINs, and subqueries using the classic EMP and DEPT tables.

Assumed Tables:

Table Columns
EMP Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
DEPT Deptno, Dname, Loc

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

Setup — Connect to the database¶

In [1]:
import sqlite3

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

print('Connected to company.db')

Connected to company.db

1) Select Entire Table¶

Question: Display all employee records.

In [ ]:
# SQL Query:
# ---------
# SELECT * FROM EMP;
In [7]:
# Q: Display all employee records.
query = '''
    SELECT * FROM emp
'''

cursor.execute(query).fetchall()
Out[7]:
[(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 900.0, None, 20),
 (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300.0, 30),
 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500.0, 30),
 (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.0, None, 20),
 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400.0, 30),
 (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.0, None, 30),
 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.0, None, 10),
 (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.0, None, 20),
 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10),
 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, 0.0, 30),
 (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.0, None, 20),
 (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.0, None, 30),
 (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.0, None, 20),
 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, None, 10)]

Question: Display all department records.

In [ ]:
# SQL Query:
# ---------
# SELECT * FROM DEPT;
In [8]:
# Q: Display all department records.
query = '''
    SELECT * FROM dept
'''

cursor.execute(query).fetchall()
Out[8]:
[(10, 'ACCOUNTING', 'NEW YORK'),
 (20, 'RESEARCH', 'DALLAS'),
 (30, 'SALES', 'CHICAGO'),
 (40, 'OPERATIONS', 'BOSTON')]

2) Select Specific Columns¶

Question: Display employee names and salaries.

In [ ]:
# SQL Query:
# ---------
# SELECT Ename, Sal
# FROM EMP;
In [12]:
# Q: Display employee names and salaries.
query = '''
    SELECT ename, sal, deptno
    FROM emp
    WHERE deptno in (10, 20)
'''

cursor.execute(query).fetchall()
Out[12]:
[('SMITH', 900.0, 20),
 ('JONES', 2975.0, 20),
 ('CLARK', 2450.0, 10),
 ('SCOTT', 3000.0, 20),
 ('KING', 5000.0, 10),
 ('ADAMS', 1100.0, 20),
 ('FORD', 3000.0, 20),
 ('MILLER', 1300.0, 10)]

Question: Display department names and locations.

In [ ]:
# SQL Query:
# ---------
# SELECT Dname, Loc
# FROM DEPT;
In [13]:
# Q: Display department names and locations.
query = '''
    SELECT dname, loc
    FROM dept
'''

cursor.execute(query).fetchall()
Out[13]:
[('ACCOUNTING', 'NEW YORK'),
 ('RESEARCH', 'DALLAS'),
 ('SALES', 'CHICAGO'),
 ('OPERATIONS', 'BOSTON')]

3) Select Specific Rows (WHERE)¶

Question: Show employees working in department 10.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Deptno = 10;
In [14]:
# Q: Show employees working in department 10.
query = '''
    SELECT *
    FROM emp
    WHERE deptno = 10
'''

cursor.execute(query).fetchall()
Out[14]:
[(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.0, None, 10),
 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10),
 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, None, 10)]

Question: Show employees whose salary is greater than 3000.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Sal > 3000;
In [17]:
# Q: Show employees whose salary is greater than 3000.
query = '''
    SELECT *
    FROM emp
    WHERE sal > 3000
'''

cursor.execute(query).fetchall()
Out[17]:
[(7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10)]

4) ORDER BY¶

Question: Display employees sorted by salary (ascending).

In [ ]:
# SQL Query:
# ---------
# SELECT Ename, Sal
# FROM EMP
# ORDER BY Sal ASC;
In [22]:
# Q: Display employees sorted by salary (ascending).
query = '''
    SELECT ename, sal
    FROM emp
    ORDER BY sal DESC
'''

cursor.execute(query).fetchall()
Out[22]:
[('KING', 5000.0),
 ('SCOTT', 3000.0),
 ('FORD', 3000.0),
 ('JONES', 2975.0),
 ('BLAKE', 2850.0),
 ('CLARK', 2450.0),
 ('ALLEN', 1600.0),
 ('TURNER', 1500.0),
 ('MILLER', 1300.0),
 ('WARD', 1250.0),
 ('MARTIN', 1250.0),
 ('ADAMS', 1100.0),
 ('JAMES', 950.0),
 ('SMITH', 900.0)]

Question: Display employees sorted by name (descending).

In [ ]:
# SQL Query:
# ---------
# SELECT Ename
# FROM EMP
# ORDER BY Ename DESC;
In [ ]:
# Q: Display employees sorted by name (descending).
query = '''
    SELECT ename
    FROM emp
    ORDER BY ename DESC
'''

cursor.execute(query).fetchall()

Conditional Operators : >, >=, <, <=, == (=), != (<>)

Logical Operators: AND, OR, NOT

5) Using Operators¶

AND¶

Question: Employees in department 20 earning more than 2000.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Deptno = 20 AND Sal > 2000;

AND Logical Operator¶

TRUE AND TRUE = TRUE TRUE AND FALSE = FALSE FALSE AND TRUE = FALSE FALSE AND FALSE = FALSE

OR Logical Operator¶

TRUE OR TRUE = TRUE TRUE OR FALSE = TRUE FALSE OR TRUE = TRUE FALSE OR FALSE = FALSE

In [26]:
# Q: Employees in department 20 earning more than 2000.
query = '''
    SELECT *
    FROM emp
    WHERE deptno = 20 AND sal > 2000
'''

cursor.execute(query).fetchall()
Out[26]:
[(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.0, None, 20),
 (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.0, None, 20),
 (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.0, None, 20)]

OR¶

Question: Employees in department 10 or 30.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Deptno = 10 OR Deptno = 30;
In [29]:
# Q: Employees in department 10 or 30.
query = '''
    SELECT *
    FROM emp
    WHERE deptno = 10 OR deptno = 30
'''

cursor.execute(query).fetchall()
Out[29]:
[(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300.0, 30),
 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500.0, 30),
 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400.0, 30),
 (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.0, None, 30),
 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.0, None, 10),
 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10),
 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, 0.0, 30),
 (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.0, None, 30),
 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, None, 10)]
In [ ]:

, >=

BETWEEN¶

Question: Employees earning between 2000 and 4000.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Sal BETWEEN 2000 AND 4000;
In [ ]:
# Q: Employees earning between 2000 and 4000.
query = '''
    SELECT *
    FROM emp
    WHERE sal BETWEEN 2000 AND 4000
'''

cursor.execute(query).fetchall()

NOT¶

Question: Employees not working in department 10.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE NOT Deptno = 10;
In [ ]:
# Q: Employees not working in department 10.
query = '''
    SELECT *
    FROM emp
    WHERE deptno = 10 OR deptno = 30
'''

cursor.execute(query).fetchall()
Out[ ]:
[(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300.0, 30),
 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500.0, 30),
 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400.0, 30),
 (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.0, None, 30),
 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.0, None, 10),
 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10),
 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, 0.0, 30),
 (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.0, None, 30),
 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, None, 10)]

6) IN Operator¶

Question: Employees working in departments 10, 20, or 30.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Deptno IN (10,20,30);
In [36]:
# Q: Employees working in departments 10, 20, or 30.
query = '''
    SELECT *
    FROM emp
    WHERE deptno IN (10,20)
'''

cursor.execute(query).fetchall()
Out[36]:
[(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 900.0, None, 20),
 (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.0, None, 20),
 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.0, None, 10),
 (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.0, None, 20),
 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.0, None, 10),
 (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.0, None, 20),
 (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.0, None, 20),
 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, None, 10)]

7) NULL Values¶

Question: Employees with no commission.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Comm IS NULL;
In [38]:
# Q: Employees with no commission.
query = '''
    SELECT *
    FROM emp
    WHERE comm IS NOT NULL
'''

cursor.execute(query).fetchall()
Out[38]:
[(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300.0, 30),
 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500.0, 30),
 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400.0, 30),
 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, 0.0, 30)]

Question: Employees having commission.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Comm IS NOT NULL;
In [ ]:
# Q: Employees having commission.
query = '''
    SELECT *
    FROM emp
    WHERE comm IS NOT NULL
'''

cursor.execute(query).fetchall()

8) LIKE Operator¶

Question: Employees whose names start with 'S'.

% = for searching 0 or more characters, _ = for searching single character

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Ename LIKE 'S%';
In [ ]:
# Q: Employees whose names start with 'S'.
query = '''
    SELECT *
    FROM emp
    WHERE ename LIKE 'S%'
'''

cursor.execute(query).fetchall()

Question: Employees whose names end with 'N'.

In [ ]:
# SQL Query:
# ---------
# SELECT *
# FROM EMP
# WHERE Ename LIKE '%N';
In [39]:
# Q: Employees whose names end with 'N'.
query = '''
    SELECT *
    FROM emp
    WHERE ename LIKE '%N'
'''

cursor.execute(query).fetchall()
Out[39]:
[(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300.0, 30),
 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400.0, 30)]

9) Aggregate Functions¶

Question: Find total salary of all employees.

In [ ]:
# SQL Query:
# ---------
# SELECT SUM(Sal) AS Total_Salary
# FROM EMP;
In [40]:
# Q: Find total salary of all employees.
query = '''
    SELECT SUM(sal) AS Total_salary
    FROM emp
'''

cursor.execute(query).fetchall()
Out[40]:
[(29125.0,)]

Question: Find average salary.

In [ ]:
# SQL Query:
# ---------
# SELECT AVG(Sal) AS Avg_Salary
# FROM EMP;
In [41]:
# Q: Find average salary.
query = '''
    SELECT AVG(sal) AS Avg_salary
    FROM emp
'''

cursor.execute(query).fetchall()
Out[41]:
[(2080.3571428571427,)]

Question: Find maximum salary.

In [ ]:
# SQL Query:
# ---------
# SELECT MAX(Sal) AS Highest_Salary
# FROM EMP;
In [42]:
# Q: Find maximum salary.
query = '''
    SELECT MAX(sal) AS Highest_salary
    FROM emp
'''

cursor.execute(query).fetchall()
Out[42]:
[(5000.0,)]

Question: Count total employees.

In [ ]:
# SQL Query:
# ---------
# SELECT COUNT(*) AS Total_Employees
# FROM EMP;
In [46]:
# Q: Count total employees.
query = '''
    SELECT COUNT(*) AS total_employees
    FROM emp
'''

cursor.execute(query).fetchall()
Out[46]:
[(14,)]

10) GROUP BY¶

Question: Find total salary department-wise.

In [ ]:
# SQL Query:
# ---------
# SELECT Deptno, SUM(Sal)
# FROM EMP
# GROUP BY Deptno;
In [47]:
# Q: Find total salary department-wise.
query = '''
    SELECT deptno, SUM(sal)
    FROM emp
    GROUP BY deptno
'''

cursor.execute(query).fetchall()
Out[47]:
[(10, 8750.0), (20, 10975.0), (30, 9400.0)]

Question: Count employees in each job role.

In [ ]:
# SQL Query:
# ---------
# SELECT Job, COUNT(*)
# FROM EMP
# GROUP BY Job;
In [48]:
# Q: Count employees in each job role.
query = '''
    SELECT job, COUNT(*)
    FROM emp
    GROUP BY job
'''

cursor.execute(query).fetchall()
Out[48]:
[('ANALYST', 2),
 ('CLERK', 4),
 ('MANAGER', 3),
 ('PRESIDENT', 1),
 ('SALESMAN', 4)]

11) HAVING Clause¶

Question: Departments having total salary greater than 5000.

In [ ]:
# SQL Query:
# ---------
# SELECT Deptno, SUM(Sal)
# FROM EMP
# GROUP BY Deptno
# HAVING SUM(Sal) > 5000;
In [ ]:
# Q: Departments having total salary greater than 5000.
query = '''
    SELECT deptno, SUM(sal)
    FROM emp
    GROUP BY deptno
    HAVING SUM(sal) > 5000
'''

cursor.execute(query).fetchall()

12) INNER JOIN¶

Question: Display employee names with department names.

In [ ]:
# SQL Query:
# ---------
# SELECT E.Ename, D.Dname
# FROM EMP E
# INNER JOIN DEPT D
# ON E.Deptno = D.Deptno;
In [ ]:
# Q: Display employee names with department names.
query = '''
    SELECT E.ename, D.dname
    FROM emp E
    INNER JOIN dept D
    ON E.deptno = D.deptno
'''

cursor.execute(query).fetchall()

13) LEFT JOIN¶

Question: Show all employees with department details.

In [ ]:
# SQL Query:
# ---------
# SELECT E.Ename, D.Dname
# FROM EMP E
# LEFT JOIN DEPT D
# ON E.Deptno = D.Deptno;
In [ ]:
# Q: Show all employees with department details.
query = '''
    SELECT E.ename, D.dname
    FROM emp E
    LEFT JOIN dept D
    ON E.deptno = D.deptno
'''

cursor.execute(query).fetchall()

14) RIGHT JOIN¶

Question: Show all departments with employee details.

Note: SQLite does not support RIGHT JOIN directly. We simulate it by swapping the table order in a LEFT JOIN.

In [ ]:
# SQL Query:
# ---------
# SELECT E.Ename, D.Dname
# FROM EMP E
# RIGHT JOIN DEPT D
# ON E.Deptno = D.Deptno;
In [ ]:
# Q: Show all departments with employee details.
# Note: SQLite does not support RIGHT JOIN directly. We simulate it by swapping the table order in a LEFT JOIN.
query = '''
    SELECT E.ename, D.dname
    FROM dept D
    LEFT JOIN emp E
    ON D.deptno = E.deptno
'''

cursor.execute(query).fetchall()

15) FULL OUTER JOIN¶

Question: Show all employees and all departments.

Note: SQLite does not support FULL OUTER JOIN directly. We simulate it using UNION of LEFT JOIN and a reversed LEFT JOIN.

In [ ]:
# SQL Query:
# ---------
# SELECT E.Ename, D.Dname
# FROM EMP E
# FULL OUTER JOIN DEPT D
# ON E.Deptno = D.Deptno;
In [ ]:
# Q: Show all employees and all departments.
# Note: SQLite does not support FULL OUTER JOIN directly. We simulate it using UNION of LEFT JOIN and a reversed LEFT JOIN.
query = '''
    SELECT E.ename, D.dname
        FROM emp E
        LEFT JOIN dept D ON E.deptno = D.deptno
        UNION
        SELECT E.ename, D.dname
        FROM dept D
        LEFT JOIN emp E ON D.deptno = E.deptno
'''

cursor.execute(query).fetchall()

16) Subqueries¶

Question: Find employees earning more than average salary.

In [ ]:
# SQL Query:
# ---------
# SELECT Ename, Sal
# FROM EMP
# WHERE Sal > (
#     SELECT AVG(Sal)
#     FROM EMP
# );
In [49]:
# Q: Find employees earning more than average salary.
query = '''
    SELECT ename, sal
    FROM emp
    WHERE sal > (
        SELECT AVG(sal)
        FROM emp
    )
'''

cursor.execute(query).fetchall()
Out[49]:
[('JONES', 2975.0),
 ('BLAKE', 2850.0),
 ('CLARK', 2450.0),
 ('SCOTT', 3000.0),
 ('KING', 5000.0),
 ('FORD', 3000.0)]

Question: Find employees working in SALES department.

In [ ]:
# SQL Query:
# ---------
# SELECT Ename
# FROM EMP
# WHERE Deptno = (
#     SELECT Deptno
#     FROM DEPT
#     WHERE Dname = 'SALES'
# );
In [ ]:
# Q: Find employees working in SALES department.
query = '''
    SELECT ename
    FROM emp
    WHERE deptno = (
        SELECT deptno
        FROM dept
        WHERE dname = 'SALES'
    )
'''

cursor.execute(query).fetchall()

Close the connection¶

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

Summary¶

Topic SQL Concept Used
Select entire table SELECT * FROM table
Select specific columns SELECT col1, col2 FROM table
Filter rows WHERE condition
Sort results ORDER BY col ASC / DESC
Operators AND, OR, BETWEEN, NOT, IN
NULL checks IS NULL, IS NOT NULL
Pattern matching LIKE 'pattern'
Aggregates SUM, AVG, MAX, COUNT
Group and filter GROUP BY ... HAVING ...
Joins INNER, LEFT, RIGHT, FULL OUTER JOIN
Subqueries WHERE col > (SELECT ...)

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