HP 3000 Manuals HP 3000 Manuals
The Select List
The select list tells ALLBASE/SQL which columns to retrieve and specifies
the order of the columns to be displayed.
Selecting All Columns
To select all the columns from a table, use an asterisk (*). An example
of a simple SELECT command is:
isql=> SELECT * FROM PurchDB.SupplyPrice;
This query (SELECT command) retrieves all the columns from the
SupplyPrice table. PurchDB is the owner of the SupplyPrice table.
The result of the SELECT command is called a query result and looks like
this:
__________________________________________________________________________________
| |
| SELECT * FROM PurchDB.SupplyPrice; |
| ----------------+------------+----------------+------------------+---------|
| PARTNUMBER |VENDORNUMBER|VENDPARTNUMBER |UNITPRICE|DELIVERYD |
| ----------------+------------+----------------+------------------+---------|
| 1123-P-01 | 9002|1110 |450.00 | |
| 1123-P-01 | 9003|90005 |475.00 | |
| 1123-P-01 | 9007|35001 |550.00 | |
| 1123-P-01 | 9008|750001 |475.00 | |
| 1123-P-01 | 9009|19101 |500.00 | |
| 1123-P-01 | 9012|71705 |525.00 | |
| 1133-P-01 | 9002|1115 |180.00 | |
| 1133-P-01 | 9003|90015 |200.00 | |
| 1133-P-01 | 9007|35011 |220.00 | |
| 1133-P-01 | 9009|29201 |195.00 | |
| 1143-P-01 | 9004|10175 |180.00 | |
| 1143-P-01 | 9007|35101 |185.00 | |
| 1143-P-01 | 9008|750101 |175.00 | |
| 1143-P-01 | 9009|39201 |180.00 | |
| 1153-P-01 | 9002|1113 |210.00 | |
| 1153-P-01 | 9003|90035 |220.00 | |
| ---------------------------------------------------------------------------|
| First 16 rows have been selected. |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > right |
__________________________________________________________________________________
All the information for the above query result does not fit on the
screen. To see the other columns to the right, type r or right, as shown
in the example.
When you execute the SELECT command, the query result goes to a temporary
file called isqlout. If you want the query result in a different file,
use the ISQL SET command to change the name before you execute the SELECT
command:
isql=> SET OUTPUT filename;
Selecting Specific Columns
In the example below, the select command uses an expression that
comprises the column names PartNumber and VendPartNumber. Use commas to
separate the column names.
__________________________________________________________________________________
| |
| isql=> SELECT PartNumber, VendPartNumber |
| > FROM PurchDB.SupplyPrice; |
| |
| SELECT PartNumber, VendPartNumber FROM PurchDB.SupplyPrice; |
| ----------------+---------------- |
| PARTNUMBER |VENDPARTNUMBER |
| ----------------+---------------- |
| 1123-P-01 |1110 |
| 1123-P-01 |90005 |
| 1123-P-01 |35001 |
| 1123-P-01 |750001 |
| 1123-P-01 |19101 |
| 1123-P-01 |71705 |
| 1133-P-01 |1115 |
| 1133-P-01 |90015 |
| 1133-P-01 |35011 |
| 1133-P-01 |29201 |
| 1143-P-01 |10175 |
| 1143-P-01 |35101 |
| 1143-P-01 |750101 |
| 1143-P-01 |39201 |
| 1153-P-01 |1113 |
| 1153-P-01 |90035 |
| ---------------------------------------------------------------------------|
| First 16 rows have been selected. |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________
Using Constants in the Select List
A constant can be used in the following ways:
* As a numeric value to perform a calculation.
* As a character value to describe a row.
Either way, a column is added to the query result. The column is not
part of the table in the database.
When you use a constant to perform a calculation, ALLBASE/SQL creates a
column called (EXPR) in the query result. EXPR is an abbreviation for
expression.
Suppose you want to order three of every part in the SupplyPrice table
and you needed a list of the total price for each part. Your SELECT
command consists of the UnitPrice column, the arithmetic multiplication
operator (*) and the constant (3). The query result looks like this:
__________________________________________________________________________________
| |
| isql=> SELECT UnitPrice * 3 |
| > FROM PurchDB.SupplyPrice; |
| |
| SELECT UnitPrice * 3 FROM PurchDB.SupplyPrice; |
| ------------------ |
| (EXPR) |
| ------------------ |
| 1350.00 |
| 1425.00 |
| 1650.00 |
| 1425.00 |
| 1500.00 |
| 1575.00 |
| 540.00 |
| 600.00 |
| 660.00 |
| 585.00 |
| 540.00 |
| 555.00 |
| 525.00 |
| 540.00 |
| 630.00 |
| 660.00 |
| ---------------------------------------------------------------------------|
| First 16 rows have been selected. |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________
You can select a constant in place of a column. A constant results in a
column called (CONST) in a query result. You can use string constants to
make the output more meaningful. Suppose you want a comment stating
"preferred vendor" for vendors who deliver parts in fewer than 15 days
and allow a discount on an ordered quantity of less than 10. The example
below displays that comment in the (CONST) column. The character string
must be enclosed in single quotes.
__________________________________________________________________________________
| |
| isql=> SELECT VendorNumber, 'preferred vendor' |
| > FROM PurchDB.SupplyPrice |
| > WHERE DeliveryDays < 15 |
| > AND DiscountQty < 10; |
| |
| SELECT VendorNumber, 'preferred vendor' FROM |
| PurchDB.SupplyPrice WHERE Delive... |
| ------------+-------------- |
| VENDORNUMBER|(CONST) |
| ------------+-------------- |
| 9014|preferred vendor |
| 9007|preferred vendor |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| ---------------------------------------------------------------------------|
| Number of rows selected is 2 |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________
Using the DISTINCT Option in the Select List
The DISTINCT option eliminates duplicate rows. For example, if you want
to see a list of parts without any part number appearing more than once,
use the DISTINCT option as shown below.
__________________________________________________________________________________
| |
| isql=> SELECT DISTINCT PartNumber |
| > FROM PurchDB.SupplyPrice; |
| |
| SELECT DISTINCT PartNumber FROM PurchDB.SupplyPrice; |
| ---------------- |
| PARTNUMBER |
| ---------------- |
| 1123-P-01 |
| 1133-P-01 |
| 1143-P-01 |
| 1153-P-01 |
| 1223-MU-01 |
| 1233-MU-01 |
| 1243-MU-01 |
| 1323-D-01 |
| 1333-D-01 |
| 1343-D-01 |
| 1353-D-01 |
| 1423-M-01 |
| 1433-M-01 |
| 1523-K-01 |
| 1623-TD-01 |
| 1723-AD-01 |
| ---------------------------------------------------------------------------|
| First 16 rows have been selected. |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________
Using Aggregate Functions in the Select List
An aggregate can be used with all the columns, an expression, or the
DISTINCT option.
You can use DISTINCT with COUNT, MIN, MAX, AVG, or SUM. However, MAX and
MIN DISTINCT are not meaningful because the same maximum or minimum value
is returned regardless of duplicate rows. Only one column in a select
list can use the DISTINCT option with an aggregate function.
The AVG and SUM functions can only be used with numeric data types and
intervals, while COUNT, MAX, and MIN can be used with columns of any data
type, including character and date/time data types.
Here are some examples using aggregate functions in the select list.
Suppose you want to know the price of the most expensive part in the
SupplyPrice table. The following example shows an aggregate function
used with a column name.
_____________________________________
| |
| isql=> SELECT MAX (UnitPrice) |
| > FROM PurchDB.SupplyPrice; |
_____________________________________
If you want to determine the effect of a 5 percent increase in prices by
a particular vendor, you can use the query below. This example shows an
aggregate function with an expression. (Remember, an expression is a
value obtained from a column or columns in a table.)
_________________________________________
| |
| isql=> SELECT AVG (1.05*UnitPrice)|
| > FROM PurchDB.SupplyPrice |
| > WHERE VendorNumber=35001; |
_________________________________________
Suppose you want to know how many different parts are in the SupplyPrice
table. This example uses the aggregate function COUNT to count the
number of rows in the PartNumber column. The DISTINCT option eliminates
rows with duplicate values.
________________________________________________
| |
| isql=> SELECT COUNT (DISTINCT PartNumber)|
| > FROM PurchDB.SupplyPrice; |
________________________________________________