HP 3000 Manuals HP 3000 Manuals
The WHERE Clause
The WHERE clause is used to control the selection of rows based on one or
more search conditions. Any kind of expression can be contained in a
WHERE clause except one with an aggregate function or one containing a
LONG data type.
Suppose you want to see information related to a particular part number
in the SupplyPrice table. You specify the part number in the WHERE
clause to retrieve the qualifying rows.
Character data must always be enclosed in single quotes. This is not
necessary for numeric data types. Because the PartNumber column is
defined as character data type, it is enclosed in quotes in the WHERE
clause.
In the example below, the search condition is a specific part number.
__________________________________________________________________________________
| |
| isql=> SELECT * FROM PurchDB.SupplyPrice |
| > WHERE PartNumber='1123-P-01'; |
| |
| SELECT * FROM PurchDB.SupplyPrice WHERE PartNumber='1123-P- |
| 01'; |
| ----------------+------------+----------------+------------------+---------|
| 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| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| ---------------------------------------------------------------------------|
| Number of rows selected is 6 |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________
Using Search Conditions
A search condition specifies criteria for choosing rows. Search
conditions can be used for two purposes:
* In the WHERE clause, to determine rows to retrieve for further
processing.
* In the HAVING clause, to specify a test to apply to each group of
rows producing the GROUP BY clause. The GROUP BY and HAVING clauses
are discussed later in this appendix.
Examples of search conditions follow this list of predicates:
* Between Predicate determines whether an expression is within a
certain range of values.
* Comparison Predicate compares two expressions. The comparison
operators are:
= equals
<> not equal
> greater than
>= greater than or equal
< less than
<= less than or equal
* Exists Predicate determines whether a subquery returns a row or rows
that satisfy some condition. A subquery is a query nested within a
query.
* In Predicate compares an expression with a specified set of values or
a set of values derived by the use of a sub-query.
* Like Predicate determines whether an expression contains a particular
character string pattern. Only expressions of a CHAR or VARCHAR data
type can be used in a LIKE predicate. A pattern in a LIKE predicate
can consist of one or more characters, an underscore, or percent
sign. The underscore is a wild card representing a single character;
the percent sign is a wild card representing a string of zero or more
characters.
* Null Predicate determines whether a value is NULL. Precede NULL with
the keyword IS.
* Quantified Predicate determines whether an expression bears a
particular relationship to a specified set.
* (Search Condition) is one of the above predicates, enclosed in
parentheses.
Here are some examples of commonly used predicates.
To find the part numbers with a unit price in the range of $100.00 to
$300.00, use the BETWEEN predicate:
___________________________________________________
| |
| isql=> SELECT UnitPrice |
| > FROM PurchDB.SupplyPrice |
| > WHERE UnitPrice BETWEEN 100.00 AND 300.00;|
___________________________________________________
To find the part numbers that begin with the pattern 1123, use the LIKE
predicate:
_______________________________________
| |
| isql=> SELECT PartNumber |
| > FROM PurchDB.SupplyPrice |
| > WHERE PartNumber LIKE '1123%';|
_______________________________________
To determine the vendor part numbers that do not have discount quantities
associated with them, use the NULL predicate:
________________________________________________
| |
| isql=> SELECT VendPartNumber, DiscountQty|
| > FROM PurchDB.SupplyPrice |
| > WHERE DiscountQty IS NULL; |
________________________________________________