in Databases edited by
16,096 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.
in Databases edited by
16.1k views

2 Comments

In the question it is given that select custId..

and we are performing not exists in it from table E, how can this even be possible as table E has no attribute such as CustId...!
7
7
@Hirak, We don’t care whether E has cusid attribute or not ,all the not exists care about is whether the inner has any tuples or not it returns true if no tuples are returned by inner query.
1
1

7 Answers

65 votes
65 votes
Best answer

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

All such employees are chosen.
Answer = option D

edited by

4 Comments

So <>  in SQL is equivalent to != ?

0
0

@anupam_yedida

Yes both are same.

0
0
0
0
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

3 Comments

kindly shows us your process of coming up with a venn diagram for this.
4
4
edited by

i think what he means is we can draw a two circles one inside other with inner one representing the literal meaning of inner query and outter one representing the outter query and then figure out the meaning of inner circle wrt to outter one, as the inner circle will be the literal answer.

in this context NOTEXISTS which will give something like A-B venn diag. with A as outter circle and B the inner one, but it marks " a Employee has to be GOOD to be BAD which is confusing " frown

1
1

@  Aravind Inner query selects All customer, those give bad ratings(not good ratings) ,not the given one u said below-

inner query selects "employees with atleast one bad rating" 

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

1 comment

It cleared my doubt. :)
0
0
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