Relational Databases and SQL Questions

Focuses on relational database fundamentals and practical SQL skills. Candidates should be able to write and reason about SELECT queries, JOINs, aggregations, grouping, filtering, common table expressions, and window functions. They should understand schema design trade offs including normalization and denormalization, indexing strategies and index types, query performance considerations and basic optimization techniques, how to read an execution plan, and transaction semantics including isolation levels and ACID guarantees. Interviewers may test writing efficient queries, designing normalized schemas for given requirements, suggesting appropriate indexes, and explaining how to diagnose and improve slow queries.

EasyTechnical

0 practiced

Explain the difference between clustered and non-clustered indexes, and describe how a clustered index affects physical row order and range-scan performance. As a data analyst, when would you request a clustered index on a table used for time-series reporting?

MediumTechnical

0 practiced

Given daily_revenue table:

daily_revenue(day DATE, region VARCHAR, revenue NUMERIC)

Write a SQL query that computes a 7-day moving average of revenue per region using window functions. Return day, region, revenue, moving_avg_7d. Use PostgreSQL or ANSI SQL and explain the window frame clause you used.

MediumTechnical

0 practiced

Discuss pros and cons of surrogate integer keys versus natural keys in analytical data modeling. Provide examples where surrogate keys simplify joins and examples where natural keys are preferable for auditability or deduplication.

EasyTechnical

0 practiced

Table: orders(order_id, customer_id, total_amount)

Write an SQL query that classifies each order as 'low', 'medium', or 'high' based on total_amount using CASE: low < 50, medium between 50 and 200 (inclusive), high > 200. Return order_id, total_amount, and category. Also describe when CASE vs mapping table is preferable for business rules.

HardTechnical

0 practiced

Discuss multi-column (composite) indexes: how column ordering affects which queries can use the index, how the index supports WHERE and ORDER BY, and when to include non-key columns (e.g., INCLUDE in Postgres) to make the index covering. Provide examples of index definitions for queries filtering by (user_id, created_at) and ordering by created_at desc.

Unlock Full Question Bank

Get access to hundreds of Relational Databases and SQL interview questions and detailed answers.

Join thousands of developers preparing for their dream job.