Consider the following relational schema: employee(empId, empName, empDept) customer(custId, custName, custRating, custRepId) salesRepSal is a foreign key in customer referencing empId of employee. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee WHERE NOT EXISTS (SELECT custId FROM customer WHERE custRepId = empId AND custRating = 'GOOD'); A. Names of all employees with at least one of their customers having a 'GOOD' rating B. Names of all employees with all of their customers having a 'GOOD' rating C. Names of all employees with none of their customers having a 'GOOD' rating D. Names of all employees with at least one of their customers not having a 'GOOD' rating

GATE 2014 · Databases · SQL · medium

Answer: C. Names of all employees with none of their customers having a 'GOOD' rating

1. Parse the NOT EXISTS correlated subquery: For each employee row in the outer query, the subquery selects customers who: (a) are represented by this employee (custRepId = empId), AND (b) have custRating = 'GOOD'. NOT EXISTS is TRUE only if this subquery returns no rows — meaning NO customer of this employee has a GOOD rating.
2. Map to the answer options: Option A: 'at least one customer with GOOD' — this would use EXISTS (not NOT EXISTS). Wrong. Option B: 'all customers have GOOD' — this would require a different logic (double negation or ALL). Wrong. Option C: 'none of their customers have GOOD' — exactly what NOT EXISTS gives when checking for GOOD-rated customers. Correct. Option D: 'at least one customer NOT having GOOD' — this would need EXISTS with condition NOT GOOD, or NOT ALL are GOOD. Wrong.