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)