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)