SQL proficiency is expected in every Java backend role. Whether you use Hibernate, Spring Data JPA, or raw JDBC, you must understand relational database fundamentals, query optimisation, and transaction management.
| INNER JOIN | Returns rows matching in both tables |
| LEFT JOIN | All rows from left + matching from right (NULL if no match) |
| WHERE vs HAVING | WHERE filters rows before grouping; HAVING filters groups after |
| ACID | Atomicity, Consistency, Isolation, Durability |
| N+1 Problem | 1 query for list + N queries for each item — fix with JOIN FETCH |
| Index | B-tree structure — use on columns in WHERE/JOIN/ORDER BY |
(intersection)
1. What is the difference between WHERE and HAVING?
-- WHERE filters individual rows BEFORE grouping
SELECT department, COUNT(*) FROM employees
WHERE salary > 50000 -- filters rows first
GROUP BY department;
-- HAVING filters groups AFTER aggregation
SELECT department, AVG(salary) as avg_sal FROM employees
GROUP BY department
HAVING AVG(salary) > 60000; -- filters groups2. What are ACID properties?
Atomicity — transaction is all-or-nothing. Either all statements commit or all are rolled back. Consistency — database moves from one valid state to another. Isolation — concurrent transactions don't see each other's uncommitted data. Durability — committed transactions survive system failures (written to disk/WAL).
3. Transaction Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | ✕ Possible | ✕ Possible | ✕ Possible |
| READ COMMITTED | ✓ Safe | ✕ Possible | ✕ Possible |
| REPEATABLE READ | ✓ Safe | ✓ Safe | ✕ Possible |
| SERIALIZABLE | ✓ Safe | ✓ Safe | ✓ Safe |
4. DELETE vs TRUNCATE vs DROP
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| WHERE clause | Yes | No | No |
| Rollback | Yes | No (DDL) | No (DDL) |
| Speed | Slow (row-by-row log) | Fast (deallocate pages) | Fastest |
| Table exists after | Yes | Yes | No |
5. What is the N+1 problem in JPA and how do you fix it?
// N+1 PROBLEM: 1 query to load orders + N queries for each order's items
List<Order> orders = orderRepo.findAll(); // 1 query
for (Order o : orders) {
System.out.println(o.getItems().size()); // N lazy queries!
}
// FIX 1: JOIN FETCH in JPQL
@Query("SELECT o FROM Order o JOIN FETCH o.items")
List<Order> findAllWithItems();
// FIX 2: @EntityGraph
@EntityGraph(attributePaths = {"items"})
List<Order> findAll();
// FIX 3: Batch fetching (hibernate.default_batch_fetch_size=10)6. Window Functions
-- Rank employees by salary within each department
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
-- Running total
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;❓ Frequently Asked Questions
Q: What is a CTE and when do you use it?
A: A Common Table Expression (WITH clause) creates a named temporary result set for the duration of a query. Use for readability, recursive queries (org charts), and re-using sub-results without repeating subqueries.
Q: What is normalisation?
A: Normalisation eliminates data redundancy. 1NF: atomic values, no repeating groups. 2NF: no partial dependencies on composite key. 3NF: no transitive dependencies. Denormalise when read performance outweighs write consistency.
Q: When should you add an index?
A: Add indexes on columns frequently used in WHERE, JOIN ON, and ORDER BY. Avoid indexes on low-cardinality columns (boolean, gender) and on heavily-updated tables (write overhead). Use EXPLAIN to identify full table scans.
Q: JPQL vs Native SQL — when to use which?
A: Use JPQL for most queries (database-agnostic, works on entities). Use native SQL for database-specific functions, window functions, or performance-critical queries that JPQL can't express efficiently.
Q: What is an execution plan?
A: EXPLAIN (MySQL/Postgres) shows how the database executes a query — which indexes are used, estimated rows scanned, join types. Use it to identify slow queries and validate index usage.
Post a Comment
Add