Find Second Highest Salary - Five Different Approaches

▶

Steps

setup

Start Sequential Scan on Employee Table

The query planner begins by scanning all rows in the Employee table to retrieve all Salary values.

💡 Sequential scan reads every row because no index on Salary exists; this is the first step to gather data for further processing.

Line:

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')

💡 Understanding that the scan reads all rows is crucial because the query needs all salaries to find distinct values.

fill_row

Aggregate Distinct Salaries

The Aggregate operator processes the scanned rows to extract distinct Salary values, removing duplicates.

💡 Removing duplicates is essential to correctly identify the second highest salary without counting repeated highest salaries multiple times.

Line:

SELECT DISTINCT Salary FROM Employee

💡 Distinct aggregation reduces the salary list from [100, 200, 300, 300] to [100, 200, 300].

fill_cells

Sort Salaries in Descending Order

The Sort operator orders the distinct salaries from highest to lowest: 300, 200, 100.

💡 Sorting is necessary to identify the highest and second highest salaries by position.

Line:

ORDER BY Salary DESC

💡 Sorting places the highest salary first, so offsetting by 1 will skip it to get the second highest.

prune

Apply OFFSET to Skip Highest Salary

The Limit operator skips the first salary (300) using OFFSET 1, moving the cursor to the second salary.

💡 OFFSET allows skipping the highest salary so the next fetched row is the second highest.

Line:

LIMIT 1 OFFSET 1

💡 OFFSET 1 moves the pointer past the highest salary to the second highest.

traverse

Fetch the Second Highest Salary

The Limit operator fetches the next salary after offset, which is 200, the second highest salary.

💡 Fetching the row after offset returns the desired second highest salary.

Line:

LIMIT 1 OFFSET 1

💡 The query returns 200 as the second highest salary after skipping 300.

reconstruct

Return Result to Client

The query result 200 is returned to the client application as the second highest salary.

💡 Returning the result completes the query execution and provides the answer to the user.

Line:

result = cursor.fetchone()
print(result[0] if result else 'NULL')

💡 The client receives the correct second highest salary value 200.

prune

Handle Case When No Second Highest Salary Exists

If the query returns no rows (e.g., only one distinct salary), the client prints NULL.

💡 This step ensures the query gracefully handles cases with insufficient distinct salaries.

Line:

print(result[0] if result else 'NULL')

💡 The query result can be empty; the client must handle this to avoid errors.

reconstruct

Summary of Query Plan Execution

All operators have completed their tasks, producing the final result.

💡 Reviewing the completed states helps consolidate understanding of the query execution flow.

Line:

N/A

💡 The query plan stages are sequential and build upon each other to produce the final answer.

prune

Explain Planner's Choice of Sequential Scan

The planner chooses a sequential scan because no index exists on the Salary column.

💡 Understanding planner decisions helps optimize queries and schema design.

Line:

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')

💡 Without an index, scanning all rows is necessary to access all salaries.

reconstruct

Final Confirmation of Result

The final output 200 is confirmed as the second highest salary and printed.

💡 This final step confirms the query's correctness and output.

Line:

print(result[0] if result else 'NULL')

💡 The query correctly identifies and returns the second highest salary.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# STEP 1: Create Employee table
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')

# STEP 2: Insert sample data
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300), (4, 300)])

# STEP 3: Define query to find second highest salary
query = '''
SELECT Salary FROM (
  SELECT DISTINCT Salary FROM Employee
  ORDER BY Salary DESC
  LIMIT 1 OFFSET 1
)
'''

# STEP 4: Execute query
cursor.execute(query)

# STEP 5: Fetch result
result = cursor.fetchone()

# STEP 6: Print result or NULL if none
print(result[0] if result else 'NULL')

# STEP 7: Close connection
conn.close()

📊

Find Second Highest Salary - Five Different Approaches - Watch the Algorithm Execute, Step by Step

Watching each step of the query plan execution reveals how SQL engines optimize and execute queries, making it easier to understand complex SQL operations beyond just reading code.

Sequential scan reads all 4 rows from Employee table.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salary

SortORDER BY Salary DESC

LimitLIMIT 1 OFFSET 1

