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

92 practiced

Explain what a covering index is and how it can produce index-only scans. Given a reporting query that selects columns (customer_id, order_date, total_amount) with WHERE order_date BETWEEN X AND Y, recommend an index to make the query an index-only scan and justify column order in the index.

Sample Answer

A covering index stores all columns a query needs so the database can satisfy the query using the index alone — avoiding fetching table (heap) rows. In PostgreSQL this enables an index-only scan: the planner reads index pages and returns rows when the visibility map shows tuples are visible; otherwise it falls back to heap fetches.

For your query:- SELECT customer_id, order_date, total_amount- WHERE order_date BETWEEN X AND Y

Recommendation (Postgres >=11):

sql

CREATE INDEX idx_orders_orderdate_incl ON orders (order_date) INCLUDE (customer_id, total_amount);

Why this ordering and form:- Put order_date in the indexed key because it's used in a range filter; the index can quickly seek to the start of the range and scan sequentially.- Put customer_id and total_amount as INCLUDE columns (non-key) so they are present in the index payload without affecting ordering or b-tree size significantly.- Because all selected columns are in the index, the planner can perform an index-only scan and avoid random heap I/O when visibility bits indicate tuples are visible.

Practical notes:- Ensure regular VACUUM to maintain the visibility map; otherwise index-only scans may still require heap lookups.- If you often sort by another column (e.g., order_date DESC or customer_id), consider ordering keys accordingly; but for a range filter first, filter column should be the leading key.

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.

Sample Answer

To find customers with no orders in the last 12 months, we can either use an anti-join (LEFT JOIN ... IS NULL) or NOT EXISTS. Both return the same results; performance depends on the RDBMS, indexes, and optimizer.

LEFT JOIN / IS NULL:

sql

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
  AND o.order_date >= CURRENT_DATE - INTERVAL '12 months'
WHERE o.order_id IS NULL;

NOT EXISTS:

sql

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '12 months'
);

Performance trade-offs and preference:- NOT EXISTS often performs better or equal on large datasets because it can short-circuit per row and avoids creating large join intermediate results; optimizers translate it into efficient anti-seek operations when orders.customer_id is indexed.- LEFT JOIN may cause the engine to build a hash/join result or scan more rows, especially if order_date filter is in WHERE instead of ON. Make sure the date filter is in the JOIN condition as shown to avoid filtering out customers prematurely.- Indexes: ensure an index on orders(customer_id, order_date) or at least orders(customer_id) to speed lookups.- For large datasets I prefer NOT EXISTS for clarity and typically better optimizer behavior; test both with EXPLAIN on your DB and ensure proper indexing.

EasyTechnical

66 practiced

You need to break a complex query into readable steps. Given:

orders(order_id, customer_id, order_date, amount)

Write a Common Table Expression (CTE) that first computes monthly_revenue (month, customer_id, revenue) and then selects the top 5 customers by revenue for the latest month. Use PostgreSQL-compatible SQL and explain when CTEs can harm performance.

Sample Answer

Approach: create a first CTE that aggregates revenue per customer per month, then a second CTE or final query that finds the latest month and returns top 5 customers by revenue for that month.

sql

WITH monthly_revenue AS (
  SELECT
    date_trunc('month', order_date)::date AS month,
    customer_id,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1, 2
),
latest_month AS (
  SELECT MAX(month) AS month FROM monthly_revenue
)
SELECT mr.month, mr.customer_id, mr.revenue
FROM monthly_revenue mr
JOIN latest_month lm ON mr.month = lm.month
ORDER BY mr.revenue DESC
LIMIT 5;

Key points:- date_trunc('month', ...) groups by month boundary; casting to date keeps it readable.- Two-step CTEs improve readability and make debugging easier.

When CTEs can harm performance:- In PostgreSQL versions prior to 12, a CTE is an "optimization fence": the planner materializes it and cannot push filters/joins into it, which can cause full scans and worse performance.- Even in v12+, CTEs default to inline where possible, but MATERIALIZED can still force temporary storage. Use simple subqueries or derived tables when you want the optimizer to reorder operations; benchmark on real data.

