Index_S

S

SAG (SQL Access Group), 557

sales tax, calculating, 371

sales, total grouped by customer, 339–340

salesmen

customers without a telephone number, 258–259

unassigned, listing, 255

sample database

column constraints and defaults, 617

described, 9

general information and business rules, 613–614

indexes, 617

installing

on DB2 UDB 8.1, 691–693

on MS SQL Server 2000, 693–694

on Oracle 9i, 689–691

naming conventions, 614–615

scripts, 617–627

tables, relationships between, 615

savepoint, 30

scalability, importance of, 5

scale

functions, Oracle 9i, 709–715

number, allowable, 54

parameters, DB2 IBM SQL, 494

Scandinavian language, 356

schema. See also system catalog

creating

DB2 UDB 8.1, 128

Oracle 9i, 126–128

SQL99, 126, 129

DDL statements, 729–730

described, 125–126

information, listing, DB2 UDB 8.1, 467

objects

altering and destroying, 165

creating, 125–129

setting, 208

SQL statements, validating, 528

views, binding, 118

scientific database, 10

scientific notation, 59

scope, adding to reference type column, 163

script

ACME sample database, 617–627

errors

DB2 UDB 8.1, 692–693

MS SQL Server 2000, 694

Oracle 9i, 690

user-defined functions, 365

scrolling cursor, 501, 504

Second Normal Form, 633–634

secondary key, viewing information, 472

seconds. See date and time

Secure Socket Layer. See SSL

security. See also system catalog

account, 129

basic mechanisms, 397–398

certification, auditing, 398

constraints, 438–439

data, 38–39

database auditing, 447–451

database user, defining, 399–404

GRANT privileges

described, 404–405

object level, 415–421

system level, 405–415

importance, 3

materialized views, creating, 138

MS SQL Server 2000 functions, 476, 722–723

objects, encapsulating, 601

ODBC standard, 562

privileges, 38

REVOKE privileges

described, 421–422

object level, 424–427

system level, 422–424

roles, 427–435

standards, 451–454

stored procedures

data encryption, 442–446

described, 439–442

MS SQL Server 2000, 476

user-defined functions, 489–490

views, 38–39, 435–438

SELECT INTO statement, embedded SQL, 535–536

SELECT statement

BETWEEN operator, 256

copying portions of tables, 102

cursor name, associating (DECLARE), 500–501

FROM clause

aliases, 251–252

subqueries, 253

tables and views, 251

literals, functions, and calculated columns, 245–249

membership test, setting (IN operator), 256–258

multicolumn

all columns plus an extra, 242–243

all columns, selecting, 242

distinct values, 243–245

several columns, selecting, 241–242

nulls, testing (IS NULL operator), 258–259

privilege, assigning, 38

query output, sorting (ORDER BY clause), 267–270

single column, 240–241

single table, 239–240

subqueries

FROM clause, 252

generating values dynamically, 376

horizontal limits, setting (WHERE clause), 259–263

rows, deleting, 201–202

SELECT statement, 249–250

values, inserting, 178

with views, 121

views, creating, 118

WHERE clause, unknown, 377–381

self-join

described, 287

inner joins, 287–289

old syntax, 289

SQL99, 287–288

semicolon (;)

C programming delimiter, 531

format templates, 349

PL/SQL statements, 490

sequences

accessing

DB2 UDB 8.1, 137–138

Oracle 9i, 136–137

altering, 168

ascending and descending, 135

caching, 136

collating, 87–88

creating, 100, 133–138

cycling, 135

described, 133–134

dropping

DB2 UDB 8.1, 169

Oracle 9i, 169

generating, 74

information, listing, 467

inserting, 189

listing accessible, 460, 461, 462

order of values, guaranteeing, 136

syntax, 134

sequential programming, 489

server

defined, 28

roles, 433

sessions

date, retrieving current, 332

described, 207–216

local temporary tables, 82, 84

lock, trying for same resource, 233–234

parameters, setting, 208, 210

privilege, granting, 410–411

set and list types, collections, 68–69

SET clause, modifying existing data (UPDATE), 193

SET statement, standard, 208

set theory

equality, 780

identities, 785–786

listing, 779–780

operations

cardinality, 784–785

Cartesian product, 783–784

complement, 782

difference, 783

intersection, 782

multielement operands, 785

multiple, 784

union, 781

subsets, 780

settings, date and time functions (GETDATE, SYSDATE, and CURRENT DATE), 330

several columns, multicolumn SELECT statement, 241–242

shareware programs, 611

Simonyi, Dr. Charles (Hungarian notation developer), 615

Simple Object Access Protocol. See SOAP

single column

modifying existing data (UPDATE), 194

SELECT statement, 240–241

