Native Relational Operations (Cont.)

Selection (Cont.)
Query: Retrieve all pairs of agents, both with a percentage commission of at least 6%, and both stationed in the same city.
agent
aid aname city percent
a01 Smith New York 6
a02 Jones Newark 6
a03 Brown Tokyo 7
a04 Gray New York 6
a05 Otasi Duluth 5
a06 Smith Dallas 5
L/M
aid aname city percent
a01 Smith New York 6
a02 Jones Newark 6
a03 Brown Tokyo 7
a04 Gray New York 6 
L := AGENT where percent >= 6
M := AGENT where percent >= 6
(Wrong!👎) PAIRS := ( L×M ) where L.city=M.city
PAIRS
L.aid L.aname L.city L.percent M.aid M.aname M.city M.percent
a01 Smith New York 6 a01 Smith New York 6
a01 Smith New York 6 a04 Gray New York 6
a02 Jones Newark 6 a02 Jones Newark 6
a03 Brown Tokyo 7 a03 Brown Tokyo 7
a04 Gray New York 6 a01 Smith New York 6
a04 Gray New York 6 a04 Gray New York 6 
(Good!👍) PAIRS2 := ( L×M ) where L.city=M.city and L.aid<M.aid
PAIRS2
L.aid L.aname L.city L.percent M.aid M.aname M.city M.percent
a01 Smith New York 6 a04 Gray New York 6 
(Wrong!👎) PAIRS3 := ( L×M ) where L.city='New York'
   and M.city='New York' and L.aid<M.aid
PAIRS3
L.aid L.aname L.city L.percent M.aid M.aname M.city M.percent
a01 Smith New York 6 a04 Gray New York 6

Though PAIRS2 and PAIRS3 gave the same result, the PAIRS3 query is known as a content-dependency error.




      My grief counselor died the other day.    
      He was so good at this job, I don’t even care.