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

the attributes SPNAME and CUSTNUM don't have to be qualified because each
appears in only one of the tables included in the SELECT statement.
Here is an example of a join involving three tables, assuming for the moment
that salesperson names are unique:

''List the names of the products of which salesperson Adams has sold more
than 2000 units.''

The salesperson name data appears only in the SALESPERSON table and the
product name data appears only in the PRODUCT table. The SALES table shows
the linkage between the two, including the quantities sold. And so the SELECT
statement will be:

SELECT PRODNAME
FROM SALESPERSON, PRODUCT, SALES
WHERE SALESPERSON.SPNUM=SALES.SPNUM
AND SALES.PRODNUM=PRODUCT.PRODNUM
AND SPNAME='Adams'
AND QUANTITY > 2000;

which results in:

PRODNAME

Hammer
Saw

Subqueries
A variation on the way that the SELECT statement works is when one SELECT
statement is ''nested'' within another in a format known as a subquery. This can
go on through several levels of SELECT statements, with each successive SELECT
statement contained in a pair of parentheses. The execution rule is that the innermost
SELECT statement is executed first and its results are then provided as input to the
SELECT statement at the next level up. This procedure can be an alternative to
the join. Furthermore, there are certain circumstances in which this procedure must

4.1 Q UERIES G ALORE ! YOUR

TURN

H aving a relational database to
query in any business environment opens up a new world
of information for managers to use to help them run their
portion of the business.

supermarket, a department store, even a sports league.
Write a list of ten questions that you would like to be
able to ask that would enhance your interaction with
that environment. Is it reasonable that a database could
be constructed that would support your ability to ask
the questions you've come up with? Do you think that
you would be able to formulate your questions using
SQL? Explain.

Q UESTION :
Think about a business environment that you are familiar
with from your daily life. It might be a university, a