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