Rows processed: 4

SeqScan chosen due to lack of index on Salary.

Aggregate operator outputs distinct salaries: 100, 200, 300.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESC

LimitLIMIT 1 OFFSET 1

Rows processed: 3

Aggregate distinct reduces duplicates before sorting.

Sort operator orders salaries descending: 300, 200, 100.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1

Rows processed: 3

Sorting prepares data for offset and limit.

Limit operator skips first row (300) due to OFFSET 1.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1rows≈1cost=1

Rows processed: 1

Limit with offset fetches the second highest salary.

Limit operator returns salary 200 as second highest.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1rows≈1cost=1

Rows processed: 1

Final step returns the second highest salary.

Result 200 returned to client.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1rows≈1cost=1

Rows processed: 1

Final output delivered.

No second highest salary found; client prints NULL.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈1cost=5

SortORDER BY Salary DESCrows≈1cost=3

LimitLIMIT 1 OFFSET 1rows≈0cost=1

Rows processed: 0

Handles empty result gracefully.

All operators done; final result ready.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1rows≈1cost=1

Rows processed: 1

Query plan executed successfully.

Planner chose SeqScan due to no index on Salary.

SeqScanEmployeerows≈4cost=10

AggregateDISTINCT Salaryrows≈3cost=5

SortORDER BY Salary DESCrows≈3cost=3

LimitLIMIT 1 OFFSET 1rows≈1cost=1

Rows processed: 4

SeqScan is the most efficient available method here.

Key Takeaways

✓ The query planner executes the query in stages: scan, distinct aggregation, sorting, and limit with offset.

This stepwise execution is not obvious from the SQL code alone but is critical to understanding performance and correctness.

✓ DISTINCT removes duplicates before sorting, ensuring the second highest salary is unique and correct.

Without DISTINCT, duplicate highest salaries could cause incorrect results.

✓ OFFSET 1 skips the highest salary, allowing LIMIT 1 to fetch the second highest salary precisely.

Understanding OFFSET and LIMIT interaction clarifies how SQL queries can select specific ranked rows.

Practice

1. Trace the sequence of events when a transaction running at Repeatable Read isolation level reads a row twice, but another concurrent transaction inserts a new row matching the query condition between the two reads. What anomaly occurs and why?

easy

A. Non-repeatable read, because the same row changed between reads

B. Phantom read, because a new row appeared between reads

C. Dirty read, because uncommitted data was read

D. No anomaly, Repeatable Read prevents all read inconsistencies

Solution

1. Step 1: Identify the Isolation Level Guarantees

   Repeatable Read prevents non-repeatable reads by locking rows read, but does not prevent phantom reads caused by new rows.

2. Step 2: Trace the Scenario

   Transaction reads rows matching condition; concurrent transaction inserts a new matching row and commits.

3. Step 3: Second Read Outcome

   Second read sees the new row, causing a phantom read anomaly.

4. Final Answer:

   Option B -> Option B

5. Quick Check:

   Phantom reads are new rows appearing between reads, not changes to existing rows.

Hint: Repeatable Read prevents row changes, not new rows (phantoms)

Common Mistakes:

• Confusing phantom reads with non-repeatable reads
• Assuming Repeatable Read prevents phantoms
• Thinking dirty reads occur at Repeatable Read

2. In which scenario is MVCC most beneficial compared to traditional locking mechanisms in a database system?

easy

A. When strict serializability is required and no stale reads are allowed

B. When transactions are short-lived and mostly write-heavy with minimal reads

C. When many concurrent transactions perform mostly read operations with occasional writes

D. When the database workload consists primarily of batch updates with no concurrent reads

Solution

1. Step 1: Understand MVCC's strength in concurrency

   MVCC allows readers to access snapshot versions without blocking writers, making it ideal for workloads with many reads and fewer writes.

2. Step 2: Analyze write-heavy workloads

   Write-heavy workloads with minimal reads benefit less from MVCC since version maintenance overhead can be costly.

3. Step 3: Analyze batch updates without concurrent reads

   Batch updates without concurrent reads do not leverage MVCC's snapshot isolation benefits.

