sql - What does this query do? -


i'm studying final exam , comes in past exam:

table: nurse - (nid:int, nname:string, age:real, salary:real, sid:int)
table: supervisor - (sid:int, rating:real)
table: worksin - (nid:int, hid:int, hours:real)
table: hospital - (hid:int, hname:string, tid:int)
table: town - (tid:int, tname:string, mayor:string)

explain following query retrieves:

select nname nurse n not exists     (    (select h.hid           hospital h           h.town = ‘toronto’)      except          (select w.hid          worksin w          n.nid = w.nid)) 

what query do? can understand subquery returns hospitals in toronto doesn't have nurse work in, don't know how connects not exist part.

consider in stages:

the set of nurses    there not exist     hospital in toronto nurse doesn't work. 

by law of excluded middle becomes

the set of nurses   nurse works in toronto hospitals 

note these points raised in discussion commentator:

  • the sub-query evaluated separately each nurse, not singly on set of nurses
  • that say, each nurse such set of toronto hospitals nurse doesn't work empty set. meaning each nurse, (s)he works in toronto hospitals.
  • read subquery as: "the set of toronto hospitals except (read set difference or minus) hospitals nurse works."

Comments

Popular posts from this blog

c# - Send Image in Json : 400 Bad request -

javascript - addthis share facebook and google+ url -

ios - Show keyboard with UITextField in the input accessory view -