single quote (')

character literals, 51

date and time, handling, 67

numeric literals, 57

single row

Oracle 9i functions, listed, 702–703

updating column

assignment value, deriving from another, 195–196

described, 195

update with correlated, 196–197

single table

DQL statements, syntax, 732

SELECT statement, 239–240

skipping

cross join, 291

values for columns, 178

smallest orders, 344

SOAP (Simple Object Access Protocol), 594

software

caution about renaming tables, 151

CD-ROM, back of the book, 611–612

name of, MS SQL Server 2000, 482–483

software, installing

ACME database

on DB2 UDB 8.1, 691–693

on MS SQL Server 2000, 693–694

on Oracle 9i, 689–691

DB2 UDB 8.1, 658–666

MS SQL Server 2000, 666–673

Oracle 9i

general information, 637–638

on Linux, 653–658

on SUN Solaris, 648–653

on Windows NT/2000/XP, 638–648

uninstalling

DB2 UDB 8.1, 666

MS SQL Server 2000, 672–673

Oracle 9i, 646–647, 653

sorting, SELECT statement output (ORDER BY clause), 267–270

speed. See performance

SQL99

ALTER statements, listed, 169–170

collections, 68–69

compliance, 753–765

dates and times, 61, 64

DROP statements, listed, 171–172

index recommendations, 108–109

keywords, reserved, 738–741

new developments, 39–40

objects, creating, 141–143

privileges, revoking, 422

schemas

described, 126, 129

Oracle Data Dictionary views, corresponding, 462–464

stored procedures, 508

synonyms, 122–123

tables

altering, 146

creating, 80

populating (INSERT), 187–188

temporary, 83

transactions, isolation levels, 225

triggers, creating, 520–521

user-defined functions, creating, 515

views, creating, 112–113

SQL Access Group. See SAG

SQL data types. See data types

SQL99 data types

abstract, 68

approximate numbers, 58, 59

binary strings, 52, 53

character strings, 49

date and time, 64

exact numbers, 54

national character strings, 47–48

SQL functions. See functions

SQL2 functions, 308–309

SQL99 joins

cross, 289–290

equijoin, 282–283

inner, 280–281

more than two tables, 293

outer, 294

right outer, 299

self, 287–288

SQL operators. See operators

SQL PL, 28

SQL Server. See MS SQL Server 2000

SQL (SQL3), 31–32

SQL92 standard, 606

SQL standards. See standards

SQLBase v8.0, 769

SQLJ, 606

SQL/MED, 30

SQL*Plus

described, 675–679

session parameters, 210–211

worksheet, 679–680

square brackets ([]), 378

SSL (Secure Socket Layer), 40, 445

standards

ANSI/ISO documents, 29

dynamic SQL, 543

embedded SQL, 529

Network Database, 14

Oracle 9i data dictionary, 462–464

RDBMS, 20–25

security, 451–454

SQL (SQL3), 30–31

SQL89 (SQL1), 23

SQL92 (SQL2), 23–24

SQL99 (SQL3), 24–25

starting

DB2 UDB 8.1, 666

Oracle 9i, 647–648, 653

privileges, 411

string position and required character length (SUBSTR and SUBSTRING), 320–322

statements

basic SQL, 33

executing, 559–560

processing steps, 527–529

static SQL. See embedded SQL

static view prefixes, Oracle 9i data dictionary, 460

statistical functions, MS SQL Server 2000, 725

stopping

DB2 UDB 8.1, 666

Oracle 9i, 647–648, 653

privileges, 411

stored procedures

creating, 440, 508

DB2 UDB 8.1, 511–512

DDL statements, syntax, 730

information, viewing

DB2 UDB 8.1, 467

MS SQL Server 2000, 472, 478–480

MS SQL Server 2000, 512–514

Oracle 9i, 509–511

removing, 515

security, 439–446, 489–490

for security

data encryption, 442–446

described, 439–442

StorHouse/RM, 770

string functions

blank spaces, removing (TRIM, LTRIM, and RTRIM), 327–328

characters

finding and replacing within (TRANSLATE), 326–327

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

number of (LENGTH), 322–323

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

replicating (REPLICATE and REPEAT), 325–326

starting position and required length (SUBSTR and SUBSTRING), 320–322

concatenating (CONCAT), 317–318

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

described, 309, 315–317

replacing expressions (REPLACE), 325

strings

converting to Unicode (UNISTR), 356

format templates, 347–349

MS SQL Server 2000 functions, listed, 716–717

striping, RAID (Redundant Array of Independent Disks), 391

structural SQL data types, 68

subprograms, procedural language, 491–493

subqueries

deleting rows, 201–202

FROM clause, 253

generating values dynamically, 376

horizontal limits (WHERE clause), 259–263

inserting values, 178

SELECT statement, 249–250

with views, 121

subsets, set theory, 780

subtraction operator (-), 367

summaries

limiting display, 437

SELECT statement results (GROUP BY and HAVING clauses), 263–267

summary tables

altering, 153

creating, 103–104

similar case in Oracle 9i, 138–140, 251

table constraints, 105

sums

Boolean OR, 775–776

column values, 37, 339–240

NULL, ignoring, 342

SUN Solaris, installing Oracle 9i software, 648–653

support, database software, selecting, 6–7

Sybase

domains, 130

early commercial relational database, 21

market share, 6

Oracle, accessing data, 580

system stored procedures, 476

wildcard operators, 379

Sybase Adaptive Server, 770

symbols. See operators

synchronizing, columns, Oracle 9i, 203–204

synonym

altering, 164–165

creating, 121–125

destroying, 164–165

Oracle 9i

examples, 124

listing, 460, 462

objects, 124

public versus private, 123

remote database objects, 124

reasons to use, 121–122

SQL99, 122–123

syntax

BNF, 727–728

DCL statements, 731

DDL statements, 728–731

DML statements, 731–732

DQL statements, 732–734

inner joins, 279–281

outer joins, 294–296

predicates, 734–736

queries, 548–550

transactional control statements, 734

system

date and time

obtaining current, 332–333

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

functions, MS SQL Server 2000

configuration, 480–481

listed, 482–483, 723–725

metadata, 481

security level

granting, 405–415

revoking, 422–424

system catalog

accessing, 561

DB2 UDB 8.1, 466–470

described, 455–458

MS SQL Server 2000

INFORMATION_SCHEMA views, 470–475

stored procedures, 475–476

system functions, 480–483

Oracle 9i data dictionary, 458–466

setting, 208

view definition, removing

DB2 UDB 8.1, 164

MS SQL Server 2000, 164

Oracle 9i, 163

System R (IBM), 20

system requirements

CD-ROM, back of the book, 609

DB2 UDB 8.1, installing, 658–659

MS SQL Server 2000, 667

Oracle 9i, installing, 638–639, 649, 653–654