* language *
SQL

* comment *
SQL for Sybase

* comment *
--------------------------------------------------------------------------------
syntax
--------------------------------------------------------------------------------

* alias *
select
SELECT

select
Select, retrieve and copy database data
  select [ all | distinct ] { <select-field-expression> [ , ... ] | * }
  [ into <table-name> ]         -- this injects results into a destination table
  [ from <table-name> [ <table-alias> ] [ , ... ] [ union | holdlock ]
    [ , { <table-name> cross join <table-name> |
          <table-name> { inner | left | right | full } join <table-name>
              on <search-oconditions> } [ , ... ] ]
  [ where <boolean-expression> ]
  [ group by <qualified-columns-list> ]
  [ having <having-clause> ]
  [ order by <order-by-list> ]
  [ compute <compute-clause> ]
  [ for { read only | update [ of ( <column-name> [ , ... ] ) ] } ]
  [ at isolation { read uncommitted | read committed | serializable } ]
  [ for browse ]

Set a variable
  select @<variable-name> = <expression> [ , ... ]

See also keywords union, order by, where

* alias *
union
UNION

union
select <select-field-expression> [into <clause>]
        [ from <from-tables> ]
        [ where <boolean-expression> ]
        [ group by <qualified-columns-list> ]
        [ having <having-clause> ] ] ...
[union [ all ]
    select <select-field-expression>
        [ from <from-tables> ]
        [ where <boolean-expression> ]
        [ group by <qualified-columns-list> ]
        [ having <having-clause> ] ] ...
[ order by <order-by-list> ]
[ compute <compute-clause> ]

* alias *
order by
ORDER BY
order
ORDER

order by
In select statement:

