|
Introduction |
|
Using SQL Data Sets |
|
|
Local SQL commands and functions
SQL
keywords:
|
A
|
|
|
ABS
|
ADD
|
AFTER
|
ALL
|
ALTER
|
AND
|
ANY
|
AS
|
ASC
|
ASSERT
|
ATAN
|
ATAN2
|
ATN2
|
ATOMIC
|
AUTHORIZATION
|
AUTOINC
|
AVG
|
|
B
|
|
|
BEFORE
|
BEGIN
|
BETWEEN
|
BIGINT
|
BINARY
|
BLOB
|
BLOCK
|
BLOCKSIZE
|
BOOL
|
BOOLEAN
|
BOTH
|
BROUND
|
BY
|
BYTE
|
BYTEARRAY
|
C
|
|
|
CALL
|
CALLED
|
CASCADE
|
CASE
|
CAST
|
CATCH
|
CEIL
|
CEILING
|
CHAR
|
CHAR_LENGTH
|
CHARACTER
|
CHARACTER_LENGTH
|
CHARACTERS
|
CHECK
|
CHR
|
CLOB
|
CLR
|
COALESCE
|
CODEPAGE
|
COLLATE
|
COLLATION
|
COLUMN
|
COMMIT
|
CONSTRAINT
|
CONTAINS
|
COS
|
COUNT
|
CREATE
|
CROSS
|
CURRENT_DATE
|
CURRENT_TIME
|
CURRENT_TIMESTAMP
|
CURRENT_USER
|
D
|
|
|
DATA
|
DATE
|
DATETIME
|
DAY
|
DEC
|
DECIMAL
|
DECLARE
|
DEFAULT
|
DELETE
|
DELETING
|
DESC
|
DESCRIPTION
|
DETERMINISTIC
|
DISTINCT
|
DO
|
DOUBLE
|
DROP
|
DWORD
|
E
|
|
|
EACH
|
ELSE
|
ELSEIF
|
EMPTY
|
ENCRYPT
|
ENCRYPTION
|
END
|
ENGINE
|
EQUIVALENT
|
ERROR_MESSAGE
|
ESCAPE
|
EXCEPT
|
EXISTS
|
EXP
|
EXTENDED
|
EXTERNAL
|
EXTRACT
|
|
F
|
|
|
FALSE
|
FIRST
|
FLOAT
|
FLOOR
|
FOR
|
FOREIGN
|
FROM
|
FULL
|
FUNCTION
|
G
|
|
|
GLOBAL
|
GROUP
|
GROW
|
GROWSIZE
|
GUID
|
|
H
|
|
|
HAVING
|
HOUR
|
|
I
|
|
|
IDENTITY
|
IF
|
IGNORE
|
IMAGE
|
IN
|
INDEX
|
INITIAL
|
INITIALSIZE
|
INNER
|
INOUT
|
INPUT
|
INSERT
|
INSERTING
|
INT
|
INTEGER
|
INTERSECT
|
INTERVAL
|
INTO
|
IS
|
ITERATE
|
|
J
|
|
|
JOIN
|
|
|
K
|
|
|
KANA
|
KEY
|
|
L
|
|
|
LANGUAGE
|
LARGE
|
LARGEINT
|
LAST
|
LASTAUTOINC
|
LEADING
|
LEAVE
|
LEFT
|
LIKE
|
LIST
|
LN
|
LOCAL
|
LOCALE
|
LOCALTIME
|
LOCALTIMESTAMP
|
LOWER
|
|
|
M
|
|
|
MATCH
|
MAX
|
MED
|
MIN
|
MINUTE
|
MOD
|
MODIFIES
|
MONEY
|
MONTH
|
N
|
|
|
NAME
|
NATIONAL
|
NATURAL
|
NCHAR
|
NCLOB
|
NEW
|
NEWGUID
|
NO
|
NONSPACE
|
NOT
|
NSINGLECHAR
|
NULL
|
NULLIF
|
NULLS
|
NULLSTRING
|
NUMERIC
|
NVARCHAR
|
|
O
|
|
|
OBJECT
|
OCTET_LENGTH
|
OCTETS
|
ODD
|
OF
|
OLD
|
ON
|
OR
|
ORD
|
ORDER
|
OUT
|
OUTER
|
P
|
|
|
PARTIAL
|
PASSWORDS
|
PERCENT
|
PI
|
POSITION
|
POWER
|
PRECISION
|
PRIMARY
|
PROCEDURE
|
R
|
|
|
RAND
|
READS
|
REAL
|
RECREV
|
REFERENCES
|
REFERENCING
|
REMOVE
|
REPEAT
|
RESTRICT
|
RETURN
|
RETURNS
|
RIGHT
|
ROLLBACK
|
ROUND
|
ROUTINE
|
ROW
|
ROWSAFFECTED
|
ROWSREAD
|
S
|
|
|
SECOND
|
SELECT
|
SET
|
SHORTINT
|
SHORTSTRING
|
SIGNAL
|
SIMPLE
|
SIN
|
SINGLECHAR
|
SMALLINT
|
SNAPSHOT
|
SOME
|
SORT
|
SQL
|
SQRT
|
START
|
STD
|
STORAGE
|
STRING
|
SUBSTRING
|
SUM
|
SYMBOLS
|
SYSTEM_ROW#
|
|
T
|
|
|
TABLE
|
TEMPORARY
|
TEXT
|
THEN
|
TIME
|
TIMESTAMP
|
TINYINT
|
TO
|
TOP
|
TOSTRING
|
TOSTRINGLEN
|
TRAILING
|
TRANSACTION
|
TRIGGER
|
TRIM
|
TRUE
|
TRY
|
TYPE
|
U
|
|
|
UNION
|
UNIQUE
|
UNKNOWN
|
UNTIL
|
UPDATE
|
UPDATING
|
UPPER
|
USE
|
USER
|
USING
|
|
|
V
|
|
|
VALUES
|
VARCHAR
|
VARYING
|
VIEW
|
|
|
W
|
|
|
WHEN
|
WHERE
|
WHILE
|
WIDTH
|
WITH
|
WORD
|
WORK
|
|
|
Y
|
|
|
YEAR
|
|
|
|
SQL functions:
|
AVG
|
COUNT
|
LIST
|
MAX
|
MED
|
MIN
|
STD
|
SUM
|
|
|
|
Function
|
Example
|
POSITION
|
SELECT
*
FROM courses
WHERE POSITION( 'C' IN courseName ) = 1
|
EXTRACT
|
SELECT
orderID,
EXTRACT( YEAR FROM orderDate ) AS "Year",
EXTRACT( MONTH from orderDate ) AS "Month",
EXTRACT( DAY FROM orderDate ) AS "Day",
orderTotal
FROM orders
|
CHAR_LENGTH
/
CHARACTER_LENGTH
|
SELECT
firstName, lastName
FROM students
WHERE CHAR_LENGTH( lastName ) > CHARACTER_LENGTH( firstName
)
|
ABS
|
ABS( 5
)
|
MOD
|
MOD(
7, 2 )
|
LN
|
LN( 2
)
|
EXP
|
EXP( 2
)
|
POWER
|
POWER(
2, 3 )
|
SQRT
|
SQRT(
4 )
|
FLOOR
|
FLOOR(
5 )
|
CEILING
|
CEILING(
5 )
|
ATAN
|
ATAN(
2.75 )
|
COS
|
COS(
5.25 )
|
SIN
|
SIN(
5.25 )
|
ORD
|
ORD('A')
|
PI
|
|
ROUND
|
ROUND(
7.5 )
|
BROUND
|
BROUND(
7.5 )
|
|
|
Function
|
Example
|
SUBSTRING
|
SUBSTRING(
'Hello World' FROM 1 )
SUBSTRING( 'Hello World' FROM 1 FOR 5 )
|
UPPER
|
UPPER(
'Hello World' )
|
LOWER
|
LOWER(
'Hello World' )
|
TRIM
|
TRIM(
' Meet the Dream Team ' )
TRIM( BOTH FROM ' Meet the Dream Team ' )
TRIM( LEADING 'M' FROM 'Meet the Dream TeaM' )
TRIM( TRAILING 'M' FROM 'Meet the Dream TeaM' )
TRIM( BOTH 'M' FROM 'Meet the Dream TeaM' )
|
CHR
|
SELECT
*
FROM customers
WHERE memo LIKE '%' || CHR( 13 ) || CHR( 10 ) || '%'
|
TOSTRING
|
SELECT
studentName, TOSTRING( studentID ) AS student_code
FROM students
ORDER BY student_code
|
TOSTRINGLEN
|
SELECT
documentID, title, TOSTRINGLEN( content, 100 ) AS hex_content
FROM documents
ORDER BY documentID
|
|
|