- 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, orDELETE. 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. 🚀
