/*  Answers to quiz 1 questions, Fall 1999			*/
1> use pubs2
1> 
2> /* Stores not located in California				*/
3> select stor_name, city, state 
4> from stores
5> where state <> 'CA'
6> 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> 
3> /* Authors with first name starting w/S or last name starting C-G */
4> 
5> /* Alternative 1						*/
6> select au_fname, au_lname
7> from authors
8> where au_fname like 'S%' or au_lname between 'C' and 'H'
 au_fname             au_lname                                 
 -------------------- ---------------------------------------- 
 Cheryl               Carson                                   
 Michel               DeFrance                                 
 Ann                  Dull                                     
 Marjorie             Green                                    
 Morningstar          Greene                                   
 Burt                 Gringlesby                               
 Sheryl               Hunter                                   
 Stearns              MacFeather                               
 Sylvia               Panteley                                 

(9 rows affected)
1> 
2> /* Alternative 2						*/
3> select au_fname, au_lname
4> from authors
5> where substring(au_fname, 1, 1)='S' or substring(au_lname, 1, 1)
6>    in ('C', 'D', 'E', 'F', 'G')
 au_fname             au_lname                                 
 -------------------- ---------------------------------------- 
 Cheryl               Carson                                   
 Michel               DeFrance                                 
 Ann                  Dull                                     
 Marjorie             Green                                    
 Morningstar          Greene                                   
 Burt                 Gringlesby                               
 Sheryl               Hunter                                   
 Stearns              MacFeather                               
 Sylvia               Panteley                                 

(9 rows affected)
1> 
2> /* Alternative 3						*/
3> select au_fname, au_lname
4> from authors
5> where (au_fname > 'S' and au_fname < 'T') or      
6>    (au_lname > 'C' and au_lname < 'H')
 au_fname             au_lname                                 
 -------------------- ---------------------------------------- 
 Cheryl               Carson                                   
 Michel               DeFrance                                 
 Ann                  Dull                                     
 Marjorie             Green                                    
 Morningstar          Greene                                   
 Burt                 Gringlesby                               
 Sheryl               Hunter                                   
 Stearns              MacFeather                               
 Sylvia               Panteley                                 

(9 rows affected)
1> 
2> /* Increase book prices... 					*/
3> select title_id as "Title ID", price as "Old price",
4> 	convert(smallmoney, price*1.1) as "New price"
5> from titles
6> where type = 'psychology'
7> and price < 15
 Title ID Old price                New price                
 -------- ------------------------ ------------------------ 
 PS2091                      10.95                    12.05 
 PS2106                       7.00                     7.70 
 PS7777                       7.99                     8.79 

(3 rows affected)
1> 
2> /* How many titles?  How many types of book?			*/
3> select count(distinct title_id) as Number_of_titles
4> from titles
 Number_of_titles 
 ---------------- 
               18 

(1 row affected)
1> 
2> select count(distinct type) as Number_of_types
3> from titles
 Number_of_types 
 --------------- 
               6 

(1 row affected)
1> 
2> /* Maximum price for each type of book.				*/
3> select type, max(price)
4> from titles
5> group by type
 type                                  
 ------------ ------------------------ 
 UNDECIDED                        NULL 
 business                        19.99 
 mod_cook                        19.99 
 popular_comp                    22.95 
 psychology                      21.59 
 trad_cook                       20.95 

(6 rows affected)

/*  Number of days to go between day of quiz and last day of class */
1>  select datediff(dd, '10/28/99', '12/16/99')
2> go
             
 ----------- 
          49 

(1 row affected)