SQL & Database Interview Questions & Answers (2026) – RankWeb3

SELECT u.name, COUNT(o.id) AS orders

FROM users u

LEFT JOIN orders o ON u.id = o.user_id

WHERE u.active = 1

GROUP BY u.id, u.name

HAVING COUNT(o.id) > 5

ORDER BY orders DESC

LIMIT 10;

-- 10 rows returned ✓

SQL Databases All Levels 30 Questions Updated 2026

SQL & Database Interview
Questions & Answers

📅 Updated: March 2026

⏱️ Read time: ~22 min

🎯 30 questions — Beginner to Advanced

✍️ By RankWeb3 Team

Total Questions

Beginner

Intermediate

Advanced

🌱Beginner QuestionsQ1–Q10

What is a database and what is SQL?

BeginnerVery Common

A database is an organised collection of structured data stored and accessed electronically. A DBMS (Database Management System) is the software that manages databases (MySQL, PostgreSQL, SQLite, SQL Server, Oracle).

SQL (Structured Query Language) is the standard language for interacting with relational databases. It's used to create, read, update, and delete data (CRUD).

SQL — Basic Statements
SELECT * FROM users;                      -- read data
INSERT INTO users (name, email) VALUES ('Meraj', 'm@x.com');
UPDATE users SET name = 'Meraj A' WHERE id = 1;
DELETE FROM users WHERE id = 1;

SQL commands are grouped into sub-languages: DDL (Data Definition: CREATE, ALTER, DROP), DML (Data Manipulation: SELECT, INSERT, UPDATE, DELETE), DCL (Data Control: GRANT, REVOKE), and TCL (Transaction Control: COMMIT, ROLLBACK).

What are primary keys, foreign keys, and unique constraints?

BeginnerVery Common

SQL
CREATE TABLE users (
  id      INT          PRIMARY KEY AUTO_INCREMENT, -- unique row identifier
  email   VARCHAR(255) UNIQUE NOT NULL,             -- must be unique
  name    VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id      INT          PRIMARY KEY AUTO_INCREMENT,
  user_id INT          NOT NULL,
  total   DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE    -- delete orders when user is deleted
    ON UPDATE CASCADE    -- cascade id updates
);

Primary Key: Uniquely identifies each row. Cannot be NULL. One per table (can be composite).
Foreign Key: A column that references the primary key of another table. Enforces referential integrity.
UNIQUE: All values in the column must be different. Can be NULL (unless also NOT NULL). Multiple unique constraints allowed per table.

💡ON DELETE CASCADE vs RESTRICT: CASCADE automatically deletes child rows. RESTRICT (default) prevents deletion of a parent row if child rows exist. SET NULL nullifies the foreign key instead of deleting.

What is the difference between WHERE and HAVING?

BeginnerVery Common

Feature	WHERE	HAVING
Filters	Individual rows	Groups (after GROUP BY)
Aggregate functions?	❌ Cannot use (e.g. SUM, COUNT)	✅ Yes
Execution order	Before GROUP BY	After GROUP BY

SQL
-- WHERE filters rows BEFORE grouping:
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1           -- only active employees
GROUP BY department;

-- HAVING filters groups AFTER grouping:
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1
GROUP BY department
HAVING AVG(salary) > 50000;  -- only depts with avg > 50k

What are the different types of SQL JOINs?

BeginnerVery Common

JOIN Type	Returns	Unmatched rows?
`INNER JOIN`	Rows with matches in BOTH tables	❌ Excluded
`LEFT JOIN`	All left rows + matched right rows	Left: ✅ kept (right = NULL)
`RIGHT JOIN`	All right rows + matched left rows	Right: ✅ kept (left = NULL)
`FULL OUTER JOIN`	All rows from both tables	✅ Both sides kept (NULL for no match)
`CROSS JOIN`	Cartesian product (every combination)	N/A
`SELF JOIN`	Table joined to itself	Used for hierarchies

SQL — JOIN Examples
-- INNER JOIN — only users who have placed orders:
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN — ALL users, orders if they exist:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Users with 0 orders will show order_count = 0 ✓

-- SELF JOIN — employees and their manager:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

What are aggregate functions in SQL?

