ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 517
SQL Statements S - Z
SELECT
cursor, you can use the BULK option to retrieve multiple rows.
BULK SELECT *
INTO :Items, :Start, :NumRow
FROM PurchDB.Inventory
5. UNION Option
Retrieves all rows from two Parts tables into a single query result ordered by
PartNumber. PartNumber and PartValue are comparable; SalesPrice and Price are
comparable.
SELECT PartNumber, SalesPrice
FROM P1988.Parts
UNION
SELECT PartValue, Price
FROM P1989.Parts
ORDER BY PartNumber
6. Nested query or subquery
Obtain a list of customer orders whose totals are higher than the largest order of 1988.
SELECT OrderNumber, SUM(PurchasePrice)
FROM PurchDB.OrderItems
GROUP BY OrderNumber
HAVING SUM(PurchasePrice) > (SELECT MAX(PurchasePrice)
FROM FY1988.Orders)
Get vendor numbers for all vendors located in the same city as vendor number 9005.
SELECT VendorNumber
FROM PurchDB.Vendors
WHERE VendorCity = (SELECT VendorCity
FROM PurchDB.Vendors
WHERE VendorNumber = '9005')
Get supplier names for suppliers who provide at least one red part.
SELECT SNAME
FROM S
WHERE SNO IN ( SELECT SNO
FROM SP
WHERE EXISTS (SELECT PNO
FROM P
WHERE P.PNO = SP.PNO
AND COLOR = 'RED' ))
Get supplier number for suppliers who supply the most parts.
SELECT SNO
FROM SP
GROUP BY SNO
HAVING COUNT(DISTINCT PNO) >= ALL ( SELECT COUNT(DISTINCT PNO)
FROM SP
GROUP BY SNO )