/* Quiz 2, Nov. 18, 1999 - possible solutions to all questions */
/* 1. Which authors are non-primary authors on one or more books? */
/* Give complete author names and book titles. */
/* */
1> select au_lname, title
2> from authors a, titleauthor ta, titles t
3> where a.au_id = ta.au_id
4> and ta.title_id = t.title_id
5> and au_ord in (2,3)
6> go
au_lname
title
----------------------------------------
--------------------------------------------------------------------------------
Green
The Busy Executive's Database Guide
O'Leary
Cooking with Computers: Surreptitious Balance Sheets
Ringer
The Gourmet Microwave
Hunter
Secrets of Silicon Valley
MacFeather
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Ringer
Is Anger the Enemy?
O'Leary
Sushi, Anyone?
Gringlesby
Sushi, Anyone?
(8 rows affected)
1>
/* 2. Which book titles are included in sales order number Asoap132, which */
/* store placed the order, and when was the order placed? Show title, store */
/* name, and date. */
/* */
1> select title, stor_name, date
2> from titles t, salesdetail sd, sales s, stores sr
3> where t.title_id = sd.title_id
4> and sd.ord_num = s.ord_num
5> and s.stor_id = sr.stor_id
6> and sd.ord_num='Asoap132'
7> go
title
stor_name date
--------------------------------------------------------------------------------
---------------------------------------- --------------------------
You Can Combat Computer Stress!
Doc-U-Mat: Quality Laundry and Books Nov 16 1986 12:00AM
The Gourmet Microwave
Doc-U-Mat: Quality Laundry and Books Nov 16 1986 12:00AM
The Busy Executive's Database Guide
Doc-U-Mat: Quality Laundry and Books Nov 16 1986 12:00AM
(3 rows affected)
1>
/* 3. How much has the publisher New Age Books paid in advances (total)? */
/* */
1> select sum(advance)
2> from titles
3> where pub_id in
4> (select pub_id
5> from publishers
6> where pub_name = 'New Age Books')
7> go
------------------------
24,400.00
(1 row affected)
1>
/* 4. Which store(s), by name, has/have ordered more than the average */
/* total number of books ordered by all stores? */
/* */
1> select stor_name
2> from stores s, salesdetail sd
3> group by sd.stor_id
4> having s.stor_id = sd.stor_id
5> and sum(qty) > avg(sum(qty))
6>
7> go
stor_name
----------------------------------------
Thoreau Reading Discount Chain
(1 row affected)
1>
/* 5. Which store or stores ordered more than the total number of books */
/* ordered by Barnum's? Show the store name(s) and the total number of */
/* books ordered. */
/* */
1> select stor_name, sum(qty)
2> from stores s, salesdetail sd
3> where s.stor_id = sd.stor_id
4> group by stor_name
5> having sum(qty) > any
6> (select sum(qty)
7> from stores s, salesdetail sd
8> where s.stor_id = sd.stor_id
9> group by stor_name
10> having stor_name = "Barnum's")
11> go
stor_name
---------------------------------------- -----------
Bookbeat 3733
News & Brews 2900
Thoreau Reading Discount Chain 82674
(3 rows affected)
1>
/* 6. Which titles (show in full) has the store Bookbeat ordered */
/* and how many of each? */
/* */
1> select title, sum(qty)
2> from titles t, salesdetail sd
3> group by stor_id, sd.title_id
4> having t.title_id = sd.title_id
5> and stor_id in
6> (select stor_id
7> from stores
8> where stor_name = 'Bookbeat')
9> go
title
--------------------------------------------------------------------------------
-----------
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 Mediterranean
720
Fifty Years in Buckingham Palace Kitchens
13968
(11 rows affected)