SQL Query Execution Order - FROM to LIMIT

🎯

SQL Query Execution Order - FROM to LIMIT

easyDBMSAmazonTCSInfosys

Imagine you are debugging a slow SQL query in a large e-commerce database. Understanding the exact order in which SQL clauses execute can help you optimize the query and improve performance.

💡 Many beginners write SQL queries without understanding the internal execution order of clauses. This leads to confusion when queries don't behave as expected, especially with filtering, grouping, and limiting results.

📋

Problem Statement

Given a SQL SELECT query with clauses FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, explain the order in which these clauses are executed internally by the SQL engine. Provide a clear explanation and example illustrating this execution order.

The query contains standard SQL clauses: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMITClauses appear in syntactically correct orderFocus on logical execution order, not physical optimization

💡

Example

Input"SELECT name, COUNT(*) FROM employees WHERE department = 'Sales' GROUP BY name HAVING COUNT(*) > 1 ORDER BY name LIMIT 5;"

OutputExecution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

First, rows are selected from employees (FROM), filtered by department (WHERE), grouped by name (GROUP BY), groups filtered by count (HAVING), columns selected (SELECT), results ordered by name (ORDER BY), and finally limited to 5 rows (LIMIT).

• Query without WHERE clause → execution skips WHERE step
• Query without GROUP BY but with HAVING → HAVING acts like WHERE on aggregated results
• Query with LIMIT but no ORDER BY → LIMIT applies to unordered results
• Query with nested subqueries → each subquery executes its clauses in order before outer query

⚠️

Common Mistakes

Assuming SQL executes clauses in written order

Misinterpretation of query results and incorrect assumptions about filtering and grouping

✅ Learn and explain the logical execution order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Using HAVING without GROUP BY

Query may fail or HAVING acts like WHERE on aggregated results, causing unexpected results

✅ Use HAVING only with GROUP BY or understand its behavior without grouping

Applying LIMIT without ORDER BY expecting sorted results

LIMIT returns arbitrary rows, not necessarily sorted, leading to inconsistent outputs

✅ Always use ORDER BY with LIMIT to guarantee deterministic results

Confusing WHERE and HAVING filters

Filters applied at wrong stage, causing incorrect row or group filtering

✅ Remember WHERE filters rows before grouping; HAVING filters groups after aggregation

Ignoring subquery execution order

Misunderstanding nested query results and their impact on outer queries

✅ Treat subqueries as independent queries executing fully before outer query

🧠

Brute Force Explanation (Literal Clause Order)

💡 This approach helps beginners by reading the query top-to-bottom as written, which is intuitive but incorrect for understanding execution.

Intuition

Assume SQL executes clauses in the order they appear in the query, from SELECT down to LIMIT.

Algorithm

1. Read the query from top to bottom as written.
2. Assume SELECT executes first, then FROM, then WHERE, and so on.
3. Explain the output based on this literal order.
4. Highlight why this approach leads to confusion.

💡 This approach is simple but misleading; it helps beginners realize why understanding execution order matters.

</>

Code

def literal_order_execution(query_clauses):
    # Just print clauses in written order
    order = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT']
    executed = [clause for clause in order if clause in query_clauses]
    return executed

# Driver code
query_clauses = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT']
print(literal_order_execution(query_clauses))

Line Notes

def literal_order_execution(query_clauses):Defines a function to simulate naive execution order

order = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT']List clauses in the literal written SQL order

executed = [clause for clause in order if clause in query_clauses]Filter only clauses present in the query

return executedReturn the naive execution order

print(literal_order_execution(query_clauses))Demonstrate output for full clause list

import java.util.*;
public class LiteralOrder {
    public static List<String> literalOrderExecution(List<String> queryClauses) {
        List<String> order = Arrays.asList("SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT");
        List<String> executed = new ArrayList<>();
        for (String clause : order) {
            if (queryClauses.contains(clause)) {
                executed.add(clause);
            }
        }
        return executed;
    }
    public static void main(String[] args) {
        List<String> queryClauses = Arrays.asList("SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT");
        System.out.println(literalOrderExecution(queryClauses));
    }
}

Line Notes

public static List<String> literalOrderExecution(List<String> queryClauses)Method to simulate naive execution order

List<String> order = Arrays.asList(...)Defines clause order as written in SQL

