Example: Querying a three-table database

Table: SALESPERSON

Name	Age	Salary
Abe	60	120000.00
Betty	30	140000.00
Charles	34	80000.00
David	29	65000.00
Edgar	34	90000.00

Table: PURCHASEORDER

Number CustName SalespersonName Amount

100 ABC Abe 560

200 DEF Charles 1800

300 ABC Betty 480

400 XYZ Charles 2500

500 HIJ David 6000

600 ABC Abe 700

Table: CUSTOMER

Name	City	Industry
ABC	Anaheim	B
DEF	Irvine	F
HIJ	Memphis	B
XYZ	Memphis	B

Query example (with SQL):

Who is the oldest (i.e., "maximum age") salesperson?

    SELECT Name
    FROM SALESPERSON
    WHERE Age = MAX(Age)

Subquery example:

Find the age of salespersons who have a purchase order with a customer in Memphis?

SELECT Age
FROM SALESPERSON
WHERE Name IN
       (SELECT SalespersonName
       FROM PURCHASEORDER
       WHERE CustName IN
             (SELECT Name
             FROM CUSTOMER
             WHERE City = 'Memphis'))

Join example:

Find the age of salespersons who have a purchase order with a customer in Memphis?

SELECT Age
FROM SALESPERSON, PURCHASEORDER, CUSTOMER
WHERE SALESPERSON.Name = PURCHASEORDER.SalespersonName
AND PURCHASEORDER.CustName = CUSTOMER.Name
AND CUSTOMER.City = 'Memphis'

Number	CustName	SalespersonName	Amount
100	ABC	Abe	560
200	DEF	Charles	1800
300	ABC	Betty	480
400	XYZ	Charles	2500
500	HIJ	David	6000
600	ABC	Abe	700