Oracle Database 10g SQL (Osborne ORACLE Press Series)

S

salary_grades table, 19

sample_files directory, 428

SAVE command, 146

savepoints, 260 “262

schemas, 2

creating, 8 “19

defined, 26

SCN query flashbacks, 268 “269

SCN (system change number), 266, 268 “269

SCOPE IS clause, 372

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 ON privilege, 278

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, 386

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 DEFINE command, 154

SET DESCRIBE DEPTH command, 371

SET ECHO OFF command, 158

SET LINESIZE command, 151

set methods (JDBC), 485 “486

SET operator, 422

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 command, 154

SET VERIFY OFF command, 154, 158

set_example() procedure, 422

setNULL() method (JDBC), 511

SHOW ERRORS command, 351 “352

SIGN() function, 7175 “76

SIGNTYPE data type, 556

simple loops , 336

simple views, 321 “322

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

SMALLINT data type, 553

sorting groups using ORDER BY, 95 “96

sorting rows and columns , 43 “44

SOUNDEX() function, 70

SPOOL command, 147 “148

SPOOL OFF command, 147 “148

SQL DDL statements, 3, 10 “19, 498

SQL format, defined, 515

SQL operators, 39 “42

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 tuning, 529 “550

SQL Tuning Advisor, 550

SQL Tuning Set, 550

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 window, 6

SQL*Plus Worksheet, 8 “9

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

SQLAccess Advisor, 550

SQLException class (Java), 498 “499

SQRT() function, 71, 76

START command, 146

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

statspack package, 549

STDDEV() function, 92

STORE AS clause, 396

store schema (example)

creating, 8 “19

information held in, 13

tables, 13 “19

stored subprograms, 352

store_schema.sql, 9 “10, 183, 329, 353

String columns with null values, 35

STRING data type, 556

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

subtype, 382

sum, reporting on, 229 “230

SUM() function, 92, 230 “231

Sun Microsystems Java SDK, 476

supertype , 382 “383

supplied year, 114

synonyms, 283

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

System R, 3

SYSTEM tablespace, 272

SYSTIMESTAMP() function, 128 “129