for (String clause : order)Iterate over clauses in written order

if (queryClauses.contains(clause))Check if clause is in the query

executed.add(clause)Add clause to naive execution list

System.out.println(literalOrderExecution(queryClauses))Print the naive execution order

#include <iostream>
#include <vector>
#include <string>
#include <algorithm>
using namespace std;

vector<string> literalOrderExecution(const vector<string>& queryClauses) {
    vector<string> order = {"SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT"};
    vector<string> executed;
    for (const string& clause : order) {
        if (find(queryClauses.begin(), queryClauses.end(), clause) != queryClauses.end()) {
            executed.push_back(clause);
        }
    }
    return executed;
}

int main() {
    vector<string> queryClauses = {"SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT"};
    vector<string> result = literalOrderExecution(queryClauses);
    for (const string& c : result) cout << c << " ";
    cout << endl;
    return 0;
}

Line Notes

vector<string> literalOrderExecution(const vector<string>& queryClauses)Function to simulate naive execution order

vector<string> order = {...}List clauses in literal written SQL order

for (const string& clause : order)Loop over clauses in written order

if (find(queryClauses.begin(), queryClauses.end(), clause) != queryClauses.end())Check if clause is present

executed.push_back(clause)Add clause to naive execution list

for (const string& c : result) cout << c << " ";Print the naive execution order

function literalOrderExecution(queryClauses) {
    const order = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT'];
    const executed = order.filter(clause => queryClauses.includes(clause));
    return executed;
}

const queryClauses = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT'];
console.log(literalOrderExecution(queryClauses));

Line Notes

function literalOrderExecution(queryClauses)Defines function to simulate naive execution order

const order = [...]List clauses in literal written SQL order

order.filter(clause => queryClauses.includes(clause))Filter clauses present in query

return executedReturn naive execution order

console.log(literalOrderExecution(queryClauses))Print the naive execution order

⏱

Complexity

TimeO(n)

SpaceO(n)

We iterate over a fixed list of clauses and filter those present in the query, linear in number of clauses.

💡 For typical queries with up to 7 clauses, this means at most 7 checks, which is very fast.

Interview Verdict: Incorrect for understanding execution order but useful to expose misconceptions

This approach is a stepping stone to realize that SQL execution order differs from written order.

🧠

Logical Execution Order Explanation

💡 This approach teaches the actual logical order SQL executes clauses internally, which is crucial for writing correct queries.

Intuition

SQL processes data starting from data source (FROM), filters rows (WHERE), groups data (GROUP BY), filters groups (HAVING), selects columns (SELECT), orders results (ORDER BY), and finally limits output (LIMIT).

Algorithm

1. Start with FROM clause to identify data sources and joins.
2. Apply WHERE clause to filter rows before grouping.
3. Group rows using GROUP BY clause.
4. Filter groups using HAVING clause.
5. Select columns and expressions in SELECT clause.
6. Order the result set with ORDER BY clause.
7. Limit the number of rows returned with LIMIT clause.

💡 This stepwise explanation matches how SQL engines logically process queries, which differs from written order.

</>

Code

def logical_execution_order(query_clauses):
    order = ['FROM', 'WHERE', 'GROUP BY', 'HAVING', 'SELECT', 'ORDER BY', 'LIMIT']
    executed = [clause for clause in order if clause in query_clauses]
    return executed

# Driver code
query_clauses = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT']
print(logical_execution_order(query_clauses))

Line Notes

def logical_execution_order(query_clauses):Defines function to return logical execution order

order = ['FROM', 'WHERE', 'GROUP BY', 'HAVING', 'SELECT', 'ORDER BY', 'LIMIT']List clauses in actual execution order

executed = [clause for clause in order if clause in query_clauses]Filter only clauses present in the query

return executedReturn logical execution order

print(logical_execution_order(query_clauses))Demonstrate output for full clause list

import java.util.*;
public class LogicalOrder {
    public static List<String> logicalExecutionOrder(List<String> queryClauses) {
        List<String> order = Arrays.asList("FROM", "WHERE", "GROUP BY", "HAVING", "SELECT", "ORDER BY", "LIMIT");
        List<String> executed = new ArrayList<>();
        for (String clause : order) {
            if (queryClauses.contains(clause)) {
                executed.add(clause);
            }
        }
        return executed;
    }
    public static void main(String[] args) {
        List<String> queryClauses = Arrays.asList("SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT");
        System.out.println(logicalExecutionOrder(queryClauses));
    }
}

