Python SQL Interview Questions and Answers (With Runnable Code)

Key takeaways

• Always pass values as parameters (? in sqlite3, %s in most other drivers), never build SQL with f-strings.
• Know transactions: commit on success, roll back on error, and why with conn: helps.
• Be ready to explain raw SQL vs an ORM, and when pandas read_sql is enough.
• Run every example in this post yourself. Interviewers notice when you have typed code, not memorised it.

Setup for every example

All code below runs on Python 3 with no installs, using the built-in sqlite3 module and an in-memory database.

import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.executescript('''
CREATE TABLE dept (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER, salary INTEGER);
INSERT INTO dept VALUES (1,'IT'),(2,'HR'),(3,'Ops');
INSERT INTO emp VALUES (1,'Asha',1,50),(2,'Ravi',1,60),(3,'Meera',2,45),(4,'Kabir', NULL,40);
''')

The salary numbers are toy data for practice only.

Basics

How do you connect to an SQL database from Python?

Use a DB-API driver for your database: sqlite3 (built in), psycopg for PostgreSQL, mysql-connector-python or PyMySQL for MySQL. You create a connection, get a cursor, execute SQL, then close both. Details differ by driver, but they all follow the DB-API standard described in PEP 249.

What is a cursor?

A cursor is the object that executes statements and holds the position in a result set. fetchone(), fetchmany(n) and fetchall() read rows from it. For large results, iterate over the cursor instead of calling fetchall() so you do not load everything into memory.

What is the difference between fetchone, fetchmany and fetchall?

fetchone returns the next row or None. fetchmany(n) returns up to n rows. fetchall returns every remaining row as a list. Use the first two when the result may be large.

Safe queries and SQL injection

How do you prevent SQL injection in Python?

Use parameterised queries. The driver sends the SQL and the values separately, so input can never change the query structure.

name = "Asha' OR '1'='1"

# Unsafe: user input becomes part of the SQL text
# cur.execute(f"SELECT * FROM emp WHERE name = '{name}'")

# Safe: value is passed as a parameter
cur.execute("SELECT * FROM emp WHERE name =?", (name,))
print(cur.fetchall()) # [], the quote trick does nothing

Placeholder style varies by driver: ? for sqlite3, %s for psycopg and MySQL drivers, :name for named parameters in sqlite3. Never use % string formatting or f-strings to insert values. Table and column names cannot be parameters, so if they come from users, check them against a fixed allow-list. The OWASP SQL Injection Prevention Cheat Sheet covers this in depth; also read the post on injection attacks.

Does an ORM make you safe from SQL injection?

Mostly, because ORMs parameterise by default. You become unsafe again when you pass user input into raw SQL fragments, such as text() in SQLAlchemy or .raw() in Django, without bind parameters.

Transactions

What is a transaction and how do you handle it in Python?

A transaction groups statements so they all succeed or all fail. In sqlite3 a transaction opens automatically before data-changing statements, and you end it with commit() or rollback(). The connection works as a context manager that commits on success and rolls back on an exception.

try:
 with conn:
 cur.execute("INSERT INTO emp VALUES (5,'Nina',3,55)")
 cur.execute("INSERT INTO emp VALUES (5,'Dup',3,55)") # primary key clash
except sqlite3.IntegrityError as e:
 print("rolled back:", e)

Neither insert is kept, because the second one failed. Note that with conn: does not close the connection.

What does ACID mean?

Atomicity (all or nothing), Consistency (rules such as constraints always hold), Isolation (concurrent transactions do not see each other's half-done work) and Durability (committed data survives a crash).

Joins and aggregates from Python

Explain INNER, LEFT and FULL joins with an example.

INNER returns only matching rows. LEFT returns every row from the left table plus matches, with NULL where none exist. FULL OUTER returns unmatched rows from both sides; SQLite added it only in version 3.39, and MySQL does not support it directly.

cur.execute('''
SELECT e.name, d.name AS dept
FROM emp e LEFT JOIN dept d ON e.dept_id = d.id
ORDER BY e.id
''')
for r in cur: print(r["name"], r["dept"])

Kabir appears with None as department. An INNER JOIN would drop him. That difference is the most common join question.

How do you find the second-highest salary?

cur.execute('''
SELECT DISTINCT salary FROM emp
ORDER BY salary DESC LIMIT 1 OFFSET 1
''')
print(cur.fetchone()[0])

Mention the alternatives: a subquery with MAX(salary) WHERE salary < (SELECT MAX(salary)...), or DENSE_RANK() in databases with window functions. Ask what to do with ties.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

cur.execute('''
SELECT dept_id, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM emp WHERE dept_id IS NOT NULL
GROUP BY dept_id HAVING COUNT(*) > 1
''')
print([tuple(r) for r in cur])

Libraries and design choices

sqlite3 vs SQLAlchemy vs pandas?

sqlite3 is the standard-library driver for SQLite. SQLAlchemy has two layers: Core for building SQL in Python and an ORM that maps tables to classes, working across many databases. pandas read_sql pulls a query result into a DataFrame, which suits analysis. Use raw SQL for simple, performance-critical queries, an ORM for application models with relations, and pandas for analysis. See the SQLAlchemy docs to compare.

What is the N+1 query problem?

You run one query for a list and then one more query per row to fetch related data. With 1,000 rows that is 1,001 round trips. Fix it with a JOIN or eager loading (selectinload or joinedload in SQLAlchemy).

How do you speed up a slow query?

Check the plan first: EXPLAIN QUERY PLAN in SQLite, EXPLAIN or EXPLAIN ANALYZE in PostgreSQL and MySQL. Add an index on columns used in WHERE and JOIN, avoid SELECT *, filter early, and paginate large results. Indexes speed reads but slow writes, so add them for real query patterns, not by habit.

What is a connection pool?

A pool keeps open connections ready for reuse, because opening a connection for every request is slow. SQLAlchemy provides pooling for server databases. SQLite in-process usually does not need it.

How do you load a query into pandas?

import pandas as pd
df = pd.read_sql("SELECT * FROM emp WHERE salary >?", conn, params=(45,))
print(df)

Note the parameter again. The same injection rule applies in pandas.

How to prepare

1. Re-type each example above instead of copying it.
2. Break each one on purpose: remove the parameter, trigger a rollback, change the join type, and see what changes.
3. Practise on a real server database later, because behaviour differs (placeholders, types, locking).
4. Prepare one short story of a bug you hit with data or queries. Interviewers value that more than a perfect definition.

Next steps

Revise core SQL with the MySQL and SQL course or build the Python side in the Python programming course. For database role questions, see the database administration interview questions.

Related reading

• Can ChatGPT Write Better SQL Than a Data Analyst? Complete Comparison with Real Examples
• MySQL Course in Pune | Learn SQL with Certification & Job Assistance