GATE 2014 CS (CS3) – Question 64
Schemas: employee(empId,empName,empDept), customer(custId,custName,salesRepId,rating). salesRepId references employee.empId. Each employee makes a sale to at least one customer. What does this query return?
SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
FROM customer C
WHERE C.salesRepId = E.empId
AND C.rating <> 'GOOD');
Practise this question in The GATE Grind →
Show answer and explanation
Correct answer: (D) Names of employees with all customers rated GOOD
Explanation
For each employee, the subquery looks for an associated customer whose rating is notGOOD. NOTEXISTSkeeps employees for whom no such customer exists, so all their customers areGOOD, **D**. This interpretation assumes ratings are nonnull; SQLNULLwould make rating<>GOODunknown and would not be selected by the subquery.