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
Post a Comment