Index_C

C

calculating set of values

average (AVG), 343

column values, adding (SUM), 37, 339–340

described, 338

exclusion of NULL values, 342

functions listed by database

DB2 UDB 8.1, 339

MS SQL Server 2000, 339, 719–720

Oracle 9i, 339, 704–705

GROUP BY clause, 264

minimum and maximum (MIN AND MAX), 344

quantity for all orders, 265

records in table or view (COUNT), 340–342

summary tables, 105

views, joining, 119–120

call-level interface. See CLI

capacity, sufficient, need for, 2

cardinality, set theory operations, 784–785

caret wildcard operator (^), 378

Cartesian product

inner joins, 289–290

old syntax, 290

set theory operations, 783–784

SQL99, 289–290

CASCADE automatic changes

foreign key, 92–93

privileges, revoking, 425, 429

rows, deleting, 201

table constraints, 90

tables, dropping, 157

C/C++ programming language

data types, corresponding, 532

dynamic query, 551–552

dynamic SQL, executing, 544, 546–547

error handling, 538–539, 540

multirow query results, storing with cursors, 537

OCI, 575–578

SELECT INTO statement, 535

XML, developing, 593

CD-ROM, back of the book

applications, 611–612

author-created materials, 610–611

eBook, 612

system requirements, 609

troubleshooting, 612

Windows, using with, 609–610

changes

all or none made, 217

automatic (CASCADE)

foreign key, 92–93

privileges, revoking, 425, 429

rows, deleting, 201

table constraints, 90

tables, dropping, 157

committing, 82

tablespaces, 166

character set

conversion functions, 355–357

default, 208

character strings

columns, collating sequence, 87–88

fixed-length, 44–45

functions, replicating (REPLICATE and REPEAT), 325–326

literals, 51

national, 45–51

varying length, 45

characters

ANSI/ISO code, 549

columns, changing

DB2 UDB 8.1, 152

Oracle 9i, 148

encrypting, 443

finding and replacing within string (TRANSLATE), 326–327

length of expression, 308

number of (LENGTH), string functions, 322–323

Oracle 9i functions, listed, 697–698

position, determining (CHARINDEX, INSTR, LOCATE, and POSSTR), 318–320

programming languages, corresponding, 532

check constraints

columns

described, 85–86, 186

dropping, 149, 155, 201

updating, 194

data entry, 199

deferring, 93–94

domain, 457

information, viewing

DB2 UDB 8.1, 466

MS SQL Server 2000, 471

listing, 460

mode, setting, 208

name, maximum length, 8

relational database design, 635–636

security, 438–439

tables

CHECK, 88

copy, creating new table as, 101–104

creating, 149–150, 152–153

deferring, 93–97

describing, 457

disabling and enabling, 150–151

example, 88–89

FOREIGN KEY, 88, 89–90

identity clause, 100–101

INITIALLY DEFERRED, 97

modifying, 148, 149

ON COMMIT clause, 97

physical properties, 97–100

PRIMARY KEY, 88

REFERENCES, 89–90

removing, 150, 153, 156, 157

restrictions, 89–93

summary tables, 105

temporary, 97

UNIQUE, 88

viewing, 456

without, finding, 474–475

child object

automatic changes (CASCADE), 90, 92–93

B-Tree indexes, 106

circular references, 126

classes

approach, described, 601

news, created from old, 601–602

clause, populating tables (INSERT)

described, 178–179

inserting value for specified column, 179–181

C2-level security certification, 451, 453–454

CLI (call-level interface)

DB2 UDB 8.1, 571–573

defined, 30

MS SQL Server 2000, 683–686

Oracle 9i, 557–562

client application

data, accessing from, 39

described, 28

explicit transactions, 218

CLOB (large, nondatabase-structured objects)

described, 48, 50

encrypting, 443

cloning tables, 101–104

Cloudscape, 767

CLP (Command Line Processor) compiling tool, 512

CMEA, 445

COBOL

data types, corresponding, 532

development, 14

dynamic queries, 545, 547, 552–553

error handling, 539, 540

multirow results, storing with cursors, 537

programming delimiter, 531

SELECT INTO statement, 536

CODASYL (Conference on Data Systems Languages), 14, 23

Codd, Dr. Edward Frank (IBM researcher), 22, 597

code reusability, 365, 490

collating sequence, table columns, 87–88

collections

OOP, 605

reference type, 68

set and list types, 68–69

colon (:)

format templates, 349

host variables, 531

color bit mask, 390

columns

accessible

describing, 456, 458, 460

listing, MS SQL Server 2000, 478

characters, displaying, DB2 UDB 8.1, 213

collating sequence, 87–88

concatenating when updating subquery, 195

constraints, 85–86, 617

data dictionary objects, 465

DB2 UDB 8.1 functions, listed, 715

default values, 86–87

defined, 15, 80, 85

domains

accessible, describing, 457

columns, 85

fully qualified names, 283

integrity, 635

integrity constraints, 438

RDBMS objects, creating, 130

SQL99, 130

indexed, 108

information

listing, 473–474

viewing, 471

inserting, 147–148, 152, 154

