SQL Interview Questions for AI Training Work

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN keeps every row from the left table, filling in NULLs where there's no match on the right, which is what you use when you need every parent record even if it has no matching children. A FULL OUTER JOIN keeps unmatched rows from both sides, useful for finding discrepancies between two tables that should mostly line up.

The query planner estimates the cost of an index scan versus a full table scan based on table statistics, like how selective the filter is. If a query would return a large fraction of the table, like more than roughly 10-20%, a full scan is often cheaper than jumping between an index and the table row by row, so the planner skips the index even though one exists.

WHERE filters individual rows before any grouping happens, so it can't reference an aggregate like COUNT or SUM. HAVING filters groups after GROUP BY has run, which is why it's the clause you use to say something like "only show customers with more than 5 orders," since that condition depends on an aggregated value that doesn't exist until the rows are grouped.

READ COMMITTED means each query within a transaction sees only data committed before that query started, so two reads of the same row within one transaction can return different values if another transaction commits in between, a phenomenon called a non-repeatable read. REPEATABLE READ locks in a consistent snapshot for the whole transaction, so the same query always returns the same rows, at the cost of more locking or storage overhead depending on the database.

Group by the columns that define a duplicate, then filter with HAVING COUNT(*) > 1 to surface the groups with more than one row. To get the actual duplicate rows rather than just the count, you'd join that grouped result back to the original table, or use a window function like ROW_NUMBER() partitioned by those columns to tag and inspect each duplicate individually.

I'd start with EXPLAIN ANALYZE to see the actual query plan and find where time is really going, rather than guessing. Common culprits at scale are a missing index on a filter or join column, a function applied to a column in the WHERE clause that prevents index use, or a join that's producing far more intermediate rows than expected. I'd fix whichever the plan actually points to rather than adding indexes speculatively.

I'd batch the update into smaller chunks, like a few thousand rows at a time, instead of one massive UPDATE statement, since a single huge transaction can hold locks long enough to block other queries and bloat the transaction log. I'd also run it inside explicit transactions with a verification query after each batch, and have a rollback plan ready before starting, not improvised after something goes wrong.

I'd use an `orders` table for order-level data, an `order_items` table with a foreign key back to `orders` for each line item, and put the discount on the `order_items` row rather than the order, since discounts apply per item. Normalizing this way avoids duplicating order-level data across items and keeps each discount tied to exactly the item it applies to.

I once had a report double-counting revenue because a join to a one-to-many table multiplied each order row by its number of line items before the aggregation. The query wasn't wrong syntactically, the join itself was the problem. I fixed it by aggregating the line items into a subquery first, then joining that pre-aggregated result to orders, and added a row-count sanity check to catch that class of bug earlier next time.

Someone asked for a quick DELETE against a shared table without a WHERE clause scoped tightly enough, and running it as written would have removed far more than intended. I asked for the exact record IDs instead, wrote the DELETE with an explicit list, and ran a SELECT with the same WHERE clause first to confirm the row count matched expectations before actually deleting anything.