|
|
table
ACME sample database, relationships between, 615
adding rows, 541
Cartesian product of two
inner joins, 289–290
old syntax, 290
set theory operations, 783–784
SQL99, 289–290
changing, recompiling views after, 162
columns
adding, 147–148, 152, 154
collating sequence, 87–88
constraints, 85–86
default values, 86–87
definitions, 85
described, 80
modifying, 148, 154–155
removing, 149, 155–156
constraints
CHECK, 88
copy, creating new table as, 101–104
creating, 149–150, 152–153, 156
deferrable, 93–97
disabling and enabling, 150–151
example of usage, 88–89
FOREIGN KEY, 88, 89–90
identity clause, 100–101
INITIALLY DEFERRED, 97
modifying, 149
ON COMMIT clause, 97
physical properties, 97–100
PRIMARY KEY, 88
REFERENCES, 89–90
removing, 150, 153, 156
restrictions, 89–93
summary tables, 105
UNIQUE, 88
creating
DB2 UDB 8.1, 81
MS SQL 2000, 81–82
Oracle 9i, 80–81
SQL99, 80
DDL statements, syntax, 728–729
FROM clause, 251
functions, 716
identifying, 629
identity column options, modifying, 152
indexed, storing unique addresses (UROWID), 73
information, listing, DB2 UDB 8.1, 467
listing
MS SQL Server 2000, 472, 477–478
Oracle 9i, 460–461
maximum name length restrictions, 8
MS SQL Server 2000, 154–156
name and creation date belonging to current user, 463
Oracle 9i, 146–151
partitioning, 188
permanent, 82
physical attributes, changing, 147
privileges, 410, 457
records (COUNT), 340–342
renaming, 151
rows, 79
SELECT statement, identifying, 239
SQL99, 146
stored procedures, 477–478
summary table options, altering, 153
temporary
constraints, 97
DB2 UDB 8.1, 84
MS SQL Server 2000, 84–85
Oracle 9i, 83
in SQL 99, 83
values, changing, 541
VARCHAR column size, increasing, 152
without constraints, finding, MS SQL Server 2000, 474–475
table queries, multiple
inline views, 302–303
inner joins
cross join, 289–290
equijoin, 282–284
nonequijoin, 284–286
self-join, 287–289
syntaxes, 279–281
more than two tables, 290–293
outer joins (columns containing NULL)
full outer join, 301–302
left outer join, 296–299
right outer join, 299–301
syntaxes, 294–296
union join, 302
SELECT, DQL statements, syntax, 733–734
with correlated queries, 303–304
tablespace
altering and destroying, 166–167
changing, when table created in wrong, 147
creating
DB2 UDB 8.1, 131–132
MS SQL Server 2000, 132–133
Oracle 9i, 131
described, 130–131
dropping, 167
files, adding, Oracle 9i, 166
information, listing, DB2 UDB 8.1, 467
specifying, 98, 110
TCP/IP, 445
TCSEC (Trusted Computer System Evaluation Criteria), 453
telecommuting
DB2 UDB 8.1, 332
MS SQL Server 2000, 330
Oracle 9i, 331
telephone numbers
customers with orders, rgtrieving, 303–304
leaving space for future entry, 75
missing, listing customers, 258–259
multiple, handling, 69
salesmen without assigned customers, 271–272
temporary tables
constraints, 97
copying, 104
DB2 UDB 8.1, 84
MS SQL Server 2000, 84–85
Oracle 9i, 83
in SQL 99, 83
terminating
sessions, 216
transactions, 218
text
character sets, converting, Oracle 9i, 355–357
functions, MS SQL Server 2000, 720
objects
binary, nonstructured (BLOB), 52, 443
large, nondatabase-structured (CLOB), 48, 50
searching, stored procedures, MS SQL Server 2000, 475
Third Normal Form, 634–635
time. See also date and time
interval literals, Oracle 9i, 66
orders snapshot, summarizing automatically, 139–140
time zone
date and time functions, 61–62, 330–333
default, setting, 208
timeout, 234
TIMESTAMP
Oracle 9i, 65
SQL data types, 61–62, 74
TimesTen RDBMS, 770
TOAD utility (Quest Software Ltd.), 675
transaction
COMMIT, 218–220
control statements, 734
described, 82, 217–218
distributed, 224
ending, 541
explicit and implicit, 218
isolation levels, 225–228
managing, 561
nested, 220
ROLLBACK, 221–222
rolling changes back to specified point (SAVEPOINT), 222–223
Transact-SQL
conditional execution, 498
cursors, programming, 504
data encryption, 445–446
error handling, 507
loops, 499
parameters, passing, 494
privileges, 415
repeated execution, 499
session environment, controlling, 212–216
stored procedures, creating, 440–441, 508
triggers, creating, 523–525
user-defined functions, 516, 519–520
variables, 491
transforming data while retrieving
described, 37
GROUP BY clause, 264
MS SQL Server 2000, 719–720
Oracle 9i, 704–705
summary tables, 105
views, joining, 119–120
triggers
accessible, listing, 460, 461, 462
caution about renaming tables, 151
creating
DB2, 523
PL/SQL, 521–522
SQL99, 520–521
Transact-SQL, 523–525
DDL statements, 731
defined, 30
information, listing, 467
removing, 525
security
data encryption, 442–446
described, 439–442, 489–490
troubleshooting, back-of-the-book CD-ROM, 612
true and false. See Boolean expressions
truncating, DML (Data Manipulation Language), 204–205
Trusted Computer System Evaluation Criteria. See TCSEC
two-digit years, 351
two-step execution, DML and DDL, 545–548
types. See data types
|
|