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

3. Begin constructing the WHERE clause by equating the join attributes from the
tables that are in the FROM clause. Once this job is out of the way, you can
begin considering the row limitations that must be stated in the WHERE clause.
4. Continue filling in the details of the WHERE clause, the GROUP BY clause,
and any subqueries.
One final piece of advice: If you are new to writing SQL SELECT commands
but you have a programming background, you may be tempted to avoid setting up
joins and try writing subqueries instead. Resist this temptation, for two reasons!
One is that joins are an essential part of the relational database concept. Embrace
them; don't be afraid of them. The other is that writing multiple levels of nested
subqueries can be extremely error prone and difficult to debug.

EXAMPLE: GOOD READING BOOK STORES

The best way to gain confidence in understanding SQL SELECT statements is to
write some! And there are some further refinements of the SQL SELECT that we
have yet to present. We will use the same three example databases that appeared
in previous chapters but, as with the General Hardware database, we will shorten
the attribute names. We will state a variety of queries and then give the SELECT
statements that will satisfy them, plus commentary as appropriate. You should try
to write the SELECT statements yourself before looking at our solutions!
Figure 4.2 is the Good Reading Bookstores relational database. Here is a list
of queries for Good Reading Bookstores.

PUBLISHER table

PUBNAME CITY COUNTRY TELEPHONE YRFOUND

AUTHOR table

AUTHORNUM AUTHORNAME YEARBORN YEARDIED

BOOK table

BOOKNUM BOOKNAME PUBYEAR PAGES PUBNAM E

CUSTOMER table

CUSTNUM CUSTNAME STREET CITY STATE COUNTRY

WRITING table

BOOKNU M AUTHORNU M

SALE table

FIGURE 4.2
Good reading Bookstores Relational
database

BOOKNUM CUSTNUM DATE PRICE QUANTITY