1> /*	   SQL/Sybase Programming, Fall 2000 - Quiz 2		*/
2> /*								*/
3> /*	      Possible result set for all questions		*/
4> /*								*/
5> use pubs2
1> /*								*/
2> /*  Avg. price and avg. advance for each type of cookbook.	*/
3> /*								*/
4> select type, avg(price) as Avg_Price, avg(advance) as Avg_Advance
5> from titles
6> where type like '%cook%'
7> group by type
 type         Avg_Price                Avg_Advance              
 ------------ ------------------------ ------------------------ 
 mod_cook                        11.49                 7,500.00 
 trad_cook                       15.96                 6,333.33 

(2 rows affected)
1> /*							       	   */
2> /*  First authors of computing books (au_ord included as check).*/
3> /*								   */
4> select convert(varchar(20), au_fname + ' ' + au_lname) "First author", 
5>   convert(varchar(30), title) Title, au_ord "Author order"
6> from authors a, titleauthor ta, titles t
7> where a.au_id = ta.au_id
8> and ta.title_id = t.title_id
9> and type like '%comp%'
10> and au_ord = 1
 First author         Title                          Author order 
 -------------------- ------------------------------ ------------ 
 Cheryl Carson        But Is It User Friendly?                  1 
 Ann Dull             Secrets of Silicon Valley                 1 
 Chastity Locksley    Net Etiquette                             1 

(3 rows affected)
1> /*								*/
2> /*  Titles, store, and date for order Asoap132.		*/
3> /*								*/
4> select convert(varchar(60), title) as Title, 
5> 	convert(varchar(40), stor_name) as Store, 
6> 	convert(char(12), date, 101) as OrderDate
7> from titles t, salesdetail sd, sales s, stores sr
8> where t.title_id = sd.title_id
9> and sd.ord_num = s.ord_num
10> and s.stor_id = sr.stor_id
11> and sd.ord_num='Asoap132'
 Title                                                       
	 Store                                    OrderDate    
 ------------------------------------------------------------ 
	---------------------------------------- ------------ 
 You Can Combat Computer Stress!                             
	 Doc-U-Mat: Quality Laundry and Books     11/16/1986   
 The Gourmet Microwave                                       
	 Doc-U-Mat: Quality Laundry and Books     11/16/1986   
 The Busy Executive's Database Guide                         
	 Doc-U-Mat: Quality Laundry and Books     11/16/1986   

(3 rows affected)
1> /*								*/
2> /*  How much has New Age Books paid in advances (total)?	*/
3> /*								*/
4> select sum(advance) as "New Age Books TotalAdvances"           
5> from titles
6> where pub_id in
7>   (select pub_id  
8>    from publishers
9>    where pub_name = 'New Age Books')
 New Age Books TotalAdvances 
 --------------------------- 
                   24,400.00 

(1 row affected)
1> /*								*/
2> /*  Which store(s) has/have ordered more than the avg. total	*/
3> /*  number of books ordered by all stores?			*/
4> /*								*/
5> select stor_name
6> from stores s, salesdetail sd
7> group by sd.stor_id
8> having s.stor_id = sd.stor_id
9> and sum(qty) > avg(sum(qty))
 stor_name                                
 ---------------------------------------- 
 Thoreau Reading Discount Chain           

(1 row affected)
1> /*								 */
2> /*  Which store(s) ordered more than the total number of books*/
3> /*  ordered by Barnum's?  Show store name(s) and total number.*/
4> /*								 */
5> select convert(varchar(30), stor_name) as Store, 
6> 	sum(qty) as "Total ordered"
7> from stores s, salesdetail sd
8> where s.stor_id = sd.stor_id
9> group by stor_name 
10> having sum(qty) > any
11>   (select sum(qty)
12>    from stores s, salesdetail sd
13>    where s.stor_id = sd.stor_id
14>    group by stor_name 
15>    having stor_name = "Barnum's")
 Store                          Total ordered 
 ------------------------------ ------------- 
 Bookbeat                                3733 
 News & Brews                            2900 
 Thoreau Reading Discount Chain         82674 

(3 rows affected)
1> /*								*/
2> /*  Which titles has Bookbeat ordered and how many of each?	*/
3> /*								*/
4> select convert(varchar(55), title) Title, sum(qty) TotalOrdered	
5> from titles t, salesdetail sd
6> group by stor_id, sd.title_id
7> having t.title_id = sd.title_id
8> and stor_id in
9>   (select stor_id
10>    from stores
11>    where stor_name = 'Bookbeat')
 Title                                                   TotalOrdered 
 ------------------------------------------------------- ------------ 
 The Busy Executive's Database Guide                             7092 
 Cooking with Computers: Surreptitious Balance Sheets            7020 
 You Can Combat Computer Stress!                                 3240 
 Straight Talk About Computers                                   5400 
 The Gourmet Microwave                                          13302 
 But Is It User Friendly?                                        9594 
 Life Without Fear                                               1998 
 Prolonged Data Deprivation: Four Case Studies                   1620 
 Emotional Security: A New Algorithm                             3240 
 Onions, Leeks, and Garlic: Cooking Secrets of the Medit          720 
 Fifty Years in Buckingham Palace Kitchens                      13968 

(11 rows affected)