S
sample_files directory, 428
schemas, 2
creating, 8 “19
defined, 26
SCN query flashbacks, 268 “269
SCN (system change number), 266, 268 “269
search capabilities for strings, 83
searched CASE expressions, 192 “194
searches, using DECODE() in, 190 “191
second, datetime formatting parameters for, 107
security (database), 271 “292
SELECT privileges, 278, 282, 285
SELECT statement, 21, 374 “379, 394, 398
INSERT with, 250
for joined tables, 46
multi-table, 44 “46, 48 “49
to retrieve rows, 314
retrieving rows into a cursor, 337
single-table, 28 “29
using SQL*Plus, 7 “9
on a view, 322
SELECT statement FROM clause. See FROM clause
SELECT statement WHERE clause. See WHERE clause
self joins, 49, 54 “55
in combination with outer joins, 54
using SQL/92, 59
semicolon (;)
PL/SQL statement-ending, 333
SQL statement-ending, 28
in SQL*Plus, 7
sequence of integers, generating, 309
sequences, 309 “315
creating, 309 “311
dropping, 315
getting information on, 311 “312
modifying, 315
populating a primary key using, 314
using, 312 “313
SERIALIZABLE transaction isolation level, 265
serializable transactions, 265 “266
server-side internal driver (JDBC), 481
server-side Thin driver (JDBC), 481
session time zone, 121 “122
SESSIONTIMEZONE() function, 122
set of characters , search for, 83
SET clause in an UPDATE statement, 22, 251
SET DESCRIBE DEPTH command, 371
SET ECHO OFF command, 158
SET LINESIZE command, 151
set methods (JDBC), 485 “486
set operators, 182 “188
combining, 187 “188
order of evaluation, 187 “188
restriction on using, 182
table of, 182
SET PAGESIZE command, 150 “151
SET ROLE command, 290 “291
SET SERVEROUTPUT ON statement, 362
SET TRANSACTION statement, 265
SET VERIFY OFF command, 154, 158
set_example() procedure, 422
setNULL() method (JDBC), 511
SHOW ERRORS command, 351 “352
SIGN() function, 7175 “76
single quote ('), in column values, 250
single row functions, 62 “88
single row operators, 167 “168
single row subqueries, 166 “171
single row subquery error, 170
single-table SELECT statements, 28 “29
slash character (/)
in PL/SQL, 333 “334
in SQL, 145 “147, 153
in Unix/Linux, 10, 433
sorting groups using ORDER BY, 95 “96
sorting rows and columns , 43 “44
SPOOL OFF command, 147 “148
SQL DDL statements, 3, 10 “19, 498
SQL statements
case-sensitivity of, 539
editing, 143 “145
ending, 28, 146
five types of, 3
generating automatically, 162 “163
non-identical, 538 “539
Oracle caching of, 538
parsing, 509
that produce SQL statements, 162 “163
SQL (Structured Query Language), 3 “4, 26
running using Java, 475 “527
using LOBs from within, 431 “434
SQL types (Oracle), 552 “555
SQL*Plus, 26, 141 “163
automatic numbering of lines, 21
editing commands, 143 “145
editing the last SQL statement, 8
executing statements, 8
quitting from, 25
SELECT statement using, 7 “9
SQL statements stored in buffer, 143, 145
starting the command-line version, 5 “6
starting the Windows version, 4 “5
terminating statements, 7, 10
using, 4 “6
variable types, 152
SQL*Plus buffer contents, editing using Notepad, 147
SQL*Plus Log On dialog box, 5
SQL*Plus Worksheet window, 7 “8
SQL/86 ANSI standard syntax for joins, 46
SQL/92 ANSI standard syntax for joins, 46
SQL/92 syntax, joins using, 55 “60
SQLException class (Java), 498 “499
START WITH clause, 198
and CONNECT BY clause, 196
subquery in, 199
Statement class (JDBC), 491
Statement object (JDBC), 487 “488, 491, 522
statement-level triggers, 359
statistical computations , 89
statistics, Oracle 10g gathering of, 542
store schema (example)
creating, 8 “19
information held in, 13
tables, 13 “19
store_schema.sql, 9 “10, 183, 329, 353
String columns with null values, 35
String objects (Java), 486
strings
converting datetimes to, 104 “109
converting to datetimes, 109 “112
converting numbers to, 81 “82
to format the date, 108 “109
to format the time, 109
shifting each character in, 188 “189
using MAX() and MIN(), 91
subgroups, dividing groups into, 218
SUBMULTISET operator, 419 “420
submultiset_example() procedure, 419
subqueries, 165 “179
correlated, 173 “176
DELETE statements containing, 179
errors you might encounter, 170 “171
explained, 166
in a FROM clause, 169 “170
in a HAVING clause, 168 “169
multiple-column, 173
multiple-row, 171 “173
nested, 176 “178
ORDER BY and, 170 “171
related to outer query, 173 “176
single-row, 166 “171
in a START WITH clause, 199
subtypes of, 166
types of, 166
UPDATE statements containing, 178
in a WHERE clause, 166 “168
within parentheses, 166
substitution variables , 152
SUBSTR() function, 70 “71, 467 “468
subtotal rows, filtering out, 210
subtotals, computing, 161 “162
subtraction operator (-), 30 “32
sum, reporting on, 229 “230
SUM() function, 92, 230 “231
Sun Microsystems Java SDK, 476
SYSDATE() function, 7, 105, 119, 386
SYS_EXTRACT_UTC() function, 131
System Identifier (SID), 482 “483
system privileges, 274 “277
commonly used, 275
granting to a user , 274 “275
making use of, 276 “277
revoking from a user, 277
system privileges granted to a role, checking, 287 “288
system privileges granted to a user, checking, 275 “276
SYSTIMESTAMP() function, 128 “129