You can use expressions with functions. These functions can be applied to the data in the results table or specified in search conditions.
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.
Function Descriptions in the Reference Manual |
Examples of this Function Group |
---|---|
TRUNC, SIGN, ABS |
|
SIN, ATAN, DEGREES |
|
SUBSTR, UPPER, MAPCHAR |
|
SUBDATE, DAYOFMONTH, DAYNAME |
|
ADDTIME, TIMEDIFF, MAKETIME |
|
DAY, TIMESTAMP, DATE |
|
VALUE, LEAST, CASE functions |
|
NUM, HEXTOROW, CHAR |
|
Call of a database function defined using CREATE FUNCTION |
See also: SQL Reference Manual, Function (dbfunction_call)
Most of these functions are self-explanatory. For this reason, examples of just a few selected functions will be given here.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
You can use the string functions to change the display of character strings.
SELECT cno, name & ', ' & firstname name
FROM hotel.customer
WHERE cno = 3000
First and last names are linked to one another.
Result
CNO |
NAME |
3000 |
Porter, Jenny |
See also: SQL Reference Manual, Concatenation (concatenation)
SELECT cno, SUBSTR(firstname,1,1) & '. ' & name name
FROM hotel.customer
WHERE cno = 3000
First and last names are linked to one another; the first name is displayed in initials only.
Result
CNO |
NAME |
3000 |
J. Porter |
See also: SQL Reference Manual, SUBSTR(x,a,b)
Date functions are applied to date and timestamp values or return a date or timestamp value as a result.
SELECT rno, arrival, DAYNAME(arrival) arrival_day
FROM hotel.reservation
WHERE rno < 150
Displaying the day of arrival
Result
RNO |
ARRIVAL |
ARRIVAL_DAY |
100 |
2004-11-13 |
Saturday |
110 |
2004-12-24 |
Friday |
120 |
2004-11-14 |
Sunday |
130 |
2005-02-01 |
Tuesday |
140 |
2004-04-12 |
Monday |
See also: SQL Reference Manual, DAYNAME/MONTHNAME(t)
You can use extraction functions to display parts of date, time, or timestamp values or to create date, time, or timestamp values.
SELECT name, 675456 number_of_days, DATE(675456) counted_date
FROM hotel.city
WHERE zip = '12203'
Displaying when 675456 days after 31.12.0000 have passed
Result
NAME |
NUMBER_OF_DAYS |
COUNTED_DATE |
Albany |
675456 |
1850-05-03 |
See also: SQL Reference Manual, DATE(a)
The general CASE function analyzes a sequence of search conditions and delivers a result for each search condition.
SELECT hno,
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 70 THEN 'Very cheap'
WHEN price >= 70 AND price < 100 THEN 'Normal price'
ELSE 'Expensive hotel'
END price_range
FROM hotel.room
WHERE hno < 100 AND type = 'single'
Estimating the price classes of single rooms in a selection of hotels
Result
HNO |
PRICE_RANGE |
10 |
Expensive hotel |
20 |
Normal price |
30 |
Very cheap |
40 |
Normal price |
50 |
Expensive hotel |
60 |
Expensive hotel |
70 |
Expensive hotel |
80 |
Normal price |
90 |
Normal price |
See also: SQL Reference Manual, General CASE Function (searched_case_function)
The simple CASE function compares an expression with a sequence of simple expressions to determine in each case a result expression.
SELECT hno,
CASE price
WHEN NULL THEN 'Not yet priced'
WHEN 70 THEN 'Very cheap'
WHEN 90 THEN 'Cheap'
WHEN 100 THEN 'Normal hotel'
WHEN 115 THEN 'Expensive hotel'
WHEN 135 THEN 'Very expensive hotel'
ELSE 'Not yet in price_range list'
END price_range
FROM hotel.room
WHERE hno < 100 AND type = 'single'
Comparing the prices of a selection of hotels with a rating scale for single room prices
Result
HNO |
PRICE_RANGE |
10 |
Very expensive hotel |
20 |
Very cheap |
30 |
Not yet in price_range list |
40 |
Not yet in price_range list |
50 |
Not yet in price_range list |
60 |
Not yet in price_range list |
70 |
Expensive hotel |
80 |
Cheap |
90 |
Cheap |
See also: SQL Reference Manual, Simple CASE Function (simple_case_function)
Before you can execute this example, you have to create the database function avgprice(zip), as described in Database Functions.
SELECT hno, avgprice(zip) avgprice
FROM hotel.hotel
WHERE hno = 40
Result
HNO |
AVGPRICE |
40 |
87.5 |
See also: SQL Reference Manual, Database Function Call (dbfunction_call)
More examples for Data Query