Click to expand
- Basics
- SQL Queries
- Joins
- Indexing
- Normalization & Denormalization
- Transactions & ACID
- Stored Procedures & Triggers
- Views
- Backup & Restore
- Performance Tuning
- Security
- NoSQL Concepts
- Replication & Sharding
- Database Design & Modeling
- Monitoring & Troubleshooting
- Production-Level Scenarios
- Best Practices
- Commands / Queries Cheat Sheet
Click to expand
Q: What is a database?
A: A structured collection of data stored and accessed electronically.
Q: Difference between SQL and NoSQL?
A: SQL = relational, structured schema; NoSQL = non-relational, flexible schema.
⚡ Q: What is a primary key?
A: Unique identifier for a table row; cannot be NULL.
Q: What is a foreign key?
A: A field that links to the primary key of another table to enforce referential integrity.
Q: What are constraints in SQL?
A: Rules to maintain data integrity, e.g., NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY.
Click to expand
⚡ Q: How to fetch top N records in SQL?
A: SELECT * FROM table ORDER BY column DESC LIMIT N; (MySQL/PostgreSQL)
Q: Difference between WHERE and HAVING?
A: WHERE filters rows before aggregation; HAVING filters after aggregation.
Q: Difference between DISTINCT and GROUP BY?
A: DISTINCT removes duplicates; GROUP BY aggregates data.
Q: How to update multiple rows conditionally?
A: UPDATE table SET column=value WHERE condition;
Click to expand
Q: Difference between INNER JOIN and LEFT JOIN?
A: INNER JOIN returns matching rows only; LEFT JOIN returns all left table rows with NULL for non-matching.
Q: RIGHT JOIN vs FULL OUTER JOIN?
A: RIGHT JOIN returns all right table rows with NULL for non-matching; FULL OUTER JOIN returns all rows from both tables.
Q: Self Join?
A: Joining a table to itself to compare rows within the same table.
Click to expand
⚡ Q: What is an index?
A: A database structure that speeds up data retrieval.
Q: Difference between clustered and non-clustered index?
A: Clustered = sorts table physically; Non-clustered = separate structure pointing to rows.
Q: When not to use an index?
A: On small tables or frequently updated columns (can slow writes).
Click to expand
Q: What is normalization?
A: Process of organizing data to reduce redundancy (1NF, 2NF, 3NF, BCNF).
Q: What is denormalization?
A: Combining tables to improve read performance at the cost of redundancy.
Q: ⚡ Difference between 1NF, 2NF, 3NF?
A: 1NF = atomic values; 2NF = no partial dependency; 3NF = no transitive dependency.
Click to expand
⚡ Q: What are ACID properties?
A: Atomicity, Consistency, Isolation, Durability.
Q: Difference between COMMIT and ROLLBACK?
A: COMMIT saves changes; ROLLBACK discards changes in a transaction.
Q: What is isolation level?
A: Defines how/when changes by one transaction are visible to others (Read Uncommitted, Read Committed, Repeatable Read, Serializable).
Click to expand
Q: What is a stored procedure?
A: Precompiled SQL statements stored in DB for reuse.
Q: What is a trigger?
A: Automated action executed on insert/update/delete events.
Q: ⚡ Difference between trigger and stored procedure?
A: Trigger runs automatically on events; procedure runs manually or via call.
Click to expand
Q: What is a view?
A: Virtual table based on a SELECT query.
Q: ⚡ Difference between a view and a table?
A: View stores query logic, not data; table stores actual data.
Q: Updatable view?
A: A view that allows INSERT, UPDATE, DELETE on underlying tables if certain conditions are met.
Click to expand
Q: What are different backup types?
A: Full, Incremental, Differential.
⚡ Q: How to restore a DB in SQL?
A: Use RESTORE DATABASE db_name FROM backup_file;
Q: Hot vs cold backup?
A: Hot = backup while DB is online; Cold = offline backup.
Click to expand
Q: How to optimize slow queries?
A: Use indexing, avoid SELECT *, use JOINs wisely, analyze execution plans.
Q: What is query execution plan?
A: Step-by-step DB operations to execute a query.
Q: ⚡ How to handle large tables efficiently?
A: Partitioning, indexing, denormalization, caching.
Click to expand
Q: How to secure a database?
A: Use authentication, roles, permissions, encryption, and SSL connections.
Q: Difference between GRANT and REVOKE?
A: GRANT = provide access; REVOKE = remove access.
Click to expand
Q: Difference between MongoDB, Redis, Cassandra?
A: MongoDB = document; Redis = key-value; Cassandra = columnar wide-table.
Q: ⚡ What is CAP theorem?
A: Consistency, Availability, Partition tolerance — choose any 2 in distributed DB.
Q: MongoDB indexing?
A: Supports single, compound, TTL, text, and unique indexes.
Q: Redis data structures?
A: Strings, Hashes, Lists, Sets, Sorted Sets.
Click to expand
Q: What is replication?
A: Copying data across multiple DB servers for redundancy.
Q: What is sharding?
A: Splitting a DB horizontally across multiple servers for scalability.
Q: Master-Slave vs Master-Master replication?
A: Master-Slave = one writable node, many read nodes; Master-Master = multiple writable nodes.
Click to expand
Q: What is ER modeling?
A: Entity-Relationship diagrams to design relational databases.
Q: ⚡ Difference between logical and physical design?
A: Logical = abstract schema; Physical = actual implementation in DB.
Q: What is data modeling?
A: Structuring data to define relationships, constraints, and storage.
Click to expand
Q: How to monitor DB performance?
A: Use metrics like queries/sec, slow queries, locks, CPU/memory usage.
Q: Tools for monitoring?
A: Prometheus, Grafana, Datadog, native DB logs.
Q: How to troubleshoot deadlocks?
A: Identify queries causing locks, kill/blocking sessions, optimize transactions.
Click to expand
⚡ Q: How to handle slow queries in production?
A: Analyze execution plan, add indexes, rewrite queries, consider caching.
Q: How to fix deadlocks?
A: Optimize transactions, reduce lock contention, use proper isolation levels.
⚡ Q: What to do if replication lag occurs?
A: Check network, slave load, apply pending logs, monitor write volume.
Q: Backup failures?
A