Top 100+ SQL Interview & University Exam Questions and Answers

Proposed by Dr. Edgar F. Codd in 1985, these 12 rules (numbered 0 to 12) define what constitutes a true Relational Database Management System. Key rules include the Information Rule (all information must be represented logically as table values), Guaranteed Access Rule (every atomic datum is accessible via table name, primary key, and column name), and Systematic Treatment of Null Values.

Normalization is the structural design technique of organizing tables to eliminate data redundancy and prevent insertion, update, and deletion anomalies:
• 1NF (First Normal Form): All column values must be atomic (no arrays or multi-valued lists) and each record must have a unique identifier.
• 2NF (Second Normal Form): Must be in 1NF, and all non-key columns must depend on the entire primary key (eliminates partial functional dependencies in composite keys).
• 3NF (Third Normal Form): Must be in 2NF, and non-key columns must not depend on other non-key columns (eliminates transitive dependencies).

A table can have only ONE Primary Key, and it strictly forbids NULL values. A table can have MULTIPLE Unique Keys, and Unique columns permit NULL values in standard SQL.

SELECT email, COUNT(*) AS occurrences
FROM students
GROUP BY email
HAVING COUNT(*) > 1;

-- Using CTE and ROW_NUMBER() in PostgreSQL / SQL Server / MySQL 8.0:
WITH RankedDuplicates AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM students
)
DELETE FROM students 
WHERE id IN (SELECT id FROM RankedDuplicates WHERE rn > 1);

-- Standard Modern Approach using DENSE_RANK():
WITH SalaryRank AS (
    SELECT emp_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT emp_name, salary 
FROM SalaryRank 
WHERE rnk = 2; -- Change '2' to any N!

A Deadlock occurs when Transaction A holds a lock on Resource 1 and waits for Resource 2, while Transaction B holds a lock on Resource 2 and waits for Resource 1. Neither can proceed. Modern engines implement Deadlock Detection algorithms (using wait-for graphs); when detected, the engine automatically selects the cheaper transaction as the "victim" and rolls it back with an error.

MVCC is the concurrency mechanism used by PostgreSQL and MySQL InnoDB where readers do not block writers, and writers do not block readers. When a row is updated, the engine retains old versions in an Undo Log, presenting a consistent historical snapshot to read transactions without acquiring intrusive table locks.