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.

MediumTechnical

76 practiced

You need to return all customers who are in the customers table but have no orders in the last 12 months. Given:

customers(customer_id, name)
orders(order_id, customer_id, order_date)

Write two SQL solutions: one using LEFT JOIN/IS NULL (anti-join) and one using NOT EXISTS. Explain performance trade-offs and which you prefer for large datasets.

MediumTechnical

87 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

139 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

89 practiced

You see an EXPLAIN ANALYZE where the planner chooses Nested Loop joins and a Seq Scan on a large table, and the query runs for minutes. The snippet:

Nested Loop
  -> Index Scan on customers (cost ...)
  -> Seq Scan on orders (cost 1000000)

Describe step-by-step how you'd diagnose and fix the performance problem: statistics, indexes, rewriting the query, materialized intermediates, and parallelism. Be concrete about commands or actions.

MediumTechnical

69 practiced

Table: user_events(event_id, user_id, event_time, payload)

There are duplicate events for some users. Write a SQL statement that selects one canonical row per (user_id, payload) pair, keeping the latest event_time. Use ROW_NUMBER() to deduplicate and then show how you would delete duplicates in a single statement (Postgres or other dialect may vary).

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.