Interview Prep

SQL Interview Questions for Backend Developer (2026)

35 Questions
With Answers
10 Basic15 Intermediate10 Advanced

Quick Navigation

Basic Questions (10)Intermediate (15)Advanced (10)

Basic SQL Questions

Q1. What is a primary key and why is it important?

basic

Answer

A primary key uniquely identifies each row in a table. It enforces entity integrity, cannot be NULL, must be unique, and is automatically indexed. It's used for lookups and foreign key relationships.

Example Code

SQL
1CREATE TABLE users (
2  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
3  email VARCHAR(255) UNIQUE NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);
6
7-- Alternative: UUID primary key
8CREATE TABLE orders (
9  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10  user_id INT REFERENCES users(id)
11);

Q1. What is a primary key and why is it important?

basic

Answer

A primary key uniquely identifies each row in a table. It enforces entity integrity, cannot be NULL, must be unique, and is automatically indexed. It's used for lookups and foreign key relationships.

Example Code

SQL
1CREATE TABLE users (
2  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
3  email VARCHAR(255) UNIQUE NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);
6
7-- Alternative: UUID primary key
8CREATE TABLE orders (
9  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10  user_id INT REFERENCES users(id)
11);

Q1. What is a primary key and why is it important?

basic

Answer

A primary key uniquely identifies each row in a table. It enforces entity integrity, cannot be NULL, must be unique, and is automatically indexed. It's used for lookups and foreign key relationships.

Example Code

SQL
1CREATE TABLE users (
2  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
3  email VARCHAR(255) UNIQUE NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);
6
7-- Alternative: UUID primary key
8CREATE TABLE orders (
9  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10  user_id INT REFERENCES users(id)
11);

Q1. What is a primary key and why is it important?

basic

Answer

A primary key uniquely identifies each row in a table. It enforces entity integrity, cannot be NULL, must be unique, and is automatically indexed. It's used for lookups and foreign key relationships.

Example Code

SQL
1CREATE TABLE users (
2  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
3  email VARCHAR(255) UNIQUE NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);
6
7-- Alternative: UUID primary key
8CREATE TABLE orders (
9  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10  user_id INT REFERENCES users(id)
11);

Q1. What is a primary key and why is it important?

basic

Answer

A primary key uniquely identifies each row in a table. It enforces entity integrity, cannot be NULL, must be unique, and is automatically indexed. It's used for lookups and foreign key relationships.

Example Code

SQL
1CREATE TABLE users (
2  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
3  email VARCHAR(255) UNIQUE NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);
6
7-- Alternative: UUID primary key
8CREATE TABLE orders (
9  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10  user_id INT REFERENCES users(id)
11);

Q2. Explain the difference between DELETE, TRUNCATE, and DROP.

basic

Answer

DELETE removes specific rows (can use WHERE, triggers fire, can rollback). TRUNCATE removes all rows (faster, resets identity, minimal logging). DROP removes the entire table structure. Use DELETE for selective removal, TRUNCATE to empty table, DROP to remove table entirely.

Example Code

SQL
1-- DELETE: Remove specific rows, can rollback
2DELETE FROM orders WHERE status = 'cancelled';
3
4-- TRUNCATE: Remove all rows, reset identity
5TRUNCATE TABLE temp_data RESTART IDENTITY;
6
7-- DROP: Remove table completely
8DROP TABLE IF EXISTS old_logs;

Q2. Explain the difference between DELETE, TRUNCATE, and DROP.

basic

Answer

DELETE removes specific rows (can use WHERE, triggers fire, can rollback). TRUNCATE removes all rows (faster, resets identity, minimal logging). DROP removes the entire table structure. Use DELETE for selective removal, TRUNCATE to empty table, DROP to remove table entirely.

Example Code

SQL
1-- DELETE: Remove specific rows, can rollback
2DELETE FROM orders WHERE status = 'cancelled';
3
4-- TRUNCATE: Remove all rows, reset identity
5TRUNCATE TABLE temp_data RESTART IDENTITY;
6
7-- DROP: Remove table completely
8DROP TABLE IF EXISTS old_logs;

