A. SQL Reverse Lookup Reference

1

Data retrieval

Retrieve data by specifying a range.

BETWEEN predicate

2

Retrieve data that matches any of multiple values.

IN predicate

3

Retrieve data that contains a specific character string.

LIKE predicate

4

Retrieve data by using a regular expression

LIKE_REGEX predicate

5

Retrieve null-valued data.

NULL predicate

6

Eliminate duplication in the retrieval results.

SELECT DISTINCT

7

Sort retrieval results in ascending or descending order.

ORDER BY clause

8

Specify the maximum number of rows in the retrieval results.

LIMIT clause

9

Re-use the same derived table within a SELECT statement.

WITH clause

10

Change a column name in the retrieval results.

AS clause

11

Retrieve by specifying multiple branch conditions.

CASE expression

12

Retrieve by joining multiple tables.

Joined tables

13

Perform a subquery.

Subquery

14

EXISTS predicate

15

IN predicate

16

Comparison predicate

17

Quantified predicate

18

Create the union of query results from multiple tables.

UNION ALL

UNION DISTINCT

19

Data deletion

Delete all the rows in a base table.

TRUNCATE TABLE statement

20

Delete all the rows in a chunk in a base table.

PURGE CHUNK statement

21

Data aggregation

Determine the sum of retrieved values.

General set function SUM

22

Determine the maximum value.

General set function MAX

23

Determine the minimum value.

General set function MIN

24

Determine the average of retrieved values.

General set function AVG

25

Determine the row count (number of results)

General set function COUNT

26

Set function COUNT(*)

27

Determine the standard deviation of a population.

General set function STDDEV_POP

28

Determine the standard deviation of a sample.

General set function STDDEV_SAMP

29

Determine the variance of a population.

General set function VAR_POP

30

Determine the variance of a sample.

General set function VAR_SAMP

31

Determine the median of an ordered set of values.

Inverse distribution function MEDIAN

32

Determine the percentile of an ordered set of values.

Inverse distribution function PERCENTILE_CONT

33

Inverse distribution function PERCENTILE_DISC

34

Concatenate a series of ordered values and insert a separator character string between the values to obtain a string.

LISTAGG set function

35

Create array data with the values compiled by the value expression as array elements, starting from the top.

ARRAY_AGG set function

36

Set a range in which to aggregate data.

Window functions

37

Aggregate the data into groups.

GROUP BY clause

38

HAVING clause

39

Character strings

Check whether the target data contains character strings that meet the search condition expression.

Scalar function CONTAINS

40

Concatenate two character string data items.

Scalar function CONCAT

41

Concatenation operations

42

Remove specific characters from character string data.

Scalar function TRIM

43

Scalar function LTRIM

44

Scalar function RTRIM

45

Extract a substring from character string data.

Scalar function SUBSTR

46

Scalar function LEFT

47

Scalar function RIGHT

48

Pad the beginning or end of character string data with any specified character string.

Scalar function LPAD

49

Scalar function RPAD

50

Replace any character string in the target data.

Scalar function REPLACE

51

Replace any character in character string data.

Scalar function TRANSLATE

52

Determine the number of characters in character string data.

Scalar function LENGTH

53

Search the target data for a character string and return the starting position of the string.

Scalar function INSTR

54

Convert uppercase letters to lowercase.

Scalar function LOWER

55

Convert lowercase letters to uppercase.

Scalar function UPPER

56

Binary data

Concatenate two binary data items.

Scalar function CONCAT

57

Extract a substring from binary data.

Scalar function SUBSTRB

58

Determine the value resulting from shifting the bits of a binary data value to the left.

Scalar function BITLSHIFT

59

Determine the value resulting from shifting the bits of a binary data value to the right.

Scalar function BITRSHIFT

60

Determine the bitwise logical AND of two binary data items.

Scalar function BITAND

61

Determine the bitwise inclusive OR of two binary data items.

Scalar function BITOR

62

Determine the bitwise logical NOT of a binary data item.

Scalar function BITNOT

63

Determine the bitwise exclusive OR of two binary data items.

Scalar function BITXOR

64

Convert binary data to a binary string representation (character string data consisting of 0 and 1).

Scalar function BIN

65

Convert binary data to a hex string representation (character string data consisting of 0 to 9, and A to F).

Scalar function HEX

66

Numerical calculations

Determine the remainder after a division.

Scalar function MOD

67

Determine the absolute value.

Scalar function ABS

68

Determine the square root.

Scalar function SQRT

69

Determine the sign of the data (positive, negative, or 0).

