next up previous contents
Next: Who works for Up: Simple Queries Previous: Which dept is

Who is older than Edna's manager?

 

To give some idea of how easy it is to construct complex SQL queries, with nested SELECTS, using Squirrel.

ENG: who is older than edna's manager
SYN: (s(np(ipron(who)))(vp(v(is))(adj(older))
      (pp(prep(than))(np(np(np(propn(edna)))(possessive(s)))
       (n(n(manager)))))))
 PT: some(y,`of:`edna:`manager:y and `than:y:(`older:`be):fva1)
FOL: exists(y,of(y,`manager,`edna)&than(fva1,`older:`be,y))
URC: {fva1;exists(y,[`edna,y]:boss&exists(tt,[fva1,tt,*,*]:emp&tt>y))}
CHK: Query not meaningful with respect to current database

URC: {fva1;exists(y,[`edna,y]:boss&exists(mm,
      exists(nn,[fva1,mm,*,*]:emp&[y,nn,*,*]:emp&mm>nn)))}
DRC: {fva1:emp![name];exists(y:emp![name],[`edna,y]:boss&
      exists(mm:emp![age],
      exists(nn:emp![age],[fva1,mm,*,*]:emp&[y,nn,*,*]:emp&mm>nn)))}
OP1: {fva1:emp![name];exists(skf3:emp![age],exists(skf2:emp![age],
      exists(skf1:emp![name],[`edna,skf1]:boss&
       [fva1,skf2,*,*]:emp&[skf1,skf3,*,*]:emp&skf2>skf3)))}
TRC: {fva1!name;fva1:emp;
      exists(skf3:emp,exists(skf2:emp,exists(skf1:emp,
       exists(tuple1:boss,tuple1!name=edna&tuple1!mgr=skf1!name)&
        exists(tuple2:emp,tuple2!name=fva1!name&tuple2!age=skf2!age)&
         exists(tuple3:emp,tuple3!name=skf1!name&tuple3!age=skf3!age)&
          skf2!age>skf3!age)))}
OP2: {fva1!name;fva1:emp;exists(sk4:boss,exists(sk5:emp,
      exists(sk6:emp,sk4!name=edna&sk5!name=fva1!name&
       sk5!age>sk6!age&sk6!name=sk4!mgr)))}
OP3: {fva1!name;fva1:emp;exists(sk4:boss,
      exists(sk6:emp,sk4!name=edna&fva1!age>sk6!age&
       sk6!name=sk4!mgr))}
SQL: SELECT DISTINCT fva1.name FROM emp fva1 
      WHERE EXISTS(SELECT * FROM boss sk4 
       WHERE EXISTS(SELECT * FROM emp sk6 
        WHERE (sk4.name='edna' 
        AND (fva1.age>sk6.age AND sk6.name=sk4.mgr))));

|name      |
|----------|
|ted       |
|----------|
(1 rows)



Chris Fox, September 1995