Selecting Specific Rows
Rows are selected in the SELECT statement according to the search condition in the WHERE clause. This condition relates column value(s) to expressions.
Comparison Conditions and WHERE
Comparison operators that may be used in the WHERE clause are:
equal to not equal to less than less than or equal to greater than greater than or equal to
Comparisons can be combined in the search condition using the logical operators AND and OR, and reversed using NOT.
Each comparison must be expressed in full; for exampleWHERE PRICE > 800 AND PRICE < 1000
may not be expressed asWHERE PRICE > 800 AND < 1000
Comparing Character Strings
Character strings are compared character by character from left to right.
If strings are of different lengths, the shorter is conceptually padded to the right with blanks before the comparison is made (i.e. character difference takes precedence over length difference).
The collating sequence for characters is an extended ASCII character set as defined by ISO 8859-1, see the Mimer SQL Reference Manual for the exact sequence.
To retrieve the room type, price, and date from which the prices apply for all rooms with hotel code LAP and a cost of under 700:SELECT ROOMTYPE, PRICE, FROM_DATE, TO_DATE FROM ROOM_PRICES WHERE HOTELCODE = 'LAP' AND PRICE < 700;
Comparing Temporal Data
When stating conditions on temporal data in tables, datetime and interval literals can be specified. There are also the pseudo literals CURRENT_DATE, LOCALTIME and LOCALTIMESTAMP which read the server clock and return that value.
If there is more than one occurrence of these pseudo literals in a statement the clock is only read once.
Retrieve guests who requested a wake up call at 6 o'clock today.SELECT ROOMNO FROM WAKE_UP WHERE WAKE_DATE = CURRENT_DATE AND WAKE_TIME = TIME '06:00:00';
Are there any guests scheduled for check in today?SELECT RESERVED_FNAME, RESERVED_LNAME FROM BOOK_GUEST WHERE ARRIVE = CURRENT_DATE;
For an example of interval literals, see Datetime Arithmetic and Functions.
LIKE is used to search for character strings that match a specified pattern.
Patterns in the LIKE condition can be written with 'wildcard' characters (also called 'meta-characters'):
_ (underscore) stands for any single character % stands for any sequence of zero or more characters
Wildcards only have significance in LIKE predicates.
Find all guests at the Hotel Laponia whose names include 'HANSEN':SELECT GUEST_LNAME FROM BOOK_GUEST WHERE GUEST_LNAME LIKE '%HANSEN%' AND HOTELCODE = 'LAP';
Find all guests at the Hotel Laponia whose last names do not include 'HANSEN'.SELECT GUEST FROM BOOK_GUEST WHERE GUEST_LNAME NOT LIKE '%HANSEN%' AND HOTELCODE = 'LAP';
Remember that character strings in Mimer SQL statements are always written within apostrophes (').
A LIKE predicate where the pattern string does not contain any wildcard characters is essentially equivalent to a basic predicate using the '=' operator, except that comparison strings in an '=' comparison are conceptually padded with blanks whereas those in the LIKE comparison are not.
For example:'SKYLINE ' = 'SKYLINE' is true 'SKYLINE ' LIKE 'SKYLINE ' is true 'SKYLINE ' LIKE 'SKYLINE%' is true
but'SKYLINE ' LIKE 'SKYLINE' is false
The LIKE predicate may include an ESCAPE clause defining a character which is used to 'escape' wildcard characters. A wildcard character immediately following an escape character is taken at face value. See the Mimer SQL Reference Manual for more details.
More about Searching for Character Strings
Some other examples of searching for character strings are:
matches any string that contains an upper-case 'P'. matches any four letter character string ending with lower case 'abc'. matches any string ending with 'A%'. matches any four letter string with D and d in the first and third positions respectively. Examples of possible values: Dude, Dads.
IN and Not IN
The operator IN finds the values that are contained in a set of values. The set is given as a comma-separated list enclosed in parentheses.
NOT IN finds values which are not contained in the specified set.
Which hotels are in Stockholm or Copenhagen?SELECT NAME, CITY FROM HOTEL WHERE CITY IN ('STOCKHOLM','COPENHAGEN');
Which hotels are not in Stockholm or Copenhagen?SELECT NAME, CITY FROM HOTEL WHERE CITY NOT IN ('STOCKHOLM','COPENHAGEN');
BETWEEN and NOT BETWEEN
The operators BETWEEN and NOT BETWEEN are used to find values that are within or outside an interval. The interval includes the limits specified in the BETWEEN condition.
Find which room types that have prices in the range 700 to 1000 at hotel LAPONIA:SELECT ROOMTYPE, PRICE FROM ROOM_PRICES WHERE PRICE BETWEEN 700 AND 1000 AND HOTELCODE = 'LAP'
Find the date, charge code and amount for items billed on dates outside the range 1997-08-30 and 1997-09-01 for the reservation number 1371:SELECT ON_DATE, CHARGE_CODE, COST FROM BILL WHERE RESERVATION = 1371 AND ON_DATE NOT BETWEEN TIMESTAMP '1997-08-30 00:00:00' AND TIMESTAMP '1997-09-01 23:59:59';
BETWEEN may also be used for character comparisons. Strings are compared character by character from left to right.
For example:SELECT NAME FROM HOTEL WHERE NAME BETWEEN 'SKYLINE' AND 'WINSTON';
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40