Index_D

D

Daffodil DB Java, 767

dash (-), 349

dash, double (--), 94

data

client application, accessing from, 39

consistent state rule, 217

corruption, restoring, 391–392

encryption

DB2 UDB 8.1, 442–443

MS SQL Server 2000, 444–446

input/output, 32–35

loads, 97

manipulating while retrieving, 35–36

redundancy, 11, 13

security, 38–39

source information, 561–562

tablespaces, specifying, 98

transforming while retrieving, 37

data entry

errors, limiting, 199

invalid, constraining, 438

Data Manipulation Language. See DML

data, restricting accessible

accessible, describing, 458, 460, 461

altering

DB2 UDB 8.1, 163

MS SQL Server 2000, 163

Oracle 9i, 162

based on another view example, 120

creating

DB2 UDB 8.1, 116–118

MS SQL Server 2000, 117–118

Oracle 9i, 114–116

SQL99, 112–113

DDL statements, syntax, 729

different totals, 120

dropping

DB2 UDB 8.1, 164

MS SQL Server 2000, 164

Oracle 9i, 163

FROM clause, 251

indexes, creating, MS SQL Server 2000, 112

INFORMATION_SCHEMA objects, 466–467

joining, 119–120

listing

DB2 UDB 8.1, 467

MS SQL Server 2000, 473

nested tables, 70

OR REPLACE, simulating, 119

Oracle data directory, 459–462

records in (COUNT), 340–342

security, 38–39, 435–438

stored procedures, 477–478

with subquery, 121

with UNION example, 120–121

updateable, 469

data retrieval. See retrieval, data

Data Source Name. See DSN

Data Transformation Services. See DTS

data types

absence of value (NULL), 75

approximate numbers, 58–59

attribute names, pairing, 68

benefits of using, 43–44

BFILE, 74

binary strings, 51–53

BIT, 74

bitwise operands, compatibility for, 392

BOOLEAN, 73

character strings, 44–51

choosing, 630

collections, 68–69

column values

constraining, 186

displaying, 213

complex, 60–61

constraints, 636

converting

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

described, 344–345

different character sets, 355–357

listed by database, 345

pitfalls, 365

specific types, by database, 357–358

between types (CAST AND CONVERT), 345–355

correspondence, host variables, 531–532

DATALINK, 74

date and time implementations, 61–68

DB2 UDB 8.1, 72–73

defining using existing types, 72–73

described, 30

encrypting, 443

exact numbers, 54–57

indexed tables, storing unique addresses (UROWID), 73

information about, 466

matching (IN operator), 375

Oracle 9i, 69–71

programming languages, 43

rows, unique addresses (ROWID), 73

storage blocks, operating systems and, 41

structural, defining (ADT), 68

table, changing

DB2 UDB 8.1, 152

Oracle 9i, 148

TIMESTAMP, 74

UROWID, 73

user-defined, viewing, 471

valid, listing, 466

XML, 594

data warehouse system, 8, 10

database

example, 31–32

listing, 472, 478–479

multiple, transactions with, 224

name of current, 481

OOP, 605–608

database administrator. See DBA

database auditing

DB2 UDB 8.1, 448–449

described, 397, 398

MS SQL Server 2000, 449–451

Oracle 9i, 447–448

database 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

DataBase Console Command. See DBCC

DataBase Library. See DBLIB

database links

accessible, listing, 461

creating, 140–141

database objects. See objects

database user. See user

DATALINK, SQL data types, 74

date and time

ANSI/ISO code, 549

arithmetic, 369–370

complex data types, overview, 60–61

DB2 UDB 8.1, 63, 64, 66–67

formatting, 209–210, 348–351

functions

adding dates (ADD_MONTHS and DATEADD), 333–335

current, returning, 308

listed by database, 329

months between two dates (MONTHS_BETWEEN AND DATEDIFF), 337–338

MS SQL Server 2000, listed, 719

name of date/time part, returning (DAYNAME, MONTHNAME, AND DATENAME), 336–337

Oracle 9i, 698–699

part of date/time, extracting (EXTRACT and DATEPART), 335

settings (GETDATE, SYSDATE, and CURRENT DATE), 330

strings, formatting, 354–355

time zone, 330–333

MS SQL Server, 63–64, 67–68

