Essential Database Knowledge

As a fresher, you don't need to master all types of databases, but you should have solid foundational knowledge in the following areas:

Core SQL Knowledge

• SQL Fundamentals: Focus on basic to intermediate SQL queries
• Practice Resource: LeetCode Top SQL 50 - Excellent for practicing essential SQL queries
• Key SQL Concepts:
  • SELECT, WHERE, GROUP BY, HAVING, ORDER BY
  • JOINs (INNER, LEFT, RIGHT, FULL)
  • Subqueries and Common Table Expressions (CTEs) IGNORABLE
  • Aggregate functions IGNORABLE

Essential Database Theories

1. Database Normalization IMPORTANT

• Purpose: Reduce data redundancy and improve data integrity
• Normal Forms: Understand 1NF, 2NF, 3NF, and BCNF
• When to Denormalize: Performance optimization scenarios

2. Indexing IMPORTANT

• Types: B-tree, Hash, Bitmap indexes
• When to Use: Query performance optimization
• Trade-offs: Faster reads vs. slower writes and storage overhead
• Composite Indexes: Multi-column indexing strategies

3. ACID Properties IMPORTANT

• Atomicity: All operations succeed or none do
• Consistency: Database remains in valid state after transactions
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist even in system failure

4. Transactions IMPORTANT

• Transaction Control: BEGIN, COMMIT, ROLLBACK
• Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
• Deadlocks: Detection and prevention strategies

5. ORMs (Object-Relational Mapping) IGNORABLE

• Purpose: Bridge between object-oriented code and relational databases
• Popular Options:
  • Python: SQLAlchemy, Django ORM
  • JavaScript/Node.js: Sequelize, TypeORM
  • Java: Hibernate, JPA
• Pros and Cons: Abstraction benefits vs. performance considerations

6. Database Migrations IGNORABLE

• Purpose: Version control for database schema changes
• Tools:
  • Alembic (Python)
  • Flyway (Java ecosystem)
  • Prisma Migrate (Node.js)
• Best Practices: Rollback strategies and testing migrations

References

• LeetCode Top SQL 50 - Primary resource for SQL practice
• SQLZoo
• Use The Index, Luke