Database Reference
In-Depth Information
EXAMPLE 21
60
List the number and name of all customers.
PROJECT Customer OVER (CustomerNum, CustomerName)
GIVING Answer
This command creates a new table named Answer that contains the CustomerNum and CustomerName
columns for all the rows in the Customer table.
EXAMPLE 22
List the number and name of all customers with credit limits of $7,500.
This example requires a two-step process. You first use a SELECT command to create a new table that
contains only those customers with credit limits of $7,500. Then, you project the new table to restrict the
result to only the indicated columns.
SELECT Customer WHERE CreditLimit=7500
GIVING Temp
PROJECT Temp OVER (CustomerNum, CustomerName)
GIVING Answer
The first command creates a new table named Temp that contains all the columns from the Customer
table, but only those rows in which the credit limit is $7,500. The second command creates a new table
named Answer that contains all the rows from the Temp table (that is, only customers with credit limits of
$7,500), but only the CustomerNum and CustomerName columns.
JOIN
The join operation is the core operation of relational algebra because it is the command that allows you to
extract data from more than one table. In the most common form of the join, two tables are combined based
on the values in matching columns, creating a new table containing the columns in both tables. Rows in this
new table are the concatenation (combination) of a row from the first table and a row from the second table
that match on the common column (often called the join column). In other words, two tables are joined on
the join column.
For example, suppose you want to join the two tables shown in Figure 2-40 on RepNum (the join
column), creating a new table named Temp.
