Consider the following relational schema: employee (empId,empName,empDept)…
2014
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');- A.
Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
- B.
Names of all the employees with at most one of their customers having a 'GOOD' rating.
- C.
Names of all the employees with none of their customers having a 'GOOD' rating.
- D.
Names of all the employees with all their customers having a 'GOOD' rating.
Attempted by 117 students.
Show answer & explanation
Correct answer: D
Answer: Names of all employees whose every customer has rating 'GOOD'.
Key idea: The WHERE NOT EXISTS(...) clause checks that there is no customer for the employee with rating <> 'GOOD'.
Therefore, every customer of that employee must have rating = 'GOOD'.
Because the question states each employee has at least one customer, 'no customer with rating <> "GOOD"' implies 'all customers are "GOOD"'.
A video solution is available for this question — log in and enroll to watch it.