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’);

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’); Correct Answer Names of all the employees with all their customers having a ‘GOOD’ rating.

Concept:

• NOT EXISTS operator returns true if the underlying sub query return no record.
• If single record is matched by the inner sub query, the NOT EXISTS operator will return false, and the sub query execution can be stopped.

Explanation:

Example:

Employee table:

Customer table:

• Employee table entry E1 will matched with customer C1 in customer table but rating is GOOD so inner query will not return a record and NOT EXISTS will return true now employee entry e1 will matched with customer C2 but rating is bad so NOT EXISTS will return false and the sub query execution can be stopped and E1 will not print.
• Employee entry E2 will matched with customer C3 but Rating is BAD so NOT EXISTS will return false and E2 will not print.
• Employee entry E3 will matched with customer C4 but Rating is GOOD so NOT EXISTS will return true and E3 will print.
• This is nothing but name of all the employees with all their customers having a GOOD rating.