Infosys SQL Interview Questions

1NF: atomic values (no repeating groups), primary key exists. 2NF: 1NF + no partial dependencies (every non-key column depends on the entire primary key — relevant for composite PKs). 3NF: 2NF + no transitive dependencies. Example violation: Employee(EmpID, Dept, DeptHead) — DeptHead depends on Dept, not EmpID. Fix: Employee(EmpID, Dept) and Department(Dept, DeptHead).

Precompiled SQL statements stored in the database and executed as a unit. Advantages: Performance (query plan compiled once and cached, reducing parse time on repeated calls), Security (grant EXECUTE without exposing table permissions), Reusability (business logic centralized, callable from Java/.NET), Network reduction (one call executes multiple SQL statements). At Infosys BPM, stored procedures encapsulate ETL logic callable by multiple application teams without duplicating SQL code.

Stored procedure is explicitly called by an application or scheduler. Trigger fires automatically on INSERT, UPDATE, or DELETE events on a table — cannot be called directly. AFTER trigger: fires post-operation, commonly used for audit logs. INSTEAD OF trigger: overrides the DML operation, used on Views. At Infosys, AFTER INSERT triggers automatically write audit records ensuring 100% audit coverage without application code changes. Cascading triggers (trigger A fires trigger B) are dangerous and banned in many Infosys coding standards.

SELECT e.employee_name, e.salary AS emp_salary, m.salary AS mgr_salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary; This self-join compares each employee to their manager in the same table. Follow-up: include department name by adding a JOIN to the departments table on e.department_id.

A transaction is a logical unit of work that completes entirely or not at all. Atomicity: all statements succeed together or all rollback (bank transfer debit + credit must both succeed). Consistency: database moves from one valid state to another. Isolation: concurrent transactions do not interfere — controlled by isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE). Durability: committed changes survive crashes via write-ahead logging. Commands: BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.

Deadlock: two transactions each hold a lock the other needs, causing indefinite waiting. SQL Server detects deadlocks and kills the transaction with lower deadlock priority. Prevention strategies: 1) Always access tables in the same order across all transactions. 2) Keep transactions short. 3) Use row-level locking. 4) Use READ COMMITTED SNAPSHOT isolation (RCSI) which uses row versioning to eliminate read-write deadlocks — the standard at Infosys production databases.