SQL basics- complete reference guide - part8 SQL System Commands

Part8: SQL System Commands Reference guide

TOPIC	TEXT	SYNTAX	Example
ARRAY_GET	Returns one element of an array.	ARRAY_GET(arrayExpression, indexExpression)
ARRAY_LENGTH	Returns the length of an array.	ARRAY_GET(arrayExpression)
AUTOCOMMIT	Returns true if auto commit is switched on for this session.	AUTOCOMMIT()
CANCEL_SESSION	Cancels the currently executing statement of another session.	CANCEL_SESSION(sessionInt)
CASEWHEN Function	Returns 'a' if the boolean expression is true, otherwise 'b'.	CASEWHEN(boolean, aValue, bValue)
CAST	Converts a value to another data type.	CAST(value AS dataType)
COALESCE	Returns the first value that is not null.	COALESCE(aValue, bValue [,...])
CONVERT	Converts a value to another data type.	CONVERT(value, dataType)
CURRVAL	Returns the current (last) value of the sequence, independent of the session.	CURRVAL( [ schemaName, ] sequenceString )
CSVREAD	Returns the result set of reading the CSV (comma separated values) file.	CSVREAD(fileNameString [, columnsString [, csvOptions ] ] )
CSVWRITE	Writes a CSV (comma separated values).	CSVWRITE ( fileNameString, queryString [, csvOptions [, lineSepString] ] )
DATABASE	Returns the name of the database.	DATABASE()
DATABASE_PATH	Returns the directory of the database files and the database name, if it is file based.	DATABASE_PATH()
FILE_READ	Returns the contents of a file.	FILE_READ(fileNameString [,encodingString])
GREATEST	Returns the largest value that is not NULL, or NULL if all values are NULL.	GREATEST(aValue, bValue [,...])
IDENTITY	Returns the last inserted identity value for this session.	IDENTITY()
IFNULL	Returns the value of 'a' if it is not null, otherwise 'b'.	IFNULL(aValue, bValue)
LEAST	Returns the smallest value that is not NULL, or NULL if all values are NULL.	LEAST(aValue, bValue [,...])
LOCK_MODE	Returns the current lock mode.	LOCK_MODE()
LOCK_TIMEOUT	Returns the lock timeout of the current session (in milliseconds).	LOCK_TIMEOUT()
LINK_SCHEMA	Creates table links for all tables in a schema.	LINK_SCHEMA(targetSchemaString, driverString, urlString, userString, passwordString, sourceSchemaString)
MEMORY_FREE	Returns the free memory in KB (where 1024 bytes is a KB).	MEMORY_FREE()
MEMORY_USED	Returns the used memory in KB (where 1024 bytes is a KB).	MEMORY_USED()
NEXTVAL	Returns the next value of the sequence.	NEXTVAL ( [ schemaName, ] sequenceString )
NULLIF	Returns NULL if 'a' is equals to 'b', otherwise 'a'.	NULLIF(aValue, bValue)
READONLY	Returns true if the database is read-only.	READONLY()
ROWNUM	Returns the number of the current row.	ROWNUM()
SCHEMA	Returns the name of the default  schema for this session.	SCHEMA()
SCOPE_IDENTITY	Returns the last inserted identity value for this session for the current scope.	SCOPE_IDENTITY()
SESSION_ID	Returns the unique session id number for the current database connection.	SESSION_ID()
SET	Updates a variable with the given value.	SET(@variableName, value)
TABLE	Returns the result set.	{ TABLE | TABLE_DISTINCT } ( { name dataType = expression } [,...] )
TRANSACTION_ID	Returns the current transaction id for this session.	TRANSACTION_ID()
USER	Returns the name of the current user of this session.	{ USER | CURRENT_USER } ()	Select User();