JDBC's architecture, why PreparedStatement matters beyond convenience, the Nth-highest-salary drill, WHERE vs HAVING, CTEs, keyset pagination, and the SQL vs NoSQL decision that actually matters.
Published September 23, 2026
DriverManager.getConnection(url) → locates a registered Driver
→ Driver produces a Connection
Connection.createStatement() / prepareStatement(sql) → produces a Statement/PreparedStatement
Statement.executeQuery(sql) → produces a ResultSet
Every JDBC interaction follows this same chain: DriverManager finds the right Driver implementation for a given connection URL (each database vendor ships its own), the Driver produces a Connection (the actual network session to the database), and every query or update flows through a Statement/PreparedStatement created from that connection, ultimately producing a ResultSet for queries.
// Statement — string concatenation, vulnerable to SQL injection
Statement stmt = connection.createStatement();
stmt.executeQuery("SELECT * FROM users WHERE email = '" + userInput + "'");
// PreparedStatement — parameter binding, safe and reusable
PreparedStatement ps = connection.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, userInput);
ps.executeQuery();
PreparedStatement precompiles the query once and binds parameters separately from the SQL text — this is what prevents SQL injection (userInput is never concatenated into the query string, so it can never be interpreted as SQL syntax, only as a literal value) and improves performance for repeated execution (the database can reuse the same execution plan across calls with different bound parameters, rather than re-parsing a new query string every time).
A primary key uniquely identifies a row within its table and can never be null. A foreign key references another table's primary key, enforcing referential integrity — the database rejects an insert/update that would create a foreign key value with no matching primary key row, preventing "orphaned" references at the database level rather than relying on application code to maintain that invariant correctly every time.
-- Via subquery + DISTINCT + LIMIT/OFFSET
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET (N - 1); -- Nth highest, 1-indexed
-- Via window function (handles ties more explicitly)
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = N;
DENSE_RANK() (see Window Functions) is generally the more robust version — DISTINCT + OFFSET treats tied salaries as a single position correctly too, but DENSE_RANK makes the tie-handling explicit and is the version most interviewers expect you to reach for once you know window functions exist.
-- WHERE filters ROWS before grouping
SELECT dept, AVG(salary) FROM employees WHERE hire_date > '2020-01-01' GROUP BY dept;
-- HAVING filters GROUPS after aggregation — using WHERE for this is a SYNTAX ERROR
SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) > 80000;
WHERE operates on individual rows before GROUP BY collapses them; HAVING operates on the aggregated groups afterward. Trying to filter on an aggregate value (AVG(salary) > 80000) in a WHERE clause fails, because at the point WHERE is evaluated, no aggregation has happened yet — the aggregate value doesn't exist yet to filter on.
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
The canonical GROUP BY + HAVING COUNT(*) > 1 pattern — group rows by the column(s) that should be unique, then keep only the groups where more than one row shares that value.
-- CTE — named, readable, can be referenced multiple times in the same query
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 100000
)
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;
-- Equivalent subquery — inline, can get hard to read when nested deeply
SELECT dept, COUNT(*) FROM (SELECT * FROM employees WHERE salary > 100000) AS high_earners GROUP BY dept;
A CTE (WITH clause) names an intermediate result for readability, and can be referenced multiple times within the same query without repeating its definition — a subquery is inline and, when nested several levels deep, becomes considerably harder to read. Performance is typically equivalent (most query planners treat them the same way) — the difference is almost entirely about clarity and reusability, not execution speed.
-- LIMIT/OFFSET — simple, but slows down at high offsets
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000; -- must scan and discard 10,000 rows first
-- Keyset (cursor-based) — stays fast at any depth
SELECT * FROM posts WHERE id > :last_seen_id ORDER BY id LIMIT 20;
OFFSET forces the database to scan and discard every skipped row before returning the requested page — cheap at low offsets, increasingly expensive as the offset grows. Keyset pagination instead carries forward the last-seen row's key and filters directly from there, with cost independent of how deep into the result set you are — the same tradeoff covered from the product/API side in Design a News Feed System's cursor-vs-offset discussion.
SQL fits strong consistency requirements and complex relational queries across structured, well-understood data (joins across many related entities). NoSQL fits horizontal scale, flexible/evolving schema, or access patterns that don't naturally decompose into joins (a document that's always read/written as one atomic unit, a simple key-value lookup at massive scale). The deeper mindset shift: in SQL, normalize for integrity, then selectively denormalize for specific read paths (see Denormalization & Schema Trade-offs); in NoSQL, design the schema around the queries you'll actually run, not the other way around — a NoSQL schema modeled the way a relational schema would be (heavily normalized, joined at read time) usually performs poorly, because most NoSQL stores aren't optimized for join-heavy access patterns the way a relational engine is.
Check EXPLAIN/EXPLAIN ANALYZE (see Query Execution Plans) for full table scans on large tables, add missing indexes on filter/join columns, avoid SELECT * (fetching unused columns wastes I/O and bandwidth, and defeats covering-index optimizations), and rewrite correlated subqueries as joins where possible (a correlated subquery re-executes once per outer row, while an equivalent join is typically planned and executed as a single set operation).
Q: Does PreparedStatement's precompilation benefit apply even for a query executed only once? A: The SQL-injection safety benefit applies regardless of execution count — the performance benefit from plan reuse specifically requires repeated execution (or, on some databases, the driver/connection caching prepared statement plans across calls) to actually pay off; for a genuinely one-off query, the security benefit alone is still reason enough to default to PreparedStatement.
Q: Why would DISTINCT + OFFSET give a different answer than DENSE_RANK() in some edge cases? A: If ties exist and the interview question means 'the Nth distinct salary value' (as most phrasings intend), both approaches agree — but if it means something subtly different (e.g. 'the row at the Nth position including duplicates'), DENSE_RANK's explicit tie semantics make the intended meaning unambiguous in a way DISTINCT+OFFSET's implicit deduplication doesn't as clearly communicate.
Q: Is keyset pagination strictly better than offset pagination, with no downsides? A: No — keyset pagination can't easily jump to an arbitrary page number ('go to page 47' requires knowing page 46's last key first, effectively requiring sequential traversal), while OFFSET can jump anywhere directly, just at increasing cost. Keyset wins for infinite-scroll-style sequential consumption; OFFSET is still reasonable for a bounded, page-number-driven UI over a small-to-moderate result set.
Q: How does a foreign key constraint interact with denormalization? A: Denormalized copies of data (see Denormalization & Schema Trade-offs) generally can't be protected by a foreign key constraint in the same way normalized data is, since the whole point of denormalization is redundant, potentially-stale copies rather than a single referentially-enforced source of truth — consistency for denormalized data has to be maintained by application logic, events, or background jobs instead of the database's own constraint system.