ALLBASE/SQL Reference Manual (36216-90216)

516 Chapter12
SQL Statements S - Z
SELECT
SELECT PartNumber, AVG(UnitPrice)
FROM PurchDB.SupplyPrice
GROUP BY PartNumber
The query result is the same as the query result for the previous SELECT statement,
except it contains rows only for parts that can be delivered in fewer than 20 days.
SELECT PartNumber, AVG(UnitPrice)
FROM PurchDB.SupplyPrice
GROUP BY PartNumber
HAVING MAX(DeliveryDays) < 20
3. Joining
This join returns names and locations of California suppliers. Rows are returned in
ascending PartNumber order; rows containing duplicate PartNumbers are returned in
ascending VendorName order. The FROM clause defines two correlation names (v and
s), which are used in both the select list and the WHERE clause. VendorNumber is the
only common column between Vendors and SupplyPrice.
SELECT PartNumber, VendorName, s.VendorNumber, VendorCity
FROM PurchDB.SupplyPrice s, PurchDB.Vendors v
WHERE s.VendorNumber = v.VendorNumber
AND VendorState = 'CA'
ORDER BY PartNumber, VendorName
This query is identical to the query immediately above except that it uses the explicit
JOIN syntax.
SELECT PartNumber, VendorName, VendorNumber, VendorCity
FROM PurchDB.SupplyPrice
NATURAL JOIN PurchDB.Vendors
WHERE VendorState = 'CA'
ORDER BY PartNumber, VendorName
This query joins table PurchDB.Parts to itself in order to determine which parts have
the same sales price as part 1133-P-01.
SELECT q.PartNumber, q.SalesPrice
FROM PurchDB.Parts p, PurchDB.Parts q
WHERE p.SalesPrice = q.SalesPrice
AND p.PartNumber = '1133-P-01'
This query does a left outer join between the Vendors and SupplyPrice tables. Since
every part supplied by a vendor has an entry in the SupplyPrice table, the result first
displays every vendor who supplies a part. The result then displays every vendor who
does not supply any parts.
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.Vendors v
LEFT JOIN Purchdb.SupplyPrice s
ON s.VendorNumber = v.VendorNumber
ORDER BY PartNumber, VendorName
4. BULK SELECT
Programmatically, when you do not need to use the capabilities associated with a