/* Advanced SQL Programming Fall 2002 */ /* */ 1> /* Queries with quantified subquery predicates - any, all */ 2> /* */ 3> /* Will be looking for publisher offering best advance - see */ 4> /* all advances first. */ 5> /* */ 6> use pubs2 1> -- 2> select p.pub_id, pub_name, advance 3> from publishers p, titles t 4> where p.pub_id = t.pub_id pub_id pub_name advance ------ ---------------------------------------- ------------------------ 0736 New Age Books 10,125.00 0736 New Age Books 2,275.00 0736 New Age Books 6,000.00 0736 New Age Books 2,000.00 0736 New Age Books 4,000.00 0877 Binnet & Hardley 0.00 0877 Binnet & Hardley 15,000.00 0877 Binnet & Hardley NULL 0877 Binnet & Hardley 7,000.00 0877 Binnet & Hardley 7,000.00 0877 Binnet & Hardley 4,000.00 0877 Binnet & Hardley 8,000.00 1389 Algodata Infosystems 5,000.00 1389 Algodata Infosystems 5,000.00 1389 Algodata Infosystems 5,000.00 1389 Algodata Infosystems 7,000.00 1389 Algodata Infosystems 8,000.00 1389 Algodata Infosystems NULL (18 rows affected) 1> -- 2> /* */ 3> /* Which publishers pay higher advances than Algodata and on */ 4> /* which titles? */ 5> /* */ 6> select t.pub_id, title_id, advance from titles t 7> where advance > any 8> (select advance 9> from publishers p, titles t 10> where p.pub_id = t.pub_id 11> and pub_name like 'A%' 12> and advance is not null) pub_id title_id advance ------ -------- ------------------------ 0736 BU2075 10,125.00 0877 MC3021 15,000.00 1389 PC1035 7,000.00 1389 PC8888 8,000.00 0877 PS1372 7,000.00 0736 PS2106 6,000.00 0877 TC3218 7,000.00 0877 TC7777 8,000.00 (8 rows affected) 1> -- 2> /* */ 3> /* Find only the advances that are better than any paid by */ 4> /* Algodata and who pays them. */ 5> /* */ 6> select t.pub_id, advance from titles t 7> where advance > all 8> (select advance 9> from publishers p, titles t 10> where p.pub_id = t.pub_id 11> and pub_name like 'A%' 12> and advance is not null) pub_id advance ------ ------------------------ 0736 10,125.00 0877 15,000.00 (2 rows affected) 1> -- 2> /* */ 3> /* Note: Previous query requires taking care of nulls: */ 4> /* */ 5> select t.pub_id, advance from titles t 6> where advance > all 7> (select advance 8> from publishers p, titles t 9> where p.pub_id = t.pub_id 10> and pub_name like 'A%') pub_id advance ------ ------------------------ (0 rows affected) 1> -- 2> /* */ 3> /* Another way to find best advance - no need to check for nulls */ 4> /* */ 5> select t.pub_id, advance from titles t 6> where advance > 7> (select max(advance) 8> from publishers p, titles t 9> where p.pub_id = t.pub_id 10> and pub_name like 'A%') pub_id advance ------ ------------------------ 0736 10,125.00 0877 15,000.00 (2 rows affected) 1> -- 2> /* */ 3> /* To show error message if subquery returns more than one row */ 4> /* running almost same as query as above, without agg. function:*/ 5> /* */ 6> select t.pub_id, advance from titles t 7> where advance > 8> (select advance 9> from publishers p, titles t 10> where p.pub_id = t.pub_id 11> and pub_name like 'A%') Msg 512, Level 16, State 1: Line 7: Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression. Command has been aborted. (0 rows affected)