Q2. Explain the difference between DELETE, TRUNCATE, and DROP.

basic

Answer

DELETE removes specific rows (can use WHERE, triggers fire, can rollback). TRUNCATE removes all rows (faster, resets identity, minimal logging). DROP removes the entire table structure. Use DELETE for selective removal, TRUNCATE to empty table, DROP to remove table entirely.

Example Code

SQL
1-- DELETE: Remove specific rows, can rollback
2DELETE FROM orders WHERE status = 'cancelled';
3
4-- TRUNCATE: Remove all rows, reset identity
5TRUNCATE TABLE temp_data RESTART IDENTITY;
6
7-- DROP: Remove table completely
8DROP TABLE IF EXISTS old_logs;

Q2. Explain the difference between DELETE, TRUNCATE, and DROP.

basic

Answer

DELETE removes specific rows (can use WHERE, triggers fire, can rollback). TRUNCATE removes all rows (faster, resets identity, minimal logging). DROP removes the entire table structure. Use DELETE for selective removal, TRUNCATE to empty table, DROP to remove table entirely.

Example Code

SQL
1-- DELETE: Remove specific rows, can rollback
2DELETE FROM orders WHERE status = 'cancelled';
3
4-- TRUNCATE: Remove all rows, reset identity
5TRUNCATE TABLE temp_data RESTART IDENTITY;
6
7-- DROP: Remove table completely
8DROP TABLE IF EXISTS old_logs;

Q2. Explain the difference between DELETE, TRUNCATE, and DROP.

basic

Answer

DELETE removes specific rows (can use WHERE, triggers fire, can rollback). TRUNCATE removes all rows (faster, resets identity, minimal logging). DROP removes the entire table structure. Use DELETE for selective removal, TRUNCATE to empty table, DROP to remove table entirely.

Example Code

SQL
1-- DELETE: Remove specific rows, can rollback
2DELETE FROM orders WHERE status = 'cancelled';
3
4-- TRUNCATE: Remove all rows, reset identity
5TRUNCATE TABLE temp_data RESTART IDENTITY;
6
7-- DROP: Remove table completely
8DROP TABLE IF EXISTS old_logs;

Intermediate SQL Questions

Q3. What are database indexes and when should you use them?

intermediate

Answer

Indexes are data structures that speed up data retrieval at the cost of slower writes and storage space. Use indexes on: columns frequently in WHERE clauses, JOIN columns, ORDER BY columns. Avoid over-indexing tables with frequent writes.

Example Code

SQL
1-- Create index for frequent queries
2CREATE INDEX idx_orders_customer ON orders(customer_id);
3
4-- Composite index for multi-column queries
5CREATE INDEX idx_orders_date_status ON orders(order_date, status);
6
7-- Partial index for specific conditions
8CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
9
10-- Check if index is being used
11EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Q3. What are database indexes and when should you use them?

intermediate

Answer

Indexes are data structures that speed up data retrieval at the cost of slower writes and storage space. Use indexes on: columns frequently in WHERE clauses, JOIN columns, ORDER BY columns. Avoid over-indexing tables with frequent writes.

Example Code

SQL
1-- Create index for frequent queries
2CREATE INDEX idx_orders_customer ON orders(customer_id);
3
4-- Composite index for multi-column queries
5CREATE INDEX idx_orders_date_status ON orders(order_date, status);
6
7-- Partial index for specific conditions
8CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
9
10-- Check if index is being used
11EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Q3. What are database indexes and when should you use them?

intermediate

Answer

Indexes are data structures that speed up data retrieval at the cost of slower writes and storage space. Use indexes on: columns frequently in WHERE clauses, JOIN columns, ORDER BY columns. Avoid over-indexing tables with frequent writes.

Example Code