BeginnerVery Common

SQL
-- COUNT — number of rows:
SELECT COUNT(*) FROM users;           -- all rows
SELECT COUNT(email) FROM users;        -- non-NULL emails only
SELECT COUNT(DISTINCT country) FROM users; -- unique countries

-- SUM, AVG, MIN, MAX:
SELECT
  SUM(total)  AS revenue,
  AVG(total)  AS avg_order,
  MIN(total)  AS smallest,
  MAX(total)  AS largest
FROM orders
WHERE created_at >= '2026-01-01';

-- GROUP BY with aggregates:
SELECT
  department,
  COUNT(*) AS headcount,
  AVG(salary) AS avg_salary,
  MAX(salary) AS top_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

💡NULL behaviour: Aggregate functions ignore NULL values (except COUNT(*) which counts all rows including NULLs). COUNT(column) only counts non-NULL values.

What is the SQL query execution order?

BeginnerVery Common

SQL clauses are written in one order but executed in a different order. Understanding this prevents confusing errors like "column in WHERE can't use SELECT alias".

SQL — Execution Order
-- WRITTEN ORDER:          EXECUTION ORDER:
SELECT   columns        -- 6. Select columns
FROM     table           -- 1. From / load table
JOIN     other_table     -- 2. Join tables
WHERE    condition       -- 3. Filter rows
GROUP BY column          -- 4. Group rows
HAVING   group_condition -- 5. Filter groups
ORDER BY column          -- 7. Sort results
LIMIT    n               -- 8. Limit rows returned

-- Why this matters: SELECT alias can't be used in WHERE!
-- ❌ This fails — WHERE runs before SELECT:
SELECT salary * 12 AS annual FROM emp WHERE annual > 60000;

-- ✅ Use a subquery or CTE instead:
SELECT * FROM (
  SELECT salary * 12 AS annual FROM emp
) t WHERE t.annual > 60000;

What is the difference between DELETE, TRUNCATE, and DROP?

BeginnerVery Common

Command	Removes	WHERE clause?	Rollback?	Speed
`DELETE`	Specific rows	✅ Yes	✅ Yes (DML)	Slow (row by row, logs each)
`TRUNCATE`	All rows (keeps table)	❌ No	⚠️ Usually not	Fast (deallocates pages)
`DROP`	Entire table + data + structure	❌ No	❌ Usually not	Instant

SQL
DELETE FROM orders WHERE status = 'cancelled'; -- specific rows
TRUNCATE TABLE audit_logs;                        -- clear all, keep structure
DROP TABLE IF EXISTS temp_import;               -- destroy entire table

⚠️Always use transactions and backups before running DELETE or TRUNCATE in production. TRUNCATE also resets AUTO_INCREMENT counters and cannot be rolled back in most databases.

What are NULL values in SQL and how do you handle them?

BeginnerCommon

NULL represents the absence of a value — not zero, not an empty string. NULL is unknown. This means any comparison with NULL (including = NULL) returns NULL (unknown), not TRUE or FALSE.

SQL
-- ❌ Wrong — this returns NO rows:
SELECT * FROM users WHERE phone = NULL;

-- ✅ Correct — use IS NULL / IS NOT NULL:
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE — return first non-NULL value:
SELECT name, COALESCE(phone, email, 'no contact') AS contact
FROM users;

-- NULLIF — return NULL if two values are equal:
SELECT NULLIF(score, 0) FROM results; -- turns 0 into NULL

-- IFNULL / ISNULL (MySQL / SQL Server):
SELECT IFNULL(phone, 'N/A') FROM users;

What are SQL data types? What is the difference between CHAR and VARCHAR?

BeginnerCommon

