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

Notice that in this last SELECT statement, there are two limitations One,
that the Salesperson Number must be at least 150, appears in the WHERE clause
and the other, that the sum of the number of units sold must be at least 5000,
appears in the HAVING clause. It is important to understand why this is so. If the
limitation is based on individual attribute values that appear in the database, then
the condition goes in the WHERE clause. This is the case with the limitation based
on the Salesperson Number value. If the limitation is based on the group calculation
performed with the built-in function , then the condition goes in the HAVING clause.
This is the case with the limitation based on the sum of the number of product units
sold.
The Join
Up to this point, all the SELECT features we have looked at have been shown in
the context of retrieving data from a single table. The time has come to look at how
the SQL SELECT command can integrate data from two or more tables or ''join''
them. There are two specifications to make in the SELECT statement to make a
join work. One is that the tables to be joined must be listed in the FROM clause.
The other is that the join attributes in the tables being joined must be declared and
matched to each other in the WHERE clause. And there is one more point. Since two
or more tables are involved in a SELECT statement that involves a join, there is the
possibility that the same attribute name can appear in more than one of the tables.
When this happens, these attribute names must be ''qualified'' with a table name
when used in the SELECT statement. All of this is best illustrated in an example.
Consider the following query, which we discussed earlier in this topic:
''Find the name of the salesperson responsible for Customer Number 1525.''
The SELECT statement to satisfy this query is:
SELECT SPNAME
FROM SALESPERSON, CUSTOMER
WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM
AND CUSTNUM=1525;
and the result is:
SPNAME
Carlyle
Let's take a careful look at this last SELECT statement. Notice that the two
tables involved in the join, SALESPERSON and CUSTOMER, are listed in the
FROM clause. Also notice that the first line of the WHERE clause:
SALESPERSON.SPNUM = CUSTOMER.SPNUM
links the two join attributes: the SPNUM attribute of the SALESPERSON table
(SALESPERSON.SPNUM) and the SPNUM attribute of the CUSTOMER table
(CUSTOMER.SPNUM). The notational device of having the table name ''.'' the
attribute name is known as ''qualifying'' the attribute name. As we said earlier,
this qualification is necessary when the same attribute name is used in two or more
tables in a SELECT statement. By the way, notice in the SELECT statement that