edited by
16,208 views
43 votes
43 votes

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.
edited by

7 Answers

Best answer
65 votes
65 votes

So, an employee whose $ALL$ customers gives him GOOD rating is chosen;

All such employees are chosen.
Answer = option D

edited by
19 votes
19 votes
(D)

inner query selects "employees with atleast one bad rating"

so negation on the above stmt give -> "employees with all ratings as good"

PS:put a ven diagram and practice for these kind of questions
10 votes
10 votes
If any employee has received rating other than 'good' from some customer,
then there will be some rows returned by the inner query.

And not exists will return false so that employee won't be printed 
only those employees which have got rating good from all their 
customers will be printed.
6 votes
6 votes

T

Try applying quantification rules as above.

Ans(D)."every customer of given employee has given a good rating"

Answer:

Related questions

39 votes
39 votes
4 answers
1
go_editor asked Sep 28, 2014
13,026 views
A prime attribute of a relation scheme $R$ is an attribute that appearsin all candidate keys of $R$in some candidate key of $R$in a foreign key of $R$only in the primary ...
37 votes
37 votes
11 answers
3