1> /* SQL/Sybase, Fall 2000 */
2> /* */
3> /* Results for assignment 2 (11/04/2000) */
4> /* Note: There may be alternate ways to get the same answers. */
5> /* */
6> use pubs2
1> /* */
2> /*------------------------------1----------------------------*/
3> /* Books written by each author, including author order. */
4> /* */
5> select convert(varchar(40), au_fname + ' ' + au_lname) Author,
6> au_ord "Author order", convert(varchar(65), title) Title
7> from authors a, titleauthor ta, titles t
8> where a.au_id = ta.au_id
9> and t.title_id = ta.title_id
10> order by au_lname
Author Author order
Title
---------------------------------------- ------------
-----------------------------------------------------------------
Abraham Bennet 1
The Busy Executive's Database Guide
Reginald Blotchet-Halls 1
Fifty Years in Buckingham Palace Kitchens
Cheryl Carson 1
But Is It User Friendly?
Michel DeFrance 1
The Gourmet Microwave
Ann Dull 1
Secrets of Silicon Valley
Marjorie Green 1
You Can Combat Computer Stress!
Marjorie Green 2
The Busy Executive's Database Guide
Burt Gringlesby 3
Sushi, Anyone?
Sheryl Hunter 2
Secrets of Silicon Valley
Livia Karsen 1
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Chastity Locksley 1
Net Etiquette
Chastity Locksley 1
Emotional Security: A New Algorithm
Stearns MacFeather 1
Cooking with Computers: Surreptitious Balance Sheets
Stearns MacFeather 2
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Michael O'Leary 2
Sushi, Anyone?
Michael O'Leary 2
Cooking with Computers: Surreptitious Balance Sheets
Sylvia Panteley 1
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Albert Ringer 1
Life Without Fear
Anne Ringer 2
Is Anger the Enemy?
Albert Ringer 1
Is Anger the Enemy?
Anne Ringer 2
The Gourmet Microwave
Dick Straight 1
Straight Talk About Computers
Johnson White 1
Prolonged Data Deprivation: Four Case Studies
Akiko Yokomoto 1
Sushi, Anyone?
Innes del Castillo 1
Silicon Valley Gastronomic Treats
(25 rows affected)
1> /* */
2> /*------------------------------2----------------------------*/
3> /* Stores and publishers not located in same city (list state).*/
4> /* */
5> select stor_name as Name, s.city as City, 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
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> /* Next statement doesn't work because cities are different -*/
3> /* gets Cartesian product, 7 x 3. */
4> /* */
5> select convert(varchar(35), stor_name) Store,
6> convert(varchar(35), pub_name) Publisher,
7> s.city, s.state, p.city, p.state
8> from stores s, publishers p
9> where s.city != p.city
Store Publisher
city state city state
----------------------------------- -----------------------------------
-------------------- ----- -------------------- -----
Barnum's New Age Books
Tustin CA Boston MA
News & Brews New Age Books
Los Gatos CA Boston MA
Doc-U-Mat: Quality Laundry and Book New Age Books
Remulade WA Boston MA
Bookbeat New Age Books
Portland OR Boston MA
Eric the Read Books New Age Books
Seattle WA Boston MA
Fricative Bookshop New Age Books
Fremont CA Boston MA
Thoreau Reading Discount Chain New Age Books
Concord MA Boston MA
Barnum's Binnet & Hardley
Tustin CA Washington DC
News & Brews Binnet & Hardley
Los Gatos CA Washington DC
Doc-U-Mat: Quality Laundry and Book Binnet & Hardley
Remulade WA Washington DC
Bookbeat Binnet & Hardley
Portland OR Washington DC
Eric the Read Books Binnet & Hardley
Seattle WA Washington DC
Fricative Bookshop Binnet & Hardley
Fremont CA Washington DC
Thoreau Reading Discount Chain Binnet & Hardley
Concord MA Washington DC
Barnum's Algodata Infosystems
Tustin CA Berkeley CA
News & Brews Algodata Infosystems
Los Gatos CA Berkeley CA
Doc-U-Mat: Quality Laundry and Book Algodata Infosystems
Remulade WA Berkeley CA
Bookbeat Algodata Infosystems
Portland OR Berkeley CA
Eric the Read Books Algodata Infosystems
Seattle WA Berkeley CA
Fricative Bookshop Algodata Infosystems
Fremont CA Berkeley CA
Thoreau Reading Discount Chain Algodata Infosystems
Concord MA Berkeley CA
(21 rows affected)
1> /* */
2> /*-------------------------------3---------------------------*/
3> /* Titles for which more than 3000 copies have been ordered.*/
4> /* (ord_num added to show why more than one row in one case)*/
5> /* */
6> select convert(varchar(45), title) Title, qty, ord_num
7> from titles t, salesdetail s
8> where t.title_id = s.title_id
9> and qty > 3000
Title qty ord_num
--------------------------------------------- ------ --------------------
You Can Combat Computer Stress! 4000 AB-123-DEF-425-1Z3
The Gourmet Microwave 5000 AB-872-DEF-732-2Z1
The Gourmet Microwave 4500 BS-345-DSE-860-1F2
The Gourmet Microwave 3200 ZS-645-CAT-415-1B2
Fifty Years in Buckingham Palace Kitchens 3500 NF-123-ADS-642-9G3
(5 rows affected)
1> /* */
2> /* The question can be interpreted as sum(qty) > 3000... */
3> /* */
4> select convert(varchar(55), title), sum(qty)
5> from titles t, salesdetail s
6> where t.title_id = s.title_id
7> group by title
8> having sum(qty) > 3000
------------------------------------------------------- -----------
But Is It User Friendly? 8780
Cooking with Computers: Surreptitious Balance Sheets 3876
Emotional Security: A New Algorithm 3336
Fifty Years in Buckingham Palace Kitchens 15096
Prolonged Data Deprivation: Four Case Studies 4072
Secrets of Silicon Valley 4095
Straight Talk About Computers 4095
Sushi, Anyone? 4095
The Busy Executive's Database Guide 4095
The Gourmet Microwave 22246
You Can Combat Computer Stress! 18722
(11 rows affected)
1> /* */
2> /*-------------------------------4---------------------------*/
3> /* Which authors live in the same city (list by city). */
4> /* */
5> select distinct convert(varchar(40), a1.au_fname + ' ' + a1.au_lname)
6> as Author, a1.city
7> from authors a1, authors a2
8> where a1.city = a2.city
9> and a1.au_id != a2.au_id
10> order by a1.city
Author 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> /*-------------------------------5---------------------------*/
3> /* Titles and quantities of each ordered by News & Brews. */
4> /* */
5> select convert(varchar(55), title) Title, sum(qty) TotalOrdered
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 like '%Brew%'
10> group by title
Title TotalOrdered
------------------------------------------------------- ------------
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)
1> /* */
2> /* Similar statement, not as good, one title repeated. */
3> /* */
4> select convert(varchar(55), title) Title, 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 stor_name like '%Brew%'
Title qty
------------------------------------------------------- ------
The Busy Executive's Database Guide 136
Cooking with Computers: Surreptitious Balance Sheets 175
You Can Combat Computer Stress! 230
Straight Talk About Computers 360
The Gourmet Microwave 270
But Is It User Friendly? 34
Secrets of Silicon Valley 335
Is Anger the Enemy? 200
Prolonged Data Deprivation: Four Case Studies 345
Emotional Security: A New Algorithm 250
Fifty Years in Buckingham Palace Kitchens 512
Fifty Years in Buckingham Palace Kitchens 53
(12 rows affected)