[order by
     { [ <table-name>. | <view-name>. ] <column-name>
       | <select-list-number>
       | <expression> } [ asc | desc ]
 [ , { [<table-name>. | <view-name>. ] <column-name>
       | <select-list-number>
       | <expression> } [ asc | desc ] ...]

* alias *
where
WHERE

where
where [not] <expression> <comparison-operator> <expression>
where [not] <expression> [not] like  "match-string" [escape "escape-character"]
where [not] <expression> is [not] null
where [not] <expression> [not] between <expression> and <expression>
where [not] <expression> [not] in ( { <value-list> | <subquery> } )
where [not] exists ( <subquery> )
where [not] <expression> <comparison-operator> { any | all } ( <subquery>)
where [not] <column-name> <join-operator> <column-name>
where [not] <boolean-expression>
where [not] <expression> { and | or } [not] <expression>

* alias *
insert
INSERT

insert
Insert database rows

Using insert...values
    insert [ into ] { <table-name> | <view-name> }
    [ ( <column-name> [ , ... ] ) ]     -- not necessary if all row values given
    values
      ( { <expression> | default } [ , ... ] )
(Microsoft allows the keywords "default values" instead of a value list)

Using insert...select
    -- copy data from another identically structured table
    insert [ into ] { <table-name> | <view-name> }
    select * from <other-table-name>

    -- copy data from dissimilar source table(s)
    insert [ into ] { <table-name> | <view-name> }
    ( <column-name> [ , ... ] )
    select <select-field-expression> [ , ... ] -- converting appropriately
    [ from { <table-name> | <view-name> } [ , ... ] ]
    [ where <where-clauses> | current of <cursor-name> ]

Using insert...execute (Microsoft only)
    -- inserts the result set from a stored procedure
    insert [ into ] { <table-name> | <view-name> }
    [ ( <column-name> [ , ... ] ) ]     -- not necessary if all row values given
    exec[ute] <procedure-name>
    [ [ @<param-name> = ] { <expression> [output] | default } [ , ... ] ]

* alias *
delete
DELETE

delete
Delete database rows

delete [ from ] { <table-name> | <view-name> }
[ where <where-clauses> | current of <cursor-name> ]

* alias *
update
UPDATE

update
Update chosen fields in database rows

update { <table-name> | <view-name> }
set { <column-name> | @<variable-name> = { <expression> | default } [ , ... ]
[ from { <table-name> | <view-name> } [ , ... ] ]
[ where <where-clauses> | current of <cursor-name> ]

* alias *
truncate
TRUNCATE

truncate
Empty a database table, leaving its structure intact

truncate [ [ <database> . ] <owner> . ] <table-name>

* alias *
execute
EXECUTE
exec
EXEC

execute
Call a procedure

[ @<variable-name> = ]
exec [ [ <database> . ] <owner> . ] <stored-procedure-name>
    { [ @<in-arg-name> = ] <in-value-expression> |
      [ @<out-arg-name> = ] @<variable-name> output } [, ...]

* comment *
--------------------------------------------------------------------------------

* alias *
case
CASE

case
The SQL If/Then/Else or Select/Case expression
(Transact-SQL extension)
This can be used in a <select-field-expression>.

The Select/Case form: if no "else" default limb, returns NULL.
    case <test-value-expression>
        { when <compare-value-expression> then <result-value-expression> } [...]
        [ else <default-result-value-expression> ]
    end

The If/Then/Else form: if no "else" default limb, returns NULL.
    case
        { when <conditional-expression> then <result-value-expression> } [...]
        [ else <default-result-value-expression> ]
    end

* comment *
--------------------------------------------------------------------------------
functions
--------------------------------------------------------------------------------

* alias *
()
functions

()
SQL functions
_____________________________________________
Aggregate: avg, count, max, min, sum
Mathematic: abs, acos, asin, atan, atn2, cieling, cos, cot, degrees,
    exp, floor, log, log10, pi, power, radians, rand, round, sign, sin,
    sqrt, tan
String: +, ascii, char, char_length, charindex, datalength, difference,
    lower, ltrim, patindex, replicate, reverse, right, rtrim, soundex,
    space, stuff, substring, upper
Date: current_timestamp, dateadd, datediff, datename, datepart, getdate
Text/Image: textptr, textvalid
Conversion: convert, str, hextoint, inttohex
Type checking: isdate, isnumeric, valid_name
Null comparisons: coalesce, isnull, nullif
Database: db_id, db_name, getanisnull
Database objects: object_id, object_name, stats_date
Table sizes: data_pgs, reserved_pgs, rowcnt, used_pgs
Table columns: col_length, col_name, datalength, ident_incr, ident_seed,
    index_col
User Session: current_user, host_id, host_name, proc_role, session_user,
    show_role, suser_id, suser_name, system_user, user_id, user_name, user
System: lct_admin, tsequal

* alias *
ascii and char
ascii
char

ascii and char
ascii(char_expr)
  Return ASCII code of first character in expression.
char(ascii_value)
  Return character whose ASCII code point is given.

* alias *
charindex and patindex
charindex
patindex

charindex and patindex
charindex(substring, string)
  Finds first occurrence of substring in string,
  returning its 1-based position. Fails with 0.
patindex(pattern, string)
  Finds first occurrence of pattern instring,
  returning its 1-based position. Fails with 0.

Pattern wildcards:
%       zero or more characters
_       any one character
[...]   any one character in the set ...
[^...]  any one character not in the set ...

datalength
datalength(value)
  Returns size of value in bytes.

char_length
char_length(string_value)
  Sybase only: Returns lengh of string_value in characters.

* alias *
lower and upper
lower
upper

lower and upper
lower(string_value)
  Return string_value with all letters converted to lowercase.
upper(string_value)
  Return string_value with all letters converted to uppercase.

* alias *
ltrim and rtrim
ltrim
rtrim

ltrim and rtrim
ltrim(string_value)
  Return string_value with all leading (leftmost) spaces removed.
rtrim(string_value)
  Return string_value with all trailing (rightmost) spaces removed.

* alias *
replicate and space
replicate
space

replicate and space
replicate(string_value, repeat_count)
  Return a string made up of the string_value repeated repeat_count times.
space(repeat_count)
  Return a string of repeat_count spaces (like replicate(" ", repeat_count)).

reverse
reverse(string_value)
  Return a string of string_value's characters in reverse order.

substring
substring(string_or_binary_value, start, length)
  Return a string or binary value consisting of length characters or bytes
  copied from the 1-based start position within string_or_binary_value.
  length may not be elided. Returns NULL if start is out of bounds or
  length <= 0.

right
right(string_or_binary_value, length)
  Return a string or binary value consisting of length characters or bytes
  taken from the end of string_or_binary_value. Implemented as
    substring(string_or_binary_value,
              datalength(string_or_binary_value) - length + 1,
              length)

stuff
stuff(string1, start, length, string2)
  Return a string obtained by replacing the substring of given length
  at position start in string1 with the value string2. Out of range values
  of start or negative length cause NULL to be returned.

* alias *
datepart and datename
datepart
datename

datepart and datename
datepart(datepart, date_value)
  Returns the integer value of the required date part for
  the given date_value.
datename(datepart, date_value)
  Returns a string representation of the required date part
  for given date_value. For weekdays and months these will be names.

(see "datepart values" tip)


* alias *
datepart values
year
yy
quarter
qq
month
mm
dy
day
dd
week
wk
weekday
dw
hour
hh
minute
mi
second
ss
millisecond
ms

datepart values
datepart       values
year/yy        1753-9999 (or 1900-2079)
quarter/qq     1-4
month/mm       1-12
dy             1-366 (day of year)
day/dd         1-31 (day of month)
week/wk        1-53 (week of year)
weekday/dw     1-7 (Sun.-Sat.)
hour/hh        0-23
minute/mi      0-59
second/ss      0-59
millisecond/ms 0-999

getdate
getdate()
  Returns a datetime value corresponding to the time of execution.

dateadd
dateadd(datepart, number, date_value)
  Returns a datetime value obtained by adding number occurrences of
  the datepart time unit to the given date_value.

(see "datepart values" tip)

datediff
datediff(datepart, date1, date2)
  Returns the time between two dates in (integer) datepart units.

(see "datepart values" tip)

convert
convert(datatype[(length, scale)], value_expr [, style])
  Convert value to the desired data type, returning it.

datatype:
  int
  float
  numeric(width, places)
  char(length)
  binary(bytes)
  datetime
  smalldatetime

use style for date to char conversion) eg
  convert(char(10), trade_date, 101) -> 12/31/2005
(see "convert() date format styles" tip)

See also str() for formatted numeric to string conversion.

* alias *
convert() date format styles
convert()
0
100
1
101
2
102
3
103
4
104
5
105
6
106
7
107
8
108
9
109
10
110
11
111
12
112
13
113
14
114
15
115
16
116
17
18
19
20

convert() date format styles
convert() date format styles

Dates (hundreds extend format)
[10]1   mm/dd/[yy]yy              [USA date] len=8 or 10
[10]2   [yy]yy.mm.dd              [ANSI date] len=8 or 10
[10]3   dd/mm/[yy]yy              [UK, France date] len=8 or 10
[10]4   dd.mm.[yy]yy              [German date] len=8 or 10
[10]5   dd-mm-[yy]yy              [Italian date] len=8 or 10
[10]6   dd Mmm [yy]yy             [European with month name] len=9 or 11
[10]7   Mmm dd, [yy]yy            [USA with month name] len=10 or 12
[1]10   mm-dd-[yy]yy              [USA date] len=8 or 10
[1]11   [yy]yy/mm/dd              [Japan date] len=8 or 10
[1]12*  [yy]yymmdd                [ISO date] len=4 or 6
[1]13*  [yy]yy/dd/mm              [esoteric year-day-month] len=8 or 10
[1]14   mm/[yy]yy/dd              [esoteric month-year-day] len=8 or 10
[1]15   dd/[yy]yy/mm              [esoteric day-year-month] len=8 or 10

Times
[10]8   HH:MM:SS                  [24-hr standard] len=8
   17   HH:MM(AM|PM)              [12-hr time] len=7
   18   HH:MM                     [24-hr time] len=5
   19   HH:MM:SS:ttt(AM|PM)       [12-hr time with ms] len=14
   20   HH:MM:SS:ttt              [24-hr time with ms] len=12

Date and Time
[10]0   Mmm dd yyyy HH:MM(AM|PM)  [default date/time] len=19
[10]9   Mmm dd yyyy HH:MM:SS:ttt(AM|PM) [default with ms] len=26
[1]16   Mmm dd yyyy HH:MM:SS      [24-hr default] len=20

* Microsoft variations:
[1]12   HH:MM:SS:ttt              [24-hr with ms] len=12
[1]13   dd Mmm yyyy HH:MM:SS:ttt  [European 24-hr with ms] len=24

str
str(numeric_value [, width [, precision] ])
  Returns a string containing a textual representation of the given
  numeric_value. Default width is 10; default precision is 0 (which
  rounds to the nearest integer value). If the conversion requires a
  width greater than that indicated, the result is replicate('*', width).

  See also convert()

isnull
isnull(test_value, replace_value)
  Returns replace_value if test_value is NULL, test_value otherwise.
  (See also the "case" keyword)

nullif
nullif(value1, value2)
  Returns NULL if value1 equals value2, otherwise value1.
  (See also the "case" keyword)

coalesce
coalesce(value1, value2, ... valueN)
  Returns the first non-NULL value from the argument list.

* alias *
ceiling, floor, round
ceiling
floor
round

ceiling, floor, round
ceiling(value)
  Returns the smallest integral value greater than or equal to value.

floor(value)
  Returns the largest integral value less than or equal to value.

round(value, precision)
  Returns value rounded to the nearest value exactly divisible by
  10^(-precision). If positive, precision corresponds to the number of digits
  following the decimal point; if negative, the value's lower digits are
  zeroed and the result round-adjusted appropriately.

abs
abs(value)
  Returns the absolute value of the given numeric argument.

* alias *
aggregate functions
avg
count
max
min
sum

aggregate functions
Aggregate Functions
  <function>( [ALL | DISTINCT] expression )

avg([ALL | DISTINCT] expr), sum([ALL | DISTINCT] expr)
    Compute the average, sum of all [distinct] values excluding NULL across
    all rows of a result set.

count(*)
    Return the number of rows in a result set.
count(expr)
    Return the number of rows in a result set where the expression is non-NULL.
count( [ALL | DISTINCT] expr)
    Return the number of distinct non-NULL values of expr in the in the result
    set.

min(expr), max(expr)
    Return the minimum, maximum non-NULL value of expr in the in the result
    set.

* comment *
End of file