GATE CSE
First time here? Checkout the FAQ!
x
+4 votes
986 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.
asked in Databases by Veteran (77.2k points)   | 986 views

5 Answers

+12 votes
Best answer

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

All such employees are chosen.
ans = option D

answered by Veteran (27.8k points)  
selected by
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.

So answer is D
+9 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
answered by Loyal (3.2k points)  
kindly shows us your process of coming up with a venn diagram for this.

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

0 votes

T

Try applying quantification rules as above.

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

answered by Active (1.4k points)  
0 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.
answered by Loyal (3.2k points)  
0 votes
Reading the above query in simple English ( ignoring "NOT" ) gives us the desired result:

SELECT EmployeeName WHICH EXISTS { With a GOOD Customer Rating }
answered by Junior (685 points)  


Top Users May 2017
  1. akash.dinkar12

    3292 Points

  2. pawan kumarln

    1652 Points

  3. sh!va

    1650 Points

  4. Arjun

    1424 Points

  5. Bikram

    1372 Points

  6. Devshree Dubey

    1272 Points

  7. Debashish Deka

    1142 Points

  8. Angkit

    1044 Points

  9. LeenSharma

    904 Points

  10. srestha

    718 Points

Monthly Topper: Rs. 500 gift card
Top Users 2017 May 22 - 28
  1. Bikram

    458 Points

  2. Arnab Bhadra

    402 Points

  3. pawan kumarln

    278 Points

  4. Ahwan

    236 Points

  5. bharti

    194 Points


22,786 questions
29,121 answers
65,184 comments
27,661 users