GATE CS 2014 Set 3 Q.64 (CS Q.54): SQL Queries and Integrity Constraints

Consider the following relational schema:

employee(empId, empName, empDept)
customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> 'GOOD');

Names of all the employees with at least one of their customers having a 'GOOD' rating.

Names of all the employees with at most one of their customers having a 'GOOD' rating.

Names of all the employees with none of their customers having a 'GOOD' rating.

Names of all the employees with all their customers having a 'GOOD' rating.

Official answer and solution

Official answer: D
From: Official GATE 2014 answer key

Solution

Examine the NOT EXISTS condition in the correlated subquery to identify the qualifying employees.

Analyze the Subquery:
```
SELECT custId
FROM customer C
WHERE C.salesRepId = E.empId
AND C.rating <> 'GOOD'
```
For a given employee $E$ , this inner query selects any customer handled by $E$ whose rating is NOT 'GOOD'.
Analyze WHERE NOT EXISTS:
- NOT EXISTS returns TRUE if the subquery returns an empty set of rows.
- The subquery returns zero rows if and only if employee $E$ has NO customer whose rating is different from 'GOOD'.
Combine with Given Precondition:
- The problem specifies that "each employee makes a sale to at least one customer".
- Since every employee has at least one customer, and none of their customers have a rating other than 'GOOD', every single customer belonging to employee $E$ must have a 'GOOD' rating.
Therefore, the query returns the names of all employees with all their customers having a 'GOOD' rating (option D).

Final answer

Common trap

Confusing 'none of their customers have a rating <> GOOD' (all are GOOD) with 'none of their customers are GOOD' (option C).

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2014 Computer Science and Information Technology (CS) Session 3
Question: 64 of 65, printed as CS Q.54
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source