4. Step 4: Analyze strict serializability requirements

   MVCC provides snapshot isolation, which may allow some anomalies; strict serializability requires stronger mechanisms.

5. Final Answer:

   Option C -> Option C

6. Quick Check:

   MVCC shines in read-heavy concurrent environments [OK]

Hint: MVCC excels when many reads coexist with some writes

Common Mistakes:

• Assuming MVCC is best for write-heavy workloads
• Confusing snapshot isolation with strict serializability
• Believing MVCC is unnecessary without concurrent reads

3. Which of the following statements about B+ Trees is INCORRECT?

medium

A. All keys appear in the leaf nodes, and internal nodes only store keys to guide searches.

B. Leaf nodes are linked sequentially to facilitate efficient range queries.

C. Data records can be stored in both internal and leaf nodes to speed up point queries.

D. Insertion and deletion operations may cause splitting or merging of leaf and internal nodes.

Solution

1. Step 1: Recall B+ Tree data storage rules

   Data records are stored only in leaf nodes; internal nodes store keys for navigation.

2. Step 2: Verify leaf node linkage

   Leaf nodes are linked sequentially to optimize range queries.

3. Step 3: Understand insertion/deletion effects

   Splitting and merging can occur in both leaf and internal nodes to maintain tree properties.

4. Step 4: Identify incorrect statement

   Data records can be stored in both internal and leaf nodes to speed up point queries. incorrectly states data records are stored in internal nodes, which contradicts B+ Tree design.

5. Final Answer:

   Option C -> Option C

6. Quick Check:

   Data only in leaves; internal nodes are index-only.

Hint: B+ Tree data only in leaves; internal nodes are keys only

Common Mistakes:

• Believing data can be stored in internal nodes
• Confusing B+ Tree with B-Tree data placement
• Ignoring leaf node linkage importance

4. Which of the following is a common misconception about the trade-offs involved in denormalization?

medium

A. Denormalization guarantees data integrity by replicating data across tables

B. Denormalization eliminates the need for complex joins, simplifying query logic

C. Denormalization always improves read performance but can degrade write performance due to redundant updates

D. Denormalization can increase storage costs because of intentional redundancy

Solution

1. Step 1: Analyze each statement

   Denormalization always improves read performance but can degrade write performance due to redundant updates correctly states the trade-off between read and write performance.

2. Step 2: Evaluate query complexity

   Denormalization eliminates the need for complex joins, simplifying query logic is true; denormalization reduces joins, simplifying queries.

3. Step 3: Assess data integrity claims

   Denormalization guarantees data integrity by replicating data across tables is false; denormalization introduces redundancy that can cause integrity issues if not carefully managed.

4. Step 4: Consider storage implications

   Denormalization can increase storage costs because of intentional redundancy correctly notes increased storage due to redundancy.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Denormalization trades integrity for performance; it does not guarantee integrity [OK]

Hint: Denormalization trades integrity away; it does not guarantee it [OK]

Common Mistakes:

• Believing denormalization improves both read and write performance
• Assuming denormalization removes all query complexity
• Thinking redundancy ensures data correctness

5. Suppose you want to find all pairs of employees and projects they can work on, regardless of any matching keys, but your current INNER JOIN query returns no rows. Which join type should you use to get all combinations, and what is a potential downside?

hard

A. Use CROSS JOIN to get Cartesian product; downside is potentially huge result size

B. Use LEFT JOIN to include all employees and matching projects; downside is missing unmatched projects

C. Use FULL OUTER JOIN to include all employees and projects; downside is NULLs in unmatched columns

D. Use SELF JOIN on employees to pair them with projects; downside is ambiguous columns

Solution

1. Step 1: Identify join type for all combinations

   CROSS JOIN returns the Cartesian product of two tables, producing all possible pairs regardless of matching keys.

2. Step 2: Consider downside

   The Cartesian product can be very large, leading to performance issues and large result sets.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   CROSS JOIN produces all pairs but can explode result size [OK]

Hint: CROSS JOIN yields all combinations, beware size explosion [OK]

Common Mistakes:

• Using INNER JOIN expecting all pairs
• Confusing FULL OUTER JOIN with Cartesian product