Oracle 9i, 62–63, 64–66

problems, 59

sessions, controlling, MS SQL Server 2000, 214

SQL99, 61, 64

table creation and name belonging to current user, 463

TIMESTAMP versus TIMESTAMP WITH LOCAL TIME ZONE, 62

DB2 IBM SQL

parameters, passing, 494

variables, 491

DB2 UDB 8.1

ACME database, 617–627, 691–693

aliasing, 164–165, 247

ALTER statements, listed, 169–170

auditing, 448–449

blank space in string

indicating, 320

removing, 327

C program, connecting, 534

CD-ROM, back of the book, 611

CLI, 571–573

CLP

batch mode, 681–683

command mode, 681

described, 680

interactive mode, 681

COBOL, connecting, 535

columns

constraints, 86

names, 468–469

selecting all, 242–243

concurrency control mechanism, 229

conditional execution, 497–498

data types

approximate numbers, 58, 59

binary strings, 52, 53

conversion, implicit, 186

date and time, 64

described, 72–73

external files, managing large (DATALINK), 74

listed, 41

national character strings, 50

numbers, 56

OLAP, 599

OOP, 603–604

date and time

arithmetic, 334–335, 370

formatting, 63, 64, 66–67

time zone, adjusting, 332

deadlocks, 233–234

distinct types, 72–73

DROP statements, listed, 171–172

errors

check constraint message, 187

handling, 506

script, 692–693

functions

aggregate, 339

column, listed, 715

conversion, 345

data type specific conversions, 357–358

date and time, 329, 338

formatting, 353–355

grouping, 309

NULL values, finding (COALESCE), 363–364

numeric, 310

procedures, listed, 716

string, 316–317

table, listed, 716

user-defined, 72, 516, 518

indexes

creating, 110–111

dropping, 161

information, obtaining, 469–470

installing, 658–666

integer remainder, calculating, 371

keywords, reserved, 742–745

loops, 499

lowercase and uppercase, converting, 323–324

market share, 6

maximum name length restrictions, 8

modifying existing data (UPDATE), 198

objects, creating, 141–143

operating system security, 430–431, 432

operators, logical, 372

Oracle, accessing data, 580

parameters, passing, 496

physical object, lack of, 246

PSR, 488

quantities, average, 266

queries

excluding results of first in second (EXCEPT), 275–277

matching results from two, 274–275

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

reference types, 72

repeated execution, 499

right outer join, 300

schemas, 128, 165

security

data encryption, 442–443

object-level privileges, 417–420

operating system integration, 430–431, 432

revoking, 423, 425–426

system-level privileges, 411–413

sequences

accessing, 137–138

altering, 168

dropping, 169

sessions, 211

specification, exact numbers, 54–55

SQL99 compliance, 753–765

stored procedures, 508, 511–512

string, converting to Unicode (VARGRAPHIC), 357

synonyms, 122, 125

tables

altering, 151–153

constraints, 90

creating, 81

dropping, 157–158

populating (INSERT), 189–190

temporary, 84

tablespaces, 166, 167

transactions

committing, 219

isolation levels, 226–227

locking modes, 231

multiple databases, 224

ROLLBACK, 221

rolling changes back to specified point (SAVEPOINT), 223

triggers, creating, 523

user, creating, 401

views

altering, 163

creating, 116–118

dropping, 164

INFORMATION_SCHEMA objects, 466–467

updateable, 469

DBA (database administrator)

files, adding to tablespace, 166

privileges, 413, 431

sessions, manipulating, 216

tables, partitioning, 188

DBCC (DataBase Console Command), 216

DBLIB (DataBase Library), 586

dblinks, 224

DCE (distributed computing environment), 432

DDL statements

changes, recompiling views after, 162

dynamic SQL

one-step execution, 544–545

two-step execution, 545–548

indexes, 729

stored procedures, 730

tables, 157, 728–729

triggers, 731

user-defined functions, 730

views, 729

deadlocks, 233–234

decimal numbers

ANSI/ISO code, 549

converting to binary, 773–774

described, 54

programming languages, corresponding, 532

quantities, average, 266

storing, 56

truncating, 313

declaration, host variables, 530–531

DECODE and CASE functions, 359–361

default values

changing

MS SQL Server 2000, 155

Oracle 9i, 148