SQL
1-- Create index for frequent queries
2CREATE INDEX idx_orders_customer ON orders(customer_id);
3
4-- Composite index for multi-column queries
5CREATE INDEX idx_orders_date_status ON orders(order_date, status);
6
7-- Partial index for specific conditions
8CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
9
10-- Check if index is being used
11EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Q3. What are database indexes and when should you use them?

intermediate

Answer

Indexes are data structures that speed up data retrieval at the cost of slower writes and storage space. Use indexes on: columns frequently in WHERE clauses, JOIN columns, ORDER BY columns. Avoid over-indexing tables with frequent writes.

Example Code

SQL
1-- Create index for frequent queries
2CREATE INDEX idx_orders_customer ON orders(customer_id);
3
4-- Composite index for multi-column queries
5CREATE INDEX idx_orders_date_status ON orders(order_date, status);
6
7-- Partial index for specific conditions
8CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
9
10-- Check if index is being used
11EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Q3. What are database indexes and when should you use them?

intermediate

Answer

Indexes are data structures that speed up data retrieval at the cost of slower writes and storage space. Use indexes on: columns frequently in WHERE clauses, JOIN columns, ORDER BY columns. Avoid over-indexing tables with frequent writes.

Example Code

SQL
1-- Create index for frequent queries
2CREATE INDEX idx_orders_customer ON orders(customer_id);
3
4-- Composite index for multi-column queries
5CREATE INDEX idx_orders_date_status ON orders(order_date, status);
6
7-- Partial index for specific conditions
8CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
9
10-- Check if index is being used
11EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Q4. Explain ACID properties in database transactions.

intermediate

Answer

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions don't interfere), Durability (committed changes persist). Critical for financial and data-integrity applications.

Example Code

SQL
1BEGIN TRANSACTION;
2
3-- Atomicity: Both succeed or both fail
4UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7-- If any error occurs, rollback
8-- Otherwise commit
9COMMIT;
10
11-- Isolation levels
12SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
13SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q4. Explain ACID properties in database transactions.

intermediate

Answer

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions don't interfere), Durability (committed changes persist). Critical for financial and data-integrity applications.

Example Code

SQL
1BEGIN TRANSACTION;
2
3-- Atomicity: Both succeed or both fail
4UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7-- If any error occurs, rollback
8-- Otherwise commit
9COMMIT;
10
11-- Isolation levels
12SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
13SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q4. Explain ACID properties in database transactions.

intermediate

Answer

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions don't interfere), Durability (committed changes persist). Critical for financial and data-integrity applications.

Example Code

SQL
1BEGIN TRANSACTION;
2
3-- Atomicity: Both succeed or both fail
4UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7-- If any error occurs, rollback
8-- Otherwise commit
9COMMIT;
10
11-- Isolation levels
12SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
13SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q4. Explain ACID properties in database transactions.

intermediate

Answer

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions don't interfere), Durability (committed changes persist). Critical for financial and data-integrity applications.

Example Code

SQL
1BEGIN TRANSACTION;
2
3-- Atomicity: Both succeed or both fail
4UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7-- If any error occurs, rollback
8-- Otherwise commit
9COMMIT;
10
11-- Isolation levels
12SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
13SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q4. Explain ACID properties in database transactions.

intermediate

Answer

ACID ensures reliable transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions don't interfere), Durability (committed changes persist). Critical for financial and data-integrity applications.

Example Code

SQL
1BEGIN TRANSACTION;
2
3-- Atomicity: Both succeed or both fail
4UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7-- If any error occurs, rollback
8-- Otherwise commit
9COMMIT;
10
11-- Isolation levels
12SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
13SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q5. How do you prevent SQL injection in your applications?

intermediate

Answer

1) Use parameterized queries/prepared statements (never concatenate user input). 2) Use ORM with proper escaping. 3) Validate and sanitize input. 4) Use least-privilege database accounts. 5) Escape special characters if dynamic SQL is unavoidable.

Example Code

