edited by
16,199 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

6 votes
6 votes

Answer is option D. Names of all the employees with all their customers having a 'GOOD' rating.

''all their customers" because we can see in below table .For RAM one customer is saying GOOD and one is saying BAD. Then also it's name is not printed. ARUN's name is printed because it's all customers are rating GOOD for him.

 

3 votes
3 votes

Basic procedure of finding meaning of correlated subquery is

Subquery is just like nested loop

first find meaning of innermost subquery. The  for each tuple of outer query execute inner query.

SELECT custId 
    FROM customer C 
    WHERE C.salesRepId = E.empId                       
    AND C.rating <> 'GOOD' 

means select customer ID who has given Bad rating to employee(I am just assuming that there are only two rating Good and Bad for simplicity)

SELECT empName   FROM employee E   
WHERE 

means for each employee execute inner query which finds customer ID who has given bad rating to this employee.

NOT EXISTS

means no such customer ID exist who has given bad rating to this employee.

Hence it selects 

Names of all the employees with all their customers having a 'GOOD' rating.

1 votes
1 votes
Reading the above query in simple English ( ignoring "NOT" ) gives us the desired result:

SELECT EmployeeName WHICH EXISTS { With a GOOD Customer Rating }
Answer:

Related questions

39 votes
39 votes
4 answers
1
go_editor asked Sep 28, 2014
13,022 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