Category	Types	Use case
Integer	`INT`, `BIGINT`, `TINYINT`, `SMALLINT`	IDs, counts, flags
Decimal	`DECIMAL(p,s)`, `FLOAT`, `DOUBLE`	Money (DECIMAL), scientific (FLOAT)
String	`CHAR(n)`, `VARCHAR(n)`, `TEXT`	Fixed/variable length text
Date/Time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`	Temporal data
Boolean	`BOOLEAN` / `TINYINT(1)`	Flags, status
Binary	`BLOB`, `BINARY`	Files, images (usually store path instead)
JSON	`JSON`	Semi-structured data (MySQL 5.7+, PG 9.2+)

SQL — CHAR vs VARCHAR
-- CHAR(n) — fixed length, pads with spaces:
country_code CHAR(2)  -- always 2 bytes: 'IN', 'US', 'GB'

-- VARCHAR(n) — variable length, stores actual length:
email VARCHAR(255)     -- 'a@b.com' uses 7 bytes, not 255

-- Rule: CHAR for fixed-length codes (country, zip), VARCHAR for everything else
-- DECIMAL(10,2) for money — NEVER use FLOAT for currency!
price DECIMAL(10,2)   -- up to 99,999,999.99 with 2 decimal places

What is ORDER BY and LIMIT / OFFSET?

Beginner

SQL — Sorting & Pagination
-- ORDER BY — sort results:
SELECT * FROM products ORDER BY price ASC;   -- cheapest first
SELECT * FROM products ORDER BY price DESC;  -- most expensive first
SELECT * FROM users ORDER BY name ASC, created_at DESC; -- multi-column

-- LIMIT — return only N rows:
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- OFFSET — skip N rows (for pagination):
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;   -- page 3 (skip 20, take 10)

-- PostgreSQL syntax:
LIMIT 10 OFFSET 20;

-- Cursor-based pagination is faster for large tables:
SELECT * FROM posts
WHERE id < 500            -- last seen id
ORDER BY id DESC LIMIT 10;

⚡Intermediate QuestionsQ11–Q21

What are database indexes and how do they work?

IntermediateVery Common

An index is a data structure (usually a B-tree) that the database maintains separately from the table to speed up data retrieval. Like a book's index — instead of reading every page, you jump directly to the right location.

SQL — Indexes
-- Create index on frequently-queried column:
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Composite index (column order matters!):
CREATE INDEX idx_orders_user_date
  ON orders(user_id, created_at);
-- Helps: WHERE user_id=1, WHERE user_id=1 AND created_at>x
-- Doesn't help: WHERE created_at>x (without user_id first)

-- Unique index (also enforces uniqueness):
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Check which queries use an index:
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

When to add indexes: Columns frequently used in WHERE, JOIN ON, and ORDER BY. When NOT to: Small tables, columns rarely queried, columns that change very frequently (updates become expensive because the index must be updated too).

⚠️Too many indexes = slow writes. Every INSERT/UPDATE/DELETE must update all indexes on that table. Index what you measure — use EXPLAIN to confirm indexes are being used before adding more.

What is database normalization? Explain 1NF, 2NF, and 3NF.

IntermediateVery Common

Normalization is the process of organizing a database to reduce data redundancy and improve data integrity by dividing large tables into smaller, related tables.

SQL — Normalization Example
-- ❌ UNNORMALIZED — all data in one table:
-- order_id | customer | customer_email | product1, product2 | city
-- Problems: duplicate customer data, multiple values in one column

-- ✅ 1NF (First Normal Form):
-- Rule: Atomic values (no arrays/multi-values), each row unique
-- Fix: Split 'product1,product2' into separate rows

-- ✅ 2NF (Second Normal Form):
-- Rule: 1NF + no PARTIAL dependency on composite primary key
-- Fix: If PK is (order_id, product_id), 'customer_name'
--      depends only on order_id → move to separate Orders table

-- ✅ 3NF (Third Normal Form):
-- Rule: 2NF + no TRANSITIVE dependencies (non-key → non-key)
-- Fix: If 'city' determines 'country', move to a Cities table

-- Result — 3 clean, normalized tables:
CREATE TABLE customers (id, name, email);
CREATE TABLE orders    (id, customer_id, date);
CREATE TABLE order_items(id, order_id, product_id, qty);

💡Denormalization: Sometimes intentionally breaking normalization rules (adding redundant data) improves read performance. Reporting databases (OLAP) often denormalize. Transactional databases (OLTP) stay normalized.

What are subqueries and CTEs (Common Table Expressions)?

IntermediateVery Common

SQL
-- Subquery in WHERE (scalar subquery):
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM (derived table):
SELECT dept, avg_sal
FROM (
  SELECT department AS dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) t
WHERE avg_sal > 60000;

-- CTE (WITH clause) — cleaner and reusable:
WITH dept_averages AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
),
high_earners AS (
  SELECT * FROM dept_averages WHERE avg_sal > 60000
)
SELECT e.name, d.avg_sal
FROM employees e
JOIN high_earners d ON e.department = d.department;

-- Recursive CTE — traverse hierarchies:
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE manager_id IS NULL  -- anchor (CEO)
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id    -- recursive step
)
SELECT * FROM org_chart;

What are window functions in SQL?

IntermediateVery Common

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does. They use the OVER() clause.

SQL — Window Functions
-- ROW_NUMBER — sequential row number within partition:
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- RANK / DENSE_RANK — handles ties differently:
-- RANK:       1, 2, 2, 4  (skips 3)
-- DENSE_RANK: 1, 2, 2, 3  (no gaps)

-- Running total:
SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- LAG/LEAD — access previous/next row:
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

💡Get top N per group: Use ROW_NUMBER() in a CTE, then filter WHERE rn <= N. This is one of the most common interview questions: "Find the top 3 highest-paid employees in each department."

What are database transactions and ACID properties?

IntermediateVery Common

A transaction is a sequence of SQL operations that are treated as a single unit — either all succeed (COMMIT) or all fail (ROLLBACK). ACID defines the properties that guarantee reliability.

A — Atomicity: All operations in a transaction succeed, or none do. (Bank transfer: debit + credit are one atomic unit.)
C — Consistency: The database moves from one valid state to another. Constraints are never violated mid-transaction.
I — Isolation: Concurrent transactions don't interfere. Uncommitted changes in one transaction aren't visible to others (to varying degrees, based on isolation level).
D — Durability: Once committed, changes survive power failures, crashes, or errors. Data is persisted to disk.

SQL — Transactions
-- Bank transfer — must be atomic:
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit

-- Both succeeded? Commit:
COMMIT;

-- Something failed? Roll back everything:
ROLLBACK;

-- Savepoints — partial rollback:
SAVEPOINT before_update;
-- ... do some work ...
ROLLBACK TO SAVEPOINT before_update; -- only undo from savepoint

What are transaction isolation levels?

IntermediateCommon

Level	Dirty Read	Non-Repeatable Read	Phantom Read
`READ UNCOMMITTED`	✅ Possible	✅ Possible	✅ Possible
`READ COMMITTED`	❌ Prevented	✅ Possible	✅ Possible
`REPEATABLE READ`	❌ Prevented	❌ Prevented	✅ Possible
`SERIALIZABLE`	❌ Prevented	❌ Prevented	❌ Prevented

SQL
-- Set isolation level for a session:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Read phenomena:
-- Dirty Read:          T1 reads uncommitted data from T2 (T2 rolls back → T1 read garbage)
-- Non-Repeatable Read: T1 reads row, T2 updates it, T1 reads again → different result
-- Phantom Read:        T1 runs range query, T2 inserts row, T1 reruns → new "phantom" row

💡In practice: MySQL InnoDB defaults to REPEATABLE READ. PostgreSQL defaults to READ COMMITTED. For most apps READ COMMITTED is fine. Use SERIALIZABLE only for critical financial operations (very slow due to locking).

What are stored procedures, functions, and triggers?

IntermediateCommon

SQL — Stored Procedures & Triggers
-- Stored Procedure — reusable block, can modify data:
DELIMITER //
CREATE PROCEDURE transfer_funds(
  IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)
)
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END//
DELIMITER ;

CALL transfer_funds(1, 2, 500.00);

-- Trigger — auto-run on INSERT/UPDATE/DELETE:
CREATE TRIGGER log_order_changes
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, old_status, new_status, changed_at)
  VALUES ('orders', OLD.status, NEW.status, NOW());
END;

What is the difference between UNION and UNION ALL?

Intermediate

Feature	UNION	UNION ALL
Duplicates	❌ Removed (slower)	✅ Kept (faster)
Performance	Slower — sorts to dedupe	Faster — no deduplication
Use when	You need distinct results	You know results are distinct or want all rows

SQL
-- Both queries must have same number of columns and compatible types:
SELECT email FROM customers
UNION                        -- removes duplicates
SELECT email FROM suppliers;

SELECT id, amount, 'sale' AS type FROM sales
UNION ALL                    -- keep all rows including duplicates
SELECT id, amount, 'return' AS type FROM returns;

-- INTERSECT — rows in BOTH queries:
SELECT email FROM customers INTERSECT SELECT email FROM newsletter;

-- EXCEPT (MINUS in Oracle) — rows in first but NOT second:
SELECT email FROM customers EXCEPT SELECT email FROM unsubscribed;

What is the difference between SQL and NoSQL databases?

IntermediateVery Common

Feature	SQL (Relational)	NoSQL
Schema	Fixed, predefined	Flexible, dynamic
Data model	Tables + rows	Documents, key-value, graphs, columns
Relationships	Foreign keys + JOINs	Embedding or manual references
ACID	✅ Full support	Varies (MongoDB: since v4)
Scaling	Vertical (scale up)	Horizontal (scale out)
Examples	PostgreSQL, MySQL, SQLite	MongoDB, Redis, Cassandra, DynamoDB
Best for	Complex relationships, reporting, financial	Flexible data, real-time, massive scale

NoSQL types: Document (MongoDB — JSON-like), Key-Value (Redis — cache/sessions), Column-family (Cassandra — time-series, IoT), Graph (Neo4j — social networks, recommendations).

What are views in SQL?

Intermediate

A view is a named, saved SQL query that you can query like a table. It doesn't store data itself — it executes the underlying query every time it's queried. Views simplify complex queries and can restrict data access.

SQL
-- Create a view that hides sensitive columns:
CREATE VIEW public_users AS
  SELECT id, name, created_at  -- no email, password_hash
  FROM users
  WHERE active = 1;

-- Query the view just like a table:
SELECT * FROM public_users WHERE id = 1;

-- Materialized View (PostgreSQL) — stores results physically:
CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY 1;

-- Refresh materialized view when data changes:
REFRESH MATERIALIZED VIEW monthly_revenue;

How do you find and remove duplicate rows in SQL?

IntermediateCommon

SQL — Duplicates
-- Find duplicates:
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- View all duplicate rows with ids:
SELECT *
FROM users
WHERE email IN (
  SELECT email FROM users
  GROUP BY email HAVING COUNT(*) > 1
);

-- Delete duplicates, keep lowest id:
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

-- Using ROW_NUMBER (more flexible):
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY email ORDER BY id
  ) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

🔥Advanced QuestionsQ22–Q30

How does the query optimizer work and how do you use EXPLAIN?

AdvancedVery Common

The query optimizer analyzes your SQL query and generates an execution plan — deciding which indexes to use, the order of table joins, and whether to use nested loops, hash joins, or merge joins.

SQL — EXPLAIN
-- PostgreSQL EXPLAIN ANALYZE (actually runs the query):
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Key things to look for in EXPLAIN output:
-- Seq Scan    = full table scan (no index used — BAD for large tables)
-- Index Scan  = using an index (GOOD)
-- Hash Join   = joining using hash table
-- rows=       = estimated rows (compare to actual for accuracy)
-- cost=       = startup cost..total cost (lower is better)

-- Fix a slow query:
-- 1. EXPLAIN to find Seq Scans on large tables
-- 2. Add index on the column used in WHERE/JOIN
-- 3. EXPLAIN again to confirm Index Scan is now used
-- 4. Run query to measure actual improvement

What is database sharding and partitioning?

AdvancedCommon

Partitioning splits a large table into smaller pieces within the same database server. Sharding distributes data across multiple database servers (horizontal scaling).

SQL — Table Partitioning (PostgreSQL)
-- Range partitioning by date (common for logs, events):
CREATE TABLE orders (
  id         BIGINT NOT NULL,
  user_id    INT,
  created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025
  PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE orders_2026
  PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Query automatically hits only relevant partition:
SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- → Only scans orders_2026, not orders_2025

Feature	Partitioning	Sharding
Location	Same server	Multiple servers
Transparency	Fully transparent to app	App must know shard routing
Use case	Very large tables (billions of rows)	Massive scale, global distribution
Complexity	Low	High (cross-shard queries, rebalancing)

What is database replication and what are its types?

AdvancedCommon

Replication copies data from one database (primary/master) to one or more databases (replicas/secondaries). It provides high availability, fault tolerance, and read scalability.

Primary-Replica (Master-Slave): Writes go to primary, replicated to replicas. Replicas handle read-only queries. Most common pattern.
Primary-Primary (Multi-Master): Both servers accept writes. Complex conflict resolution required.
Synchronous replication: Transaction only commits after replica confirms write. Strong consistency, slower writes.
Asynchronous replication: Primary commits immediately, replica catches up. Faster writes, but replica may lag (read stale data).
Logical replication: Replicates data changes (row-level). Allows selectively replicating specific tables.
Physical replication: Copies raw disk blocks. Faster but replica must be same PostgreSQL version.

💡Read scaling pattern: Direct all write operations to the primary, distribute read-heavy queries (reports, analytics, dashboard) across replicas. This is a very common architecture in production Node.js/Python apps.

What is a deadlock in databases and how do you prevent it?

AdvancedCommon

A deadlock occurs when two or more transactions are each waiting for the other to release a lock — creating a circular dependency where none can proceed.

SQL — Deadlock Scenario & Prevention
-- Deadlock scenario:
-- T1: UPDATE users SET ... WHERE id = 1  (locks row 1)
-- T2: UPDATE users SET ... WHERE id = 2  (locks row 2)
-- T1: UPDATE users SET ... WHERE id = 2  (waits for T2)
-- T2: UPDATE users SET ... WHERE id = 1  (waits for T1) ← DEADLOCK!

-- Prevention strategy 1: Consistent lock ordering
-- Always lock rows in the same order (lowest id first):
-- T1 and T2 both: lock id=1 THEN id=2 → no deadlock

-- Prevention strategy 2: SELECT FOR UPDATE
BEGIN;
SELECT * FROM accounts
  WHERE id IN (1, 2)
  ORDER BY id             -- consistent order!
  FOR UPDATE;              -- acquire locks upfront
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

💡DB's response: Most databases detect deadlocks automatically and abort one of the transactions (the "victim") with an error. Your application should catch this error and retry the transaction.

What is the N+1 query problem and how do you solve it?

AdvancedVery Common

The N+1 problem occurs when you execute 1 query to fetch N records, then N additional queries to fetch related data for each record — instead of fetching everything in 1 or 2 queries.

SQL — N+1 Problem & Solutions
-- ❌ N+1 problem (100 users = 101 queries!):
-- Query 1: SELECT * FROM users LIMIT 100
-- Query 2: SELECT * FROM orders WHERE user_id = 1
-- Query 3: SELECT * FROM orders WHERE user_id = 2
-- ... 100 more queries

-- ✅ Solution 1: JOIN (2 queries or 1):
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LIMIT 100;  -- 1 query instead of 101!

-- ✅ Solution 2: Eager loading / batch query:
-- Query 1: SELECT * FROM users LIMIT 100
-- Query 2: SELECT * FROM orders WHERE user_id IN (1,2,...,100)
-- Then join in application code

-- In ORMs: use .include()/.populate()/.eager():
-- Mongoose:  User.find().populate('orders')
-- Prisma:    prisma.user.findMany({ include: { orders: true } })
-- Rails:     User.includes(:orders)

What is CAP theorem?

AdvancedCommon

The CAP theorem states that a distributed database system can only guarantee two of three properties simultaneously:

C — Consistency: Every read receives the most recent write or an error. All nodes see the same data at the same time.
A — Availability: Every request receives a response (not necessarily the most recent data). The system never returns an error.
P — Partition Tolerance: The system continues operating even if network communication between nodes fails.

Combination	Trade-off	Examples
CP (Consistency + Partition)	May reject requests to stay consistent	MongoDB, HBase, Zookeeper
AP (Availability + Partition)	May return stale data	Cassandra, CouchDB, DynamoDB
CA (Consistency + Availability)	Cannot tolerate network partition	Traditional RDBMS (single node)

💡In practice: Network partitions always happen in distributed systems, so P is non-negotiable. The real choice is between C and A. Choose CP for financial systems (banking, inventory). Choose AP for social media, product catalogs where eventual consistency is fine.

What is database connection pooling and why is it important?

AdvancedCommon

Creating a new database connection for every request is expensive — it involves TCP handshake, authentication, and session setup. A connection pool maintains a cache of pre-established connections that are reused across requests.

Node.js — Connection Pooling
// PostgreSQL with pg-pool:
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max:              20,   // max connections in pool
  min:              2,    // min idle connections
  idleTimeoutMillis: 30000, // close idle connections after 30s
  connectionTimeoutMillis: 2000, // throw if no free connection in 2s
});

// Use connection from pool (auto-returned after query):
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// For transactions, check out a dedicated client:
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE ...');
  await client.query('COMMIT');
} finally {
  client.release(); // ← ALWAYS release back to pool
}

What are SQL injection attacks and how do you prevent them?

AdvancedVery Common

SQL injection is when an attacker inserts malicious SQL into a query by manipulating user-supplied input — potentially reading all data, bypassing authentication, or destroying the database.

SQL — Injection Attack & Prevention
// ❌ VULNERABLE — string concatenation:
const query = `SELECT * FROM users WHERE email = '${req.body.email}'`;
// Attacker input: ' OR '1'='1
// Becomes: SELECT * FROM users WHERE email = '' OR '1'='1'
// → Returns ALL users!

// Worse: '; DROP TABLE users; --
// → Drops the entire users table!

// ✅ SAFE — parameterised queries (values never interpreted as SQL):
// PostgreSQL:
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  [req.body.email]  // ← treated as data, never as SQL
);

// MySQL:
connection.query(
  'SELECT * FROM users WHERE email = ?',
  [req.body.email]
);

// ✅ Use an ORM (Prisma, Sequelize, TypeORM):
prisma.user.findUnique({ where: { email: req.body.email } });
// ORMs always use parameterised queries internally

⚠️Golden rule: NEVER concatenate user input into SQL strings. Always use parameterised queries (prepared statements) or an ORM. SQL injection is one of the top OWASP security vulnerabilities and has caused massive data breaches.

What are database migrations and why are they important?

AdvancedCommon

Database migrations are version-controlled scripts that incrementally modify the database schema over time. They let teams evolve the schema safely, consistently, and reproducibly across all environments (dev, staging, production).

SQL — Migration Example (Prisma)
-- migrations/001_create_users.sql
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name  VARCHAR(100) NOT NULL
);

-- migrations/002_add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- migrations/003_create_orders.sql
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

Bash — Migration Tools
# Prisma (Node.js):
npx prisma migrate dev    # create + run migration
npx prisma migrate deploy # run in production

# Flyway (any language):
flyway migrate

# Liquibase:
liquibase update

💡Best practices: Never modify existing migration files — add new ones. Write rollback (down) migrations. Test migrations on a copy of production data before deploying. Use zero-downtime migration patterns for large tables (add column → backfill → add constraint in separate steps).

The Complete Interview Hub is Here

You've now covered all 7 core interview topics — JavaScript, Git, Python, React, HTML & CSS, Node.js, and SQL. Bookmark RankWeb3 and share it with anyone preparing for tech interviews.

← Node.js Q&A

JavaScript Git & GitHub Python React HTML & CSS Node.js All Articles

SQL & Database Interview
Questions & Answers

🌱Beginner QuestionsQ1–Q10

⚡Intermediate QuestionsQ11–Q21

🔥Advanced QuestionsQ22–Q30

The Complete Interview Hub is Here

we believe learning is more than just acquiring knowledge

quick link

help center

Get In Touch

Info@rankweb3.com

©2026, Rankweb3. All Rights Reserved

SQL & Database InterviewQuestions & Answers

🌱Beginner QuestionsQ1–Q10

⚡Intermediate QuestionsQ11–Q21

🔥Advanced QuestionsQ22–Q30

The Complete Interview Hub is Here

we believe learning is more than just acquiring knowledge

quick link

help center

Get In Touch

Info@rankweb3.com

©2026, Rankweb3. All Rights Reserved

SQL & Database Interview
Questions & Answers