Line Notes

public static List<String> logicalExecutionOrder(List<String> queryClauses)Method to return logical execution order

List<String> order = Arrays.asList(...)Defines actual SQL execution order

for (String clause : order)Iterate over clauses in execution order

if (queryClauses.contains(clause))Check if clause is present in query

executed.add(clause)Add clause to execution list

System.out.println(logicalExecutionOrder(queryClauses))Print logical execution order

#include <iostream>
#include <vector>
#include <string>
#include <algorithm>
using namespace std;

vector<string> logicalExecutionOrder(const vector<string>& queryClauses) {
    vector<string> order = {"FROM", "WHERE", "GROUP BY", "HAVING", "SELECT", "ORDER BY", "LIMIT"};
    vector<string> executed;
    for (const string& clause : order) {
        if (find(queryClauses.begin(), queryClauses.end(), clause) != queryClauses.end()) {
            executed.push_back(clause);
        }
    }
    return executed;
}

int main() {
    vector<string> queryClauses = {"SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT"};
    vector<string> result = logicalExecutionOrder(queryClauses);
    for (const string& c : result) cout << c << " ";
    cout << endl;
    return 0;
}

Line Notes

vector<string> logicalExecutionOrder(const vector<string>& queryClauses)Function to return logical execution order

vector<string> order = {...}List clauses in actual SQL execution order

for (const string& clause : order)Loop over clauses in execution order

if (find(queryClauses.begin(), queryClauses.end(), clause) != queryClauses.end())Check if clause is present

executed.push_back(clause)Add clause to execution list

for (const string& c : result) cout << c << " ";Print logical execution order

function logicalExecutionOrder(queryClauses) {
    const order = ['FROM', 'WHERE', 'GROUP BY', 'HAVING', 'SELECT', 'ORDER BY', 'LIMIT'];
    const executed = order.filter(clause => queryClauses.includes(clause));
    return executed;
}

const queryClauses = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT'];
console.log(logicalExecutionOrder(queryClauses));

Line Notes

function logicalExecutionOrder(queryClauses)Defines function to return logical execution order

const order = [...]List clauses in actual SQL execution order

order.filter(clause => queryClauses.includes(clause))Filter clauses present in query

return executedReturn logical execution order

console.log(logicalExecutionOrder(queryClauses))Print logical execution order

⏱

Complexity

TimeO(n)

SpaceO(n)

Iterates over fixed clause list and filters those present, linear in number of clauses.

💡 For typical queries, this is very efficient and straightforward.

Interview Verdict: Correct explanation of SQL execution order, suitable for interviews

This approach clarifies the internal processing order, which is essential knowledge.

🧠

Visualizing SQL Execution with Query Plan Simulation

💡 This approach uses a simulated query plan to visualize and reinforce the execution order, helping both beginners and experienced developers.

Intuition

Represent each clause as a step in a pipeline, showing data flow from source to final output, highlighting intermediate transformations.

Algorithm

1. Start with the FROM clause to load data sources.
2. Apply WHERE filters to remove unwanted rows.
3. Group rows using GROUP BY.
4. Filter groups with HAVING.
5. Project columns with SELECT.
6. Sort results with ORDER BY.
7. Limit output rows with LIMIT.

💡 Each step corresponds to a node in the query plan, showing how data is processed sequentially.

</>

Code

class QueryPlanNode:
    def __init__(self, name):
        self.name = name
        self.next = None
    def set_next(self, node):
        self.next = node
    def execute(self):
        print(f"Executing {self.name} clause")
        if self.next:
            self.next.execute()

# Build query plan pipeline
from_clause = QueryPlanNode('FROM')
where_clause = QueryPlanNode('WHERE')
group_by_clause = QueryPlanNode('GROUP BY')
having_clause = QueryPlanNode('HAVING')
select_clause = QueryPlanNode('SELECT')
order_by_clause = QueryPlanNode('ORDER BY')
limit_clause = QueryPlanNode('LIMIT')

