1> /* Possible solutions to In-class practice exercises, 11/11/1999 */ 2> /* */ 3> use pubs2 1> /* */ 2> /* Which stores (by name) do not carry psychology books? */ 3> /* */ 4> /* Solution using subqueries */ 5> /* */ 6> select stor_name 7> from stores 8> where stor_id not in 9> (select stor_id 10> from salesdetail 11> where title_id in 12> (select title_id 13> from titles 14> where type = 'psychology')) stor_name ---------------------------------------- Doc-U-Mat: Quality Laundry and Books Fricative Bookshop (2 rows affected) 1> /* */ 2> /* Solution using joins and subqueries */ 3> /* */ 4> select distinct stor_name 5> from stores s, salesdetail sd 6> where s.stor_id = sd.stor_id 7> and sd.stor_id not in 8> (select sd.stor_id 9> from salesdetail sd, titles t 10> where sd.title_id = t.title_id 11> and type = 'psychology') stor_name ---------------------------------------- Fricative Bookshop Doc-U-Mat: Quality Laundry and Books (2 rows affected) 1> /* */ 2> /* Query to check previous answers. Several possibilites, e.g.: */ 3> /* */ 4> select distinct stor_name 5> from stores s, salesdetail sd, titles t 6> where s.stor_id = sd.stor_id 7> and sd.title_id = t.title_id 8> and t.title_id in 9> (select title_id 10> from titles 11> where type = 'psychology') stor_name ---------------------------------------- Barnum's Bookbeat News & Brews Eric the Read Books Thoreau Reading Discount Chain (5 rows affected) 1> /* */ 2> /* Advances greater than those paid by Algodata Infosystems */ 3> /* */ 4> select title 5> from titles 6> where advance > all 7> (select advance 8> from publishers p, titles t 9> where t.pub_id = p.pub_id 10> and pub_name = 'Algodata Infosystems' 11> and advance is not null) title -------------------------------------------------------------------------------- You Can Combat Computer Stress! The Gourmet Microwave (2 rows affected) 1> /* */ 2> /* Which title has sold fewer copies than any by Binnet & Hardley?*/ 3> /* */ 4> select title, total_sales 5> from titles 6> where total_sales < 7> (select min(total_sales) 8> from titles t, publishers p 9> group by t.pub_id 10> having t.pub_id = p.pub_id 11> and pub_name = 'Binnet & Hardley') title total_sales -------------------------------------------------------------------------------- ----------- Life Without Fear 111 (1 row affected)