Index_O

O

object-level privileges

described, 415–416

granting

DB2 UDB 8.1, 417–420

MS SQL Server 2000, 420–421

Oracle 9i, 416–417

revoking, 424–427

object-oriented approach. See OOP

objects

accessible, listing, 460, 461

adding, 413

altering

aliases and synonyms, 164–165

ALTER statements cross-reference, 169–170

indexes, 158–160

schemas, 165

sequences, 167–169

tables, 145–156

tablespaces, 166–167

views, 161–163

creating

aliases and synonyms, 121–125

CREATE statement cross-reference, 141–143

database links (Oracle 9i), 140–141

domains, 130

indexes, 106–112

materialized views, 138–140

schemas, 125–129

sequences, 133–138

tables, 79–105

tablespaces and filegroups, 130–133

views, 112–121

database and object-relational database models, 19

destroying

DROP statements cross-reference, 171–172

indexes, 160–161

tables, 156–158

views, 163–164

information about, MS SQL Server 2000, 479–480

maximum name length restrictions, 8

OOP, 601

Oracle 9i, 69–70

reference functions, 708

stored procedures, 475

synonyms, 124

Ocelot SQLDBMS, 24, 769

OCI (Oracle Call Interface), 573–578

ODBC (Microsoft Open Database Connectivity)

Data Source Name, confkguring on Windows, 563–565

described, 562–563

introduction, 557–558

ODMG standard, 606

Office. See MS Office

OLAP

DB2 UDB 8.1, 599

described, 39–40, 596–598

MS SQL Server 2000, 599–600

Oracle 9i, 599

queries, 598

OLTP (online transaction processing), 8, 596

ON and USING clauses, inner joins, 280–281

ON COMMIT clause, table constraint, 97

one-step execution, DML and DDL, 544–545

OO40 (Oracle Objects for OLE), 578–580

OOP (object-oriented approach)

abstract data types, 604

collections and arrays, 605

databases using, 68, 605–608

DB2 UDB 8.1, 603–604

described, 17, 600

encapsulation, 601

identity, 602

inheritance, 601–602

MS SQL Server 2000, 604

objects and classes, 601

Oracle 9i, 602–603

polymorphism, 602

SQL99 standard, 25

operating system

accounts, 430–431

compatibility, 40–42

security, 432

SQL standards, 7, 41

operations, restricting. See views

operations set theory

cardinality, 784–785

cartesian product, 783–784

complement, 782

difference, 783

intersection, 782

multielement operands, 785

multiple, 784

union, 781

operators

arithmetic, 367–372

assignment, 385–386

bitwise, 389–392

Boolean expressions, 372–382

comparison

SQL, listed, 386–389

WHERE clause (horizontal limits, setting), 253–255

compound (AND and OR), 255–256

equal (=), 212

greater than or equal to (=>), 256

less than or equal to (<=), 256

logical

ALL, 373

AND, 381

ANY | SOME, 373

BETWEEN <expression> and <expression>, 373–375

EXISTS, 376–377

IN, 375–376

LIKE, 377–381

listed, 372

NOT, 381–382

OR, 382, 775–776

not equal (<>), 212

precedence, 382–385

user-defined, 392

wildcard, 379

optimistic concurrency, 229

optimizer, 105, 305, 528

options, session, listing, 215–216

OR bitwise operator (|), 390

OR logical operator

described, 382, 775–776

IN operator, emulating, 375–376

Oracle

first commercial relational database, 23

market share, 6

Oracle Administration and Management (Michael Ault), 209

Oracle Call Interface. See OCI

Oracle 9i

ACME database, 627, 689–690

aliasing, 247

ALTER statements, listed, 169–170

blank spaces in string

indicating, 319

removing, 328

bytes, checking number, 357

check constraint error message, 187

COBOL, connecting, 534

columns

constraints, 85

selecting all, 242–243

sorting, 269

concurrency control mechanism, 228

connection, establishing, 208

data dictionary

described, 458–459

metadata, 464–466

SQL standards, 462–464

static view prefixes, 460

structure, 459–462

views, selected, 460–462

data encryption, 442

data types

approximate numbers, 58

binary files, read-only access (BFILE), 74

binary strings, 52

character strings, 49

conversion, implicit, 186, 346, 365

date and time, 64

exact numbers specification, 54

excluding results of first query in second (MINUS), 275, 277

listed, 69–71

national character strings, 48

numeric, 56, 57, 59

database auditing, 447–448

database links, 140–141

date and time

arithmetic, 334, 370

