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

SELECT DISTINCT PUBNAME
FROM PUBLISHER, BOOK, WRITING, AUTHOR
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND BOOK.BOOKNUM=WRITING.BOOKNUM
AND WRITING.AUTHORNUM=AUTHOR.AUTHORNUM
AND COUNTRY IN ('Belgium', 'Brazil', 'Singapore')
AND YEARBORN < 1920;

''How many books did each publisher in Oslo, Norway; Nairobi, Kenya;
and Auckland, New Zealand, publish in 2001?''
The keyword here is ''each.'' This query requires a separate total for each
publisher that satisfies the conditions. This is a job for the GROUP BY clause.
We want to group together the records for each publisher and count the number of
records in each group. Each line of the result must include both a publisher name
and count of the number of records that satisfy the conditions. This SELECT
statement requires both a join and a GROUP BY. Notice the seeming complexity
but really the unambiguous beauty of the ANDs and ORs structure regarding the
cities and countries.

4.

SELECT PUBNAME, CITY, COUNTRY, COUNT(*)
FROM PUBLISHER, BOOK
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND ((CITY='Oslo' AND COUNTRY='Norway')
OR (CITY='Nairobi' AND COUNTRY='Kenya')
OR (CITY='Auckland' AND COUNTRY='New Zealand'))
AND PUBYEAR=2001
GROUP BY PUBNAME;

5.

''Which publisher published the topic that has the earliest publication year
among all the topics that Good Reading Bookstores carries?''
All that is called for in this query is the name of the publisher, not the name
of the topic. This is a case that requires a subquery. First the system has to
determine the earliest publication year, then it has to see which books have that
earliest publication year. Once you know the topic, their records in the BOOK
table give you the publisher names. Since more than one publisher may have
published a book in that earliest year, there could be more than one publisher
name in the result. And, since a particular publisher could have published more
than one book in that earliest year, DISTINCT is required to avoid having that
publisher's name listed more than once.

SELECT DISTINCT PUBNAME
FROM BOOK
WHERE PUBYEAR=
(SELECT MIN(PUBYEAR)
FROM BOOK);

EXAMPLE: WORLD MUSIC ASSOCIATION

Figure 4.3 is the World Music Association relational database. Here is a list of
queries for the World Music Association.