from_clause.set_next(where_clause)
where_clause.set_next(group_by_clause)
group_by_clause.set_next(having_clause)
having_clause.set_next(select_clause)
select_clause.set_next(order_by_clause)
order_by_clause.set_next(limit_clause)

# Execute query plan
from_clause.execute()

Line Notes

class QueryPlanNode:Defines a node representing a clause in the query plan

def __init__(self, name):Initialize node with clause name

def set_next(self, node):Link current node to next clause node

def execute(self):Simulate execution by printing clause name and calling next

from_clause = QueryPlanNode('FROM')Create node for FROM clause

from_clause.set_next(where_clause)Link FROM to WHERE clause

from_clause.execute()Start execution from FROM clause

class QueryPlanNode {
    String name;
    QueryPlanNode next;
    QueryPlanNode(String name) {
        this.name = name;
        this.next = null;
    }
    void setNext(QueryPlanNode node) {
        this.next = node;
    }
    void execute() {
        System.out.println("Executing " + name + " clause");
        if (next != null) next.execute();
    }
}

public class QueryPlanSimulation {
    public static void main(String[] args) {
        QueryPlanNode from = new QueryPlanNode("FROM");
        QueryPlanNode where = new QueryPlanNode("WHERE");
        QueryPlanNode groupBy = new QueryPlanNode("GROUP BY");
        QueryPlanNode having = new QueryPlanNode("HAVING");
        QueryPlanNode select = new QueryPlanNode("SELECT");
        QueryPlanNode orderBy = new QueryPlanNode("ORDER BY");
        QueryPlanNode limit = new QueryPlanNode("LIMIT");

        from.setNext(where);
        where.setNext(groupBy);
        groupBy.setNext(having);
        having.setNext(select);
        select.setNext(orderBy);
        orderBy.setNext(limit);

        from.execute();
    }
}

Line Notes