MediumTechnical

92 practiced

You are shown this query executed frequently:

SELECT o.id, o.customer_id, o.order_date
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31' AND c.country = 'US';

Given that orders has millions of rows and current indexes are on orders.id and customers.id, recommend indexes to improve performance, and explain trade-offs for write-heavy workloads.

Sample Answer

Recommended indexes (goal: make the WHERE + JOIN selective and allow index-only scans where possible):

1) On customers: a btree composite index on country then id CREATE INDEX idx_customers_country_id ON customers(country, id); - Why: the planner can quickly find the customer ids for country = 'US' without scanning the whole table. Having id second makes it a covering index for the join key.

2) On orders: a composite index that makes the date range selective and supports the join OPTION A (if date range is the main filter and many customers): CREATE INDEX idx_orders_order_date_customer ON orders(order_date, customer_id) INCLUDE (id); OPTION B (if filtering by customers first is more selective): CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date) INCLUDE (id); - Why: order_date first is good when the query starts by restricting dates (range scan). customer_id first is better if planner filters customers by country and then probes orders by customer_id. INCLUDE (id) makes the index "covering" in PostgreSQL for this query so the planner may avoid reading the table (index-only scan).

Notes & verification:- Run EXPLAIN (ANALYZE, BUFFERS) before/after to see which plan is chosen. The optimizer may choose to filter customers first or scan orders by date depending on selectivity—pick the order of columns accordingly.- Consider a partial index if the query always targets a small fixed window (example: January 2024): CREATE INDEX idx_orders_jan2024 ON orders(order_date, customer_id) WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'; This is smaller and faster for that query but only helps that date range.

Trade-offs for write-heavy workloads:- More indexes = slower INSERT/UPDATE/DELETE (each DML must maintain indexes). Composite and covering indexes consume more CPU and I/O on writes.- Index bloat and increased storage; more frequent VACUUM/ANALYZE needed.- Partial indexes reduce write overhead (only maintained for matching rows) and are preferred when queries target predictable subsets.- If writes dominate and queries are less frequent, prefer minimal indexes + occasional summary tables or materialized views refreshed on a schedule to serve analytics.- Balance: choose the smallest indexes that provide needed selectivity (partial or include only necessary columns), and monitor write latency after adding indexes.

Practical approach: gather workload stats (selectivity of country and date range), test both composite orders(index) variants and the customers(country,id) index with EXPLAIN ANALYZE, and consider partial indexes or materialized aggregates if write cost becomes unacceptable.

EasyTechnical

64 practiced

Schema:

products(product_id INT, name VARCHAR, price NUMERIC)
orders(order_id INT, product_id INT, qty INT, order_date DATE)

Write a correlated subquery that returns product_id, name, price, and last_order_date (the most recent order_date for that product). Use ANSI SQL and explain when a correlated subquery is less efficient than a JOIN.

Sample Answer

A correlated subquery version (ANSI SQL) to return product_id, name, price, and last_order_date:

sql

SELECT
  p.product_id,
  p.name,
  p.price,
  (
    SELECT MAX(o.order_date)
    FROM orders o
    WHERE o.product_id = p.product_id
  ) AS last_order_date
FROM products p;

Why this works:- The subquery is correlated because it references the outer query alias p.product_id for each product row and returns the most recent order_date (MAX).

When a correlated subquery is less efficient than a JOIN:- Correlated subqueries run once per outer row in many engines, causing O(N*M)-like work if not optimized; for large products tables this can be much slower.- A JOIN with aggregation pushes work into a single grouped scan which databases optimize better. Example equivalent using JOIN:

sql

SELECT p.product_id, p.name, p.price, o.last_order_date
FROM products p
LEFT JOIN (
  SELECT product_id, MAX(order_date) AS last_order_date
  FROM orders
  GROUP BY product_id
) o ON o.product_id = p.product_id;

Use the JOIN/aggregation approach when orders is large or performance matters; use correlated subquery for simplicity when table sizes are small or readability is prioritized.

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.