For different application cases, you may need to change how values are represented because the operator used can deal only with a specific representation of values.
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
The following functions are described for this purpose in the SQL reference manual: CHR(a,n), NUM(a), UPPER/LOWER(x), ASCII(x), MAPCHAR(x,n,i), ALPHA(x,n), HEX(a), CHAR(a,t), SOUNDEX(x), VALUE(x,y,...),DECODE(x,y(i),...,z).
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
SELECT * FROM hotel.hotel
WHERE CHR(hno) LIKE '1__'
Displaying the hotel entries whose numbers contain three digits and begin with 1. The hotel number is converted to character values for this purpose.
Result
HNO |
NAME |
ZIP |
ADDRESS |
100 |
Beach |
32018 |
1980 34th St. |
110 |
Atlantik |
33441 |
111 78th St. |
120 |
Long Beach |
90804 |
35 Broadway |
130 |
Star |
90029 |
13 Beechwood Place |
140 |
River Boat |
70112 |
788 MAIN STREET |
150 |
Indian Horse |
92262 |
16 MAIN STREET |
See also: SQL Reference Manual, CHR(a,n)
SELECT * FROM hotel.hotel
WHERE UPPER(name) = 'STAR'
Displaying the hotel name (not case-sensitive)
Result
HNO |
NAME |
ZIP |
ADDRESS |
130 |
Star |
90029 |
13 Beechwood Place |
See also: SQL Reference Manual, UPPER/LOWER(x)
SELECT VALUE(firstname, 'Company') firstname, name
FROM hotel.customer
WHERE firstname IS NULL
Displaying the company name; the NULL value is to be replaced by the name Company
Result
FIRSTNAME |
NAME |
Company |
Datasoft |
Company |
Toolware |
See also: SQL Reference Manual, VALUE(x,y,...)
SELECT hno, price, DECODE(type, 'single', 1, 'double', 2, 'suite', 3) code
FROM hotel.room
WHERE hno < 60
Displaying the hotel rooms with another name for the room types
Result
HNO |
PRICE |
CODE |
10 |
200 |
2 |
10 |
135 |
1 |
20 |
100 |
2 |
20 |
70 |
1 |
30 |
80 |
2 |
30 |
45 |
1 |
40 |
140 |
2 |
40 |
85 |
1 |
50 |
180 |
2 |
50 |
105 |
1 |
50 |
500 |
3 |
See also: SQL Reference Manual, DECODE(x,y(i),...,z)
More examples for Data Query