SQL Interview Questions
1. What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping or aggregation happens. HAVING filters groups after GROUP BY has produced them, so it can reference aggregate functions like COUNT(*) or SUM(...), which WHERE cannot.2. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, matching rows from the right table where they exist and NULL in the right table's columns where they don't.3. What is the difference between a primary key and a foreign key?
NULL. A foreign key is a column (or set of columns) in one table that references a primary key in another table, enforcing that the referenced row actually exists.4. What does ACID stand for?
5. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes rows one at a time (optionally filtered with WHERE), is logged, and can be rolled back. TRUNCATE removes all rows at once, is typically much faster and minimally logged, and often cannot be filtered. DROP removes the table itself, including its structure — not just the data inside it.6. What is normalization, and why does it matter?
Normalization is the process of organizing a schema to reduce redundant data and avoid update anomalies, typically by splitting data into related tables (e.g. separating customer details from orders) and enforcing that relationship with foreign keys. It matters because duplicated data can drift out of sync when only some copies get updated.
7. When would you use a subquery instead of a JOIN?
EXISTS), compare against an aggregate (a single computed value), or when expressing the logic as a join would require awkward duplication. A JOIN is usually preferred when you need columns from both tables in the output, since it is often easier for the query planner to optimize.8. What is a window function, and how does it differ from GROUP BY?
OVER (...)) computes an aggregate or ranking across a set of related rows without collapsing them into a single output row — each input row keeps its own row in the result, with the computed value attached. GROUP BY, by contrast, collapses each group down to one summary row.9. What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows, which requires an extra sorting/deduplication step. UNION ALL keeps every row from both queries, including duplicates, and is faster since it skips that step.10. How do you prevent SQL injection?
Use parameterized queries (prepared statements) so user input is always sent to the database separately from the query text, never concatenated into a SQL string. Supplement this with least-privilege database accounts and input validation, and be cautious with ORM features that allow raw SQL.
11. What is an index, and what is its trade-off?
An index is a separate data structure (commonly a B-tree) that lets the database find rows matching a condition without scanning the whole table, dramatically speeding up lookups, joins, and sorting on the indexed column. The trade-off is that every write to that column also has to update the index, so indexes speed up reads at some cost to write performance and storage.
12. What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order in which rows are stored on disk — a table can have at most one. A non-clustered index is a separate structure that points back to the row's location, and a table can have many of them.
13. What is a CTE, and why use one?
WITH, is a named temporary result set scoped to a single query. It improves readability by breaking a complex query into logical steps, and enables recursive queries that would otherwise be very awkward to express.14. What is the difference between CHAR and VARCHAR?
CHAR(n) is fixed-length and pads shorter values with spaces up to n characters. VARCHAR(n) is variable-length and only stores the characters actually present, up to a maximum of n. VARCHAR is the more common default choice for text of varying length.15. What happens if you don't use a transaction for multiple related statements?
Each statement commits independently. If an error occurs partway through the sequence, the statements that already ran stay applied while the rest never happen, potentially leaving the data in an inconsistent state — for example, money debited from one account without being credited to another.
16. What is a self-join, and when would you use one?
A self-join joins a table to itself, typically using two aliases for the same table, to compare rows within that table to each other — for example, finding employees who share the same manager, or comparing an order to the customer's previous order.
Finding employees with the same manager
SELECT e1.name, e2.name AS coworker FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id <> e2.id;
17. What is the difference between a view and a materialized view?
A regular view is just a stored query — it runs against live data every time it is queried. A materialized view stores the actual result set on disk, which is faster to read but can go stale until it is refreshed, since it does not automatically reflect new changes to the underlying tables.
18. What does EXPLAIN show you, and why use it?
EXPLAIN shows the execution plan the database intends to use for a query — whether it will use an index or scan the whole table, in what order tables are joined, and roughly how expensive each step is estimated to be. It is the standard way to diagnose why a query is slow instead of guessing.