Intermediate SQL Interview Questions for Backend Developer

Intermediate-level SQL interview questions for Backend Developer positions.

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

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.

-- Create index for frequent queries
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index for multi-column queries
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Check if index is being used
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

2. Explain ACID properties in database transactions.

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.

BEGIN TRANSACTION;

-- Atomicity: Both succeed or both fail
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If any error occurs, rollback
-- Otherwise commit
COMMIT;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

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.

-- WRONG: Vulnerable to SQL injection
$query = "SELECT * FROM users WHERE email = '" + userInput + "'";

-- CORRECT: Parameterized query
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $userInput]);

-- Using ORM (safe by default)
User.where(email: params[:email]).first