Question

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

1. Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
2. Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
3. Names of all the employees with none of their customers having a ‘GOOD’ rating.
4. Names of all the employees with all their customers having a ‘GOOD’ rating.

Answer 1

Correct Answer - Option 4 : 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:

empID	empName	EmpDept
E1	Raju	D1
E2	Ashok	D2
E3	Madhu	D1

 

Customer table:

custID	custName	salesRepID	Rating
C1	Dilip	E1	GOOD
C2	Pooja	E1	BAD
C3	Kirti	E2	BAD
C4	Kshitij	E3	GOOD

 

• 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.