populating tables (INSERT)

MS SQL Server 2000, 190–192

NULL, 183–184

setting, 86–87

deferrable table constraints, 93–97

definitions, column, 85

deleting data

described, 33, 34–35

DML (Data Manipulation Language)

described, 199

integrity constraints, 200–201

MS SQL Server 2000, 202

Oracle 9i, 202

WHERE clause subqueries, 201–202

updating tables, 193

views, restricting, 438

Department of Defense, 453

DES, 444

descending order. See sequences

description

fields, setting and retrieving, 560

products sold in specific quantity, 376

design, relational database

constraints, specifying, 635–636

described, 629–630

entities and attributes, identifying, 630–631

normalization, 631–635

pitfalls, 636

deterministic functions, 307

difference, set theory operations, 783

disconnection, 541

discounts

distinct values, selecting, 243–244

minimum and maximum order amount, 284–286

disk space. See memory

displays

accessible, describing, 458, 460, 461

altering

DB2 UDB 8.1, 163

MS SQL Server 2000, 163

Oracle 9i, 162

based on another view example, 120

creating

DB2 UDB 8.1, 116–118

MS SQL Server 2000, 117–118

Oracle 9i, 114–116

SQL99, 112–113

DDL statements, syntax, 729

different totals, 120

dropping

DB2 UDB 8.1, 164

MS SQL Server 2000, 164

Oracle 9i, 163

FROM clause, 251

indexes, creating, MS SQL Server 2000, 112

INFORMATION_SCHEMA objects, 466–467

joining, 119–120

listing

DB2 UDB 8.1, 467

MS SQL Server 2000, 473

nested tables, 70

OR REPLACE, simulating, 119

Oracle data directory, 459–462

records in (COUNT), 340–342

security, 38–39, 435–438

stored procedures, 477–478

updateable, 469

with subquery, 121

with UNION example, 120–121

distinct types, DB2 UDB 8.1, 72–73

distinct values, multicolumn SELECT statement, 243–245

distributed computing environment. See DCE

distributed transactions, 224

division, calculating remainder, 368, 371

division operator (/)

described, 368

precedence, 383

DML (Data Manipulation Language)

dynamic SQL

one-step execution, 544–545

two-step execution, 545–548

event triggers, SQL99, 520–521

introduction, 23

MERGE statement, 202–204

modifying existing information (UPDATE)

column in all rows, 194

DB2 UDB 8.1, 198

described, 192–193

integrity constraints, 197

MS SQL Server 2000, 198

multiple columns, 194

Oracle 9i, 198

SET clause, 193

single column of single row, 194

single-row subquery, 195–197

WHERE clause, 193

Oracle 9i versus MS SQL Server 2000, 205

populating tables (INSERT)

for all columns, 182–183

clauses, 178–181

DB2 UDB 8.1, 189–190

described, 177–178

integrity constraints, 186

MS SQL Server 2000, 190–192

NULL and default values, 183–184

Oracle 9i, 188–189

from other tables, 184–185

SQL99 specifics, 187–188

removing data (DELETE)

described, 199

integrity constraints, 200–201

MS SQL Server 2000, 202

Oracle 9i, 202

WHERE clause subqueries, 201–202

TRUNCATE statement, 204–205

document type definitions. See DTD

documents, holding. See binary strings

DOM (document object model), 592

domains

accessible, describing, 457

columns, 85

fully qualified names, 283

integrity, 438, 635

RDBMS objects, creating, 130

SQL99, 130

dot notation (.)

decimals, 57

fully qualified names, 15

MS SQL Server 2000, connecting C program, 534

double quotes ("), 51

DQL statements, syntax

multitable SELECT, 733–734

single table SELECT, 732

drivers

DB2, 572–573

information, 561–562

JDBC, 568–569

ODBC, 562–563

DSN (Data Source Name), 563–565

DTD (document type definitions), 592

DTS (Data Transformation Services), 440

DUAL table, 246, 315

duplicates

blocking entry, 439

eliminating from queries, 243–245

filtering, 272–273

durability rule, 217

dynamic SQL

described, 39, 542–543

DML and DDL

one-step execution, 544–545

two-step execution, 545–548

embedded SQL versus, 554

queries

executing, 550–551

sample, 551–553

syntax, 548–550

standards, 543