1> /*                SQL/Sybase Programming, Fall 2000		*/
2> /*In-class practice exercises	       Oct.  28th, 2000 */
3> /*								*/
4> /* Note:  Formatting added to make more compact file.	*/
5> /*								*/
6> use pubs2
1> /*								*/
2> /*  Title listing with title, price, and publisher names.	*/
3> /*								*/
4> select convert(char(65), title) Title, pub_name Publisher, price
5> from titles t, publishers p
6> where t.pub_id = p.pub_id
 Title                                                            
	 Publisher                                price                    
 ----------------------------------------------------------------- 
	---------------------------------------- ------------------------ 
 You Can Combat Computer Stress!                                  
	 New Age Books                                                2.99 
 Is Anger the Enemy?                                              
	 New Age Books                                               10.95 
 Life Without Fear                                                
	 New Age Books                                                7.00 
 Prolonged Data Deprivation: Four Case Studies                    
	 New Age Books                                               19.99 
 Emotional Security: A New Algorithm                              
	 New Age Books                                                7.99 
 Silicon Valley Gastronomic Treats                                
	 Binnet & Hardley                                            19.99 
 The Gourmet Microwave                                            
	 Binnet & Hardley                                             2.99 
 The Psychology of Computer Cooking                               
	 Binnet & Hardley                                             NULL 
 Computer Phobic and Non-Phobic Individuals: Behavior Variations  
	 Binnet & Hardley                                            21.59 
 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean  
	 Binnet & Hardley                                            20.95 
 Fifty Years in Buckingham Palace Kitchens                        
	 Binnet & Hardley                                            11.95 
 Sushi, Anyone?                                                   
	 Binnet & Hardley                                            14.99 
 The Busy Executive's Database Guide                              
	 Algodata Infosystems                                        19.99 
 Cooking with Computers: Surreptitious Balance Sheets             
	 Algodata Infosystems                                        11.95 
 Straight Talk About Computers                                    
	 Algodata Infosystems                                        19.99 
 But Is It User Friendly?                                         
	 Algodata Infosystems                                        22.95 
 Secrets of Silicon Valley                                        
	 Algodata Infosystems                                        20.00 
 Net Etiquette                                                    
	 Algodata Infosystems                                         NULL 

(18 rows affected)
1> /*								*/
2> /*  Address list for publishers and stores.			*/
3> /*								*/
4> select Name = pub_name, city, state
5> from publishers
6> union
7> select stor_name, city, state
8> from stores
 Name                                     city                 state 
 ---------------------------------------- -------------------- ----- 
 Barnum's                                 Tustin               CA    
 Bookbeat                                 Portland             OR    
 New Age Books                            Boston               MA    
 News & Brews                             Los Gatos            CA    
 Fricative Bookshop                       Fremont              CA    
 Binnet & Hardley                         Washington           DC    
 Eric the Read Books                      Seattle              WA    
 Algodata Infosystems                     Berkeley             CA    
 Thoreau Reading Discount Chain           Concord              MA    
 Doc-U-Mat: Quality Laundry and Books     Remulade             WA    

(10 rows affected)
1> /*								*/
2> /*  Titles written by more than one author, with author names.*/
3> /*  Question:  Can this be done with joins only?  		*/
>>>>>  See Gina's solution, using joins with one self-join.     */
4> /*  Two partial solutions:					*/ 
5> /*								*/
6> /*  Find titles and how many authors only.			*/
7> /*								*/
8> select convert(char(65), title) "Multi-Author Titles", 
9> 	count(ta.title_id) #Authors
10> from titles t, titleauthor ta
11> where t.title_id = ta.title_id
12> group by title
13> having count(ta.title_id) > 1 
14> order by title
 Multi-Author Titles                                               #Authors    
 ----------------------------------------------------------------- ----------- 
 Computer Phobic and Non-Phobic Individuals: Behavior Variations             2 
 Cooking with Computers: Surreptitious Balance Sheets                        2 
 Is Anger the Enemy?                                                         2 
 Secrets of Silicon Valley                                                   2 
 Sushi, Anyone?                                                              3 
 The Busy Executive's Database Guide                                         2 
 The Gourmet Microwave                                                       2 

(7 rows affected)
1> /*								*/
2> /*  Find second and third authors, but not first.		*/
3> /*								*/
4> select convert(char(24), au_fname + ' ' +  au_lname) as 
5> 	"Second or Third Author", au_ord "Author Order",
6> 	convert(char(65), title) as Title 
7> from authors, titles, titleauthor 
8> where authors.au_id = titleauthor.au_id 
9> and titles.title_id = titleauthor.title_id 
10> and au_ord > 1
11> order by title
 Second or Third Author   Author Order
	 Title                                                             
 ------------------------ ------------ 
	----------------------------------------------------------------- 
 Stearns MacFeather                  2
	 Computer Phobic and Non-Phobic Individuals: Behavior Variations   
 Michael O'Leary                     2
	 Cooking with Computers: Surreptitious Balance Sheets              
 Anne Ringer                         2
	 Is Anger the Enemy?                                               
 Sheryl Hunter                       2
	 Secrets of Silicon Valley                                         
 Burt Gringlesby                     3
	 Sushi, Anyone?                                                    
 Michael O'Leary                     2
	 Sushi, Anyone?                                                    
 Marjorie Green                      2
	 The Busy Executive's Database Guide                               
 Anne Ringer                         2
	 The Gourmet Microwave                                             

