Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
The Couchbase APIs support the following operators in the WHERE clause: =, !=, <, <=, >, >=, IS NULL, LIKE, AND, OR, NOT, IN.
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Couchbase supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns array of the non-MISSING values in the group, including NULL values.
column: Any column expression.
Returns new array with value appended.
column: Any column expression.
value: The value to be appended to the array.
Returns new array with the concatenation of the input arrays.
column1: Any column expression.
column2: Any column expression.
Returns new array with distinct elements of input array.
column: Any column expression.
Returns the first non-NULL value in the array, or NULL.
column: Any column expression.
Returns new array with value pre-pended.
column: Any column expression.
value: The value to be pre-pended to the array.
Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.
column: Any column expression.
value: The value to append to the array.
Returns new array with all occurrences of value removed.
column: Any column expression.
value: The value to be removed from the array.
Returns new array with all occurrences of value removed.
column: Any column expression.
value1: The value to be replaced by value2.
value2: The value to replace value1.
integer_n: The maximum number of replacements to be performed.
Returns new array with all elements in reverse order.
column: Any column expression.
Returns new array with elements sorted in N1QL collation order.
column: Any column expression.
Unmarshals the JSON-encoded string into a N1QL value. The empty string is MISSING.
column: Any column expression.
Marshals the N1QL value into a JSON-encoded string. MISSING becomes the empty string.
column: Any column expression.
Number of bytes in an uncompressed JSON encoding of the value. The exact size is implementation-dependent. Always returns an integer, and never MISSING or NULL. Returns 0 for MISSING.
column: Any column expression.
Returns length of the value after evaluating the expression. The exact meaning of length depends on the type of the value: MISSING: MISSING; NULL: NULL; String: The length of the string.; Array: The number of elements in the array.; Object: The number of name/value pairs in the object; Any other value: NULL.
column: Any column expression.
Returns number of name-value pairs in the object.
column: Any column expression.
Returns array containing the attribute names of the object, in N1QL collation order.
column: Any column expression.
Returns array containing the attribute name and value pairs of the object, in N1QL collation order of the names.
column: Any column expression.
Returns array containing the attribute values of the object, in N1QL collation order of the corresponding names.
column: Any column expression.
Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.
column: Any column expression.
Returns true if the array contains value.
column: Any column expression.
value: The value contained within the array.
Returns count of all the non-NULL values in the array, or zero if there are no such values.
column: Any column expression.
Returns the number of elements in the array.
column: Any column expression.
Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.
column: Any column expression.
Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.
column: Any column expression.
Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.
column: Any column expression.
value: The value contained within the array.
Sum of all the non-NULL number values in the array, or zero if there are no such values.
column: Any column expression.
Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns the first non-MISSING value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-NULL, non-MISSING value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.
Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.
string_fmt: The datetime format to return the system clock in.
Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.
column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.
column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.
column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.
column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.
column1: Any column expression.
string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.
Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.
column1: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.
column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.
column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns date that has been converted in a supported format to UNIX milliseconds.
column1: Any column expression.
Returns date that has been converted in a supported format to UNIX milliseconds.
column1: Any column expression.
Returns the string in the supported format to which the UNIX milliseconds has been converted.
column1: Any column expression.
string_fmt: The datetime format to return the system clock in.
Returns the UTC string to which the UNIX time stamp has been converted in the supported format.
column1: Any column expression.
string_fmt: The datetime format to return the system clock in.
Converts the UNIX time stamp to a string in the named time zone, and returns the string.
column1: Any column expression.
string_tzname: The time zone name.
string_fmt: The datetime format to return the system clock in.
Returns statement time stamp as UNIX milliseconds; does not vary during a query.
Returns statement time stamp as a string in a supported format; does not vary during a query.
string_fmt: The datetime format to return the timestamp in.
Converts the ISO 8601 time stamp to UTC.
column1: Any column expression.
Converts the supported time stamp string to the named time zone.
column1: Any column expression.
string_tzname: The time zone name.
Returns base64 encoding of expression.
expression: Any column or literal expression.
Returns absolute value of the number.
expression: Any column or literal expression.
Returns arccosine in radians.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns arctangent in radians.
expression: Any column or literal expression.
Returns arctangent of expression2/expression1.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns smallest integer not less than the number.
expression: Any column or literal expression.
Returns cosine.
expression: Any column or literal expression.
Returns radians to degrees.
expression: Any column or literal expression.
Base of natural logarithms.
Returns e^expression.
expression: Any column or literal expression.
Returns log base e.
expression: Any column or literal expression.
Returns log base 10.
expression: Any column or literal expression.
Largest integer not greater than the number.
expression: Any column or literal expression.
Returns PI.
Returns expression1^expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns degrees to radians.
expression: Any column or literal expression.
Returns pseudo-random number with optional seed.
expression: Any column or literal expression.
Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.
expression: Any column or literal expression.
Returns sine.
expression: Any column or literal expression.
Returns square root.
expression: Any column or literal expression.
Returns tangent.
expression: Any column or literal expression.
Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.
expression: Any column or literal expression.
integer_digits: The number of digits to truncate.
True if the string contains the substring.
column: Any column or literal expression.
string_substring: The substring to search for.
Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.
column: Any column or literal expression.
Returns length of the string value.
column: Any column or literal expression.
Returns lowercase of the string value.
column: Any column or literal expression.
Returns string with all leading chars removed. White space by default.
column: Any column or literal expression.
string_chars: The leading characters to remove.
Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.
column: Any column or literal expression.
string_substring: The substring to search for.
Returns string formed by repeating expression n times.
column: Any column or literal expression.
integer_n: The number of times to repeat column.
Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.
column: The column expression.
string_substring: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.
Returns string with all trailing chars removed. White space by default.
column: Any column or literal expression.
string_chars: The trailing characters to remove.
Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.
column: Any column or literal expression.
string_sep: The separator to split column on.
Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.
column: Any column or literal expression.
integer_position: The starting position.
integer_length: The total length of the substring to retrieve.
Returns string with all leading and trailing chars removed. White space by default.
column: Any column or literal expression.
string_chars: The leading and trailing characters to remove.
Returns uppercase of the string value.
column: Any column or literal expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.
column: Any column expression.
Returns True if the string value contains the regular expression pattern.
column: The column expression.
string_pattern: The regular expression to match.
Returns True if the string value matches the regular expression pattern.
column: The column expression.
string_pattern: The regular expression to match.
Returns first position of the regular expression pattern within the string, or -1.
column: The column expression.
string_pattern: The regular expression to match.
Returns new string with occurrences of pattern replaced with string_replace. If n is given, at most n replacements are performed.
column: The column expression.
string_pattern: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.
Returns True if expression is an array, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns True if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns True if expression is a Boolean, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns True if expression is a number, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns True if expression is an object, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns True if expression is a string, otherwise returns MISSING, NULL or false.
column: Any column expression.
Returns one of the following strings, based on the value of expression: missing, null, boolean, number, string, array, object, or binary.
column: Any column expression.
Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.
column: Any column expression.
Returns true if the array contains value.
column: Any column expression.
value: The value contained within the array.
Returns count of all the non-NULL values in the array, or zero if there are no such values.
column: Any column expression.
Returns the number of elements in the array.
column: Any column expression.
Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.
column: Any column expression.
Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.
column: Any column expression.
Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.
column: Any column expression.
value: The value contained within the array.
Sum of all the non-NULL number values in the array, or zero if there are no such values.
column: Any column expression.
Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns the first non-MISSING value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-NULL, non-MISSING value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.
Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.
Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.
string_fmt: The datetime format to return the system clock in.
Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.
column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.
column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.
column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.
column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.
column1: Any column expression.
string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.
Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.
column1: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.
column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.
column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
Returns date that has been converted in a supported format to UNIX milliseconds.
column1: Any column expression.
Returns date that has been converted in a supported format to UNIX milliseconds.
column1: Any column expression.
Returns the string in the supported format to which the UNIX milliseconds has been converted.
column1: Any column expression.
string_fmt: The datetime format to return the system clock in.
Returns the UTC string to which the UNIX time stamp has been converted in the supported format.
column1: Any column expression.
string_fmt: The datetime format to return the system clock in.
Converts the UNIX time stamp to a string in the named time zone, and returns the string.
column1: Any column expression.
string_tzname: The time zone name.
string_fmt: The datetime format to return the system clock in.
Returns statement time stamp as UNIX milliseconds; does not vary during a query.
Returns statement time stamp as a string in a supported format; does not vary during a query.
string_fmt: The datetime format to return the timestamp in.
Converts the ISO 8601 time stamp to UTC.
column1: Any column expression.
Converts the supported time stamp string to the named time zone.
column1: Any column expression.
string_tzname: The time zone name.
Returns base64 encoding of expression.
expression: Any column or literal expression.
Returns absolute value of the number.
expression: Any column or literal expression.
Returns arccosine in radians.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns arctangent in radians.
expression: Any column or literal expression.
Returns arctangent of expression2/expression1.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns smallest integer not less than the number.
expression: Any column or literal expression.
Returns cosine.
expression: Any column or literal expression.
Returns radians to degrees.
expression: Any column or literal expression.
Base of natural logarithms.
Returns e^expression.
expression: Any column or literal expression.
Returns log base e.
expression: Any column or literal expression.
Returns log base 10.
expression: Any column or literal expression.
Largest integer not greater than the number.
expression: Any column or literal expression.
Returns PI.
Returns expression1^expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns degrees to radians.
expression: Any column or literal expression.
Returns pseudo-random number with optional seed.
expression: Any column or literal expression.
Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.
expression: Any column or literal expression.
Returns sine.
expression: Any column or literal expression.
Returns square root.
expression: Any column or literal expression.
Returns tangent.
expression: Any column or literal expression.
Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.
expression: Any column or literal expression.
integer_digits: The number of digits to truncate.
True if the string contains the substring.
column: Any column or literal expression.
string_substring: The substring to search for.
Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.
column: Any column or literal expression.
Returns length of the string value.
column: Any column or literal expression.
Returns lowercase of the string value.
column: Any column or literal expression.
Returns string with all leading chars removed. White space by default.
column: Any column or literal expression.
string_chars: The leading characters to remove.
Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.
column: Any column or literal expression.
string_substring: The substring to search for.
Returns string formed by repeating expression n times.
column: Any column or literal expression.
integer_n: The number of times to repeat column.
Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.
column: The column expression.
string_substring: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.
Returns string with all trailing chars removed. White space by default.
column: Any column or literal expression.
string_chars: The trailing characters to remove.
Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.
column: Any column or literal expression.
string_sep: The separator to split column on.
Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.
column: Any column or literal expression.
integer_position: The starting position.
integer_length: The total length of the substring to retrieve.
Returns string with all leading and trailing chars removed. White space by default.
column: Any column or literal expression.
string_chars: The leading and trailing characters to remove.
Returns uppercase of the string value.
column: Any column or literal expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.
column: Any column expression.
Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.
column: Any column expression.
SELECT
{
[ TOP
<numeric_literal> | DISTINCT
]
{
*
| {
<expression> [ [ AS
] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO
csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM
<table_reference> [ [ AS
] <identifier> ]
} [ , ... ]
[ [
INNER
| { { LEFT
| RIGHT
| FULL
} [ OUTER
] }
] JOIN
<table_reference> [ ON
<search_condition> ] [ [ AS
] <identifier> ]
] [ ... ]
[ WHERE
<search_condition> ]
[ GROUP
BY
<column_reference> [ , ... ]
[ HAVING
<search_condition> ]
[ UNION
[ ALL
] <select_statement> ]
[
ORDER
BY
<column_reference> [ ASC
| DESC
] [ NULLS FIRST
| NULLS LAST
]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT
] <expression> } )
| { AVG
| MAX
| MIN
| SUM
| COUNT
} ( <expression> )
| NULLIF
( <expression> , <expression> )
| COALESCE
( <expression> , ... )
| CASE
<expression>
WHEN
{ <expression> | <search_condition> } THEN
{ <expression> | NULL
} [ ... ]
[ ELSE
{ <expression> | NULL
} ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE
| NOT
LIKE
| IN
| NOT
IN
| IS
NULL
| IS
NOT
NULL
| AND
| OR
| CONTAINS
| BETWEEN
} [ <expression> ]
} [ { AND
| OR
} ... ]
SELECT * FROM Customer
SELECT [TotalDue] AS MY_TotalDue FROM Customer
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Customer
SELECT * FROM Customer WHERE CustomerId = '12345'
SELECT * FROM Customer WHERE CustomerId = '12345';
SELECT COUNT(*) AS MyCount FROM Customer
SELECT COUNT(DISTINCT TotalDue) FROM Customer
SELECT DISTINCT TotalDue FROM Customer
SELECT TotalDue, MAX(AnnualRevenue) FROM Customer GROUP BY TotalDue
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Name, TotalDue FROM Customer ORDER BY TotalDue ASC
SELECT Name, TotalDue FROM Customer LIMIT 10
SELECT * FROM Customer WHERE CustomerId = @param
SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'
SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'
SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'
SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'
SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
GROUP BY TotalDue
SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Cloudant supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Cassandra supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
The Provider for Redis is SQL-92 compliant. Below are some example SELECT statements.
Return all columns:
Rename a column:
Search data:
Return the number of items in a group:
Return the number of unique items in a group:
Summarize data:
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
The provider supports SQL-92 summary functions.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Redis supports joins of multiple tables.
You can join multiple tables just like you would in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use two tables: Restaurants and Zips.
The query below returns the Restaurant records that exist, if any, for each ZIP code:
The query below returns records from both tables that match the join condition:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
The Amazon DynamoDB APIs support the following operators in the WHERE clause: =, !=, >, <, >=, <=, IS NULL, IS NOT NULL, CONTAINS, NOT CONTAINS, BETWEEN, IN, NOT IN, LIKE, NOT LIKE, AND, OR.
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Amazon DynamoDB supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Computes the average value of a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
Returns the number of elements in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
Gets the maximum value in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
Gets the minimum value in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
Computes the sum of the elements in a JSON within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for MongoDB supports joins of a nested array with its parent document and joins of multiple collections.
The provider expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested MongoDB documents. This type of query is supported through the MongoDB API.
For example, consider the following query from MongoDB's restaurants collection:
You can join multiple collections just like you would join tables in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use the restaurants and zips collections available in the MongoDB documentation.
The query below returns the restaurant records that exist, if any, for each ZIP code:
The query below returns records from both tables that match the join condition:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
see
See for more details.
SELECT
{
[ TOP
<numeric_literal> | DISTINCT
]
{
*
| {
<expression> [ [ AS
] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO
csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM
<table_reference> [ [ AS
] <identifier> ]
} [ , ... ]
[ [
INNER
| { { LEFT
| RIGHT
| FULL
} [ OUTER
] }
] JOIN
<table_reference> [ ON
<search_condition> ] [ [ AS
] <identifier> ]
] [ ... ]
[ WHERE
<search_condition> ]
[ GROUP
BY
<column_reference> [ , ... ]
[ HAVING
<search_condition> ]
[ UNION
[ ALL
] <select_statement> ]
[
ORDER
BY
<column_reference> [ ASC
| DESC
] [ NULLS FIRST
| NULLS LAST
]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT
] <expression> } )
| { AVG
| MAX
| MIN
| SUM
| COUNT
} ( <expression> )
| NULLIF
( <expression> , <expression> )
| COALESCE
( <expression> , ... )
| CASE
<expression>
WHEN
{ <expression> | <search_condition> } THEN
{ <expression> | NULL
} [ ... ]
[ ELSE
{ <expression> | NULL
} ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE
| NOT
LIKE
| IN
| NOT
IN
| IS
NULL
| IS
NOT
NULL
| AND
| OR
| CONTAINS
| BETWEEN
} [ <expression> ]
} [ { AND
| OR
} ... ]
SELECT * FROM Movies
SELECT [MovieRating] AS MY_MovieRating FROM Movies
SELECT CAST(MovieRuntime AS VARCHAR) AS Str_MovieRuntime FROM Movies
SELECT * FROM Movies WHERE MovieRating = 'R'
SELECT COUNT(*) AS MyCount FROM Movies
SELECT COUNT(DISTINCT MovieRating) FROM Movies
SELECT DISTINCT MovieRating FROM Movies
SELECT MovieRating, MAX(MovieRuntime) FROM Movies GROUP BY MovieRating
SELECT MovieRuntime, MovieRating FROM Movies ORDER BY MovieRating ASC
SELECT MovieRuntime, MovieRating FROM Movies LIMIT 10
SELECT * FROM Movies WHERE MovieRating = @param
SELECT COUNT(*) FROM Movies WHERE MovieRating = 'R'
SELECT COUNT(DISTINCT MovieRuntime) AS DistinctValues FROM Movies WHERE MovieRating = 'R'
SELECT MovieRating, AVG(MovieRuntime) FROM Movies WHERE MovieRating = 'R'
GROUP BY MovieRating
SELECT MIN(MovieRuntime), MovieRating FROM Movies WHERE MovieRating = 'R'
GROUP BY MovieRating
SELECT MovieRating, MAX(MovieRuntime) FROM Movies WHERE MovieRating = 'R'
GROUP BY MovieRating
SELECT SUM(MovieRuntime) FROM Movies WHERE MovieRating = 'R'
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
SELECT
{
[ TOP
<numeric_literal> | DISTINCT
]
{
*
| {
<expression> [ [ AS
] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO
csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM
<table_reference> [ [ AS
] <identifier> ]
} [ , ... ]
[ [
INNER
| { { LEFT
| RIGHT
| FULL
} [ OUTER
] }
] JOIN
<table_reference> [ ON
<search_condition> ] [ [ AS
] <identifier> ]
] [ ... ]
[ WHERE
<search_condition> ]
[ GROUP
BY
<column_reference> [ , ... ]
[ HAVING
<search_condition> ]
[ UNION
[ ALL
] <select_statement> ]
[
ORDER
BY
<column_reference> [ ASC
| DESC
] [ NULLS FIRST
| NULLS LAST
]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT
] <expression> } )
| { AVG
| MAX
| MIN
| SUM
| COUNT
} ( <expression> )
| NULLIF
( <expression> , <expression> )
| COALESCE
( <expression> , ... )
| CASE
<expression>
WHEN
{ <expression> | <search_condition> } THEN
{ <expression> | NULL
} [ ... ]
[ ELSE
{ <expression> | NULL
} ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE
| NOT
LIKE
| IN
| NOT
IN
| IS
NULL
| IS
NOT
NULL
| AND
| OR
| CONTAINS
| BETWEEN
} [ <expression> ]
} [ { AND
| OR
} ... ]
SELECT * FROM "CData"."Sample".Products
SELECT [Name] AS MY_Name FROM "CData"."Sample".Products
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM "CData"."Sample".Products
SELECT * FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
SELECT COUNT(*) AS MyCount FROM "CData"."Sample".Products
SELECT COUNT(DISTINCT Name) FROM "CData"."Sample".Products
SELECT DISTINCT Name FROM "CData"."Sample".Products
SELECT Name, MAX(AnnualRevenue) FROM "CData"."Sample".Products GROUP BY Name
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Id, Name FROM "CData"."Sample".Products ORDER BY Name ASC
SELECT Id, Name FROM "CData"."Sample".Products LIMIT 10
SELECT * FROM "CData"."Sample".Products WHERE Industry = @param
SELECT COUNT(*) FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
SELECT COUNT(DISTINCT Id) AS DistinctValues FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
SELECT Name, AVG(AnnualRevenue) FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT MIN(AnnualRevenue), Name FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT Name, MAX(AnnualRevenue) FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT SUM(AnnualRevenue) FROM "CData"."Sample".Products WHERE Industry = 'Floppy Disks'
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Azure Cosmos DB supports joins of a nested array with its parent document and joins of multiple collections.
The provider expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Azure Cosmos DB documents. This type of query is supported through the Azure Cosmos DB API.
For example, consider the following query from Azure Cosmos DB's restaurants collection:
You can join multiple collections just like you would join tables in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use the restaurants and zips collections available in the Azure Cosmos DB documentation.
The query below returns the restaurant records that exist, if any, for each ZIP code:
The query below returns records from both tables that match the join condition:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns the absolute (positive) value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.
numeric_expr: A numeric expression.
Returns the smallest integer value greater than, or equal to, the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
numeric_expr: A numeric expression.
Returns the corresponding angle in degrees for an angle specified in radians.
numeric_expr: A numeric expression.
Returns the largest integer less than or equal to the specified numeric expression.
numeric_expr: A numeric expression.
Returns the exponential value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the base-10 logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
Returns radians when a numeric expression, in degrees, is entered.
numeric_expr: A numeric expression.
Returns a randomly generated numeric value from [0,1).
Returns a numeric value, rounded to the closest integer value.
numeric_expr: A numeric expression.
Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the square root of the specified numeric value.
numeric_expr: A numeric expression.
Returns the square of the specified numeric value.
numeric_expr: A numeric expression.
Returns the tangent of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns a numeric value, truncated to the closest integer value.
numeric_expr: A numeric expression.
Returns the principal value of the arc tangent of y/x, expressed in radians.
y_expr: The y numeric expression.
x_expr: The x numeric expression.
Returns the natural logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
base: Optional numeric argument that sets the base for the logarithm.
Returns the constant value of PI.
Returns the value of the specified expression to the specified power.
numeric_expr: A numeric expression.
power_expr: Is the power to which to raise numeric_expr.
Returns a Boolean value indicating if the type of the specified expression is an array.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a Boolean.
expr: Any valid expression.
Returns a Boolean indicating if the property has been assigned a value.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is null.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a number.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a JSON object.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a string.
expr: Any valid expression.
Returns a string that is the result of concatenating two or more string values.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a Boolean indicating whether the first string expression contains the second.
str1: The string to search in.
str2: The string to search for.
Returns a Boolean indicating whether the first string expression ends with the second.
str1: The string to search in.
str2: The string to search for.
Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.
str1: The string to search in.
str2: The string to search for.
Returns the left part of a string with the specified number of characters.
str: A valid string expression.
num_expr: The number of characters to return.
Returns the number of characters of the specified string expression.
str: Any valid string expression.
Returns a string expression after converting uppercase character data to lowercase.
str: Any valid string expression.
Returns a string expression after it removes leading blanks.
str: Any valid string expression.
Replaces all occurrences of a specified string value with another string value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Repeats a string value a specified number of times.
str: The string expression to repeat.
repeat_num: The number of times to repeat the str expression.
Returns the reverse order of a string value.
str: Any valid string expression.
Returns the right part of a string with the specified number of characters.
str: Any valid string expression.
num_expr: The starting index.
Returns a string expression after it removes trailing blanks.
str: Any valid string expression.
Returns a Boolean indicating whether the first string expression starts with the second.
str1: The string to search in.
str2: The string to search for.
Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.
str: Any valid string expression.
start_index: The starting index.
length: The length of the string to return.
Returns a string representation of scalar expression.
expr: Any valid expression.
Returns a string expression after it removes leading and trailing blanks.
str: Any valid string expression.
Returns a string expression after converting lowercase character data to uppercase.
str: Any valid string expression.
Returns an array that is the result of concatenating two or more array values.
array_exp1: Any valid array expression.
array_exp2: Any valid array expression.
array_exp3: Any valid array expression.
Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.
array_exp1: Any array expression.
expr: The expression to search for.
bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.
Returns the number of elements of the specified array expression.
array_exp: Any valid array expression.
Returns part of an array expression.
array_exp: Any valid array expression.
start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.
max_size: Maximum number of elements in the resulting array.
Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns the absolute (positive) value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.
numeric_expr: A numeric expression.
Returns the smallest integer value greater than, or equal to, the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
numeric_expr: A numeric expression.
Returns the corresponding angle in degrees for an angle specified in radians.
numeric_expr: A numeric expression.
Returns the largest integer less than or equal to the specified numeric expression.
numeric_expr: A numeric expression.
Returns the exponential value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the base-10 logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
Returns radians when a numeric expression, in degrees, is entered.
numeric_expr: A numeric expression.
Returns a randomly generated numeric value from [0,1).
Returns a numeric value, rounded to the closest integer value.
numeric_expr: A numeric expression.
Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the square root of the specified numeric value.
numeric_expr: A numeric expression.
Returns the square of the specified numeric value.
numeric_expr: A numeric expression.
Returns the tangent of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns a numeric value, truncated to the closest integer value.
numeric_expr: A numeric expression.
Returns the principal value of the arc tangent of y/x, expressed in radians.
y_expr: The y numeric expression.
x_expr: The x numeric expression.
Returns the natural logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
base: Optional numeric argument that sets the base for the logarithm.
Returns the constant value of PI.
Returns the value of the specified expression to the specified power.
numeric_expr: A numeric expression.
power_expr: Is the power to which to raise numeric_expr.
Returns a Boolean value indicating if the type of the specified expression is an array.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a Boolean.
expr: Any valid expression.
Returns a Boolean indicating if the property has been assigned a value.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is null.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a number.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a JSON object.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a string.
expr: Any valid expression.
Returns a string that is the result of concatenating two or more string values.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a Boolean indicating whether the first string expression contains the second.
str1: The string to search in.
str2: The string to search for.
Returns a Boolean indicating whether the first string expression ends with the second.
str1: The string to search in.
str2: The string to search for.
Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.
str1: The string to search in.
str2: The string to search for.
Returns the left part of a string with the specified number of characters.
str: A valid string expression.
num_expr: The number of characters to return.
Returns the number of characters of the specified string expression.
str: Any valid string expression.
Returns a string expression after converting uppercase character data to lowercase.
str: Any valid string expression.
Returns a string expression after it removes leading blanks.
str: Any valid string expression.
Replaces all occurrences of a specified string value with another string value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Repeats a string value a specified number of times.
str: The string expression to repeat.
repeat_num: The number of times to repeat the str expression.
Returns the reverse order of a string value.
str: Any valid string expression.
Returns the right part of a string with the specified number of characters.
str: Any valid string expression.
num_expr: The starting index.
Returns a string expression after it removes trailing blanks.
str: Any valid string expression.
Returns a Boolean indicating whether the first string expression starts with the second.
str1: The string to search in.
str2: The string to search for.
Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.
str: Any valid string expression.
start_index: The starting index.
length: The length of the string to return.
Returns a string representation of scalar expression.
expr: Any valid expression.
Returns a string expression after it removes leading and trailing blanks.
str: Any valid string expression.
Returns a string expression after converting lowercase character data to uppercase.
str: Any valid string expression.
Returns an array that is the result of concatenating two or more array values.
array_exp1: Any valid array expression.
array_exp2: Any valid array expression.
array_exp3: Any valid array expression.
Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.
array_exp1: Any array expression.
expr: The expression to search for.
bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.
Returns the number of elements of the specified array expression.
array_exp: Any valid array expression.
Returns part of an array expression.
array_exp: Any valid array expression.
start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.
max_size: Maximum number of elements in the resulting array.
Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
SELECT
{
[ TOP
<numeric_literal> | DISTINCT
]
{
*
| {
<expression> [ [ AS
] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO
csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM
<table_reference> [ [ AS
] <identifier> ]
} [ , ... ]
[ [
INNER
| { { LEFT
| RIGHT
| FULL
} [ OUTER
] }
] JOIN
<table_reference> [ ON
<search_condition> ] [ [ AS
] <identifier> ]
] [ ... ]
[ WHERE
<search_condition> ]
[ GROUP
BY
<column_reference> [ , ... ]
[ HAVING
<search_condition> ]
[ UNION
[ ALL
] <select_statement> ]
[
ORDER
BY
<column_reference> [ ASC
| DESC
] [ NULLS FIRST
| NULLS LAST
]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT
] <expression> } )
| { AVG
| MAX
| MIN
| SUM
| COUNT
} ( <expression> )
| NULLIF
( <expression> , <expression> )
| COALESCE
( <expression> , ... )
| CASE
<expression>
WHEN
{ <expression> | <search_condition> } THEN
{ <expression> | NULL
} [ ... ]
[ ELSE
{ <expression> | NULL
} ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE
| NOT
LIKE
| IN
| NOT
IN
| IS
NULL
| IS
NOT
NULL
| AND
| OR
| CONTAINS
| BETWEEN
} [ <expression> ]
} [ { AND
| OR
} ... ]
SELECT * FROM [CData].[Entities].Customers
SELECT [CompanyName] AS MY_CompanyName FROM [CData].[Entities].Customers
SELECT CAST(Balance AS VARCHAR) AS Str_Balance FROM [CData].[Entities].Customers
SELECT * FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT COUNT(*) AS MyCount FROM [CData].[Entities].Customers
SELECT COUNT(DISTINCT CompanyName) FROM [CData].[Entities].Customers
SELECT DISTINCT CompanyName FROM [CData].[Entities].Customers
SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers GROUP BY CompanyName
SELECT restaurants.name, zips.city FROM restaurants INNER JOIN zips ON restaurants.address_zipcode = zips.C_id
SELECT City, CompanyName FROM [CData].[Entities].Customers ORDER BY CompanyName ASC
SELECT City, CompanyName FROM [CData].[Entities].Customers LIMIT 10
SELECT * FROM [CData].[Entities].Customers WHERE Country = @param
SELECT COUNT(*) FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT COUNT(DISTINCT City) AS DistinctValues FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT CompanyName, AVG(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US'
GROUP BY CompanyName
SELECT MIN(Balance), CompanyName FROM [CData].[Entities].Customers WHERE Country = 'US'
GROUP BY CompanyName
SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US'
GROUP BY CompanyName
SELECT SUM(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT [restaurants].[restaurant_id], [restaurants].name, [restaurants.grades].*FROM [restaurants.grades]JOIN [restaurants]WHERE [restaurants].name = 'Morris Park Bake Shop'
SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM zips zLEFT JOIN restaurants rON r.[address.zipcode] = z._id
SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM restaurants rINNER JOIN zips zON r.[address.zipcode] = z._id
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)