class QueryPlanNode {Defines a class representing a query plan node

QueryPlanNode(String name) {Constructor initializing clause name

void setNext(QueryPlanNode node) {Method to link to next clause node

void execute() {Simulates execution by printing and calling next

from.setNext(where);Link FROM to WHERE clause

from.execute();Start execution from FROM clause

#include <iostream>
#include <string>
using namespace std;

class QueryPlanNode {
public:
    string name;
    QueryPlanNode* next;
    QueryPlanNode(string n) : name(n), next(nullptr) {}
    void setNext(QueryPlanNode* node) { next = node; }
    void execute() {
        cout << "Executing " << name << " clause" << endl;
        if (next) next->execute();
    }
};

int main() {
    QueryPlanNode from("FROM"), where("WHERE"), groupBy("GROUP BY"), having("HAVING"), select("SELECT"), orderBy("ORDER BY"), limit("LIMIT");
    from.setNext(&where);
    where.setNext(&groupBy);
    groupBy.setNext(&having);
    having.setNext(&select);
    select.setNext(&orderBy);
    orderBy.setNext(&limit);
    from.execute();
    return 0;
}

Line Notes

class QueryPlanNode {Defines a class for query plan node

QueryPlanNode(string n) : name(n), next(nullptr) {}Constructor initializes name and next pointer

void setNext(QueryPlanNode* node) { next = node; }Sets pointer to next clause node

void execute() {Prints clause and calls next execute

from.setNext(&where);Link FROM to WHERE clause

from.execute();Start execution from FROM clause

class QueryPlanNode {
    constructor(name) {
        this.name = name;
        this.next = null;
    }
    setNext(node) {
        this.next = node;
    }
    execute() {
        console.log(`Executing ${this.name} clause`);
        if (this.next) this.next.execute();
    }
}

const from = new QueryPlanNode('FROM');
const where = new QueryPlanNode('WHERE');
const groupBy = new QueryPlanNode('GROUP BY');
const having = new QueryPlanNode('HAVING');
const select = new QueryPlanNode('SELECT');
const orderBy = new QueryPlanNode('ORDER BY');
const limit = new QueryPlanNode('LIMIT');

from.setNext(where);
where.setNext(groupBy);
groupBy.setNext(having);
having.setNext(select);
select.setNext(orderBy);
orderBy.setNext(limit);

from.execute();

Line Notes

class QueryPlanNode {Defines a class representing a query plan node

constructor(name) {Initializes node with clause name

setNext(node) {Links current node to next clause

execute() {Prints clause execution and calls next

from.setNext(where);Link FROM to WHERE clause

from.execute();Start execution from FROM clause

⏱

Complexity

TimeO(n)

SpaceO(n)

Each clause node is executed once in sequence, linear in number of clauses.

💡 For typical queries, this means a simple linear pipeline of steps.

Interview Verdict: Excellent for visual learners and interview explanations

This approach solidifies understanding by simulating the query plan execution flow.

📊

All Approaches - One-Glance Tradeoffs

💡 In interviews, always explain the naive approach briefly, then focus on the logical execution order. Use visualization if time permits to impress and clarify.

Approach	Time	Space	Stack Risk	Reconstruct	Use In Interview
1. Brute Force Explanation (Literal Clause Order)	O(n)	O(n)	No	N/A	Mention only to expose misconceptions
2. Logical Execution Order Explanation	O(n)	O(n)	No	N/A	Code and explain this approach in most interviews
3. Visualizing SQL Execution with Query Plan Simulation	O(n)	O(n)	No	N/A	Use to impress and clarify if time permits

💼

Interview Strategy

💡 Use this guide to understand the SQL execution order deeply before interviews. Start by clarifying the problem, then explain the naive approach to expose misconceptions, followed by the correct logical order, and finally visualize with a query plan simulation.

How to Present

Step 1: Clarify the SQL clauses involved and confirm the query structure.Step 2: State the naive literal order approach to show common misunderstandings.Step 3: Explain the actual logical execution order of SQL clauses.Step 4: Optionally, demonstrate with a query plan simulation to visualize execution.Step 5: Summarize why understanding execution order matters for query correctness and optimization.

Time Allocation

Clarify: 2min → Explain naive approach: 3min → Explain logical order: 5min → Visualize with query plan: 5min → Summarize and answer questions: 5min. Total ~20min

What the Interviewer Tests

The interviewer tests your understanding of SQL internals, your ability to explain concepts clearly, and your awareness of how execution order affects query results.

Common Follow-ups

• What happens if ORDER BY is omitted but LIMIT is present? → LIMIT applies to unordered results, so output rows are arbitrary.
• How does HAVING differ from WHERE? → WHERE filters rows before grouping; HAVING filters groups after aggregation.

💡 These follow-ups test deeper understanding of filtering and ordering nuances in SQL.

🔍

Pattern Recognition

When to Use

1) When asked about SQL query behavior or debugging, 2) When query results differ from expectations, 3) When optimizing queries, 4) When explaining SQL internals.

Signature Phrases

execution order of SQL clauseslogical processing of SELECT queryorder of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT

NOT This Pattern When

Writing SQL syntax or schema design problems, which focus on structure rather than execution order.

Similar Problems

SQL Join Execution Order - understanding how joins affect FROM clauseSQL Aggregation and Grouping - deeper dive into GROUP BY and HAVINGSQL Query Optimization - how execution order impacts performance

Practice

1. You have a relation with multi-valued attributes and repeating groups. At which normalization step do you first ensure atomicity of attributes and eliminate repeating groups?

easy

A. When converting the relation into First Normal Form (1NF)

B. When removing partial dependencies in Second Normal Form (2NF)

C. When eliminating transitive dependencies in Third Normal Form (3NF)

D. When enforcing stricter dependency conditions in Boyce-Codd Normal Form (BCNF)

Solution

1. Step 1: Understand 1NF's role in normalization

   1NF requires that all attributes be atomic and that there are no repeating groups or arrays. This is the first step in normalization to ensure the relation is well-structured.

2. Step 2: Differentiate from later normal forms

   2NF and 3NF focus on removing partial and transitive dependencies, respectively, but assume atomic attributes already exist. BCNF further refines dependency conditions but does not address atomicity.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   Atomicity and elimination of repeating groups is the hallmark of 1NF, not later forms.

Hint: Atomicity and no repeating groups -> 1NF

Common Mistakes:

• Confusing 1NF with 2NF or 3NF responsibilities
• Thinking BCNF addresses atomicity
• Assuming partial dependencies relate to atomicity

2. Consider the following SQL query intended to find the second highest salary. Which line contains a subtle bug that causes it to return the highest salary instead of the second highest?

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary NOT IN (
  SELECT MAX(Salary) FROM Employee
)

medium

A. Line 4: SELECT MAX(Salary) FROM Employee

B. Line 2: FROM Employee

C. Line 3: WHERE Salary NOT IN (

D. Line 1: SELECT MAX(Salary) AS SecondHighestSalary

Solution

1. Step 1: Understand the query logic

   The inner query selects the maximum salary, which is excluded in the outer query.

2. Step 2: Identify the bug

   If the highest salary appears multiple times, excluding it by NOT IN with a single MAX() does not exclude duplicates properly, causing MAX(Salary) to return the highest salary again.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   Using MAX(Salary) twice without excluding duplicates causes bug [OK]

Hint: Excluding MAX(Salary) once doesn't exclude duplicates of highest salary [OK]

Common Mistakes:

• Assuming NOT IN excludes all duplicates of highest salary
• Misplacing WHERE clause

3. What is the time complexity of the SQL query using window functions to rank products within categories, assuming n sales records and m unique products?

medium

A. O(n * m) due to nested aggregation and ranking

B. O(n log n) due to sorting inside window function over all rows

C. O(m^2) because ranking requires pairwise comparisons of products

D. O(n) since aggregation and ranking are linear scans

Solution

1. Step 1: Identify operations in the query

   The query aggregates sales (O(n)) then applies window function with ORDER BY, which requires sorting.

2. Step 2: Analyze sorting cost in window function

   Sorting all rows by category and total_sales costs O(n log n), dominating the complexity.

3. Final Answer:

   Option B -> Option B

4. Quick Check:

   Sorting inside window function -> O(n log n) time [OK]

Hint: Window functions with ORDER BY require sorting -> O(n log n) [OK]

Common Mistakes:

• Assuming linear time ignoring sorting
• Confusing n and m in complexity

4. Examine the following SQL query intended to perform a self join to find each employee's manager name. Identify the bug causing an SQL error.

medium

A. The alias 'A' is missing for the first table, causing ambiguous column references

B. The join condition uses 'A.manager_id' instead of 'A1.manager_id'

C. The alias 'A2' is incorrectly used for the second table

D. The SELECT clause should include 'A.manager_id' explicitly

Solution

1. Step 1: Check table aliases

   The first table is referenced as 'A' without aliasing, but the second is aliased as 'A2'. This causes ambiguity in column references.

2. Step 2: Correct alias usage

   Both tables in a self join must have distinct aliases, e.g., 'A1' and 'A2', to avoid ambiguous column references.

3. Final Answer:

   Option A -> Option A

4. Quick Check:

   Missing alias for first table causes SQL error [OK]

Hint: Self joins require distinct aliases for the same table [OK]

Common Mistakes:

• Using same table name without alias
• Incorrect join condition references

5. In a system using Snapshot Isolation (a variant of Repeatable Read), a transaction reads a consistent snapshot but later tries to update a row that another concurrent transaction has also updated and committed. What anomaly can still occur, and how does Snapshot Isolation handle it?

hard

A. Write skew anomaly; Snapshot Isolation detects conflicts and aborts one transaction

B. Lost update anomaly; Snapshot Isolation does not detect concurrent writes

C. Dirty write anomaly; Snapshot Isolation allows overwriting uncommitted data

D. Phantom read anomaly; Snapshot Isolation prevents phantoms by locking ranges

Solution

1. Step 1: Understand Snapshot Isolation

   Snapshot Isolation provides a consistent snapshot for reads and detects write-write conflicts at commit.

2. Step 2: Identify Possible Anomalies

   Dirty writes are prevented; lost updates are prevented by conflict detection; phantoms are not prevented by locking ranges.

3. Step 3: Recognize Write Skew

   Write skew can occur when two transactions read overlapping data and update disjoint rows, leading to inconsistent state.

4. Step 4: Conflict Handling

   Snapshot Isolation detects write-write conflicts and aborts one transaction to prevent lost updates, but write skew can still occur.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Write skew is a subtle anomaly Snapshot Isolation does not prevent fully.

Hint: Snapshot Isolation = no dirty writes, detects conflicts, but allows write skew

Common Mistakes:

• Confusing dirty writes with dirty reads
• Assuming Snapshot Isolation prevents all anomalies including write skew
• Believing phantoms are prevented by Snapshot Isolation