MySQL Interview Questions 2026

Design a library management system. Include entities (Book, Member, Loan), relationships, and key operations: borrow, return, search, fine calculation. Discuss how to handle concurrent borrows of the last copy of a book.

Write a SQL query to find the second highest salary from an Employee table. Show at least two approaches: using LIMIT/OFFSET, using a subquery with MAX, and using DENSE_RANK(). Discuss which is most efficient and why.

Design a library management system with online book reservation. Students can search, reserve, and borrow books. Librarians can manage inventory. Cover the database schema, REST APIs, and how you handle concurrent reservations of the last copy.

Design Paytm's wallet system that supports deposits, withdrawals, peer-to-peer transfers, and merchant payments. Ensure ACID properties for all transfers, discuss optimistic vs pessimistic locking, and explain how you handle a split-brain scenario where two nodes disagree on a wallet balance.

Explain Oracle's MVCC (Multi-Version Concurrency Control) and how it enables consistent reads without blocking writers. How does it differ from SQL Server's locking approach? What are the downsides of MVCC (undo log growth, long-running transactions)?

Explain Oracle's redo log and undo log. How do they work together to provide crash recovery and read consistency? Walk through what happens when a transaction commits, then the server crashes, then restarts.

Write a SQL query to find the Nth highest salary from an Employee table without using LIMIT/TOP. Then solve it with a window function (DENSE_RANK) and explain which is more portable across Oracle, PostgreSQL, and MySQL.