Here, also, is the promised discussion of question 10 from the first
assignment.
Let's do the problem in stages.
First, can we identify the books with co-authors?
The answer is: yes.
select bookcode
from wrote
where sequence >= 2
group by bookcode
That was good practice. Let's use this ability:
select title, price
from book
where bookcode in (select bookcode
from wrote
where sequence >= 2
group by bookcode)
That was the list of all titles and prices of books like the ones we want.
Could we print the authors, in order?
select title, price, authorfirst, authorlast
from book, wrote, author
where book.bookcode = wrote.bookcode and
wrote.authornum = author.authornum
order by book.bookcode, sequence
Great! Then we're almost done.
select title, price, authorfirst, authorlast
from book, wrote, author
where book.bookcode = wrote.bookcode and
wrote.authornum = author.authornum and
book.bookcode in (select bookcode
from wrote
where sequence >= 2
group by bookcode)
order by book.bookcode, sequence
This produces the following answer:
| title | price | authorfirst | authorlast
|
|---|
| Treasure Chests | 24.46 | Lon | Schleining
|
| Treasure Chests | 24.46 | Randy | O'Rourke
|
| Van Gogh and Gaugain | 21.00 | Bradley | Collins, Jr.
|
| Van Gogh and Gaugain | 21.00 | Bradley | Collins
|
| Black House | 18.81 | Stephen | King
|
| Black House | 18.81 | Peter | Straub
|
(Well, OK, perhaps it's exactly backwards - use DESC).
Now my question to you is this: can you make a report out of it?
A nice, clean report?
Last updated: Sep 22, 2002 by Adrian German for A114/I111
