1> /* Advanced SQL Programming Fall 2001*/ 2> /* */ 3> /* Example using keywords ALL and ANY in the WHERE clause */ 4> /* */ 5> /* Context: (pubs2 database) Publishers pay advances on some */ 6> /* titles. As an author, I would like to make sure I get the */ 7> /* best, or at least a reasonable, advance. */ 8> /* */ 9> use pubs2 1> /* */ 2> /* 1. Which publishers (by name) pay advances greater than */ 3> /* those paid by Algodata Infosystems? */ 4> /* */ 5> select pub_name, advance 6> from titles t, publishers p 7> where t.pub_id = p.pub_id 8> and advance > any 9> (select advance 10> from titles t1, publishers p1 11> where t1.pub_id = p1.pub_id 12> and pub_name like '%Al%') pub_name advance ---------------------------------------- ------------------------ New Age Books 10,125.00 Binnet & Hardley 15,000.00 Algodata Infosystems 7,000.00 Algodata Infosystems 8,000.00 Binnet & Hardley 7,000.00 New Age Books 6,000.00 Binnet & Hardley 7,000.00 Binnet & Hardley 8,000.00 (8 rows affected) 1> /* */ 2> /* 2. Which publishers, by name, pay advances greater than */ 3> /* any paid by Algodata Infosystems? */ 4> /* */ 5> select pub_name, advance 6> from titles t, publishers p 7> where t.pub_id = p.pub_id 8> and advance > all 9> (select advance 10> from titles t1, publishers p1 11> where t1.pub_id = p1.pub_id 12> and pub_name like '%Al%' 13> and advance is not null) pub_name advance ---------------------------------------- ------------------------ New Age Books 10,125.00 Binnet & Hardley 15,000.00 (2 rows affected) 1> /* */ 2> /* Comments: Think of ANY as equivalent to IN (list members */ 3> /* related by OR). ALL doesn't have an exact equivalent, but*/ 4> /* can be thought of as a list with members related by AND. */ 5> /* */ 6> /* NOT NULL required for query using ALL - no advance would be */ 7> /* greater than "unknown". */ 8> /* */ 9> /* Duplicate rows: Due to same advance paid for different */ 10> /* titles (not shown). Could remove with DISTINCT. */ 11> /* */ 12> /* Actual advances paid by all publishers, for comparison: */ 13> /* */ 14> select pub_name, title_id, advance 15> from publishers p, titles t 16> where p.pub_id = t.pub_id pub_name title_id advance ---------------------------------------- -------- ------------------------ New Age Books BU2075 10,125.00 New Age Books PS2091 2,275.00 New Age Books PS2106 6,000.00 New Age Books PS3333 2,000.00 New Age Books PS7777 4,000.00 Binnet & Hardley MC2222 0.00 Binnet & Hardley MC3021 15,000.00 Binnet & Hardley MC3026 NULL Binnet & Hardley PS1372 7,000.00 Binnet & Hardley TC3218 7,000.00 Binnet & Hardley TC4203 4,000.00 Binnet & Hardley TC7777 8,000.00 Algodata Infosystems BU1032 5,000.00 Algodata Infosystems BU1111 5,000.00 Algodata Infosystems BU7832 5,000.00 Algodata Infosystems PC1035 7,000.00 Algodata Infosystems PC8888 8,000.00 Algodata Infosystems PC9999 NULL (18 rows affected) 1> /* */ 2> dump tran evelyn with no_log