data types, 62–63, 64–66

formatting, 349, 350–352

months between two dates, 337

time zone, adjusting, 331

DML, MS SQL Server 2000 versus, 205

functions

aggregate, 339, 704–705

analytic, listed, 706–708

character, listed, 697–698

conversion, 345, 700–701

data type specific conversions, 357–358

date and time, 329

datetime, listed, 698–699

finding NULL values (NVL, NL2, and ISNULL), 363–364

grouping, 309

integer remainder, calculating, 371

NULL values, handling (COALESCE and NULLIF), 362

numeric, 310, 696

object reference, 708

scalar, 709–715

single-row, 702–703

string, 316–317

indexes

creating, 109–110

dropping, 160–161

rebuilding, 159–160

renaming, 159

tables, 99

inner join (USING option), 280–281

installation

general information, 637–638

on Linux, 653–658

on SUN Solaris, 648–653

on Windows NT/2000/XP, 638–648

JDBC driver, connecting, 570–571

keywords, reserved, 741–742

LIKE operator, 380

matching results from two queries, 274–275

materialized view, 138–139, 251

maximum name length restrictions, 8

modifying existing data (UPDATE), 198

number format templates, 352–353

objects, 69–70, 141–143

OLAP, 599

OOP, 602–603

operating systems, 41

operators, 368–369, 372, 392

outer joins, 294–295

output combining product number, price, and description in special format, 321

parameters, passing, 496

physical object, lack of, 245–246

PL/SQL procedural language

caution about renaming tables, 151

conditional execution, 497

cursors, programming, 503

described, 28

error handling, 505–506

loops, 499

parameters, passing, 493

repeated execution, 499

stored procedures, creating, 508

triggers, creating, 521–522

user-defined functions, 515–518

variables, 490

XML, developing, 593

precompiler for C/C++, 534

quantities, average, 266

removing data (DELETE), 202

revoking, 422–423, 424–425

right outer join, 300

roles, 427–430

rows

deleting all (TRUNCATE), 204–205

inserting and updating (MERGE), 202–204

unique addresses (ROWID), 73

schemas, 126–128

security

object-level privileges, 416–417, 424–425

operating system, 432

system-level privileges, 405–411, 422–423

SELECT statement, modifying output (DECODE), 360–361

sequences

accessing, 136–137

altering, 168

cycling, 135

dropping, 169

order of values, guaranteeing, 136

syntax, 134

values, caching, 136

SQL99 compliance, 753–765

stored procedures, 509–511

substitutions based on NULL, 364

synonyms

destroying, 164

objects, 122, 124

public versus private, 123

system requirements, 638–639

tables

altering, 146–151

constraints, 90, 91–92, 94

creating, 80–81

dropping, 157

indexes, 99

nested, 70–71

populating (INSERT), 188–189

temporary, 83

tablespaces

described, 131

dropping, 167

removing, 166

transactions

committing, 218–219

isolation levels, 225–226

locking modes, 229–231

multiple databases, 224

ROLLBACK, 221

rolling changes back to specified point (SAVEPOINT), 222–223

Unix/Linux, installing ACME database, 690–691

user

creating, 399–401

defining, 400

utilities

iSQL*Plus, 680

SQL*Plus, 675–679

SQL*Plus Worksheet, 679–680

varying arrays, 71

views

altering, 162

creating, 114–116

dropping, 163

Oracle Objects for OLE. See OO40

Orange Book, 453

order of values, guaranteeing, 136

orders

average by customer, 343

biggest, 344

calculating quantity for all, 265

customers without, 301–302

management system database, 9

matching values (EXISTS), 377

minimum and maximum amounts for discount, 284–286

payment terms, 297

placed by customers, total, 340–342

sequential numbers, generating for columns

accessible, listing, 460, 461, 462

accessing, 136–138

altering, 168

ascending and descending, 135

caching, 136

collating, 87–88

creating, 100, 133–138

cycling, 135

described, 133–134

dropping, 169

generating, 74

information, listing, 467

inserting, 189

order of values, guaranteeing, 136

syntax, 134

table constraints, 100–101

shipped product quantities, summarizing on demand, 139

smallest, 344

snapshot, summarizing automatically, 139–140

status, 110

telephone numbers, retrieving by customer, 303–304

values in table, manipulating, 36

orphaned sessions, 216

OS. See operating system

OSQL, 683–686

outer joins

MS SQL Server 2000, 295–296

Oracle 9i, 294–295

SQL99, 294

output

data, 32–35

input versus, host variables, 531