HP 3000 Manuals

ALLBASE/SQL Reference Manual

Using the SELECT Statement 

Use the SELECT statement to compose queries.  The SELECT statement
consists of the following components:

   1.  Select list
   2.  INTO clause
   3.  FROM clause
   4.  WHERE clause
   5.  GROUP BY clause
   6.  HAVING clause
   7.  ORDER BY clause

The select list and FROM clause are required; all other components of
this statement are optional.  The following example does not contain an
INTO clause.  Note the reference numbers identifying the above
components:

                            1
                            |
             _______________|_____________
            |                             |
            |                             |
       SELECT PartNumber, COUNT(VendorNumber)
         FROM PurchDB.SupplyPrice            ---3
        WHERE DeliveryDays < 25              ---4
     GROUP BY PartNumber                     ---5
       HAVING COUNT(VendorNumber) > 2        ---6
     ORDER BY PartNumber                     ---7

The result is presented in the form of a table, called a query result.
The result table (shown next) for this example has two columns:  part
numbers and a count of vendors who supply each part.  The query result
has rows only for parts that can be delivered in fewer than 25 days by
more than two suppliers.  The rows are ordered in ascending order by
PartNumber.

     ----------------+-----------
     PARTNUMBER      |(EXPR)
     ----------------+-----------
     1123-P-01       |          4
     1133-P-01       |          3
     1243-MU-01      |          3
     1323-D-01       |          3
     1353-D-01       |          3
     1433-M-01       |          3

       :

The select list identifies the columns you want in the query result.  In
the above example, the (EXPR) column contains the vendor count specified
as COUNT(VendorNumber) in the select list.  Computations of this kind are
called aggregate functions, which are defined in the "Expressions"
chapter.  The count function counts rows, in this case rows that satisfy
the conditions set up in the SELECT statement clauses.

This example contains no INTO clause because host variables are not being
used.  The INTO clause is used in application programs to identify host
variables for storing the query result.  For more information on host
variables, refer to the appropriate ALLBASE/SQL application programming
guide.

The FROM clause identifies tables and views from which data is to be
retrieved, in this case, PurchDB.SupplyPrice.

The WHERE clause specifies a search condition for screening rows.  Search
conditions are comparisons and other operations you can have ALLBASE/SQL
perform in order to screen rows for your query result.  The "Search
Conditions" chapter defines the ALLBASE/SQL search conditions.  In this
case, the search condition states that rows in the query result must
contain information for parts that can be delivered in fewer than 25
days.

The GROUP BY clause tells ALLBASE/SQL how to group rows before performing
an aggregate function in the select list.  The rows that satisfy the
WHERE clause are grouped.  In this example, the rows are grouped by
PartNumber.  Then ALLBASE/SQL counts the number of vendors that supply
each part.  The result is a vendor count for each part number.

The HAVING clause screens the groups.  In the above example, data for
only groups having a vendor count greater than two becomes part of the
query result.

The ORDER BY clause sorts the query result rows in order by specified
column, in this case, PartNumber.

MPE/iX 5.5 Documentation