Mock interview 3: project and SQL round
60 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)This round tests whether you really built your projects and whether you can read and write SQL. This mock uses an Online Store API and a Course Enrollment System, with the questions interviewers ask about both.
How the project and SQL round runs
A project walkthrough is a short spoken tour of one project: the problem, your part, one design choice, one bug you fixed and what you would improve.
This round is about 45 minutes: 20 on one project, 20 on SQL and 5 for your questions. This lesson uses two projects: an Online Store API and a Course Enrollment System. Example 1 shows the minutes.
Example 1 · The 45-minute script
00-20 PROJECT 60-second tour, then 4 or 5 "why?" questions20-40 SQL 4 queries: join, group, duplicates, second highest40-45 YOUR Q ask what a good first project looks like on the team20 + 20 + 5 = 45 minutes
What this code does
- The project block starts short and gets deeper with each follow-up.
- Write SQL while you speak: first the result in words, then the query.
The interviewer picks a detail from your answer and asks "why?". Only talk about code you wrote and can explain.
Project questions: design, errors and tests
Good project answers name a rule and a reason. In the Online Store API, stock must never go below zero. In the Course Enrollment System, a student cannot enrol twice and a course has a seat limit. Examples 2 and 3 show each rule in small code.
Example 2 · Online Store API: the stock rule
from dataclasses import dataclass
class OutOfStock(Exception): pass
@dataclassclass Product: name: str stock: int price: int
def place_order(product, quantity): if quantity <= 0: raise ValueError("quantity must be positive") if quantity > product.stock: raise OutOfStock(f"only {product.stock} left") product.stock -= quantity return product.price * quantity
desk = Product("Desk", 3, 120)print(place_order(desk, 2))try: place_order(desk, 2)except OutOfStock as error: print("rejected:", error)print(desk.stock)240 rejected: only 1 left 1
What this code does
- The function checks input first, then the stock, and changes stock only after both pass.
- A custom exception lets the API turn this case into a clear "409 conflict" reply, not a crash.
- Model answer: "I used a dataclass for products, one function per rule, and tests for zero, too many and exact stock."
Example 3 · Course Enrollment System: the two rules
class Course: def __init__(self, title, seats): self.title = title self.seats = seats self.students = set()
def enrol(self, student): if student in self.students: return "already enrolled" if len(self.students) >= self.seats: return "course full" self.students.add(student) return "enrolled"
course = Course("Databases", 2)for name in ["Asha", "Asha", "Ravi", "Meera"]: print(name, course.enrol(name))Asha enrolled Asha already enrolled Ravi enrolled Meera course full
What this code does
- A set stores each student once, so the duplicate check is one quick lookup.
- The seat limit is checked after the duplicate check, so a repeat never looks like a full course.
- In a real database the same rule is a unique key on (student_id, course_id).
Say one test for each rule. "I tested enrolling twice and a full course" shows you checked behaviour, not only the happy path.
SQL questions: joins, grouping and safe queries
A parameterised query keeps the SQL text fixed and passes values separately, so the database never reads user input as SQL code.
Interviewers ask for a join, a group with a filter on the group, a duplicate finder, the second highest value, and why you never build SQL with an f-string. Examples 4 to 6 run each on a tiny database.
Example 4 · SQL: join and group
import sqlite3
db = sqlite3.connect(":memory:")db.executescript("""CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Ravi', 'Delhi'), (3, 'Meera', 'Pune');INSERT INTO orders VALUES (1, 1, 500), (2, 1, 300), (3, 2, 700), (4, 2, 700);""")
rows = db.execute(""" SELECT c.name, COUNT(o.id), SUM(o.total) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id ORDER BY c.id""").fetchall()for row in rows: print(row)('Asha', 2, 800)
('Ravi', 2, 1400)
('Meera', 0, None)What this code does
LEFT JOINkeeps customers with no orders (Meera shows 0 orders and a total of None).GROUP BYmakes one row per customer soCOUNTandSUMwork per customer.- Say the join key aloud: orders.customer_id matches customers.id.
Example 5 · SQL: HAVING, duplicates and second highest
import sqlite3
db = sqlite3.connect(":memory:")db.executescript("""CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Ravi', 'Delhi'), (3, 'Meera', 'Pune');INSERT INTO orders VALUES (1, 1, 500), (2, 1, 300), (3, 2, 700), (4, 2, 700);""")
print(db.execute(""" SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id HAVING SUM(total) > 800""").fetchall())
print(db.execute(""" SELECT total, COUNT(*) FROM orders GROUP BY total HAVING COUNT(*) > 1""").fetchall())
print(db.execute(""" SELECT MAX(total) FROM orders WHERE total < (SELECT MAX(total) FROM orders)""").fetchone())[(2, 1400)] [(700, 2)] (500,)
What this code does
WHEREfilters rows before grouping;HAVINGfilters groups after it.- Duplicates: group by the column and keep groups with a count above 1.
- Second highest: the highest value below the maximum. Mention
DISTINCTandLIMIT 1 OFFSET 1as other ways, and ties.
Example 6 · SQL from Python: parameters and a transaction
import sqlite3
db = sqlite3.connect(":memory:")db.execute("CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER)")db.execute("INSERT INTO stock VALUES ('desk', 3)")
name = "desk' OR '1'='1"print(db.execute("SELECT qty FROM stock WHERE item = ?", (name,)).fetchall())
try: with db: db.execute("UPDATE stock SET qty = qty - 2 WHERE item = 'desk'") raise RuntimeError("payment failed")except RuntimeError: passprint(db.execute("SELECT qty FROM stock").fetchone())[] None
What this code does
- The
?placeholder treats the whole text as one value, so the attack text matches nothing. with db:is one transaction: on the error it rolls back, so the stock is back to 3.- Model answer: "Money-like changes go in one transaction so they all happen or none do."
Say what each query returns in words before you write it, and say which key joins the tables.
Strong and weak answers
Example 7 compares two ways to describe the same project. The strong one has a number, a decision and a limit.
Example 7 · A strong and a weak project answer
Question: "Tell me about your Course Enrollment System."Weak: "It is a project where students can enrol in courses. I used Python and a database. It has many features."Strong: "It lets students enrol in courses with a seat limit. I made a unique key on student and course so double enrolment fails in the database too. The hardest bug was two requests taking the last seat; I fixed it with a transaction. Next I would add a waiting list."Weak: nothing to follow up on. Strong: three good follow-up questions ready.
What this code does
- The weak reply has no decision, no problem and nothing to ask about.
- The strong reply gives a rule, a bug, a fix and a next step.
Prepare a 60-second version and a 3-minute version of each project. Practise the short one first.
Next step
Check your understanding
10 quick questions