Java Reference
In-Depth Information
The information in a practical database is usually distributed across several tables,
each of which contains sets of logically related data. A typical example of such a
database is shown in Tables
1-2
and 1-4 through 1-6.
When a customer places an order, an entry is made in the Orders Table, assigning an
order number and containing the Customer number and the order date. Then entries
are added to the Ordered_Items Table, recording the order number, item number,
and quantity.
One of the most powerful features of SQL is its ability to combine data from several
tables by using JOINS. For example, the following SQL statement performs a JOIN
on the ORDERS, CUSTOMERS, ORDERED_ITEMS and INVENTORY Tables to
total the purchases each customer makes:
SELECT LAST_NAME + ',' + FIRST_NAME AS NAME,
SUM(oi.QTY * COST * 1.6) AS PURCHASES
FROM ORDERS o, CUSTOMERS c, ORDERED_ITEMS oi,
INVENTORY i
WHERE O.CUSTOMER_NUMBER = C.CUSTOMER_NUMBER AND
O.ORDER_NUMBER = OI.ORDER_NUMBER AND
OI.ITEM_NUMBER = I.ITEM_NUMBER
GROUP BY LAST_NAME + ',' + FIRST_NAME;
Here are the results of this query:
NAME
PURCHASES
Adams,Kay
11.14
Corleone,Francis
11.87
Corleone,Fredo
22.69
Corleone,Vito
21.52
This example also illustrates the use of
aliases
both for column names and for tables,
as well as SQL's ability to perform arithmetic and String computations. Here the alias
NAME has been assigned to the concatenation of the Last_Name and First_Name
fields, and the alias PURCHASES to the calculated product of quantity, cost, and the
1.6 markup through the use of the expression:
SELECT LAST_NAME + ',' + FIRST_NAME AS NAME,
SUM(oi.QTY * COST * 1.6) AS PURCHASES
The use of aliases and SQL's mathematical capabilities are discussed thoroughly in
subsequent chapters.
