Database Fundamentals and Data Modeling Questions

Encompasses foundational database concepts and techniques for designing and querying data stores. Topics include relational database concepts, structured query language fundamentals for selecting and modifying data, schema design with tables and relationships, normalization principles, entity relationship modeling, indexing and basic performance considerations, transactions and data integrity, constraints and data types, and common operational tasks such as migrations, backups, and simple optimization strategies. Also discusses trade offs with nonrelational stores and when to choose different persistence models.

MediumTechnical

0 practiced

Explain when to create composite indexes versus single-column indexes. Discuss index selectivity, the importance of column order in a composite index, leading columns, and techniques to detect whether an index is being used by the query planner.

MediumTechnical

0 practiced

Compare built-in full-text search in PostgreSQL (tsvector/tsquery) with an external search engine such as Elasticsearch. Discuss indexing, scoring and relevancy tuning, scaling, consistency, and operational overhead. For product search with typos and facets, which would you choose and why?

HardTechnical

0 practiced

Describe how a cost-based query optimizer estimates cardinality and chooses join orders. Explain common causes of cardinality estimation errors (e.g., correlated columns), and pragmatic ways to mitigate bad plans such as improved statistics, histograms, extended statistics, or query rewrites.

EasyTechnical

0 practiced

Explain the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN. Give a short example query for each using two tables: users(id,name) and orders(id,user_id,amount). Describe typical use-cases where each join type is appropriate.

HardTechnical

0 practiced

Compare two-phase commit (2PC) and the Saga pattern for managing distributed transactions across microservices and independent databases. For each approach describe failure modes, compensating action design, performance implications, and when you would prefer one over the other.

Unlock Full Question Bank

Get access to hundreds of Database Fundamentals and Data Modeling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.