joining

aggregate functions, 119–120

creating, 113

by one than one, 286

restricting, 437

updates with subqueries, 197

maximum name length restrictions, 8

modifying, 148, 154–155, 194

multiple, SELECT statement

all columns, 242

all columns plus an extra, 242–243

distinct values, 243–245

several columns, 241–242

names, INFORMATION_SCHEMA, 468–469

privileges, information about, 466

removing, 149, 155–156

sorting by more than one, 268–269

storing, 74

synchronizing, 203–204

update triggers, 524

values

adding (SUM), 339–340

setting all to NULL, 194

vertical restriction, 436

view

creating, 115

definition, listing, 473

limiting, 421

ordering display, 36

COM/DCOM (Microsoft Distributed COM), 594

comma (,)

format templates, 349

multiple indexes, dropping, 161

Oracle 9i updates, 198

command line interface, 311, 683–686

Command Line Processor compiling tool. See CLP

comments, 94

commits

rows, deleting all (TRUNCATE), 205

table changes, effecting, 82

transactions, 218–225

comparison

operators

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

SQL, listed, 386–389

subqueries

choosing values (ANY and ALL), 261–262

returning one (MAX), 262

compatibility, 40–42

complement, set theory operations, 782

complex data types, overview, 60–61

compliance, SQL99, 753–765

compound operators (AND and OR), 255–256

computation, sorting by, 269–270

computer programs, storing. See binary strings

concatenating

columns when updating subquery, 195, 214

product id with blank spaces, 248

string functions (CONCAT), 317–318

concatenation operator (||)

described, 248, 368

precedence, 383

concurrency control mechanism

deadlocks, 233–234

described, 228–229

modes, 229–233

releasing (CLOSE), 502

conditional execution

DB2 UDB, 497–498

PL/SQL, 497

Transact-SQL, 498

conditions, precedence, 383

Conference on Data Systems Languages. See CODASYL

configuration functions, MS SQL Server 2000, 480–481, 722

conflicts, resolving

deadlocks, 233–234

privileges, 413

connection

embedded SQL, 533–535

ending, 541

JDBC driver to Oracle 9i, 570–571

number, MS SQL Server 2000, 480–481

opening and closing, 559

Oracle 9i, 208

switching, 208

consistency rule, 217, 398

constraints

columns

described, 85–86, 186

dropping, 149, 155, 201

updating, 194

data entry, 199

deferring, 93–94

domain, 457

information, viewing

DB2 UDB 8.1, 466

MS SQL Server 2000, 471

listing, 460

mode, setting, 208

name, maximum length, 8

relational database design, 635–636

security, 438–439

tables

CHECK, 88

copy, creating new table as, 101–104

creating, 149–150, 152–153

deferring, 93–97

describing, 457

disabling and enabling, 150–151

example, 88–89

FOREIGN KEY, 88, 89–90

identity clause, 100–101

INITIALLY DEFERRED, 97

modifying, 148, 149

ON COMMIT clause, 97

physical properties, 97–100

PRIMARY KEY, 88

REFERENCES, 89–90

removing, 150, 153, 156, 157

restrictions, 89–93

summary tables, 105

temporary, 97

UNIQUE, 88

viewing, 456

without, finding, 474–475

conversions

binary to decimal numbers, 773

constraints, deferring, 97

data type to character data type (TO_CHAR, CHAR, and STR), 324

decimals to binary numbers, 773–774

functions

column values, constraining, 186

described, 308, 344–345

listed by database, 345

numeric data into fixed-length string (CHAR), 318

Oracle 9i, listed, 700–701

specific types, by database, 357–358

implicit, 117

string expressions to lowercase or uppercase (LOWER and UPPER), 323–324

Coordinated Universal TIme. See UTC

copying tables, 101–104

correlated queries, 303–304

correlated subqueries, 263

corruption, data, 4

cost, total ownership, 6

countable numbers. See integers

credit status, customers, listing with, 360–361

cross join

inner joins, 289–290

old syntax, 290

set theory operations, 783–784

SQL99, 289–290

currency. See money

cursors

dynamic, 550

functions, listed, 720

multirow query results, storing with, 536–537

programming

current row, retrieving (FETCH), 501–502

deallocating memory, releasing locks, and making result set undefined (CLOSE), 502

described, 500

examples, 502–505

query, executing and identifying result set (OPEN), 501

SELECT statement, associating (DECLARE), 500–501

stored procedures, MS SQL Server 2000, 475

customers

aliases, checking, 364

average order, 343

credit status, listing with, 360–361

inactivating all, 194

missing elements, listing by

orders, 301–302

salesman, 270–272

telephone number, 258–259

multiple addresses, listing, 254

orders placed by, 340–342

selecting only matching, 387–389

sequential IDs, generating for columns

accessing, 136–137, 137–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

identity clause, table constraints, 100–101

information, listing, 467

inserting, 189

listing accessible, 460, 461, 462

order of values, guaranteeing, 136

syntax, 134

sorting in alphabetical order, 267–268

telephone numbers, retrieving with order, 303–304

cycling, Oracle 9i sequences, 135