Scalar function SIGN

70

Determine pseudorandom numbers that follow a uniform distribution and are greater than or equal to the value specified for the minimum value and less than the value specified for the maximum value.^#

Scalar function RANDOM

71

Scalar function RANDOMCURSOR

72

Scalar function RANDOMROW

73

Determine pseudorandom numbers that follow a normal distribution with an average μ, and a standard deviation σ.

Scalar function RANDOM_NORMAL

74

Rounding

Round off a numeric value.

Scalar function ROUND

75

Truncate a numeric value.

Scalar function TRUNC

76

Determine the greatest integer that is equal to or less than the specified numeric value.

Scalar function FLOOR

77

Determine the smallest integer that is equal to or greater than the specified numeric value.

Scalar function CEIL

78

Exponent and logarithm

Determine a power of the specified data.

Scalar function POWER

79

Determine the logarithm of the specified antilogarithm and base.

Scalar function LOG

80

Determine the natural logarithm.

Scalar function LN

81

Determine a power of the base of the natural logarithm.

Scalar function EXP

82

Trigonometric functions

Determine the sine (SIN trigonometric function).

Scalar function SIN

83

Determine the cosine (COS trigonometric function).

Scalar function COS

84

Determine the tangent (TAN trigonometric function).

Scalar function TAN

85

Determine the inverse sine (inverse trigonometric function).

Scalar function ASIN

86

Determine the inverse cosine (inverse trigonometric function).

Scalar function ACOS

87

Determine the inverse tangent (inverse trigonometric function).

Scalar function ATAN

88

Scalar function ATAN2

89

Determine the hyperbolic sine.

Scalar function SINH

90

Determine the hyperbolic cosine.

Scalar function COSH

91

Determine the hyperbolic tangent.

Scalar function TANH

92

Convert an angle from radians to degrees.

Scalar function DEGREES

93

Convert an angle from degrees to radians.

Scalar function RADIANS

94

Determine the value of .

Scalar function PI

95

Date and time

Extract a portion of a date or time (for example, extract only the month).

Scalar function EXTRACT

96

Given a date, determine the ordinal number of the date in the year.

Scalar function DAYOFYEAR

97

Given a date, determine what day of week it falls on as an ordinal number from the first day in the week.

Scalar function DAYOFWEEK

98

Determine the date of the last day of the specified month.

Scalar function LASTDAY

99

Determine the difference between the start date and time and the end date and time.

Scalar function DATEDIFF

100

Scalar function TIMESTAMPDIFF

101

Add a date and time to the datetime data.

Scalar function TIMESTAMPADD

102

Determine a person's age on a reference date given their birth date.

Scalar function GETAGE

103

Round a date by the year, month, day, hour, or second.

Scalar function ROUND

104

Truncate a date by the year, month, day, hour, or second.

Scalar function TRUNC

105

Determine the current date.

Datetime information acquisition function CURRENT_DATE

106

Determine the current time.

Datetime information acquisition function CURRENT_TIME

107

Determine the current data and time stamp.

Datetime information acquisition function CURRENT_TIMESTAMP

108

Perform operations on datetime data.

Datetime operations

109

Labeled duration

110

Null value

Determine the first non-null value among the specified data.

Scalar function COALESCE

111

Scalar function ISNULL

112

Scalar function NVL

113

Data comparison

Determine whether two data items are equal.

Scalar function NULLIF

114

Compare the values in the target data and the comparison data one at a time, and if there is a match, return the corresponding return value.

Scalar function DECODE

115

Compare the values in the target data and in the comparison data one at a time, and, if any value in the target data is less than the value in the comparison data, return the corresponding return value.

Scalar function LTDECODE

116

Determine the greatest value.

Scalar function GREATEST

117

Determine the smallest value.

Scalar function LEAST

118

Data types

Convert the data type.

Scalar function CAST

119

Scalar function CONVERT

120

Data information acquisition

Determine the number of bytes in the target data.

Scalar function LENGTHB

121

Determine the character code of the first character of character string data.

Scalar function ASCII

122

Determine the character corresponding to numeric value character code in the target data.

Scalar function CHR

123

Array data information acquisition

Determine the maximum number of elements in the array data.

Scalar function ARRAY_MAX_CARDINALITY

124

Determine the number of array elements in the array data.

Scalar function CARDINALITY

125

User information

Determine the authorization identifier of the currently executing HADB user.

User information acquisition function CURRENT_USER

126

Logical value determination

Determine the logical value of a value expression.

Logical predicate