RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 91

1.

''Find the topic number, book name, and number of pages of all the topics
published by London Publishing Ltd. List the results in order by book
name.''

This query obviously requires the PUBNAME attribute but it does not require
the PUBLISHER table. All of the information needed is in the BOOK table,
including the PUBNAME attribute, which is there as a foreign key. The SELECT
statement is:

SELECT BOOKNUM, BOOKNAME, PAGES
FROM BOOK
WHERE PUBNAME='London Publishing Ltd.'
ORDER BY BOOKNAME;

2.

''How many books of at least 400 pages does Good Reading Bookstores
carry that were published by publishers based in Paris, France?''

This is a straightforward join between the PUBLISHER and BOOK tables that
uses the built-in function COUNT. All of the attribute names are unique between
the two tables, except for PUBNAME, which must be qualified with a table name
every time it is used. Notice that 'Good Reading Bookstores' does not appear as
a condition in the SELECT statement, although it was mentioned in the query.
The entire database is about Good Reading Bookstores and no other! There is no
BOOKSTORE CHAIN table in the database and there is no STORENAME or
CHAINNAME attribute in any of the tables.

SELECT COUNT(*)
FROM PUBLISHER, BOOK
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND CITY='Paris'
AND COUNTRY='France'
AND PAGES > =400;

3.

''List the publishers in Belgium, Brazil, and Singapore that publish books
written by authors who were born before 1920.''

Sometimes a relatively simple-sounding query can be fairly involved. This
query actually requires four tables of the database! To begin with, we need the
PUBLISHER table because that's the only place that a publisher's country is
stored. But we also need the AUTHOR table because that's where author birth
years are stored. The only way to tie the PUBLISHER table to the AUTHOR table
is to connect PUBLISHER to BOOK, then to connect BOOK to WRITING, and
finally to connect WRITING to AUTHOR. With simple, one-attribute keys such
as those in these tables, the number of joins will be one fewer than the number
of tables. The FROM clause below shows four tables and the first three lines of
the WHERE clause show the three joins. Also, notice that since a publisher may
have published more than one book with the stated specifications, DISTINCT
is required to prevent the same publisher name from appearing several, perhaps
many, times in the result. Finally, since we want to include publishers in three
specific countries, we list the three countries as Belgium, Brazil, and Singapore.
But, in the SELECT statement, we have to indicate that for a record to be
included in the result, the value of the COUNTRY attribute must be Belgium,
Brazil or Singapore.