SQL
1-- WRONG: Vulnerable to SQL injection
2$query = "SELECT * FROM users WHERE email = '" + userInput + "'";
3
4-- CORRECT: Parameterized query
5$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
6$stmt->execute(['email' => $userInput]);
7
8-- Using ORM (safe by default)
9User.where(email: params[:email]).first

Q5. How do you prevent SQL injection in your applications?

intermediate

Answer

1) Use parameterized queries/prepared statements (never concatenate user input). 2) Use ORM with proper escaping. 3) Validate and sanitize input. 4) Use least-privilege database accounts. 5) Escape special characters if dynamic SQL is unavoidable.

Example Code

SQL
1-- WRONG: Vulnerable to SQL injection
2$query = "SELECT * FROM users WHERE email = '" + userInput + "'";
3
4-- CORRECT: Parameterized query
5$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
6$stmt->execute(['email' => $userInput]);
7
8-- Using ORM (safe by default)
9User.where(email: params[:email]).first

Q5. How do you prevent SQL injection in your applications?

intermediate

Answer

1) Use parameterized queries/prepared statements (never concatenate user input). 2) Use ORM with proper escaping. 3) Validate and sanitize input. 4) Use least-privilege database accounts. 5) Escape special characters if dynamic SQL is unavoidable.

Example Code

SQL
1-- WRONG: Vulnerable to SQL injection
2$query = "SELECT * FROM users WHERE email = '" + userInput + "'";
3
4-- CORRECT: Parameterized query
5$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
6$stmt->execute(['email' => $userInput]);
7
8-- Using ORM (safe by default)
9User.where(email: params[:email]).first

Q5. How do you prevent SQL injection in your applications?

intermediate

Answer

1) Use parameterized queries/prepared statements (never concatenate user input). 2) Use ORM with proper escaping. 3) Validate and sanitize input. 4) Use least-privilege database accounts. 5) Escape special characters if dynamic SQL is unavoidable.

Example Code

SQL
1-- WRONG: Vulnerable to SQL injection
2$query = "SELECT * FROM users WHERE email = '" + userInput + "'";
3
4-- CORRECT: Parameterized query
5$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
6$stmt->execute(['email' => $userInput]);
7
8-- Using ORM (safe by default)
9User.where(email: params[:email]).first

Q5. How do you prevent SQL injection in your applications?

intermediate

Answer

1) Use parameterized queries/prepared statements (never concatenate user input). 2) Use ORM with proper escaping. 3) Validate and sanitize input. 4) Use least-privilege database accounts. 5) Escape special characters if dynamic SQL is unavoidable.

Example Code

SQL
1-- WRONG: Vulnerable to SQL injection
2$query = "SELECT * FROM users WHERE email = '" + userInput + "'";
3
4-- CORRECT: Parameterized query
5$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
6$stmt->execute(['email' => $userInput]);
7
8-- Using ORM (safe by default)
9User.where(email: params[:email]).first

Advanced SQL Questions

Q6. Explain optimistic vs pessimistic locking strategies.

advanced

Answer

Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

Example Code

SQL
1-- Pessimistic: Lock row until transaction completes
2BEGIN;
3SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
4-- Other transactions wait here
5UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
6COMMIT;
7
8-- Optimistic: Version check at write time
9UPDATE inventory 
10SET quantity = quantity - 1, version = version + 1
11WHERE id = 1 AND version = 5;
12-- If affected_rows = 0, another transaction modified it

Q6. Explain optimistic vs pessimistic locking strategies.

advanced

Answer

Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

Example Code

SQL
1-- Pessimistic: Lock row until transaction completes
2BEGIN;
3SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
4-- Other transactions wait here
5UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
6COMMIT;
7
8-- Optimistic: Version check at write time
9UPDATE inventory 
10SET quantity = quantity - 1, version = version + 1
11WHERE id = 1 AND version = 5;
12-- If affected_rows = 0, another transaction modified it

Q6. Explain optimistic vs pessimistic locking strategies.

advanced

Answer

Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

Example Code

