Basic SQL Interview Questions for Backend Developer

Basic-level SQL interview questions for Backend Developer positions.

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

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.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Alternative: UUID primary key
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INT REFERENCES users(id)
);

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

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.

-- DELETE: Remove specific rows, can rollback
DELETE FROM orders WHERE status = 'cancelled';

-- TRUNCATE: Remove all rows, reset identity
TRUNCATE TABLE temp_data RESTART IDENTITY;

-- DROP: Remove table completely
DROP TABLE IF EXISTS old_logs;