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