GATE CS 2014 Set 3 Q54 — Tuple Calculus and SQL

To understand what the SQL query returns, let's analyze the inner subquery and the outer NOT EXISTS condition step-by-step:

1. The Inner Subquery:

   SELECT custId 
   FROM customer C 
   WHERE C.salesRepId = E.empId                       
   AND C.rating <> 'GOOD'

   For a given employee $E$, this subquery retrieves the IDs of all customers assigned to $E$ whose rating is not 'GOOD'.
2. The Outer Query with NOT EXISTS: The outer query selects the name of employee $E$ only if the subquery returns an empty set (i.e., NOT EXISTS evaluates to TRUE).

   $$\text{NOT EXISTS (customers of } E \text{ with rating } \neq \text{'GOOD'}) \equiv \text{No customer of } E \text{ has a rating other than 'GOOD'}$$

3. Conclusion: If an employee has no customers with a rating other than 'GOOD', it logically means that all of their customers must have a rating of 'GOOD'.

The problem states: "Assume that each employee makes a sale to at least one customer." This assumption ensures that the set of customers for any employee is non-empty, meaning the query will indeed return employees where all of their customers have a 'GOOD' rating.

Option Analysis:

• Option A: "At least one..." would correspond to an EXISTS query with rating = 'GOOD'.
• Option B: "At most one..." would require counting the customers.
• Option C: "None of their customers..." would correspond to NOT EXISTS with rating = 'GOOD'.
• Option D: Correctly matches our logical deduction.