Database rounds are critical. Practice high-frequency SQL queries on inner joins, subqueries, grouping aggregates, and normalizations with complete logical traces.

Key Query: Find the 2nd Highest Salary

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Step-by-Step Database Execution Trace

Let's assume we have a table named Employee with the following records:

Sample Employee Table:
EmpID Name Salary
1 Alice 80,000
2 Bob 120,000
3 Charlie 95,000
4 David 120,000

Here is how the database engine evaluates our query step-by-step:

  • Step 1: Evaluate Subquery - The subquery (SELECT MAX(Salary) FROM Employee) scans the salary column and finds the absolute highest salary, which is 120,000.
  • Step 2: Filter Main Query - The outer query filters out any salary not strictly less than the maximum: SELECT MAX(Salary) FROM Employee WHERE Salary < 120,000;.
  • Step 3: Find Maximum of Filtered Salaries - The database filters out Alice (80,000) and Charlie (95,000), as their salaries are less than 120,000. It then takes the maximum of this filtered set, which is 95,000. This is our 2nd highest salary!