Database Mastery: Essential Knowledge for Senior Engineers

• Position: Senior Software Engineer
• Interview Time: Sep 2024
• Company Type: Health Care
• Company Name: Private

1. DELETE vs TRUNCATE

Question: What are the key differences between DELETE and TRUNCATE?

Answer:

• DELETE: Removes specific rows based on a condition; logs each deleted row, allowing rollback in transactions. Slower due to logging overhead.
• TRUNCATE: Removes all rows in a table; logs only metadata changes, making it faster. Cannot be rolled back in most cases.

Question: When should you prefer DELETE over TRUNCATE?

Answer: Use DELETE when:

• You need to conditionally remove specific rows.
• Maintaining referential integrity with foreign key constraints is critical.

2. Stored Procedure vs Trigger vs Function

Question: What is the primary purpose of stored procedures?

Answer: Stored procedures encapsulate reusable business logic and can perform complex operations involving multiple queries.

Question: How do triggers differ from functions?

Answer:

• Triggers: Automatically execute in response to database events like INSERT, UPDATE, or DELETE. Ideal for enforcing constraints or auditing.
• Functions: Used to compute and return values. Called explicitly within queries or procedures.

3. Sync SQL vs NoSQL

Question: What are the key advantages of SQL over NoSQL?

Answer: SQL excels in structured data and complex queries with strict ACID compliance, ensuring consistency.

Question: When is NoSQL more suitable?

Answer: NoSQL is ideal for:

• Handling unstructured or semi-structured data.
• Applications needing horizontal scaling, such as social media platforms.

4. Aggregate vs Full-Text Search in NoSQL

Question: How do aggregate queries work in NoSQL?

Answer: Aggregate queries (like MongoDB’s aggregation framework) process data pipelines to compute results, such as totals and averages.

Question: What is the advantage of full-text search in NoSQL?

Answer: Full-text search, often powered by indexes, allows querying natural language data efficiently, making it great for applications like search engines.

5. LIKE vs Full-Text Search in SQL

Question: Why is LIKE slower than full-text search?

Answer: LIKE scans data sequentially and lacks optimized indexing, whereas full-text search uses specialized indices for faster pattern matching.

Question: When should LIKE be used?

Answer: Use LIKE for simple, lightweight pattern searches when full-text indexing isn’t configured.

6. When to Use SQL vs NoSQL

Question: How do you choose between SQL and NoSQL?

Answer:

• Choose SQL for financial or healthcare systems needing consistency.
• Choose NoSQL for distributed systems prioritizing speed and scalability.

7. Sharding in NoSQL

Question: What is sharding?

Answer: Sharding partitions a database horizontally across multiple servers, improving scalability by distributing load.

Question: When is sharding beneficial?

Answer: For high-traffic applications where data can be split logically (e.g., by user ID).

8. Schema Design in SQL

Question: What is normalization?

Answer: Normalization reduces redundancy by organizing data into related tables. For example, 3NF ensures no transitive dependencies.

Question: When should you denormalize?

Answer: In read-heavy applications to improve query performance by reducing JOINs.

9. Query Optimization in SQL

Question: What are common query optimization techniques?

Answer:

• Use EXPLAIN to analyze query execution plans.
• Add indexes to frequently queried columns.
• Optimize JOINs with proper indexing.

10. Polyglot Persistence

Question: What is polyglot persistence?

Answer: Using multiple types of databases (SQL and NoSQL) in the same application to leverage their respective strengths.

Question: When is polyglot persistence suitable?

Answer: For complex systems needing structured data (SQL) and unstructured data (NoSQL), such as e-commerce platforms.

🔗 Explore More Insightful Blogs!

Visit the original website for more in-depth articles and resources tailored for developers like you. 🚀