1> /*  SQL/Sybase Programming - Open-book quiz 1 - Oct. 21st, 2000 */
2> /*								*/
3> use pubs2   
1> /*								*/
2> /*  Stores not located in California, sorted by state and city. */
3> /*								*/
4> select stor_name, city, state
5> from stores
6> where state != 'CA'
7> order by state, city
 stor_name                                city                 state 
 ---------------------------------------- -------------------- ----- 
 Thoreau Reading Discount Chain           Concord              MA    
 Bookbeat                                 Portland             OR    
 Doc-U-Mat: Quality Laundry and Books     Remulade             WA    
 Eric the Read Books                      Seattle              WA    

(4 rows affected)
1> /*								*/
2> /*  Increase price of psychology books whose price is < $15 by 10%. */
3> /*								*/
4> select title_id "Title #", 
5> 	price "Old price", 
6> 	convert(decimal(5,2),price * 1.1) "New price"
7> from titles
8> where type = 'psychology'
9> and price < 15
 Title # Old price                New price 
 ------- ------------------------ --------- 
 PS2091                     10.95     12.04 
 PS2106                      7.00      7.70 
 PS7777                      7.99      8.78 

(3 rows affected)
1> /*								*/
2> /* Titles and type of books having to do with cooking, by type. */
3> /*								*/
4> select title, type 	/* Checked result - no need to show notes */
5> from titles
6> where upper(title) like '%COOK%'
7> or    type like '%cook%'
8> or    upper(notes) like '%COOK%'
9> order by type

	 title                                                                           
	 type         
 
	-------------------------------------------------------------------------------- 
	------------ 

	 The Psychology of Computer Cooking                                              
	 UNDECIDED    

	 Cooking with Computers: Surreptitious Balance Sheets                            
	 business     

	 The Gourmet Microwave                                                           
	 mod_cook     

	 Silicon Valley Gastronomic Treats                                               
	 mod_cook     

	 Sushi, Anyone?                                                                  
	 trad_cook    

	 Fifty Years in Buckingham Palace Kitchens                                       
	 trad_cook    

	 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                 
	 trad_cook    

(7 rows affected)
1> /*								*/
2> /*  Date of publication of all business books, sorted by date.	*/
3> /*								*/
4> select title_id, type, convert(char(8), pubdate, 1) as "Published"
5> from titles
6> where type = 'business'
7> order by pubdate
 title_id type         Published 
 -------- ------------ --------- 
 BU2075   business     06/30/85  
 BU1032   business     06/12/86  
 BU7832   business     06/22/87  
 BU1111   business     06/09/88  

(4 rows affected)