Databases & SQL interview questions
59 Databases & SQL interview questions with answers, from junior to mid level.
Foundations
- What is a database?
- What are PostgreSQL, MySQL and Oracle Database?
- What is SQL?
- CRUD means Create, Read, Update, Delete. Which SQL commands match these four in that order?
- In database language, what is a 'query'?
- Why do applications store important data in a database instead of only in program variables?
- Which of these are relational (SQL) databases?
- What does a database schema describe?
- Explain what a database is and why almost every app needs one.
Core
customers LEFT JOIN orders ON ...returns…- What is the difference between WHERE and HAVING?
- What does
SELECT * FROM users WHERE deleted_at = NULLreturn? - Which is true about a PRIMARY KEY vs a UNIQUE constraint?
- Why are parameterized queries preferred over string formatting?
- What is a primary key?
orders.customer_idreferencescustomers.id. What iscustomer_id?- Which query returns the names of users older than 30?
- How do you get the 5 most expensive products?
- Which query counts users per country?
- What does an INNER JOIN between
ordersandcustomersonorders.customer_id = customers.idreturn? - What happens when you run
DELETE FROM users;without a WHERE clause? - A bank transfer subtracts money from A and adds it to B. Why run both in one transaction?
- Which statement best describes SQL vs NoSQL databases?
- What is a database index, in simple terms?
- What does
SELECT DISTINCT country FROM users;return? - One customer can have many orders. Where does the link column go?
- In a relational database, what are a table, a row and a column?
- What are the main groups of SQL commands?
- How do you model a many-to-many relationship, e.g. students and courses?
- Explain primary key, foreign key, index, constraint and transaction as if I know nothing.
Intermediate
- What is the main cost of adding an index to a table?
- Table has a composite index on
(last_name, first_name). Which query can use it efficiently? - In ACID, what does Isolation guarantee?
- Which query returns the second highest distinct salary?
COUNT(*)vsCOUNT(email)?- Why might you deliberately denormalize a schema?
- Which scenario most favors a document store (e.g. MongoDB) over a relational DB?
- Replication vs sharding: which statement is correct?
EXPLAINshows a sequential scan on a 50M-row table forWHERE email = ?. Most likely fix?- UNION vs UNION ALL?
- Which is NOT a typical Redis use case?
- Why use a database connection pool?
- Design the schema for a simple e-commerce store (customers, products, orders).
- What do 1NF, 2NF and 3NF mean in plain language?
- What is the difference between
DELETE,TRUNCATEandDROP? - In what logical order does SQL evaluate a query?
- What is a view, and how does a materialized view differ?
- Which query lists customers who have NO orders?
- What is a CTE (
WITHclause) useful for? - Which query finds duplicate emails in a
userstable? - What is the difference between a surrogate key and a natural key?
- Design a relational schema for a library (books, authors, members, loans). Explain the relationships.
Advanced
- In the SQL standard, which is the lowest isolation level that prevents non-repeatable reads?
- How do you get the top-paid employee per department in one query?
- Two transactions lock rows A then B, and B then A, and deadlock. Best prevention?
- Optimistic locking is typically implemented with…
- A user updates their profile, refreshes, and sees the old data. Reads go to async read replicas. What is happening?
- A query became slow in production. How do you investigate?
- What is a covering index?