1> /* In-class / take-home exercises, 10/28/1999 */ 2> /* */ 3> use pubs2 1> /* Which book(s) did each author write or collaborate on? */ 2> /* */ 3> select au_fname, au_lname, au_ord, title 4> from authors a, titleauthor ta, titles t 5> where a.au_id = ta.au_id 6> and ta.title_id = t.title_id au_fname au_lname au_ord title -------------------- ---------------------------------------- ------ -------------------------------------------------------------------------------- Marjorie Green 2 The Busy Executive's Database Guide Abraham Bennet 1 The Busy Executive's Database Guide Michael O'Leary 2 Cooking with Computers: Surreptitious Balance Sheets Stearns MacFeather 1 Cooking with Computers: Surreptitious Balance Sheets Marjorie Green 1 You Can Combat Computer Stress! Dick Straight 1 Straight Talk About Computers Innes del Castillo 1 Silicon Valley Gastronomic Treats Michel DeFrance 1 The Gourmet Microwave Anne Ringer 2 The Gourmet Microwave Cheryl Carson 1 But Is It User Friendly? Ann Dull 1 Secrets of Silicon Valley Sheryl Hunter 2 Secrets of Silicon Valley Chastity Locksley 1 Net Etiquette Stearns MacFeather 2 Computer Phobic and Non-Phobic Individuals: Behavior Variations Livia Karsen 1 Computer Phobic and Non-Phobic Individuals: Behavior Variations Anne Ringer 2 Is Anger the Enemy? Albert Ringer 1 Is Anger the Enemy? Albert Ringer 1 Life Without Fear Johnson White 1 Prolonged Data Deprivation: Four Case Studies Chastity Locksley 1 Emotional Security: A New Algorithm Sylvia Panteley 1 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Reginald Blotchet-Halls 1 Fifty Years in Buckingham Palace Kitchens Michael O'Leary 2 Sushi, Anyone? Burt Gringlesby 3 Sushi, Anyone? Akiko Yokomoto 1 Sushi, Anyone? (25 rows affected) 1> 2> /* Which stores and publishers are not located in the same city?*/ 3> /* */ 4> /* How NOT to answer this question: */ 5> /* */ 6> select stor_name, pub_name, s.city, s.state 7> from stores s, publishers p 8> where s.city != p.city stor_name pub_name city state ---------------------------------------- ---------------------------------------- -------------------- ----- Barnum's New Age Books Tustin CA News & Brews New Age Books Los Gatos CA Doc-U-Mat: Quality Laundry and Books New Age Books Remulade WA Bookbeat New Age Books Portland OR Eric the Read Books New Age Books Seattle WA Fricative Bookshop New Age Books Fremont CA Thoreau Reading Discount Chain New Age Books Concord MA Barnum's Binnet & Hardley Tustin CA News & Brews Binnet & Hardley Los Gatos CA Doc-U-Mat: Quality Laundry and Books Binnet & Hardley Remulade WA Bookbeat Binnet & Hardley Portland OR Eric the Read Books Binnet & Hardley Seattle WA Fricative Bookshop Binnet & Hardley Fremont CA Thoreau Reading Discount Chain Binnet & Hardley Concord MA Barnum's Algodata Infosystems Tustin CA News & Brews Algodata Infosystems Los Gatos CA Doc-U-Mat: Quality Laundry and Books Algodata Infosystems Remulade WA Bookbeat Algodata Infosystems Portland OR Eric the Read Books Algodata Infosystems Seattle WA Fricative Bookshop Algodata Infosystems Fremont CA Thoreau Reading Discount Chain Algodata Infosystems Concord MA (21 rows affected) 1> 2> /* A reasonable way to answer the question: */ 3> /* */ 4> select stor_name as Store_or_Publisher, s.city as City, 5> s.state as State 6> from stores s, publishers p 7> where s.city *= p.city 8> union 9> select pub_name, p.city, p.state 10> from stores s, publishers p 11> where s.city =* p.city Store_or_Publisher 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> /* For which titles have more than 3000 copies been ordered? */ 3> /* */ 4> select t.title_id, qty 5> from titles t, salesdetail s 6> where t.title_id = s.title_id 7> and qty > 3000 title_id qty -------- ------ BU2075 4000 MC3021 5000 MC3021 4500 MC3021 3200 TC4203 3500 (5 rows affected) 1> 2> /* Alternate interpretation */ 3> /* */ 4> select t.title_id, sum(qty) 5> from titles t, salesdetail s 6> where t.title_id = s.title_id 7> group by t.title_id 8> having sum(qty) > 3000 title_id -------- ----------- BU1032 4095 BU1111 3876 BU2075 18722 BU7832 4095 MC3021 22246 PC1035 8780 PC8888 4095 PS3333 4072 PS7777 3336 TC4203 15096 TC7777 4095 (11 rows affected) 1> 2> /* Which authors live in the same city? */ 3> /* */ 4> select distinct a1.au_fname, a1.au_lname, a1.city 5> from authors a1, authors a2 6> where a1.city = a2.city 7> and a1.au_id != a2.au_id 8> order by a1.city au_fname au_lname city -------------------- ---------------------------------------- -------------------- Cheryl Carson Berkeley Abraham Bennet Berkeley Livia Karsen Oakland Dick Straight Oakland Dirk Stringer Oakland Marjorie Green Oakland Stearns MacFeather Oakland Ann Dull Palo Alto Sheryl Hunter Palo Alto Anne Ringer Salt Lake City Albert Ringer Salt Lake City (11 rows affected) 1> 2> /* Which titles has News & Brews ordered and how many of each? */ 3> /* */ 4> select title, sum(qty) 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 s.stor_name = 'News & Brews' 9> group by title title -------------------------------------------------------------------------------- ----------- But Is It User Friendly? 34 Cooking with Computers: Surreptitious Balance Sheets 175 Emotional Security: A New Algorithm 250 Fifty Years in Buckingham Palace Kitchens 565 Is Anger the Enemy? 200 Prolonged Data Deprivation: Four Case Studies 345 Secrets of Silicon Valley 335 Straight Talk About Computers 360 The Busy Executive's Database Guide 136 The Gourmet Microwave 270 You Can Combat Computer Stress! 230 (11 rows affected)