Reading a Schema Under Pressure
How to quickly parse an unfamiliar table diagram and ask the right clarifying questions in a live interview.
Reading a Schema Under Pressure is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Live-Interview Skill
In a real interview you're handed an unfamiliar schema and asked to query it on the spot. This lesson is a calm, repeatable way to read one under time pressure.
Step 1: Inventory the Tables
Step 1: inventory the tables. Read just the names and guess each entity. A name joining two entities, like order_items, is usually a junction table.
Step 2: Find the Primary Keys
Step 2: find the primary keys, usually named id or <table>_id. The key tells you the table's grain, what one row means, so joins don't multiply rows.
Step 3: Trace the Foreign Keys
Step 3: trace the foreign keys. A column like customer_id points to customers.id. Sketch the links as arrows; they are the join paths you'll use.
-- Inferred join path from the foreign keys
SELECT *
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id;Step 4: Read Cardinality From the Keys
Step 4: read cardinality from the keys. The table holding the foreign key is the many side; the one it points to is the one side. One customer, many orders.
Step 5: Note Nullability and Types
Step 5: note nullability and types. A nullable foreign key often hints at a LEFT JOIN. And check whether a date is really a DATE, or an amount really numeric.
Step 6: Ask the Right Clarifying Questions
Step 6: ask clarifying questions. "Which status means completed?" "Is email unique?" Interviewers often plant ambiguity to see whether you ask or blunder ahead.
Worked Example: A Schema in 60 Seconds
Put it together: read four e-commerce tables fast, naming each grain and the foreign keys. In under a minute you know every join path, and any question becomes answerable.
Turning the Reading Into a Query
Asked for "total revenue per customer"? Because you traced the keys first, the query nearly writes itself: revenue lives at the line-item grain, joined up to the customer.
SELECT c.id, c.name, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY revenue DESC;Common Traps to Watch For
Watch for predictable traps: wrong grain that double-counts, an inner join dropping rows, or treating email as a key. Your six steps defend against all of them.
Thinking Out Loud Scores Points
Thinking out loud scores points. Narrate: "These look like e-commerce tables; the grain is one line item; I'll join up to the customer." It lets them correct you early.
Quick Check
Apply cardinality reasoning to an unfamiliar schema.
Recap
Recap the six steps: inventory tables, find primary keys for grain, trace foreign keys, read cardinality, check nullability, then ask questions. Read first, write second.
Frequently asked questions
Is the “Reading a Schema Under Pressure” lesson free?
Yes — the full text of “Reading a Schema Under Pressure” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Reading a Schema Under Pressure”?
How to quickly parse an unfamiliar table diagram and ask the right clarifying questions in a live interview. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Reading a Schema Under Pressure” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- What Is SQL and Why Interviewers Ask It
- Logical Query Execution Order
- Primary Keys, Foreign Keys and Constraints
- Reading a Schema Under Pressure