SQL and Databases Interview Questions for Java Full Stack with React
25 SQL and Databases questions with answers for java full stack with react roles — 8 easy, 11 medium, 6 hard.
About SQL and Databases
Every full stack developer must write and tune SQL. This topic covers keys and constraints, joins, grouping, normalization, indexes, ACID and isolation levels, window functions and query plans with EXPLAIN. It also includes classic interview queries like the Nth highest salary and running totals, plus real scenarios such as deadlocks, connection pool exhaustion and slow pagination.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the difference between a primary key and a unique key?
A primary key uniquely identifies each row, cannot be null, and a table has only one, although it may span several columns. A unique key also enforces uniqueness but a table can have many, and most databases allow null values in it. Both create an index automatically. For example, id is the primary key of a users table while email and mobile have unique constraints.
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with nulls where there is no match. RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both sides, which MySQL does not support directly. CROSS JOIN returns every combination. A self join joins a table to itself, such as employees and their managers.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT or SUM. For example, SELECT dept, COUNT(*) FROM employees WHERE active = true GROUP BY dept HAVING COUNT(*) > 10. Put conditions in WHERE whenever possible because filtering earlier reduces the rows the database must group.
Atomicity means a transaction fully succeeds or fully rolls back. Consistency means it moves the database from one valid state to another, respecting constraints. Isolation means concurrent transactions do not see each other's partial work. Durability means committed data survives crashes, thanks to write-ahead logs. A bank transfer that debits one account and credits another is the classic example where all four properties matter.
05
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows using a WHERE clause, logs each row, fires triggers and can be rolled back. TRUNCATE removes all rows quickly by deallocating pages, resets identity counters in most databases and is treated as DDL, so rollback support depends on the database. DROP removes the whole table, including its structure, indexes and constraints. Use DELETE for business data and TRUNCATE for staging tables.
06
Write a query to find the second highest salary from an employees table.
-- Using a subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Using DISTINCT with OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- The first query returns NULL when there is no second salary.
07
Write a query to find duplicate email addresses in a users table and delete the extra rows.
-- Find duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Keep the lowest id, delete the rest (MySQL / PostgreSQL)
DELETE FROM users u
USING users k
WHERE u.email = k.email
AND u.id > k.id; -- PostgreSQL syntax
-- Then add: ALTER TABLE users ADD CONSTRAINT ux_email UNIQUE (email);
08
A security review found a login query built by string concatenation. What is the risk and the fix?
Concatenating user input into SQL allows SQL injection, where input like ' OR '1'='1 changes the query and can bypass login or dump data. Use parameterised queries: PreparedStatement with ? placeholders, Spring Data method queries, or @Query with named parameters, which send values separately from the SQL. Also validate input, use a database user with least privilege and never show raw SQL errors to users.
Normalization organises tables to reduce redundancy and update anomalies. 1NF requires atomic values with no repeating groups. 2NF requires 1NF and that every non-key column depends on the whole primary key, not part of a composite key. 3NF requires 2NF and no transitive dependency, so non-key columns depend only on the key. For example, store customer city in the customers table, not in every order row. Reporting systems sometimes denormalize for speed.
10
How do indexes improve performance, and when can they hurt?
An index, usually a B-tree, lets the database find rows without scanning the whole table, which speeds up WHERE, JOIN and ORDER BY on indexed columns. Indexes cost disk space and slow down INSERT, UPDATE and DELETE because each index must also be updated. They help little on low-selectivity columns like a boolean flag. Functions on the column, such as WHERE LOWER(email) = ?, or a leading wildcard in LIKE also prevent normal index use.
11
What are window functions and how are they different from GROUP BY?
Window functions compute a value over a set of related rows while keeping every row in the result, unlike GROUP BY which collapses rows into one per group. They use the OVER clause with optional PARTITION BY and ORDER BY. Examples are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running SUM. For instance, you can show each employee with their department's average salary in the same row.
12
Find the top 3 highest-paid employees in each department.
SELECT department_id, name, salary
FROM (
SELECT department_id, name, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 3
ORDER BY department_id, rnk;
-- DENSE_RANK keeps ties; use ROW_NUMBER for exactly 3 rows.
SELECT e.name AS employee,
e.salary,
m.name AS manager,
m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
-- A self join treats the same table as two roles.
14
Write a query that shows daily sales with a running total for the month.
SELECT order_date,
SUM(amount) AS daily_sales,
SUM(SUM(amount)) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
WHERE order_date >= DATE '2026-10-01'
AND order_date < DATE '2026-11-01'
GROUP BY order_date
ORDER BY order_date;
15
A search query that used to take milliseconds now takes 10 seconds as data grew. How do you troubleshoot?
Get the exact SQL from logs or the slow query log and run EXPLAIN ANALYZE to see whether it does a full scan, a bad join order or a big sort. Check for missing or unused indexes, functions on indexed columns, implicit type conversions and outdated statistics. Add the right composite index, rewrite the query, select fewer columns and paginate. For text search, use a full-text index or a search engine instead of LIKE '%term%'.
16
You are designing a new feature. When would you choose a relational database and when a NoSQL database?
Choose a relational database like PostgreSQL or MySQL when data is structured, relationships matter and you need transactions and joins, such as orders, payments and inventory. Choose NoSQL when you need a flexible schema or huge horizontal scale for simple access patterns: MongoDB for documents, Redis for caching and sessions, Cassandra for high-write time series and Elasticsearch for search. Many systems use both, with SQL as the source of truth.
17
Tell me about a mistake you made with production data and how you handled it.
Situation: I ran an update script on production that missed a condition and changed status for more orders than intended. Task: correct it quickly and honestly. Action: I informed my lead immediately, stopped further jobs, used the audit table and a backup to restore the correct values and verified with the business team. Result: data was fixed within an hour. Since then, I always run scripts in a transaction with a row-count check first.
18
Explain the database design of a project you have worked on.
In my e-commerce project I designed tables for users, addresses, products, categories, orders, order_items and payments. Orders had a foreign key to users, and order_items linked orders and products with quantity and price at purchase time, so later price changes did not affect old orders. I added unique constraints on email and SKU, indexes on order status and date, and used Flyway for version-controlled changes.
19
What is the difference between a clustered and a non-clustered index?
A clustered index defines the physical order of rows, so the table data itself is stored in the index and there can be only one. In MySQL InnoDB the primary key is the clustered index. A non-clustered or secondary index is a separate structure holding the indexed columns and a pointer, or the primary key in InnoDB, to the row. That is why a short primary key keeps every secondary index smaller.
20
Explain transaction isolation levels and the problems each one prevents.
Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL, Oracle and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, keeps rows read stable within a transaction, and InnoDB also blocks most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Higher isolation means more locking or retries, so choose per use case and set it with @Transactional(isolation = ...).
21
How does column order matter in a composite index?
A composite B-tree index on (customer_id, status, created_at) is sorted by the first column, then the second, and so on. It can serve queries filtering on customer_id, on customer_id and status, or all three, following the leftmost prefix rule, but it cannot efficiently serve a filter on status alone. Put equality columns first and range or sort columns last. A covering index includes all selected columns so the table need not be read.
EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 42
AND o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 20;
-- Look for: Seq Scan / type=ALL (full scan), large 'rows' estimates,
-- 'Using filesort' or a Sort node, and actual time per step.
-- Fix with: CREATE INDEX ix_orders_cust_status_date
-- ON orders (customer_id, status, created_at DESC);
23
Your application logs show database deadlock errors during peak hours. How do you resolve them?
A deadlock happens when two transactions lock rows in opposite order and wait for each other; the database kills one. Read the deadlock report, such as SHOW ENGINE INNODB STATUS, to find the statements. Fix by updating rows in a consistent order, keeping transactions short, avoiding user interaction or remote calls inside transactions and adding indexes so updates lock fewer rows. Also add a retry for the deadlock error because occasional ones are normal.
24
Under load, requests fail with a connection timeout from HikariCP. What do you check?
This means all pool connections are busy. Check for long-running queries, transactions held open during remote calls, and connection leaks from code that never closes resources; set leakDetectionThreshold to find them. Compare maximumPoolSize with the database's max connections across all instances, since a bigger pool is not always better. Monitor Hikari metrics through Actuator, fix slow queries and keep @Transactional boundaries short.
25
Paging through a large table with OFFSET becomes very slow on later pages. What is a better approach?
With OFFSET 500000, the database still reads and discards half a million rows. Use keyset or seek pagination instead: remember the last seen sort key and query WHERE (created_at, id) < (:lastDate, :lastId) ORDER BY created_at DESC, id DESC LIMIT 20, backed by a matching index. This is fast at any depth and stable when new rows arrive. The trade-off is no direct jump to page N, which suits infinite scroll and APIs.
Frequently asked questions
How important is SQL and Databases in Java Full Stack with React interviews?
Every full stack developer must write and tune SQL. This topic covers keys and constraints, joins, grouping, normalization, indexes, ACID and isolation levels, window functions and query plans with EXPLAIN. It also includes classic interview queries like the Nth highest salary and running totals, plus real scenarios such as deadlocks, connection pool exhaustion and slow pagination.
How should I use the SQL and Databases sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.