Index
A
ABS (absolute value), 183
accessing remote databases, 357–358
JDBC, 358
ODBC (Open Database Connectivity), 358
OLE DB, 359
through web interfaces, 359–360
vendor connectivity products, 359
adding
auto-incrementing columns to tables, 45
columns to tables, 44–45
time to dates, 196–197
addition, arithmetic operators, 133–134
ADMIN OPTION, 305
aggregate functions, 141–142
AVG, 146–147
COUNT, 142–145
creating groups, 155–158
MAX, 147–148
MIN, 148–149
SUM, 145–146
ALL, 124–126
ALTER ANY TABLE, 302
ALTER DATABASE, 302
ALTER USER, 302
altering
indexes, 263
users, 294–295
American National Standards Institute. See ANSI (American National Standards Institute)
AND, 126–127
ANSI (American National Standards Institute), 2
ANSI character functions, 169–170
ANSI SQL, 2
compliance with, 369
ANSI standard, SELECT, 368
ANY, 124–126
arithmetic operators, 133
addition, 133–134
combinations of, 135–136
division, 135
multiplication, 134
subtraction, 134
ASCII, 182–183
asterisks, 101
AUTHORIZATION keyword, 292
auto-incrementing columns, adding to tables, 45
AVG, 146–147
avoiding
HAVING clause, 277
indexes, 261–263
large sort operations, 277–278
OR operator, performance, 276–277
B
back-end application, SQL and enterprise, 356
BACKUP ANY TABLE, 302
BACKUP DATABASE, 301
base tables, joins, 218–219
batch loads, disabling indexes during, 278–279
BETWEEN, 120–121
BOOLEAN values, 29
C
call-level interface (CLI), 349–350
Cartesian product, joins, 219–221
case sensitivity, 74
SELECT, 106–107
CEILING, 183
character functions, 170
ASCII, 182–183
COALESCE, 181
combining, 186–187
CONCAT, 170–172
DECODE, 178–179
IFNULL, 180
INSTR, 176–177
LENGTH, 179–180
LOWER, 174–175
LPAD, 181–182
LTRIM, 177–178
REPLACE, 173
RPAD, 182
RTRIM, 178
SUBSTR, 175–176
TRANSLATE, 172–173
UPPER, 174
character strings
converting numbers to, 185–186
converting to dates, 203–204
converting to numbers, 184–185
check constraints, 53
CLI (call-level interface), 349–350
client/server model, 5–6
CLOSE, 340
closing cursors, 342–343
COALESCE, 181
collation, 107
adding to tables, 44–45
auto-incrementing columns, adding to tables, 45
limited columns, inserting data into, 75–76
modifying, 46
representing column names with numbers, 158–159
updating
multiple columns in one or more records, 81–82
value of a single column, 80–81
combining character functions, 186–187
comma separated arguments, 100
comparison operators, 116
combinations of, 118–119
equality, 116
less than, greater than, 118
non-equality, 117
composite indexes, 259–260
compound queries
GROUP BY clause, 248–250
ORDER BY clause, 246–248
versus single queries, 239–240
compound query operators, 240
EXCEPT, 245–246
INTERSECT, 244–245
retrieving accurate data, 250
UNION, 240–243
UNION ALL, 243–244
CONCAT, 170–172
concatenation, 170
conjunctive operators, 126
AND, 126–127
OR, 127–129
constraints
dropping, 54
integrity constraints. See integrity constraints
controlling
privileges, 308
CREATE ROLE, 308–309
DROP ROLE, 309
SET ROLE, 309
transactions, 88
COMMIT, 89–91
RELEASE SAVEPOINT, 94–95
ROLLBACK, 91–92
ROLLBACK TO SAVEPOINT, 93–94
SAVEPOINT, 92–93
SET TRANSACTION, 95
user access, 304
GRANT, 304–305
GRANT OPTION, 305
groups of privileges, 306–308
on individual columns, 306
PUBLIC database, 306
REVOKE, 305–306
conversion functions, 183–184
converting character strings to numbers, 184–185
converting numbers to character strings, 185–186
converting
character strings to dates, 203–204
character strings to numbers, 184–185
dates to character strings, 202–203
numbers to character strings, 185–186
correlated subqueries, 233–234
COS, 183
cost-based optimization, performance, 279–280
COUNT(*), 143
CREATE, 302
CREATE ANY TABLE, 302
CREATE DATABASE, 301
CREATE INDEX257, 377
CREATE PROCEDURE, 301
CREATE SCHEMA, 292–293
CREATE TABLE AS, 378
CREATE TABLE statement, 41–43, 301, 378
CREATE TYPE, 378
CUBE expression, 163–164
CUME_DIST() OVER, 351
current date, 194
cursors, 339–340
closing, 342–343
fetching data from, 341–342
opening, 340–341
CUSTOMER_TBL, 440–441, 444–445
Oracle, 447–448
D
data
defined, 21
deleting from tables, 82–83
grouping, 153–154
CUBE expression, 163–164
GROUP BY clause, 154–155
GROUP BY clause versus ORDER BY clause, 159–161
HAVING clause, 164–165
ROLLUP expression, 161–162
inserting
into limited columns of tables, 75–76
NULL values, 78–80
from other tables, 76–78
into tables, 74–75
retrieving, 250
selecting, 10
from multiple tables, 207–208
selecting data from another user’s table, 110
updating, 80
multiple columns in one or more records, 81–82
through views, 322–323
value of a single column, 80–81
used in this book, 13–15
data access, simplifying with views, 314–315
data administration commands, 9, 11
Data Control Language. See DCL (Data Control Language)
Data Definition Language. See DDL (Data Definition Language)
Data Manipulation Language. See DML (Data Manipulation Language)
Data Query Language. See DQL (Data Query Language)
data redundancy, logical database design, normalization, 63–64
data types, 22
BOOLEAN values, 29
date and time data types, 27
DATETIME data types, 192
decimal values, 25–26
domains, 30
fixed-length strings, 23
floating-point decimals, 26–27
integers, 26
large object types, 24
limitations on, 43
literal strings, 28
NULL values, 28–29
numeric values, 24–25
user-defined types, 29–30
varying-length strings, 23
database administrator. See DBA
database design information, system catalogs, 332
database management system. See DBMS (database management system)
database objects, 37
database performance, transactional control and, 95–96
database security, 299–300
database structures, DDL (Data Definition Language), 9–10
database tuning versus SQL statement tuning, 268
database vendors, 7–8
databases
defined, 4–5
relational databases, 5
web-based database systems, 6–7
date conversions, 198–199
converting character strings to dates, 203–204
converting dates to character strings, 202–203
date pictures, 199–202
date functions, 193
adding time to dates, 196–197
current date, 194
miscellaneous, 197–198
time zones, 194–195
date pictures, 199–202
dates, converting to character strings, 202–203
implementation-specific data types, 193
DATETIME elements, 192
DB_DATAWRITER, 308
DB_DDLADMIN, 307
DBA (database administrator), 21, 300, 307
DBMS (database management system), 1
DCL (Data Control Language), 9, 10–11
DDL (Data Definition Language), 9–10
decimal values, 25–26
floating-point decimals, 26–27
DECODE, 178–179
default storage, 191
subqueries, 230
deleting data from tables, 82–83
denormalization, 69–70
DENSE_RANK() OVER, 351
direct SQL versus embedded SQL, 351
disabling indexes during batch loads, 278–279
DISCONNECT, 8–9
DISTINCT
aggregate functions, 149
SELECT, 102
division, arithmetic operators, 135
DML (Data Manipulation Language), 9–10
domains, data types, 30
DQL (Data Query Language), 9–10
DROP, 302
DROP INDEX, 379
DROP ROLE, 309
DROP TRIGGER, 348
dropping
constraints, 54
indexes, 263
schemas, 293–294
synonyms, 325
tables, 48–49
triggers, 348
views, 323
dynamic SQL, 348–349
E
embedded SQL versus direct SQL, 351
embedded subqueries, 231–233
EMPLOYEE_PAY_TBL, 439, 441, 444
Oracle, 447
Oracle, 446–447
end user needs, logical database design, normalization, 63
enterprises, SQL and, 355
back-end applications, 356
front-end applications, 356–357
equality, 116
equality of joins, 208–210
EXCEPT, 245–246
EXEC SQL, 349
EXECUTE, 301
EXISTS, 123–124
EXIT, 8–9
EXP (exponential values), 183
EXPLAIN PLAN, 280
extensions
implementations, 369–370
MySQL, 372–373
PL/SQL, 371–372
SQL extensions, 370
Transact-SQL, 371
F
FETCH, 340
fetching data from cursors, 341–342
fields, 15
first normal form, 64–65
fixed-length strings, 23
FLOAT, 26
floating-point decimals, 26–27
FLOOR, 183
FOR EACH ROW, triggers, 348
foreign key constraints, 51–52
formatting SQL statements, 268–269
arranging tables in FROM clause, 271
ordering join conditions, 271–272
for readability, 269–271
restrictive conditions, 272–273
FROM clause, 381
arranging tables, 271
SELECT, 102
front-end applications, SQL and enterprise, 356–357
full table scans, 257, 274–275
functions
aggregate functions. See aggregate functions
ANSI character functions, 169–170
character functions, 170
ASCII, 182–183
COALESCE, 181
CONCAT, 170–172
DECODE, 178–179
IFNULL, 180
INSTR, 176–177
LENGTH, 179–180
LOWER, 174–175
LPAD, 181–182
LTRIM, 177–178
REPLACE, 173
RPAD, 182
RTRIM, 178
SUBSTR, 175–176
TRANSLATE, 172–173
UPPER, 174
conversion functions, 183–184
date functions, 193
adding time to dates, 196–197
current date, 194
miscellaneous, 197–198
time zones, 194–195
mathematical functions, 183
stored procedures and, 343–346
TRANSLATE, 170
windowed table functions, 351–352
G-H
generating SQL with SQL, 350
controlling user access, 304–305
GRANT OPTION, 305
granting privileges, 303–304
compound queries, 248–250
creating groups with aggregate functions, 155–158
group functions, 155
grouping selected data, 155
versus ORDER BY, 159–161
representing column names with numbers, 158–159
group functions, GROUP BY clause, 155
grouping
data, 153–154
CUBE expression, 163–164
GROUP BY clause, 154–155
GROUP BY clause versus ORDER BY clause, 159–161
HAVING clause, 164–165
ROLLUP expression, 161–162
queries, 128
selected data, GROUP BY clause, 155
groups, creating with aggregate functions, 155–158
groups of privileges, controlling user access, 306–308
GUI tools, 296
avoiding, 277
I
IFNULL, 180
implementations, 367
compliance with ANSI SQL, 369
differences between, 367–369
extensions to SQL, 369–370
implementation-specific data types, 193
implicit indexes, 260
IN, 121–122
indexes, 255–256
altering, 263
avoiding, 261–263
composite indexes, 259–260
considering, 260–261
creating groups, 258–257
creating with CREATE INDEX, 257
disabling indexes during batch loads, 278–279
dropping, 263
how they work, 256–257
implicit indexes, 260
single-column indexes, 258
unique indexes, 258–259
subqueries, 228–229
inserting data
into limited columns of tables, 75–76
NULL values, 78–80
from other tables, 76–78
into tables, 74–75
INSERT...SELECT, 380
installing
Microsoft SQL Server, 388–390
MySQL, 383–385
Oracle, 386–387
INSTR, 176–177
integers, 26
integrity constraints, 49
check constraints, 53
foreign key constraints, 51–52
NOT NULL constraints, 52
primary key constraints, 49–50
unique constraints, 50–51
interactive SQL statements, 373–374
International Standards Organization (ISO), 2
Internet, SQL and, 360
making data available to customers worldwide, 360–361
making data available to employees and privileged customers, 361
INTERSECT, 244–245
intranets, SQL and, 361–362
IS NOT NULL, 132
IS NULL, 120
ISO (International Standards Organization), 2
J-K
JDBC (Java Database Connectivity), 358
join conditions, ordering, 271–272
joins, 208
base tables, 218–219
Cartesian product, 219–221
components of a join condition, 208
equality of, 208–210
multiple keys, 216–217
non-equality, 211–212
outer joins, 212–215
self joins, 215–216
table aliases, 210
keywords
AUTHORIZATION, 292
SELECT, 100
L
large object types, 24
LENGTH, 179–180
less than, greater than, comparison operators, 118
limitations on data types, 43
literal strings, 28
logical database design, normalization, 62–63
data redundancy, 63–64
end user needs, 63
logical operators, 119–120
ALL, 124–126
ANY, 124–126
BETWEEN, 120–121
EXISTS, 123–124
IN, 121–122
IS NULL, 120
LIKE, 122–123
SOME, 124–126
LOWER, 174–175
LPAD, 181–182
LTRIM, 177–178
M
major implementation system catalog objects, 333–334
managing users, 285–287
mathematical functions, 183
MAX, 147–148
Microsoft SQL Server
creating users, 290–291
cursors, 340
closing, 343
parameters, 374
SELECT, 368
stored procedures, 344–345
triggers, creating, 346
Windows installation instructions, 388–390
MIN, 148–149
modifying
columns in tables, 46
elements of tables, 44
multiple keys, joining, 216–217
multiplication, arithmetic operators, 134
MySQL
creating users, 291
cursors, 340
closing, 343
extensions, 372–373
stored procedures, 344
triggers, creating, 346
Windows installation instructions, 383–385
N
naming conventions
normalization, 67
tables, 43
naming objects, 39
negative operators, 129
IS NOT NULL, 132
NOT BETWEEN, 130–131
NOT EQUAL, 130
NOT EXISTS, 133
NOT IN, 131
NOT LIKE, 131–132
nested views, performance, 323–324
non-equality
comparison operators, 117
joins, 211–212
normal forms, 64
first normal form, 64–65
second normal form, 65–66
third normal form, 67
normalization, 61–62
benefits of, 68–69
drawbacks of, 69
logical database design, 62–63
data redundancy, 63–64
end user needs, 63
naming conventions, 67
normal forms, 64
first normal form, 64–65
second normal form, 65–66
third normal form, 67
raw databases, 62
NOT BETWEEN, 130–131
NOT EQUAL, 130
NOT EXISTS, 133
NOT IN, 131
NOT LIKE, 131–132
NOT NULL constraints, 52
NULL value checker, 180
inserting in tables, 78–80
numbers
converting character strings to, 184–185
converting to character strings, 185–186
numeric values, 24–25
O
object privileges, 302–303
objects, naming, 39
ODBC (Open Database Connectivity), 358
OLE DB, 359
OPEN, 340
opening cursors, 340–341
operators, 115
arithmetic operators, 133
addition, 133–134
combinations, 135–136
division, 135
multiplication, 134
subtraction, 134
comparison operators, 116
combinations of, 118–119
equality, 116
less than, greater than, 118
non-equality, 117
conjunctive operators, 126
AND, 126–127
OR, 127–129
logical operators, 119–120
ALL, 124–126
ANY, 124–126
BETWEEN, 120–121
EXISTS, 123–124
IN, 121–122
IS NULL, 120
LIKE, 122–123
SOME, 124–126
negative operators, 129
IS NOT NULL, 132
NOT BETWEEN, 130–131
NOT EQUAL, 130
NOT EXISTS, 133
NOT IN, 131
NOT LIKE, 131–132
OR, 127–129
avoiding, 276–277
Oracle
creating users, 289–290
cursors, 340
closing, 343
parameters, 373
SELECT, 368–369
stored procedures, 344–345
triggers, creating, 346
Windows installation instructions, 386–387
Oracle Fusion Middleware, 359
ORDER BY clause, 382
compound queries, 246–248
versus GROUP BY clause, 159–161
SELECT, 104–106
views, 322
ordering join conditions, 271–272
Oracle, 448–449
outer joins, 212–215
P
parameters
Microsoft SQL Server, 374
Oracle, 373
PERCENT_RANK() OVER, 351
performance
avoiding
HAVING clause, 277
large sort operations, 277–278
OR operator, 276–277
cost-based optimization, 279–280
disabling indexes during batch loads, 278–279
full table scans, 274–275
LIKE operator, 275–276
nested views, 323–324
SQL statement tuning, 267
versus database tuning, 268
stored procedures, 278
subqueries, 234–235
wildcards, 275–276
performance statistics, system catalogs, 332–333
performance tools, 280
PL/SQL, 370
extensions, 371–372
populating tables with new data, 74
POWER, 183
primary key constraints, 49–50
primary keys, 16
privileges, 301
controlling, 308
CREATE ROLE, 308–309
DROP ROLE, 309
SET ROLE, 309
granting, 303–304
object privileges, 302–303
revoking, 303–304
system privileges, 301–302
Oracle, 449
PUBLIC database, 307
Q
queries, 99
compound queries
GROUP BY clause, 248–250
ORDER BY clause, 246–248
compound query operators, 240
EXCEPT, 245–246
INTERSECT, 244–245
UNION, 240–243
UNION ALL, 243–244
grouping, 128
simple queries
column aliases, 111
counting records in tables, 109–110
examples, 108–109
selecting data from another user’s table, 110
single versus compound, 239–240
querying system catalogs, 334–336
R
RANK() OVER, 351
raw databases, normalization, 62
RDBMS (relational database management system), 2
READ WRITE, 95
readability, formatting SQL statements, 269–271
records, 15–16
counting records in tables, simple queries, 109–110
REFERENCES, 303
relational database management system. See RDBMS (relational database management system)
relational databases, 5
RELEASE SAVEPOINT, 94–95
RELOAD, 302
remote databases, accessing, 357–358
JDBC, 358
ODBC (Open Database Connectivity), 358
OLE DB, 359
through web interfaces, 359–360
vendor connectivity products, 359
removing user access, 295–296
REPLACE, 173
representing column names with numbers, 158–159
RESOURCE, 307
restrictive conditions, SQL statements, 272–273
retrieving data, 250
REVOKE, 380
controlling user access, 305–306
revoking privileges, 303–304
ROLLBACK TO SAVEPOINT, 93–94
ROLLUP expression, 161–162
ROUND, 183
ROW_NUMBER() OVER, 351
rows, 41
rows of data, 15–16
RPAD, 182
RTRIM, 178
S
schemas, 37–39
creating, 292–293
dropping, 293–294
versus users, 288
second normal form, 65–66
security
controlling privileges, 308
CREATE ROLE, 308–309
DROP ROLE, 309
SET ROLE, 309
controlling user access, 304
GRANT, 304–305
GRANT OPTION, 305
groups of privileges, 306–308
on individual columns, 306
PUBLIC database, 306
REVOKE, 305–306
database security, 299–300
Internet, 361
privileges, 301
object privileges, 302–303
system privileges, 301–302
views, 315
security information, system catalogs, 332
SELECT, 10, 73, 99–102, 303, 380–381
ANSI standard, 368
case sensitivity, 106–107
FROM clause, 102
creating groups, 155–158
DISTINCT, 102
Microsoft SQL Server, 368
Oracle, 368–369
ORDER BY clause, 104–106
subqueries, 227–228
WHERE clause, 103–104
SELECT ANY TABLE, 302
selecting
data, 10
from multiple tables, 207–208
data from another table, 110
self joins, 215–216
SET ROLE, 309
SHUTDOWN, 302
SIGN (sign values), 183
simple queries
column aliases, 111
counting records in tables, 109–110
examples, 108–109
selecting data from another user’s table, 110
simplifying data access with views, 314–315
SIN, 183
single queries versus compound queries, 239–240
single quotation marks, 74
single-column indexes, 258
SOME, 124–126
sort operations, avoiding, 277–278
SQL (Structured Query Language), 2
direct versus embedded, 351
enterprises and, 355
back-end applications, 356
front-end applications, 356–357
generating SQL, 350
Internet and, 360
making data available to customers worldwide, 360–361
making data available to employees and privileged customers, 361
intranets and, 361–362
SQL commands, 9
data administration commands, 9, 11
DCL (Data Control Language), 10–11
DDL (Data Definition Language), 9–10
DML (Data Manipulation Language), 10
DQL (Data Query Language), 10
transaction control commands, 9, 11
SQL extensions, 370
SQL sessions, 8
CONNECT, 8
DISCONNECT, 8–9
EXIT, 8–9
SQL statement tuning, 267
versus database tuning, 268
SQL statements
formatting, 268–269
arranging tables in FROM clause, 271
ordering join conditions, 271–272
for readability, 269–271
restrictive conditions, 272–273
interactive SQL statements, 373–374
SQL-2008, 3–4
SQRT (square root), 183
standards, table-naming standards, 12–13
storage, default storage, 191
stored procedures
functions and, 343–346
performance, 278
strings
fixed-length strings, 23
literal strings, 28
varying-length strings, 23
Structured Query Language. See SQL (Structured Query Language)
subqueries, 225–227
correlated, 233–234
DELETE statements, 230
embedded, 231–233
INSERT statements, 228–229
performance, 234–235
SELECT statements, 227–228
UPDATE statements, 229–230
SUBSTR, 175–176
substrings, 170
subtraction, arithmetic operators, 134
SUM, 145–146
summarized data, maintaining with views, 315–316
synonyms, 324
creating, 324–325
dropping, 325
simple queries, 111
SYS, 331
system catalog objects, updating, 336
system catalogs, 327–330
contents of, 331–332
creating, 331
database design information, 332
performance statistics, 332–333
querying, 334–336
security information, 332
tables by implementation, 333–334
user data, 332
system privileges, 301–302
T
table aliases, 210
table-naming standards, 12–13
adding
auto-incrementing columns, 45
columns, 44–45
ALTER TABLE, 44
arranging in FROM clause, 271
modifying, 46
CREATE TABLE statement, 41–43
creating from existing tables, 46–48
creating from views, 321–322
data
deleting, 82–83
inserting, 74–75
dropping, 48–49
fields, 15
inserting data from another table, 76–78
modifying elements of, 44
naming conventions, 43
NULL values, 16
populating with new data, 74
primary keys, 16
records, 15–16
rows, 41
TAN, 183
TEXT data type, 24
third normal form, 67
TIME, 192
time zones, date functions, 194–195
TIMESTAMP, 192
TKPROF, 280
tools, database users, 296
transaction control commands, 9, 11
transactional control, database performance and, 95–96
transactions, 87–88
controlling, 88
COMMIT, 89–91
RELEASE SAVEPOINT, 94–95
ROLLBACK, 91–92
ROLLBACK TO SAVEPOINT, 93–94
SAVEPOINT, 92–93
SET TRANSACTION, 95
Transact-SQL, extensions, 371
TRANSLATE, 172–173
TRANSLATE function, 170
triggers, 346
creating, 346–348
dropping, 348
FOR EACH ROW, 348
U
UNION, 240–243
UNION ALL, 243–244
unique constraints, 50–51
unique indexes, 258–259
subqueries, 229–230
updating
data, 80
multiple columns in one or more records, 81–82
through views, 322–323
value of a single column, 80–81
system catalog objects, 336
UPPER, 174
USAGE, 303
user access, controlling, 304
GRANT, 304–305
GRANT OPTION, 305
groups of privileges, 306–308
on individual columns, 306
PUBLIC database, 306
REVOKE, 305–306
user data, system catalogs, 332
user-defined types, 29–30
user management, 288
creating users, 289
in Microsoft SQL Server, 290–291
in MySQL, 291
in Oracle, 289–290
user sessions, 295
users
altering, 294–295
creating, 289
in Microsoft SQL Server, 290–291
in MySQL, 291
creating groups in Oracle, 289–290
managing, 285–287
place in databases, 287
removing access, 295–296
versus schemas, 288
types of, 286
V
varying-length strings, 23
vendor connectivity products, 359
vendors, database vendors, 7–8
views, 313–314
creating, 316
from multiple tables, 318–319
from a single table, 316–318
from views, 319–320
creating tables from, 321–322
dropping, 323
as a form of security, 315
maintaining summarized data, 315–316
nested views, performance, 323–324
ORDER BY clause, 322
simplifying data access, 314–315
updating data, 322–323
WITH CHECK OPTION, 320
W–Z
web-based database systems, 6–7
web interfaces, accessing remote databases, 359–360
SELECT, 103–104
wildcards, performance, 275–276
windowed table functions, 351–352
Windows installation instructions
Microsoft SQL Server, 388–390
for MySQL, 383–385
for Oracle, 386–387
WITH CHECK OPTION, views, 320
XML, 352