2.8k views

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 | 2.8k views

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

All such employees are chosen.
ans = option D

selected
+2
Inner query will result all employees which has atleast one bad rating i.e. it will also include which has good as well as some bad rating

And outer query is asking for employees for which inner query return none .

So if any employee having both bad and good rating then they will not be part of outer query.

+1

please tell me one thing that "EXIST" acts as universal or existence quantifier?????

I mean for every tuple of outer query inner query will be executed,now on which basis the tuples of outer query will be selected(universally or existentially).

0

https://gateoverflow.in/969/gate2003-86 Then in this question also answer should be A but its C

0

@MRINMOY_HALDER  I think universally. as only then answer is correct otherwise answer would have been A

0

@ see 1st two line of this link - https://www.w3schools.com/sql/sql_exists.asp

Exist acts as an existence quantifier.

(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
kindly shows us your process of coming up with a venn diagram for this.
+1

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 "

+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"

T

Try applying quantification rules as above.

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

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.
0
It cleared my doubt. :)
+1 vote
Reading the above query in simple English ( ignoring "NOT" ) gives us the desired result:

SELECT EmployeeName WHICH EXISTS { With a GOOD Customer Rating }

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.

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.

1
2