|
Index
[ Symbols ],
[ A ],
[ B ],
[ C ],
[ D ],
[ E ],
[ F ],
[ G ],
[ H ],
[ I ],
[ J ],
[ L ],
[ M ],
[ N ],
[ O ],
[ P ],
[ Q ],
[ R ],
[ S ],
[ T ],
[ U ],
[ V ],
[ W ],
[ Y ]
Symbols[ Top ]
* (asterisk) regular-expression metacharacter, 104, 106
COUNT function and, 57
qualifying with table names, 108
returning all columns from a table, 107
*= and =*, designating left and right outer joins, 87
^ (caret) regular-expression metacharacter, 104, 105
^ = comparison operator, 97
$ (dollar sign) regular-expression metacharacter, 104, 106
( \ ) escape character, 101, 104
MySQL regular expressions and, 105
? (question mark) regular-expression metacharacter, 104, 106
% (percent), used for pattern matching, 100, 105
. (period), used for pattern matching, 100, 104, 105
| (pipe) regular-expression metacharacter, 104, 106
+ (plus) regular-expression metacharacter, 104, 106
identifying optional tables, 87
string concatenation operator, 53
| | string concatenation operator, 53
_ (underscore), used for pattern matching, 101, 105
! = comparison operator, 97
< comparison operator, 97
< > comparison operator, 97
< = comparison operator, 97, 100
< = > comparison operator, 97
= comparison operator, 97, 119
> comparison operator, 97
> = comparison operator, 97, 100
( ... ) regular-expression metacharacters, 104, 106
[ ... ] regular-expression metacharacters, 104, 105
[ [.xx.] ] regular-expression metacharacters, 106
[ [: < :] ] regular-expression metacharacters, 106
[ [: > :] ] regular-expression metacharacters, 106
[.xx.] regular-expression metacharacters, 104
[:class:] regular-expression metacharacters, 104, 106
[=chars=] regular-expression metacharacters, 104, 106
[^ ... ] regular-expression metacharacters, 104, 105
\1 ... \9 regular-expression metacharacters, 104
{x}, {x,y}, {x,} regular-expression metacharacters, 104, 106
\ \ escape sequence (MySQL), 89
\" escape sequence (MySQL), 89
\% escape sequence (MySQL), 89
\' escape sequence (MySQL), 89
\_ escape sequence (MySQL), 89
\0 escape sequence (MySQL), 89
\b escape sequence (MySQL), 89
\n escape sequence (MySQL), 89
\r escape sequence (MySQL), 89
\t escape sequence (MySQL), 89
\z escape sequence (MySQL), 89
A[ Top ]
aborting transactions, 130
ABS function, 48
ACOS function, 50
ADD_MONTHS function, 45
aggregate functions, 56
aliases
column, 110-114
UNION queries and, 133
qualifying join columns, 82
table, 35
flashback queries and, 40
in FROM clauses, 116
ALL keyword, 56, 114
comparison operators and, 97
vs. FIRST keyword, 76
ANSI/ISO CAST function, 9
MySQL, 29, 34
SQL Server, 23
ANSI/ISO EXTRACT function, 10, 29
ANSI/ISO WITH clause
correlated subqueries, factoring out, 123-125
noncorrelated subqueries, factoring out, 122
recursive, 66
ANSI_NULLS setting, 95
ANY keyword and comparison operators, 97
APPEND hint and direct-path inserts, 74
AS OF keyword, 40
ASCENDING keyword, 120
ASIN function, 50
asterisk ( * ) regular-expression metacharacter, 104, 106
COUNT function and, 57
qualifying with table names, 108
returning all columns from a table, 107
ATAN function, 50
ATAN2 function, 50
ATANH function, 50
ATN2 function (SQL Server), 50
autocommit mode, 35, 125
AVG function, 57
B[ Top ]
backslash ( \ ) escape character, 101, 104
MySQL regular expressions and, 105
BEGIN DISTRIBUTED TRANSACTION statement, 128
BEGIN TRANSACTION statement (SQL Server), 125, 127
BEGIN/BEGIN WORK statements (MySQL), 126
BETWEEN predicate, 97, 100
BIGINT function, 18, 21
bill-of-materials scenario, 66-71
tables for, 6
BIN function, 34
BINARY_DOUBLE type, 17
BINARY_FLOAT type, 17
BLOB function, 23
BLOB, converting LONG values to, 18
BULK COLLECT keywords, 38
C[ Top ]
Cartesian joins, 77-79
CASE expressions, 7-9
NULLs and, 93
case of strings, changing, 55
case sensitivity
MySQL pattern matching, 105
Oracle pattern matching, 103
punctuation/case in names, 111
CAST function, 9
MySQL, 29, 34
SQL Server, 23
CD information, tables for, 4
CEIL function, 48
CEILING function, 48
CHAR function, 18, 21, 23
CHARINDEX function, 51
chemical exposure information, tables for, 5
CLOB function, 23
CLOB, converting LOB to, 18
COALESCE function, 94
collections in FROM clause, 118
column aliases, 110-114
UNION queries and, 133
columns
applying functions to, 41
ascending/descending sorts, 120
COUNT function, 56
CUBE operation, 62, 65
FROM clause, 116-119
GROUP BY lists, reducing, 59
grouping functions, 63, 65
joining tables, 81
LOB columns, 93
names of, in result sets
controlling, 110
specifying, 107
NATURAL JOIN keywords, 82
outer joins, 85-87
qualifying names, 108, 113
selecting data, 107-114
single-row inserts, 72
updating data, 137-141
USING clause, 81
COMMENT clause (Oracle), 129
COMMIT statement
ending transactions, 129
naming transactions, 126
COMMIT TRANSACTION statement, 129
comparison operators, 96
specifying multiple values on left side, 98
CONCAT function, 53
conditional multi-table inserts, 76
CONNECT BY syntax (Oracle), 67-72
CONNECT_BY_ISCYCLE function, 70, 71
CONNECT_BY_ISLEAF function, 71
CONNECT_BY_ROOT function/CONNECT_BY_ROOT operator, 71
CONV function, 34
conversion functions, 9-34
CONVERT function
datetime conversions (SQL Server), 24-26
numeric conversions (SQL Server), 27
correlated subqueries
in EXISTS predicates, 99
factoring out with WITH clause, 123-125
generating new values, 138
ORDER BY clause and, 120
COS function, 50
COSH function, 50
COT function, 50
COUNT function, 56, 57
CREATE TABLE statement and case/punctuation in names, 112
CROSS JOIN keywords, 79
CUBE operation
Oracle, 62
SQL Server, 65
CURDATE( ), 44
CURRENT DATE function, 43
current date/time, returning, 42-44
CURRENT TIME function, 43
CURRENT TIMESTAMP function, 43
CURRENT TIMEZONE function, 43
CURRENT_DATE function
DB2, 43
MySQL, 44
Oracle, 42
CURRENT_TIME function
DB2, 43
MySQL, 44
CURRENT_TIMESTAMP function
DB2, 43
MySQL, 44
Oracle, 42
SQL Server, 44
CURRENT_TIMEZONE function, 43
cursors, updating through, 139
CURTIME( ), 44
D[ Top ]
datatype conversions, 9-34
date format elements (Oracle), 11-13
DATE function, 18, 20
date functions, 42-47
adding/subtracting intervals, 47
current date/time, returning, 42-44
performing date arithmetic
Oracle, 45
SQL Server, 46
rounding/truncating, 44
DATE_ADD function, 47
DATE_FORMAT function, 32
DATE_SUB function, 47
DATEADD function, 46
DATEDIFF function, 46
DATENAME function, 26
DATEPART function, 26
datetime conversions
DB2, 18-21
MySQL, 29-33
Oracle, 11-15
SQL Server, 23-27
datetime interval literals, 90
datetime literals, 90
DAY function, 18, 27
DAY keyword (MySQL), 47
DAY_HOUR keyword (MySQL), 47
DAY_MINUTE keyword (MySQL), 47
DAY_SECOND keyword (MySQL), 47
DAYNAME function, 18, 29
DAYOFMONTH function, 29
DAYOFWEEK function, 18, 29
DAYOFYEAR function, 18, 29
DAYS function, 18
DB2
current date/time functions, 43
datetime conversions, 18-21
EXCEPT ALL operation, 135
functions for NULLs, 95
numeric conversions, 21-23
recursive WITH clause, 66
search function, 51
translating characters, 55
trigonometric functions, 50
updating through a cursor, 139
DBA_2PC_PENDING table, 127, 130
DBCLOB function, 23
DBTIMEZONE function, 43
DECIMAL function, 18, 21
DECODE function, 7, 94
DEFAULT keyword, 72
DELETE statement, 35-39
deleting from
partitions, 37
results of table joins, 39
views and subqueries, 37
merging data, 91
returning deleted data, 38
subqueries and, 121
vs. TRUNCATE TABLE statement, 36
delimiting strings, 88
DESCENDING keyword, 120
direct-path inserts, 74
DISTINCT keyword, 56, 114
distributed transactions
aborting, 130
failure of, 127
identifying, 130
naming, 126
starting, 128
dot notation, qualifying table/view names using, 116
DOUBLE function, 21
DOUBLE_PRECISION function, 21
dual table (Oracle), 109
duplicate rows, eliminating from result set, 115
E[ Top ]
ELSE clause, used in multi-table inserts, 76
ending transactions, 129
equi-joins (see inner joins)
escape character ( \ ), 101, 104
MySQL regular expressions and, 105
escape sequences, string-literal (MySQL), 89
evaluation order for union operations, 134
example data, 4-6
EXCEPT ALL operation, 135
EXCEPT operation, 134
EXISTS predicate, 97, 99
EXP function, 48
expressions, using in SELECT statements, 109
EXTRACT function, 10, 29
extracting substrings from strings, 52
F[ Top ]
FIRST vs. ALL, 76
flashback queries, 40, 119
FLOAT function, 21
FLOAT values
converting to character strings (SQL Server), 28
writing literals, 89
floating-point numbers and NANVL function, 49
floating-point types supported by Oracle Database 10g, 17
FLOOR function, 48
format elements (Oracle)
datetime, 11-13
numeric, 15
FORMAT function, 34
format specifiers (MySQL), 32
FROM clause, 116-119
collections in, 118
DELETE statement and, 39
joining rows using, 77
partitions/subpartitions in, 118
SELECT statement, 107-114
subqueries in, 117
table aliases in, 116
in UPDATE statements, 140
FROM_DAYS function, 30
FROM_UNIXTIME function, 31
full outer joins, 86
functions, 41-56
aggregate, 56
conversion, 9-34
date, 42-47
grouping, 56-65
math/numeric, 48
scalar, 41-56
string, 50-56
summarizing, 61-65
trigonometric, 50
G[ Top ]
GETUTCDATE( ), 44
GREATEST function, 56
Gregorian dates, functions used for converting, 30
GROUP BY clause, 57
extensions to
Oracle, 61-64
SQL Server, 64
HAVING clause and, 60
reducing GROUP BY lists, 59
GROUP_ID function, 64
GROUPING function
Oracle, 63
SQL Server, 65
GROUPING SETS operation, 63
GROUPING_ID function, 64
H[ Top ]
HAVING clause, 60
predicates and, 96
HEX function, 34
hierarchical queries, 66-72
HOUR function, 18, 29
HOUR keyword (MySQL), 47
HOUR_MINUTE keyword (MySQL), 47
HOUR_SECOND keyword (MySQL), 47
I[ Top ]
identifiers and case/punctuation issues, 111
IF function, 96
IFNULL function, 95
IN predicate, 97, 99, 119
indexes on columns, preventing use of, 41
INITCAP function, 55
INNER JOIN keywords, 79
inner joins, 77, 79-83
join precedence, 80
natural joins, 82
SQL 1992 syntax, 77, 79
writing, with USING clause, 81
INSERT ALL statement, 75
INSERT statement, 72-76
direct-path inserts (Oracle), 74
multi-row inserts, 73
multi-table inserts, 75
single-row inserts, 72
subqueries and, 121
subquery inserts, 73
INSTR function
MySQL, 51
Oracle, 51
INSTRB/INSTR2/INSTR4 functions, 51
INTERSECT ALL operation, 137
INTERSECT operation, 136
INTERVAL DAY TO SECOND literals, 90
INTERVAL YEAR TO MONTH literals, 90
intervals
adding to/subtracting from dates, 46
converting to interval types, 13
keywords for numeric intervals, 47
IS [NOT] NULL comparison operator, 97
isolation levels for transactions, 127-129
J[ Top ]
JOIN clause, 77-87
joins, 77-87
Cartesian joins, 77-79
concept of, 77
CONNECT BY queries and, 69
cross joins, 79
double-FROM clauses and, 39
grouping before the join, 60
inner (see inner joins)
join conditions, 78, 79
natural joins, 82
non-equi-joins, 83
outer (see outer joins)
reversing join order, 81
JULIAN_DAY function, 18, 21
L[ Top ]
LAST_DAY function, 45
LCASE function, 56
LEAST function, 56
left outer joins, 84
left side of comparison, specifying multiple values on, 98
LEN function (SQL Server), 53
LENGTH function, 53
LENGTHB/LENGTH2/LENGTH4 functions, 53
LEVEL function, 71
LIKE predicate, 97, 100
REGEXP predicate used in MySQL, 105
SQL Server version, 104
LIKEC/LIKE2/LIKE4 predicates, 101
literals, 87-91
LN function, 48
LOB (large object), converting to CLOB, 18
LOCALTIMESTAMP function, 43
LOCATE function, 51
LOG function, 48
LOG10 function, 48
LONG RAW values, converting to CLOB, 18
LONG values, converting to BLOB, 18
LONG_VARCHAR function, 23
loops in hierarchical data, 70
LOWER function, 55
LTRIM function, 54
M[ Top ]
math functions, 48
MAX function, 57
MEDIAN function, 57
MERGE statement, 91
metacharacters, regular-expression
MySQL, 105
Oracle, 104
SQL Server, 105
Michigan tourist attractions, tables for, 4
MICROSECOND function, 18
MIDNIGHT_SECONDS function, 18
MIN function, 57
MINUS operation (Oracle), 134
MINUTE function, 18, 29
MINUTE keyword (MySQL), 47
MINUTE_SECOND keyword (MySQL), 47
MOD function, 49
MONEY values, converting to character strings, 28
MONTH function, 18, 27, 29
MONTH keyword (MySQL), 47
MONTHNAME function, 18, 29
MONTHS_BETWEEN function, 46
multi-row inserts, 73
multi-table inserts, 75
MySQL
autocommit mode, disabling/enabling, 125
current date/time functions, 44
date functions, 47
datetime conversions, 29-33
functions for NULLs, 95
numeric conversions, 34
regular expressions, 105
restrictions involving CAST function, 10
search functions, 51
string-literal escape sequences, 89, 101
substring functions, 53
transactions, starting, 128
trigonometric functions, 50
N[ Top ]
naming transactions, 126
NANVL function, 49
national character sets
converting to, 18
text literals and, 88
NATURAL JOIN keywords, 82
NCLOB, converting text data to, 18
nested table columns (Oracle), 118
NEXT_DAY function, 45
NLS_DATE_FORMAT parameter, 14
NLS_SESSION_PARAMETERS view, 14
NLS_SORT parameter, 103
NLS_TIMESTAMP_FORMAT parameter, 14
NLS_TIMESTAMP_TZ_FORMAT parameter, 14
NOCYCLE keyword, 70
noncorrelated subqueries
factoring out with WITH clause, 122
in FROM clause, 117
non-equi-joins, 83
NOT EXISTS predicate, 99
NOT LIKE predicate, 100
NOT NULL constraint, 85
NOW( ), 44
NULLIF function, 95
NULLs, 92-96
CASE expressions and, 93
DB2 functions for, 95
IN predicates and, 100
inserting into columns using NULL keyword, 73
interpreting in outer joins, 85
MySQL functions for, 95
Oracle functions for, 94
predicates for, 92
SQL Server functions for, 95
numeric conversions
DB2, 21-23
MySQL, 34
Oracle, 15-17
SQL Server, 27
numeric functions, 48
numeric literals, 89
NUMTODSINTERVAL function, 15
NUMTOYMINTERVAL function, 15
NVL/NVL2 functions, 94
O[ Top ]
OCT function, 34
ON clause
join precedence, 81
SQL92 inner join syntax, 79
vs. USING clause, 81
optional tables in outer joins, 84-87
Oracle
collections in FROM clause, 118
CONNECT BY syntax, 67-72
current date/time functions, 42
date functions, 45
datetime conversions, 11-15
datetime interval literals, 90
deleting from partitions, 37
direct-path inserts, 74
flashback queries, 40, 119
functions for NULLs, 94
GROUP BY extensions, 61-64
hierarchical sorts, 69
joins
ON clause vs. USING clause, 81
outer join syntax, 86
merging data, 91
multiple values on the left, 98
multi-table inserts, 75
numeric conversions, 15-17
numeric functions, 48
partitions/subpartitions in FROM clause, 118
recursive queries, writing, 67-72
regular expressions, 102-104
returning inserted values, 74
rounding values, 44
search functions, 51
substring function, 53
transactions, starting, 126
translating characters, 55
trigonometric functions, 50
truncating values, 44
updating partitions, 140
Oracle Database Globalization Support Guide, 14
ORDER BY clause, 120
ascending/descending sorts, 120
CONNECT BY queries and, 69
subqueries in, 121
union queries and, 132
order of evaluation for union operations, 134
order of precedence for join operations, 80
outer joins, 77, 83-87
full outer joins, 86
interpreting NULLs in, 85
left outer joins, 84
required/optional tables in, 84-87
right outer joins, 85
vendor-specific syntax, 86
P[ Top ]
parentheses
evaluation order for union operations, 134
join precedence and, 80
outer joins and, 84
subquery inserts and, 74, 113, 123
partitions
deleting from, 37
in FROM clause, 118
updating, 140
pattern matching
LIKE/NOT LIKE predicates, 100
Oracle regular-expression functions, 102-104
REGEXP predicate (MySQL), 105
percent (%), used for pattern matching, 100, 105
period (.), used for pattern matching, 100, 104, 105
pipe ( | ) regular-expression metacharacter, 104, 106
pivot tables, 6
plus ( + ) regular-expression metacharacter, 104, 106
identifying optional tables, 87
string concatenation operator, 53
precedence for joins, 80
predicates, 96-101
for NULLs, 92
PRIOR function/PRIOR operator, 68, 71
punctuation/case in names, 111
Q[ Top ]
qualifying column names, 108, 113
qualifying table/view names, 116
QUARTER function, 18, 29
queries
flashback, 40, 119
hierarchical, 66-72
union, 132-137
order of evaluation, 134
quoting delimiters (Oracle), 88
quoting identifiers (table/column names), 112
R[ Top ]
READ COMMITTED isolation level, 127-129
read-only transactions, 127
read-write transactions, 127
REAL function, 21
REAL values, converting to character strings, 28
recursive queries, 66-72
recursive WITH clause (ANSI/ISO), 66
regexes (see regular expressions)
REGEXP predicate, 97, 105
REGEXP_INSTR function, 51, 102
REGEXP_LIKE function, 97, 102
REGEXP_REPLACE function, 52, 102
REGEXP_SUBSTR function, 52, 102
regular expressions, 102-106
metacharacters, 104-106
MySQL, 105
Oracle, 102-104
SQL Server, 104
REMAINDER function, 49
REPLACE function, 52
required tables in outer joins, 84-87
result sets, 106
column names
controlling, 110
specifying, 107
combining, using UNION keyword, 132
duplicate rows, eliminating, 114
finding rows in common, using INTERSECT, 136
RETURNING clause
deleted data, returned by, 38
inserted values, returned by, 75
updated data, returned by, 140
right outer joins, 85
RLIKE comparison operator, 97
ROLLBACK statement
aborting to transaction savepoints, 131
aborting transactions, 130
deleting data and, 35
warning about TRUNCATE statement and, 36
ROLLBACK TRANSACTION statement, 130
ROLLUP operation
Oracle, 61
SQL Server, 64
ROUND function, 44, 49
rows
deleting from tables, 35
eliminating duplicates, 115
EXISTS/NOT EXISTS predicates, 99
filtering, using HAVING, 60
grouping into sets, 56-61
inserting into tables, 72-76
INTERSECT operation, 136
joining tables, 77-87
loops in hierarchical data, 70
MERGE statement, 91
retrieving all, 114
returning information about deleted rows, 38
summarizing data, 61-65
TRUNCATE TABLE vs. DELETE, 36
UNION/UNION ALL keywords, 132
updating data, 137-141
WHERE clause, 119
RTRIM function, 54
S[ Top ]
savepoints, aborting to, 131
scalar functions, 41-56
schema names, qualifying table names by, 114
SCNs (system change numbers), 40
searched CASE expressions, 8
searching strings, functions for, 51
SEC_TO_TIME function, 31
SECOND function, 18, 29
SECOND keyword (MySQL), 47
seconds since 1-Jan-1970, converting datetimes into, 31
seconds-of-the-day, converting datetimes into, 31
SELECT statement, 106-120
column names in result sets
controlling, 110
specifying, 107
embedding subqueries in, 113
expressions, using in, 109
FROM clause and, 116-119
GROUP BY queries and, 59
ORDER BY clause and, 120
recursive WITH clause and, 66
using subqueries in, 121-124
subquery inserts in, 74
union queries and, 132-137
WHERE clause and, 119
SESSIONTIMEZONE function, 43
SET AUTOCOMMIT=0 command, 35, 125
SET DATEFORMAT command, 23
SET expressions and UPDATE FROM clause, 140
SET IMPLICIT_TRANSACTIONS command, 125
SET SESSION TRANSACTION statement, 129
SET TRANSACTION statement, 126-129
SET XACT_ABORT statement, 131
SIGN function, 49
SIN function, 50
single-byte/multi-byte characters, converting between, 18
single-row inserts, 72
SINH function, 50
SMALLINT function, 21
SMALLMONEY values, converting to character strings, 28
SOME keyword and comparison operators, 97
sorts
ascending/descending, 120
hierarchical, 69
special registers, 43
SQL 1992 inner join syntax, 77, 79
SQL 1999 COALESCE, 94
SQL 2003 MERGE, 91
SQL Server
autocommit mode, disabling/enabling, 125
COMMIT TRANSACTION statement, 129
current date/time functions, 44
date functions, 46
datetime conversions, 23-27
double-FROM clauses, 39
functions for NULLs, 95
GROUP BY extensions, 64
numeric conversions, 27
regular expressions, 104
ROLLBACK TRANSACTION statement, 130
search functions, 51
transactions, starting, 127
trigonometric functions, 50
UPDATE FROM clause, 140
updating through a cursor, 139
START TRANSACTION statement (MySQL), 128
START WITH clause, 68
starting transactions, 126-129
STDDEV function, 57
string functions, 50-56
string-based interval formats (MySQL), 47
string-literal escape sequences (MySQL), 89, 101
strings
changing case of, 55
concatenating, 53
extracting substrings from, 52
finding length of, 53
replacing text in, 52
searching, 51
translating characters in, 55
trimming unwanted characters from, 54
subpartitions in FROM clause, 118
subqueries, 121-125
deleting from, 37
embedding in SELECT statements, 113
EXISTS predicates and, 99
factoring out with WITH clause, 122-125
in FROM clause, 117
generating new values from, 138
IN predicates and, 99
inserting rows using, 73
recursive WITH (ANSI/ISO), 66
specifying multiple values on the left, 98
unconditional multi-table inserts, 75
updating, 139
SUBSTR/SUBSTRB/SUBSTR2/SUBSTR4 functions, 52
SUBSTRING function, 53
SUM function, 57
summarizing data, 61-65
SYS_CONNECT_BY_PATH function, 72
SYS_EXTRACT_UTC function, 43
SYSDATE function (Oracle), 43
SYSDATE( ) (MySQL), 44
system change numbers (SCNs), 40
SYSTIMESTAMP function, 43
T[ Top ]
table aliases, 35
flashback queries and, 40
in FROM clauses, 116
TABLE function, 118
table joins (see joins)
tables
deleting from results of joins, 39
deleting rows from, 35-37
inserting rows into, 72-76
joining, 77-87
nested columns (Oracle), 118
partitions/subpartitions in FROM clause, 118
qualifying column names, 113
qualifying names, 116
required/optional in outer joins, 84-87
returning all columns from, 107
selecting data from, 106-114
TRUNCATE TABLE vs. DELETE, 36
updating data, 137-141
TAN function, 50
TANH function, 50
targets of inserts, 73
text data, converting to NCLOB, 18
text literals, 88
TIME function, 18
TIME_FORMAT function, 32
TIME_TO_SEC function, 32
timestamp formats
DB2, 18-21
Oracle, 14
Unix timestamps and MySQL, 31
TIMESTAMP function, 18
TIMESTAMP keyword, 40
timestamp literals, 90
TIMESTAMP WITH TIME ZONE value, 42, 43
TIMESTAMP_FORMAT function, 18
TIMESTAMP_ISO function, 18
TIMEZONE_ABBR element (Oracle), 11
TIMEZONE_HOUR element (Oracle), 11
TIMEZONE_MINUTE element (Oracle), 11
TIMEZONE_REGION element (Oracle), 11
TO_BINARY_DOUBLE function, 15-17
TO_BINARY_FLOAT function, 15-17
TO_CHAR function, 11-15, 15-17, 18
TO_CLOB function, 18
TO_DATE function, 11-15, 18
TO_DAYS function, 30
TO_DSINTERVAL function, 11-15
TO_LOB function, 18
TO_MULTI_BYTE function, 18
TO_NCHAR function, 18
TO_NCLOB function, 18
TO_NUMBER function, 15-17
TO_SINGLE_BYTE function, 18
TO_TIMESTAMP function, 11-15
TO_TIMESTAMP_TZ function, 11-15
TO_YMINTERVAL function, 11-15
tourist attractions, tables for, 4
transactions, 125-131
aborting, 130
aborting to savepoints, 131
autocommit mode, 35, 125
ending, 129
isolation levels for, 127-129
naming, 126
starting, 126-129
TRANSLATE function, 55
trigonometric functions, 50
TRIM function, 54
trimming unwanted characters from strings, 54
TRUNC function, 44, 49
TRUNCATE function (MySQL), 49
TRUNCATE TABLE statement vs. DELETE statement, 36
U[ Top ]
UCASE function, 56
unconditional multi-table inserts, 75
underscore ( _ ), used for pattern matching, 101, 105
Unicode characters
LIKEC predicate, 101
text literals and, 88
Unicode code points
INSTR4 function, 51
LENGTH4 function, 53
LIKE4 predicate, 101
SUBSTR4 function, 53
Unicode code units
INSTR2 function, 51
LENGTH2 function, 53
LIKE2 predicate, 101
SUBSTR2 function, 53
UNION ALL keyword, 133
UNION keyword, 132
union queries, 132-137
order of evaluation, 134
Unix timestamp format, converting to/from, 31
UNIX_TIMESTAMP function, 31, 44
UPDATE FROM clause, 140
UPDATE statement, 137-140
returning updated data, 140
subqueries and, 121
updating
partitions, 140
through cursors, 139
views/subqueries, 139
UPPER function, 55
USING clause
writing inner joins with, 81
writing outer joins with, 84
UTC date/time, returning
DB2, 43
Oracle, 43
SQL Server, 44
V[ Top ]
VALUES clause, 72
VARCHAR function, 18, 23
VARCHAR_FORMAT function, 18
VARIABLE command (SQL*Plus), 75
VARIANCE function, 57
views
deleting from, 37
inserting data into, 73
qualifying names, 116
updating, 139
W[ Top ]
WEEK function, 18, 29
WEEK_ISO function, 18
WEEKDAY function, 29
WHEN clause
conditional multi-table inserts, 76
searched CASE expressions, 8
simple CASE expressions, 8
WHERE clause, 119
CONNECT BY queries and, 69
deleting data, 35, 39
HAVING clause and, 61
inner joins and, 80
join conditions and, 77
merging data, 91
predicates and, 96-101
UPDATE FROM clause, 141
updating rows, 138
WITH clause
correlated subqueries, factoring out, 123-125
noncorrelated subqueries, factoring out, 122
recursive, 66
WITH MARK clause, 128
Y[ Top ]
YEAR function, 18, 27, 29
YEAR keyword (MySQL), 47
YEAR_MONTH keyword (MySQL), 47
YEARWEEK function, 29
Return to SQL Pocket Guide
|