(8 rows affected)
/*								*/
/*  Gina's solution - all joins, self-join on titleauthor	*/
/*  (some formatting added)					*/
/*								*/
2> select distinct convert(varchar(65), title) Title, 
3> 	convert(varchar(40), au_lname + ', '+ au_fname) Author
4> from titles t, titleauthor ta, titleauthor tb, authors a
5> where a.au_id = ta.au_id
6> and t.title_id = ta.title_id
7> and ta.title_id = tb.title_id
8> and ta.au_id != tb.au_id
 Title                                                            
	 Author                                   
 ----------------------------------------------------------------- 
	---------------------------------------- 
 Sushi, Anyone?                                                   
	 Yokomoto, Akiko                          
 Sushi, Anyone?                                                   
	 Gringlesby, Burt                         
 Sushi, Anyone?                                                   
	 O'Leary, Michael                         
 Is Anger the Enemy?                                              
	 Ringer, Anne                             
 Is Anger the Enemy?                                              
	 Ringer, Albert                           
 The Gourmet Microwave                                            
	 Ringer, Anne                             
 Secrets of Silicon Valley                                        
	 Dull, Ann                                
 The Gourmet Microwave                                            
	 DeFrance, Michel                         
 Secrets of Silicon Valley                                        
	 Hunter, Sheryl                           
 The Busy Executive's Database Guide                              
	 Bennet, Abraham                          
 The Busy Executive's Database Guide                              
	 Green, Marjorie                          
 Cooking with Computers: Surreptitious Balance Sheets             
	 O'Leary, Michael                         
 Cooking with Computers: Surreptitious Balance Sheets             
	 MacFeather, Stearns                      
 Computer Phobic and Non-Phobic Individuals: Behavior Variations  
	 Karsen, Livia                            
 Computer Phobic and Non-Phobic Individuals: Behavior Variations  
	 MacFeather, Stearns                      

(15 rows affected)
1> /*								*/
2> /*  List all information for one author, including "blurb".	*/
3> /*  (E.g., Bennet, who has a "blurb"; otherwise 0 rows.)	*/
4> /*								*/
5> /*  Note:  See example of isnull() for country.			*/
6> /*								*/
7> select a.au_id, convert(char(24), au_fname + ' ' + au_lname) Name,
8> 	phone, convert(char(20), address) Address, 
9> 	convert(char(10), a.city) City, a.state, 
10> 	isnull(country, 'USA') Country, 
11> 	postalcode, copy, convert(char(65), title) Title, contract, 
12> 	advance, total_sales, pub_name
13> from authors a, blurbs b, titleauthor ta, titles t, publishers p
14> where a.au_id = b.au_id
15> and a.au_id = ta.au_id
16> and ta.title_id = t.title_id
17> and t.pub_id = p.pub_id
18> and au_lname = 'Bennet' 
 au_id       Name                     phone        Address             
	 City       state Country      postalcode
	 copy                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
	 Title                                                            
	 contract advance                  total_sales
	 pub_name                                 
 ----------- ------------------------ ------------ -------------------- 
	---------- ----- ------------ ---------- 
	-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
	----------------------------------------------------------------- 
	-------- ------------------------ ----------- 
	---------------------------------------- 
 409-56-7008 Abraham Bennet           415 658-9932 6223 Bateman St.    
	 Berkeley   CA    USA          94705     
	 Bennet was the classic too-busy
