Database Fundamentals and SQL Literacy Questions

Core relational database concepts and basic SQL literacy. Covers database objects such as tables, rows, and columns, primary keys and foreign keys, relationships and referential integrity, simple schema design concepts and normalization, indexing basics and their effect on performance, transactions and basic consistency concepts, and reading and interpreting simple SQL queries including SELECT JOIN GROUP BY and COUNT. This topic assesses whether a candidate understands how data is stored and related and can translate simple business questions to database queries.

MediumTechnical

68 practiced

Your nightly BI queries are slow because analytics queries join many normalized tables. Describe three denormalization strategies you could apply to speed up dashboard queries, and list trade-offs for each (storage cost, data freshness, update complexity).

MediumTechnical

66 practiced

A transactions table has grown to 2 billion rows. Explain partitioning strategies you could use (range, list, hash) and recommend one for monthly financial reporting where most queries filter by transaction_date. Include pros/cons and how partition pruning helps query performance.

Sample Answer

Clarify: goal is fast monthly financial reporting where most queries filter by transaction_date (e.g., month). Key requirement: queries that read one or few months should avoid scanning 2B rows.

Options:- Range partitioning - How: partition by transaction_date ranges (monthly or quarterly). - Pros: natural fit for time-series; easy pruning for month-based filters; simple lifecycle (drop/archive old months); stats and indexes per partition. - Cons: can create many partitions (manage with automation); uneven data if write volume varies.- List partitioning - How: explicit discrete lists (e.g., business unit codes). - Pros: good when queries filter by discrete categorical keys. - Cons: not ideal here because primary filter is date; maintenance is manual if categories change.- Hash partitioning - How: distribute rows by hash(transaction_id) across N partitions. - Pros: even data distribution, parallelism for writes/reads. - Cons: does not support date-based pruning—queries for a month still touch all partitions.

Recommendation: Monthly range partitioning on transaction_date (or daily for very high-volume months). Example (Postgres style):

sql

CREATE TABLE transactions (
  id BIGINT,
  transaction_date DATE,
  amount NUMERIC,
  ...
) PARTITION BY RANGE (transaction_date);

CREATE TABLE transactions_2025_01 PARTITION OF transactions
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Partition pruning: the planner uses the WHERE transaction_date BETWEEN '2025-01-01' AND '2025-01-31' to include only the matching partition(s). That reduces I/O, index scans and CPU, lowering query latency and memory usage. Also allows partition-level maintenance (VACUUM, index rebuild, retention via DROP PARTITION).

Practical notes:- Ensure statistics per partition; create local indexes where needed (e.g., on customer_id).- Automate partition creation and retention jobs.- Consider subpartitioning (range by month, hash within month) if hotspots or parallelism are required.- Monitor partition count and query plans to validate pruning is effective.

HardTechnical

48 practiced

You're about to add a non-null, non-empty column 'customer_email' to a production customers table used by many dashboards. Describe a safe schema migration plan that avoids breaking running reports and minimizes downtime. Include steps for backfilling, adding the column, switching reads, and rolling back.

Sample Answer

Framework: zero-downtime, backward-compatible, observable, reversible. Plan phases: prepare, add nullable column, backfill asynchronously, validate & add constraint, switch reads, cleanup / rollback.

1) Prepare- Inventory BI artifacts (dashboards, views, ETL jobs) that reference customer email.- Communicate maintenance window and runbook with stakeholders.- Add monitoring metrics (row counts missing email, ETL lag, dashboard errors).

2) Add nullable column (no default to avoid table rewrite)- SQL:

sql

ALTER TABLE customers ADD COLUMN customer_email text;

- Deploy DB change only; dashboards continue reading existing sources (e.g., existing_email field or legacy pipeline).

3) Backfill asynchronously, in safe batches- Run a controlled backfill job that fills customer_email from source of truth (user table, identity service) in batches to avoid locks:

sql

-- pseudo
UPDATE customers
SET customer_email = src.email
FROM (SELECT id, email FROM user_source WHERE email IS NOT NULL LIMIT 10000) src
WHERE customers.id = src.id;

- Use id ranges, LIMIT/OFFSET or WHERE customer_email IS NULL and ORDER BY id, and sleep between batches. Track progress in a migration table.

4) Validate data quality- Run checks: COUNT(*) WHERE customer_email IS NULL; sample nulls; uniqueness/format regex; compare aggregates used by BI.- Fix remaining nulls via business rules (fallback emails, mark records as "no-email").

5) Add NOT NULL constraint without blocking (Postgres example)- Add constraint NOT VALID then validate:

sql

ALTER TABLE customers
  ADD CONSTRAINT customers_customer_email_not_null CHECK (customer_email IS NOT NULL) NOT VALID;

ALTER TABLE customers VALIDATE CONSTRAINT customers_customer_email_not_null;

- Or, in systems lacking NOT VALID, add constraint during low-traffic window after ensuring zero NULLs.

6) Switch reads (BI layer)- Prefer switching at view/ETL layer, not dashboards: update canonical customers_view to select customer_email; keep old column alias for rollback:

sql

CREATE OR REPLACE VIEW customers_view AS
SELECT id,
       COALESCE(customer_email, legacy_email) AS customer_email,
       ...
FROM customers;

- Deploy BI changes behind a feature flag; test in staging followed by canary dashboards.

7) Rollback plan- If backfill problems: stop write jobs, revert view to previous definition, and remove NOT VALID check if already added but not validated.- If constraint validated and must rollback: restore from pre-migration backup snapshot; or add temporary column mapping old value and update dashboards to use legacy field until restore completes.- Always have recent backup and export of affected dashboards and datasets.

8) Cleanup- Once stable for N days, remove legacy columns/aliases and drop migration metadata.

Key risks & mitigations:- Long-running backfill locks → use small batches and id-based updates.- BI breakage → use view abstraction and feature-flagged deploys.- Incomplete data → validation queries and stakeholder sign-off before enforcing NOT NULL.

Metrics to watch:- % rows with customer_email populated, dashboard error rate, ETL job latency, user-reported anomalies.

EasyTechnical

58 practiced

Describe 1NF, 2NF, and 3NF in simple terms and why normalization matters for an operational OLTP system. Then explain one reason BI teams sometimes denormalize schemas for analytics workloads.

EasyTechnical

48 practiced

You need a report that lists every order and the customer name. Two tables:

customers(customer_id PK, name)orders(order_id PK, customer_id FK, total)

Write an SQL query that returns all orders and the customer name, but also includes orders with NULL customer_id. Which JOIN type do you use and why?

Sample Answer

Use a LEFT JOIN so you keep every row from orders and bring in customer.name when customer_id matches; LEFT JOIN preserves orders with NULL or unmatched customer_id.

sql

SELECT
  o.order_id,
  o.customer_id,
  COALESCE(c.name, 'Unknown / Missing') AS customer_name,
  o.total
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Key points:- LEFT JOIN (orders LEFT JOIN customers) returns all orders even when customer_id is NULL or there is no matching customer row.- COALESCE handles missing names for clearer reporting (you can choose NULL, 'Unknown', or an audit flag).- If you used INNER JOIN you'd lose orders with NULL/unmatched customer_id; RIGHT JOIN could work but is less clear/readable in this schema where orders is primary in the result.

Edge cases / data-quality:- Orphaned customer_id values (referential integrity broken) will show with NULL customer_name — flag these for data cleanup.- Large tables: ensure appropriate indexes on customers.customer_id for performance.

Unlock Full Question Bank

Get access to hundreds of Database Fundamentals and SQL Literacy interview questions and detailed answers.

Join thousands of developers preparing for their dream job.