SQL
1-- Pessimistic: Lock row until transaction completes
2BEGIN;
3SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
4-- Other transactions wait here
5UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
6COMMIT;
7
8-- Optimistic: Version check at write time
9UPDATE inventory 
10SET quantity = quantity - 1, version = version + 1
11WHERE id = 1 AND version = 5;
12-- If affected_rows = 0, another transaction modified it

Q6. Explain optimistic vs pessimistic locking strategies.

advanced

Answer

Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

Example Code

SQL
1-- Pessimistic: Lock row until transaction completes
2BEGIN;
3SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
4-- Other transactions wait here
5UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
6COMMIT;
7
8-- Optimistic: Version check at write time
9UPDATE inventory 
10SET quantity = quantity - 1, version = version + 1
11WHERE id = 1 AND version = 5;
12-- If affected_rows = 0, another transaction modified it

Q6. Explain optimistic vs pessimistic locking strategies.

advanced

Answer

Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

Example Code

SQL
1-- Pessimistic: Lock row until transaction completes
2BEGIN;
3SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
4-- Other transactions wait here
5UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
6COMMIT;
7
8-- Optimistic: Version check at write time
9UPDATE inventory 
10SET quantity = quantity - 1, version = version + 1
11WHERE id = 1 AND version = 5;
12-- If affected_rows = 0, another transaction modified it

Q7. How would you handle database migrations in a production environment?

advanced

Answer

1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

Example Code

SQL
1-- Safe column addition (non-blocking)
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- Safe column rename (two-step deployment)
5-- Step 1: Add new column, update code to write to both
6ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
7UPDATE users SET full_name = name;
8
9-- Step 2 (after code deployed): Remove old column
10ALTER TABLE users DROP COLUMN name;

Q7. How would you handle database migrations in a production environment?

advanced

Answer

1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

Example Code

SQL
1-- Safe column addition (non-blocking)
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- Safe column rename (two-step deployment)
5-- Step 1: Add new column, update code to write to both
6ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
7UPDATE users SET full_name = name;
8
9-- Step 2 (after code deployed): Remove old column
10ALTER TABLE users DROP COLUMN name;

Q7. How would you handle database migrations in a production environment?

advanced

Answer

1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

Example Code

SQL
1-- Safe column addition (non-blocking)
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- Safe column rename (two-step deployment)
5-- Step 1: Add new column, update code to write to both
6ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
7UPDATE users SET full_name = name;
8
9-- Step 2 (after code deployed): Remove old column
10ALTER TABLE users DROP COLUMN name;

Q7. How would you handle database migrations in a production environment?

advanced

Answer

1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

Example Code

SQL
1-- Safe column addition (non-blocking)
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- Safe column rename (two-step deployment)
5-- Step 1: Add new column, update code to write to both
6ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
7UPDATE users SET full_name = name;
8
9-- Step 2 (after code deployed): Remove old column
10ALTER TABLE users DROP COLUMN name;

Q7. How would you handle database migrations in a production environment?

advanced

Answer

1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

Example Code

SQL
1-- Safe column addition (non-blocking)
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- Safe column rename (two-step deployment)
5-- Step 1: Add new column, update code to write to both
6ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
7UPDATE users SET full_name = name;
8
9-- Step 2 (after code deployed): Remove old column
10ALTER TABLE users DROP COLUMN name;

Interview Tips

  • Practice writing queries without an IDE to simulate whiteboard interviews
  • Explain your thought process as you solve problems
  • Ask clarifying questions about edge cases
  • Consider query performance and scalability

Related Content

Continue Learning

interviewBasic SQL Interview Questions for Backend Developer
interviewIntermediate SQL Interview Questions for Backend Developer
interviewAdvanced SQL Interview Questions for Backend Developer
interviewSQL Interview Questions for Data Analyst (2026)
interviewSQL Interview Questions for Software Engineer (2026)

From Our Blog

The SQL Interview ToolkitSQL Interviews: What Companies Actually Test

Ready for your interview?

Practice with our interactive SQL sandbox and get instant feedback.