executive.  After discovering computer databases he now has the time to
run several successful businesses and sit on three major corporate
boards.  Bennet also donates time to community service organizations.
Miraculously, he also finds time to write and market executive-oriented
in-depth computer hardware and software reviews.  "I'm hyperkinetic,
so being dynamic and fast-moving is a piece of cake.  But being
organized isn't easy for me or for anyone I know.  There's just one
word for that: `databases!' Databases can cure you or kill you.  If you
get the right one, you can be like me.  If you get the wrong one, watch
out.  Read my book!"
	 The Busy Executive's Database Guide                              
	        1                 5,000.00        4095
	 Algodata Infosystems                     

(1 row affected)
1> /*								*/
2> /* How many copies of each title must be sold to cover advances?*/
3> /* Note use of ceiling() function to round up to nearest whole*/
4> /* number of books - unrounded number included for comparison.*/
5> /*						 		 */
6> select convert(char(65), title) Title, 
7> 	convert(char(20), pub_name) Publisher, 
8> 	convert(char(9), advance) as Advance, 
9> 	convert(char(9), price) as Price, 
10> 	convert(decimal(6,2), advance/price) as Unrounded, 
11> 	convert(decimal(6,0), ceiling(advance/price)) as BreakEven
12> from titles t, publishers p
13> where t.pub_id = p.pub_id
14> and advance is not null 
15> and advance > 0
 Title                                                            
	 Publisher            Advance   Price     Unrounded BreakEven 
 ----------------------------------------------------------------- 
	-------------------- --------- --------- --------- --------- 
 The Busy Executive's Database Guide                              
	 Algodata Infosystems   5000.00     19.99    250.13       251 
 Cooking with Computers: Surreptitious Balance Sheets             
	 Algodata Infosystems   5000.00     11.95    418.41       419 
 You Can Combat Computer Stress!                                  
	 New Age Books         10125.00      2.99   3386.29      3387 
 Straight Talk About Computers                                    
	 Algodata Infosystems   5000.00     19.99    250.13       251 
 The Gourmet Microwave                                            
	 Binnet & Hardley      15000.00      2.99   5016.72      5017 
 But Is It User Friendly?                                         
	 Algodata Infosystems   7000.00     22.95    305.01       306 
 Secrets of Silicon Valley                                        
	 Algodata Infosystems   8000.00     20.00    400.00       400 
 Computer Phobic and Non-Phobic Individuals: Behavior Variations  
	 Binnet & Hardley       7000.00     21.59    324.22       325 
 Is Anger the Enemy?                                              
	 New Age Books          2275.00     10.95    207.76       208 
 Life Without Fear                                                
	 New Age Books          6000.00      7.00    857.14       858 
 Prolonged Data Deprivation: Four Case Studies                    
	 New Age Books          2000.00     19.99    100.05       101 
 Emotional Security: A New Algorithm                              
	 New Age Books          4000.00      7.99    500.63       501 
 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean  
	 Binnet & Hardley       7000.00     20.95    334.13       335 
 Fifty Years in Buckingham Palace Kitchens                        
	 Binnet & Hardley       4000.00     11.95    334.73       335 
 Sushi, Anyone?                                                   
	 Binnet & Hardley       8000.00     14.99    533.69       534 

(15 rows affected)
1> /*								*/
2> /*  Which titles, by name, have been ordered by Bookbeat?	*/
3> /*								*/
4> select distinct convert(char(65), title) Title, stor_name
5> from titles t, salesdetail sd, stores s
6> where t.title_id = sd.title_id
7> and sd.stor_id = s.stor_id
8> and stor_name = 'Bookbeat'
 Title                                                            
	 stor_name                                
 ----------------------------------------------------------------- 
	---------------------------------------- 
 But Is It User Friendly?                                         
	 Bookbeat                                 
 Cooking with Computers: Surreptitious Balance Sheets             
	 Bookbeat                                 
 Emotional Security: A New Algorithm                              
	 Bookbeat                                 
 Fifty Years in Buckingham Palace Kitchens                        
	 Bookbeat                                 
 Life Without Fear                                                
	 Bookbeat                                 
 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean  
	 Bookbeat                                 
 Prolonged Data Deprivation: Four Case Studies                    
	 Bookbeat                                 
 Straight Talk About Computers                                    
	 Bookbeat                                 
 The Busy Executive's Database Guide                              
	 Bookbeat                                 
 The Gourmet Microwave                                            
	 Bookbeat                                 
 You Can Combat Computer Stress!                                  
	 Bookbeat                                 

(11 rows affected)
1> /*								*/
2> /*  (As in previous)  How many of each title?		*/
3> /*  Question:  Why no need for distinct keyword here?	*/
4> /*								*/
5> select convert(char(65), title) Title, sum(qty) "# Ordered"
6> from titles t, salesdetail sd, stores s
7> where t.title_id = sd.title_id
8> and sd.stor_id = s.stor_id
9> and stor_name = 'Bookbeat'
10> group by title
 Title                                                             # Ordered   
 ----------------------------------------------------------------- ----------- 
 But Is It User Friendly?                                                  533 
 Cooking with Computers: Surreptitious Balance Sheets                      390 
 Emotional Security: A New Algorithm                                       180 
 Fifty Years in Buckingham Palace Kitchens                                 776 
 Life Without Fear                                                         111 
 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean            40 
 Prolonged Data Deprivation: Four Case Studies                              90 
 Straight Talk About Computers                                             300 
 The Busy Executive's Database Guide                                       394 
 The Gourmet Microwave                                                     739 
 You Can Combat Computer Stress!                                           180 

(11 rows affected)
1> /*								*/
2> /*  How many copies of some title (e.g., Life Without Fear) will*/ 
3> /*  Bookbeat have to sell to break even?  To make a 15% profit? */
4> /*								*/
5> /*  Question:  Is there enough data to calculate 15% profit?	*/
6> /*								*/
7> select convert(char(65), title) Title, sum(qty) SellAll_AtCost
8> from titles t, salesdetail sd, stores s
9> where t.title_id = sd.title_id
10> and sd.stor_id = s.stor_id
11> and stor_name = 'Bookbeat'
12> and title = 'Life Without Fear'
13> group by title
 Title                                                            
	 SellAll_AtCost 
 ----------------------------------------------------------------- 
	-------------- 
 Life Without Fear                                                
	            111 

(1 row affected)