• % (percent sign) character, 231

• * (asterisk) character, 144, 352
• _ (underscore) character, 231
• 1NF (first normal form), 136–137
• 2NF (second normal form), 137–138
• 3NF (third normal form), 138–139
• 4GLs (fourth-generation languages), 85
• 4NF (fourth normal form), 139
• 5NF (fifth normal form), 139

A

• abnormal forms, 140
• ABS function, 203
• abstract data types (ADTs), 44
• accessing data
  • FROM clause, 224, 225–226
  • FETCH capability, 250–251
  • GROUP BY clause, 224, 245–247
  • HAVING clause, 224, 247–248
  • logical connectives, 243–245
  • ORDER BY clause, 224, 225, 248–249
  • overview, 223
  • WHERE clause
    • ALL qualifier, 234–236
    • ANY qualifier, 234–236
    • comparison predicates, 227–228
    • defined, 224
    • DISTINCT predicate, 238
    • EXISTS predicate, 236–237
    • BETWEEN keyword, 228–229
    • LIKE predicate, 231
    • MATCH predicate, 239–242
    • NOT IN predicate, 230
    • NOT LIKE predicate, 231
    • NULL predicate, 232–233
    • OVERLAPS predicate, 238–239
    • overview, 226–227
    • IN predicate, 229–230
    • referential integrity, 240–242
    • SIMILAR predicate, 232
    • SOME qualifier, 234–236
    • UNIQUE predicate, 237–238
  • windows
    • groups of rows, 256
    • navigating within, 253–255
    • nesting functions, 255–256
    • overview, 251–252
    • partitioning into bucket with NTILE function, 252–253
    • row pattern recognition, 257
• ACID database, 344
• ACOS function, 204
• ActiveX controls, 373
• ad hoc queries, 25
• ad infinitum condition, 305
• adding new data
  • block of rows to tables, 152–155
  • overview, 150
  • by row, 151–152
  • to selected columns, 152
• ADTs (abstract data types), 44
• advanced value expressions. See value expressions
• aggregate functions. See set functions
• ALL qualifier
  • reducing subquery to single value with, 292–293
  • WHERE clause, 234–236
• ALTER TABLE statement, 67
• AND logical connective, 243–244, 471–472
• ANY qualifier
  • reducing subquery to single value with, 292–293
  • WHERE clause, 234–236
• application-period time tables
  • applying referential integrity constraints to, 169–170
  • designating primary keys in, 168–169
  • overview, 165–168
  • querying, 170–171
• applications, using SQL within
  • asterisk (*) character, 352
  • integrating SQL with procedural languages
    • embedded SQL, 355–357
    • module language, 358–360
    • object-oriented RAD tools, 360–361
    • using SQL with Microsoft Access, 361–363
  • overview, 351–352
  • problems combining SQL and procedural languages, 353–354
  • strengths and weaknesses of procedural languages, 353
  • strengths and weaknesses of SQL, 353
• approximate numeric data types
  • DOUBLE PRECISION type, 32
  • FLOAT type, 33
  • overview, 31
  • REAL type, 32
• ARRAY data type, 42–44
• ARRAY_MAX_CARDINALITY function, 203
• arrays
  • cardinality of, 43
  • two-dimensional, 14–15
• ASIN function, 204
• assertions, 50, 133
• asterisk (*) character, 144, 352
• ATAN function, 204
• AVG function, 74–75, 191

B

• backing up data, 343, 472
• bad input data, 128
• base tables, 145
• BCNF (Boyce-Codd normal form), 139
• BETWEEN keyword, 228–229
• BIGINT data type, 30
• BINARY data type, 36
• BINARY LARGE OBJECT (BLOB) data type, 36
• binary strings
  • BINARY data type, 36
  • BINARY LARGE OBJECT data type, 36
  • BINARY VARYING data type, 36
  • overview, 35
• BINARY VARYING (VARBINARY) data type, 36
• bitemporal tables, 175–176
• BLOB (BINARY LARGE OBJECT) data type, 36
• Booleans
  • SQL, 36
  • XML, 70–71
• Boyce-Codd normal form (BCNF), 139
• building databases
  • multi-table relational databases
    • data integrity, 122–133
    • designing, 110–119
    • indexes, 119–122
    • normalizing, 134–140
    • overview, 109
  • simple databases
    • overview, 85–86
    • portability, 107
    • with RAD tool, 86–98
    • with SQL DDL, 98–106

C

• CARDINALITY function, 203
• cardinality of arrays, 43
• Cartesian product
  • basic join, 267
  • cross join, 269–270
  • defined, 225
• cascading deletions, 125–126, 301
• CASE…END CASE statement
  • general discussion, 435
  • searched CASE statement, 436–437
  • simple CASE statement, 436
• CASE conditional expressions
  • COALESCE expression, 216–217
  • NULLIF expression, 215–216
  • overview, 210
  • using with search conditions, 211–212
  • using with values, 212–214
• CAST expression
  • overview, 217–219
  • using between SQL and host language, 220
  • using within SQL, 219
• catalogs, 65
• CEIL or CEILING function, 206
• chain of dependency, 329
• CHARACTER (CHAR) data type, 34
• CHARACTER LARGE OBJECT (CLOB) data type, 34
• character sets, 115
• character strings, 33–35
• CHARACTER VARYING data type, 34
• CHARACTER_LENGTH function, 202
• clients, 52
• client/server system
  • ODBC in, 370
  • SQL in, 50–52
• CLOB (CHARACTER LARGE OBJECT) data type, 34
• cloud, defined, 10–11
• clusters, 56, 65
• COALESCE expression, 216–217, 280–281
• Codd, E. F., 13, 41, 43, 136, 398
• collations (collating sequences), 67, 116
• collection data types
  • ARRAY type, 42–44
  • multiset type, 44
  • UDTs from, 48
• collection value expressions, 71
• column constraints, 131
• column references, 185
• column-name joins, 271–272
• columns
  • adding/deleting from existing table, 128
  • relations, 14–15
• comma-delimited values, 151
• COMMIT statement
  • DCL, 76
  • reducing vulnerability with, 342
• comparison operators, 296–298
• comparison predicates, 227–228
• complete logical views. See schemas
• composite keys, 117, 137
• compound statements
  • assignment, 434
  • atomicity, 429–430
  • condition handlers, 431–434
  • conditions, 431
  • cursors, 430
  • overview, 428–429
  • unhandles conditions, 434
  • variables, 430
• conceptual views. See schemas
• concurrent transactions, 333–335
• condition joins, 270–271
• conditional value expression, 189
• constraints
  • assertions, 133
  • column constraints, 131
  • defined, 50
  • overview, 130
  • protecting data and, 345–349
  • relational databases, 19
  • table constraints, 131–133
• constructors, 46
• containment hierarchy, 82
• CONTENT predicate, 390
• CONVERT function, 198
• correlated subqueries
  • defined, 294
  • in HAVING clause, 298–299
  • introduced with comparison operators, 296–298
  • introduced with IN predicate, 295–296
• CORRESPONDING operation, 262
• COS function, 204
• COSH function, 205
• COUNT function, 73, 189–191
• CREATE statement, 66–67
• cross joins, 269–270
• cross product. See Cartesian product
• cursors
  • closing, 425
  • declaring
    • ORDER BY clause, 417–419
    • overview, 416–417
    • query expression, 417
    • scrollability, 420
    • sensitivity, 419–420
    • updatability clause, 419
  • fetching data from single row, 422–424
  • opening, 421
  • overview, 415–416

D

• Data Control Language. See DCL
• Data Definition Language. See DDL
• data dictionary, defined, 9
• data integrity
  • multi-table relational databases
    • adding/deleting columns from existing table, 128
    • bad input data, 128
    • constraints, 130–133
    • data redundancy, 129–130
    • domain integrity, 124
    • entity integrity, 122–123
    • exceeding capacity of DBMS, 130
    • malice, 129
    • mechanical failure, 129
    • operator error, 129
    • overview, 122
    • referential integrity, 124–127
  • threats to
    • concurrent access, 333–335
    • equipment failure, 332–333
    • platform instability, 332
• data management. See also accessing data; relational operators
  • adding new data, 150–155
  • deleting data, 161
  • functions, 189–208
  • nested queries, 283–302
  • overview, 143–144
  • recursive queries, 303–312
  • retrieving data, 144–145
  • temporal data, 163–177
  • transferring data, 158–161
  • updating data, 155–158
  • value expressions, 186–189
  • values, 179–185
  • views, 145–150
• Data Manipulation Language. See DML
• data redundancy, 129–130
• data types
  • Booleans, 36
  • collection types, 42–44
  • datetimes, 36–38
  • intervals, 38
  • literals that conform to, 48–49
  • numeric, 29–33
  • overview, 28–29
  • REF types, 44
  • ROW types, 41–42
  • strings
    • binary, 35–36
    • character, 33–35
  • structured
    • constructors, 46
    • example, 47
    • mutators, 46
    • observers, 46
    • subtypes, 46
    • supertypes, 46
  • UDTs
    • from collection types, 48
    • distinct types, 45–46
    • overview, 44–45
    • structured types, 46–47
  • XML
    • overview, 38–41, 378–379
    • subtypes, 378
    • when not to use, 380
    • when to use, 379–380
• database administrator (DBA), 317
• database management system (DBMS), 1, 10–11, 130
• database object owners, 317–318
• database objects
  • defined, 317
  • defining, 110
  • locking, 343
• database servers, 51–52
• databases. See also building databases; names of specific kinds of databases
  • defined, 9
  • designing, 20–21
  • overview, 7–8
  • purpose of, 8–9
• DATE data type, 37
• dates, formatting and parsing, 176–177
• datetime data type, 36–38
• datetime value expressions, 70, 187–188
• datetime value functions, 207–208
• datetimes, 36–38
• day-time interval, 38
• DBA (database administrator), 317
• DBMS (database management system), 1, 10–11, 130
• DCL (Data Control Language)
  • defined, 55
  • overview, 316
  • referential integrity, 80–82
  • security, 82
  • transactions, 76–77
  • users and privileges, 77–80
• DDL (Data Definition Language)
  • building simple databases with
    • altering tables, 105–106
    • creating tables, 101–105
    • deleting tables, 106
    • index, 105, 106
    • overview, 98
    • SQL with Microsoft Access, 99–101
  • creating tables, 57–59
  • defined, 55
  • overview, 56–57
  • schemas, 64–65
  • statements, 66–67
  • views, 59–64
• DECFLOAT data type, 31
• DECIMAL data type, 31
• DEFERRABLE constraints, 346
• DELETE statements
  • nested queries and, 299–301
  • positioned, 424
• deleting data, 161
• departmental (workgroup) database, 10
• dirty read, 338–339
• distinct data types, 45–46
• DISTINCT predicate, 238
• DK/NF (domain-key normal form), 139–140
• DLLs (dynamic link libraries), 369
• DML (Data Manipulation Language)
  • defined, 55
  • LISTAGG function, 75
  • logical connectives, 73
  • predicates, 72
  • set functions, 73–75
  • subqueries, 76
  • value expressions
    • Boolean, 70–71
    • collection, 71
    • datetime, 70
    • interval, 70
    • numeric, 69
    • overview, 68–69
    • reference, 71
    • row, 71
    • string, 69–70
    • user-defined, 71
• DOCUMENT predicate, 390
• domain integrity, 124
• domain-key normal form (DK/NF), 139–140
• domains
  • multi-table relational databases, 115
  • relational databases, 18
• DOUBLE PRECISION data type, 32
• DROP TABLE statement, 67
• dynamic link libraries (DLLs), 369
• dynamic SQL, defined, 350

E

• embedded SQL, 355–357
• enterprise database, 10
• entity integrity, 122–123
• equi-joins, 267–269
• equipment failure, 332–333
• error handling
  • adding constraints to existing tables, 453–454
  • constraint violation example, 452–453
  • diagnostics areas, 448–452
  • exception handling, 455–456
  • SQLSTATE status parameter, 445–447, 454–455
  • WHENEVER clause, 447–448
• exact numeric data types
  • BIGINT type, 30
  • DECFLOAT type, 31
  • DECIMAL type, 31
  • INTEGER type, 29–30
  • NUMERIC type, 30–31
  • SMALLINT type, 30
• EXCEPT operator, 264–265
• exception avoidance, 212
• exception handling, 455–456
• EXISTS predicate
  • nested queries and, 294–295
  • WHERE clause, 236–237
• EXP function, 206
• eXtensible Markup Language data. See XML data
• EXTRACT function, 201

F

• FETCH statement, 250–251, 423
• fifth normal form (5NF), 139
• first normal form (1NF), 136–137
• FIRST_VALUE window function, 255
• flat files, 12–13
• FLOAT data type, 33
• floating-point numbers, 32
• FLOOR function, 206
• flow of control statements
  • CASE…END CASE statement, 435–437
  • FOR…DO…END FOR statement, 439
  • IF…THEN…ELSE…END IF statement, 435
  • ITERATE statement, 439–440
  • LEAVE statement, 437–438
  • LOOP…ENDLOOP statement, 437
  • REPEAT…UNTIL…END REPEAT statement, 438–439
  • WHILE…DO…END WHILE statement, 438
• FOR…DO…END FOR statement, 439
• foreign keys, 118–119, 240–241
• fourth normal form (4NF), 139
• fourth-generation languages (4GLs), 85
• Fowler, Adam, 13
• FROM clause, 224, 225–226
• full outer join, 275–276
• functional dependency, 137
• functions. See names of specific functions

G

• GRANT OPTION FOR clause, REVOKE statement, 328–329
• GRANT statement, 78, 318–319, 329–330
• GROUP BY clause, 224, 245–247, 471

H

• HAVING clause, 224, 247–248, 298–299

I

• IDEs (integrated development environments), 85
• IF…THEN…ELSE…END IF statement, 435
• impedance mismatch, 44
• implementations, defined, 26
• IN predicate
  • correlated subqueries introduced with, 295–296
  • subqueries introduced by, 286–288
  • WHERE clause, 229–230
• indexed sequential access method (ISAM) files, 369
• indexes
  • creating with DDL, 105
  • creating with RAD tool, 95–97
  • deleting with DDL, 106
  • multi-table relational databases, 119–122
• information schemas, 65
• inner joins, 272
• INSERT statement, 299–301
• INTEGER data type, 29–30
• integrated databases, defined, 9
• integrated development environments (IDEs), 85
• interactive SQL, 105, 351–352, 357
• Internet-based database system, 52–53
• INTERSECT operator, 262–264
• interval value expressions, 70, 188
• interval value functions, 208
• intervals, 38
• IS JSON predicate, 411
• ISAM (indexed sequential access method) files, 369
• isolation
  • READCOMMITTED level, 339–340, 341
  • READUNCOMMITTED level, 338–339, 341
  • REPEATABLEREAD level, 340, 341
  • SERIALIZABLE level, 340, 341
• ITERATE statement, 439–440

J

• Java DataBase Connectivity (JDBC), 373–375
• JavaScript Object Notation. See JSON
• JDBC (Java DataBase Connectivity), 373–375
• joins
  • basic, 265–267
  • ON clause, 282
  • column-name joins, 271–272
  • condition joins, 270–271
  • cross joins, 269–270
  • equi-joins, 267–269
  • inner joins, 272
  • natural joins, 270
  • outer joins, 272–276
  • union joins, 276–282
  • WHERE clause, 282
• JSON (JavaScript Object Notation)
  • general discussion, 399–400
  • SQL/JSON data model, 400–402
  • SQL/JSON functions
    • API common syntax, 403–404
    • constructor functions, 408–411
    • IS JSON predicate, 411
    • JSON nulls and SQL nulls, 411
    • query functions, 404–408
  • SQL/JSON Path language, 411–412
  • website with additional information about, 412
• JSON output clause, 404
• JSON value expression, 403–404
• JSON_ARRAY function, 410
• JSON_ARRAYAGG function, 410–411
• JSON_EXISTS function, 405
• JSON_OBJECT function, 408–409
• JSON_OBJECTAGG function, 409
• JSON_QUERY function, 407–408
• JSON_TABLE function, 408
• JSON_VALUE function, 406–407

K

• keys
  • composite keys, 117, 137
  • foreign keys, 118–119, 240–241
  • primary keys
    • designating in application-period time tables, 168–169
    • designating in system-versioned tables, 173
    • indexing, 96–97
    • multi-table relational database, 117–118
  • unique keys, 240–241
• KISS principle, 287

L

• LAG window function, 253–254
• LAST_VALUE window function, 255
• LEAD window function, 254
• LEAVE statement, 437–438
• left outer join, 273–275
• LIKE predicate, 231
• LISTAGG function, 75, 192–193
• literal values, 180–182
• literals that conform, 48–49
• LN function, 205
• LOG function, 205
• LOG10 function, 205
• logical connectives
  • AND, 243–244
  • DML, 73
  • NOT, 244–245
  • OR, 244
  • using parentheses with, 471–472
• logical schemas, 64
• logins, 316
• LOOP…ENDLOOP statement, 437
• LOWER function, 198

M

• MATCH predicate, 239–242
• MATCH_RECOGNIZE window function, 257
• materials processing, 311–312
• MAX function, 74, 192
• mechanical failure, 129
• MERGE statement, 158–160
• metadata, defined, 9
• Microsoft Access, using SQL with, 99–101, 361–363
• MIN function, 74, 192
• MOD function, 204
• modification anomalies, 134–140, 301
• modified attributes, creating views with, 148–149
• module language, 358–360
• multiset data type, 44
• multi-table relational databases, 109–140
  • character sets, 115
  • collations, 116
  • data integrity
    • adding/deleting columns from existing table, 128
    • bad input data, 128
    • constraints, 130–133
    • data redundancy, 129–130
    • domain integrity, 124
    • entity integrity, 122–123
    • exceeding capacity of DBMS, 130
    • malice, 129
    • mechanical failure, 129
    • operator error, 129
    • overview, 122
    • referential integrity, 124–127
  • domains, 115
  • identifying columns, 110–111
  • indexes, 119–122
  • keys, 116–119
  • normalization, 134–140
  • objects, 110
  • overview, 109
  • tables, 110–115
  • translations, 116
• multi-table view, 60–64
• mutators, 46

N

• natural joins, 270
• nested queries
  • correlated subqueries, 295–299
  • defined, 284
  • DELETE statement, 299–301
  • as existence test, 293–295
  • INSERT statement, 299–301
  • overview, 283–285
  • pipelined DML capability, 301–302
  • that return sets of rows, 285–289
  • that return single value, 289–293
  • UPDATE statement, 299–301
• nesting subqueries, 76
• NEXT keyword, 251
• nonrepeatable read, 339
• normal forms
  • Boyce-Codd normal form, 139
  • defined, 41
  • domain-key normal form, 139–140
  • fifth normal form, 139
  • first normal form, 136–137
  • fourth normal form, 139
  • overview, 136
  • second normal form, 137–138
  • third normal form, 138–139
• normalization
  • defined, 58
  • multi-table relational databases, 134–140
• NOT DEFERRABLE constraints, 346
• NOT EXISTS predicate, 295
• NOT IN predicate
  • subqueries introduced by, 288–289
  • WHERE clause, 230
• NOT LIKE predicate, 231
• NOT logical connective, 244–245, 471–472
• NOT NULL constraint, 345–346
• NTH_VALUE window function, 254–255
• NTILE function, 252–253
• NULL predicate, 232–233
• NULLIF expression, 215–216
• nulls
  • defined, 49–50
  • handling, 383–384
  • JSON, 411
  • SQL, 49–50, 411
  • SQL/JSON, 401
• NUMERIC data type, 30–31
• numeric data types
  • approximate, 31–33
  • exact, 29–31
• numeric value expressions, 69, 187
• numeric value functions
  • ABS function, 203
  • ACOS function, 204
  • ARRAY_MAX_CARDINALITY function, 203
  • ASIN function, 204
  • ATAN function, 204
  • CARDINALITY function, 203
  • CEIL or CEILING function, 206
  • CHARACTER_LENGTH function, 202
  • COS function, 204
  • COSH function, 205
  • EXP function, 206
  • EXTRACT function, 201
  • FLOOR function, 206
  • LN function, 205
  • LOG function, 205
  • LOG10 function, 205
  • MOD function, 204
  • OCCURRENCES_REGEX function, 200–201
  • OCTET_LENGTH function, 202–203
  • overview, 199
  • POSITION function, 200
  • POSITION_REGEX function, 201
  • POWER function, 206
  • SIN function, 204
  • SINH function, 205
  • SQRT function, 206
  • TAN function, 204
  • TANH function, 205
  • TRIM_ARRAY function, 203

O

• object databases, 19–20
• object-oriented rapid application development (RAD) tools, 352, 360–361
• object-relational databases, 19
• observers, 46
• OCCURRENCES_REGEX function, 200–201
• OCTET_LENGTH function, 202–203
• ODBC (Open DataBase Connectivity)
  • in client/server environment, 370
  • components of, 369
  • defined, 368
  • interface, 368
  • Internet and, 370–373
  • intranet and, 373
• OFFSET keyword, 251
• ON clause, 282
• Open DataBase Connectivity. See ODBC
• OPEN statement
  • date-time values, 422
  • opening cursors, 421
• operations control
  • database security
    • Data Control Language, 316
    • overview, 315–316
    • user access levels, 316–318
    • user privileges, 318–330
  • integrating SQL with procedural languages
    • embedded SQL, 355–357
    • module language, 358–360
    • object-oriented RAD tools, 360–361
    • using SQL with Microsoft Access, 361–363
  • protecting data
    • concurrent access, 333–335
    • constraints, 345–349
    • equipment failure, 332–333
    • overview, 331
    • platform instability, 332
    • reducing vulnerability with, 336–345
    • SQL injection attacks, 350
  • using SQL within applications
    • asterisk (*) character, 352
    • overview, 351–352
    • problems combining SQL and procedural languages, 353–354
    • strengths and weaknesses of procedural languages, 353
    • strengths and weaknesses of SQL, 353
• operator error, 129
• OR logical connective, 244, 471–472
• ORDER BY clause, 224, 225, 248–249, 417–419
• outer joins
  • full outer join, 275–276
  • left outer join, 273–275
  • overview, 272–273
  • right outer join, 275
• OVERLAPS predicate, 238–239
• OVERLAY function, 197

P

• parentheses, using with AND, OR, and NOT, 471–472
• PASSING clause, 404
• percent sign (%) character, 231
• persistent stored modules
  • compound statements
    • assignment, 434
    • atomicity, 429–430
    • condition handlers, 431–434
    • conditions, 431
    • cursors, 430
    • overview, 428–429
    • unhandles conditions, 434
    • variables, 430
  • flow of control statements
    • CASE…END CASE statement, 435–437
    • FOR…DO…END FOR statement, 439
    • IF…THEN…ELSE…END IF statement, 435
    • ITERATE statement, 439–440
    • LEAVE statement, 437–438
    • LOOP…ENDLOOP statement, 437
    • REPEAT…UNTIL…END REPEAT statement, 438–439
    • WHILE…DO…END WHILE statement, 438
  • general discussion, 427–428
  • privileges, 442–443
  • stored functions, 442
  • stored modules, 443–444
  • stored procedures, 440–441
• personal databases, 10
• phantom read, 340
• physical schemas, 64
• pipelined DML capability, 301–302
• platform instability, 332
• polymorphic table functions (PTFs), 208
• portability, 107
• POSITION function, 200
• POSITION_REGEX function, 201
• POWER function, 206
• predicates
  • DML, 72
  • XML, 390–391
• primary keys
  • designating in application-period time tables, 168–169
  • designating in system-versioned tables, 173
  • indexing, 96–97
  • multi-table relational database, 117–118
• privileges
  • Data Control Language, 77–80
  • deleting obsolete rows from tables, 322
  • general discussion, 318–320, 442–443
  • GRANT statement, 329–330
  • granting, 325–327
  • inserting data, 320–321
  • modifying table data, 321–322
  • referencing related tables, 322–323
  • removing, 328–329
  • REVOKE statement, 330
  • roles, 320
  • triggers, 324
  • using domains, 323–324
  • viewing data, 321
• procedural languages
  • integrating SQL with, 354–363
  • problems combining SQL and, 353–354
  • strengths and weaknesses of, 353
• PTF (polymorphic table function), 318

Q

• qualifying table names, 65
• quantified comparison operator, 289
• querying
  • application-period time tables, 170–171
  • queries, defined, 24
  • system-versioned tables, 174–175

R

• RAD (rapid application development) tool
  • building simple databases with
    • creating tables, 87–95
    • deleting tables, 97–98
    • index, 95–97
    • tracking information, 86–87
  • Microsoft Access, 99–101
  • object-oriented, 352, 360–361
• range checks, 128–129
• rapid application development tool. See RAD tool
• READCOMMITTED isolation level, 339–340, 341
• READUNCOMMITTED isolation level, 338–339, 341
• REAL data type, 32
• records, 9, 14–15
• recursive queries
  • defined, 306
  • materials processing, 311–312
  • overview, 306–308
  • recursion processing, 303–306
  • series of, 308–309
  • single, 309–311
• redundancy, 332–333
• REF types, 44
• reference value expressions, 71
• referential integrity
  • applying constraints to application-period time tables, 169–170
  • applying constraints to system-versioned tables, 174
  • cascading deletions, 125–126
  • controlling update anomalies, 127
  • Data Control Language, 80–82
  • MATCH predicate and, 240–242
  • overview, 124
  • relationship between parent and child tables, 125
• relational databases. See also multi-table relational databases
  • constraints, 19
  • domains, 18
  • overview, 13–14
  • relations, 14–15
  • schemas, 18
  • SQL and, 1–2
  • views (virtual tables), 16–18
• relational operators
  • EXCEPT operator, 264–265
  • INTERSECT operator, 262–264
  • joins
    • basic, 265–267
    • ON clause, 282
    • column-name joins, 271–272
    • condition joins, 270–271
    • cross joins, 269–270
    • equi-joins, 267–269
    • inner joins, 272
    • natural joins, 270
    • outer joins, 272–276
    • union joins, 276–282
    • WHERE clause, 282
  • UNION operator, 259–262
• relations (tables), defined, 14–15
• REPEAT…UNTIL…END REPEAT statement, 438–439
• REPEATABLEREAD isolation level, 340, 341
• repeating groups, 43
• reserved words
  • list of, 473–477
  • overview, 28
• RESTRICT option, REVOKE statement, 328
• retrieving data. See also data management
  • backing up databases regularly, 472
  • controlling retrieval privileges, 472
  • double-checking queries that include joins, 470
  • handling error conditions gracefully, 472
  • overview, 144–145
  • summarizing data with GROUP BY, 471
  • triple-checking queries with subselects, 470
  • trying queries on test database, 470
  • using parentheses with AND, OR, and NOT, 471–472
  • verifying database structure, 470
  • watching GROUP BY clause restrictions, 471
• REVOKE statement, 78, 330
• right outer join, 275
• roles, defined, 320
• ROLLBACK statement, 76, 342
• ROW types, 41–42
• row value expressions, 71, 221–222
• row values, 180
• rows (records; tuples), 14–15

S

• savepoints, 344–345
• scalable DBMS, 11
• scale of number, defined, 30
• schemas (conceptual views; complete logical views)
  • catalogs, 65
  • collecting tables into, 64–65
  • relational databases, 18
• second normal form (2NF), 137–138
• security
  • Data Control Language, 82, 316
  • overview, 315–316
  • protecting data
    • concurrent access, 333–335
    • constraints, 345–349
    • equipment failure, 332–333
    • overview, 331
    • platform instability, 332
    • reducing vulnerability with, 336–345
    • SQL injection attacks, 350
  • reducing vulnerability to data corruption
    • ACID database, 344
    • backing up data, 343
    • with COMMIT statement, 342
    • default transaction, 338
    • isolation, 338–341
    • locking database objects, 343
    • with ROLLBACK statement, 342
    • savepoints, 344–345
    • with SET TRANSACTION statement, 341–342
    • with SQL transactions, 336–338
    • subtransactions, 344–345
    • transaction-starting statement, 341
  • user access levels
    • database administrator, 317
    • database object owners, 317–318
    • overview, 316
    • public, 318
  • user privileges
    • deleting obsolete rows from tables, 322
    • GRANT statement, 329–330
    • granting across levels, 325–327
    • granting power to grant privileges, 327
    • inserting data, 320–321
    • modifying table data, 321–322
    • overview, 318–320
    • referencing related tables, 322–323
    • removing privileges, 328–329
    • REVOKE statement, 330
    • roles, 320
    • triggers, 324
    • using domains, 323–324
    • viewing data, 321
• selection condition, 147–148
• self-consistent columns, relations, 15
• self-describing databases, 9
• SERIALIZABLE isolation level, 340, 341
• serialization of concurrent transactions, 335
• servers (database servers), 51–52
• set (aggregate) functions
  • AVG function, 74–75
  • COUNT function, 73
  • MAX function, 74
  • MIN function, 74
  • SUM function, 74
• SET CONSTRAINTS DEFERRED statement, 348
• set functions, 291
• SET TRANSACTION statement, 338, 341–342
• SIMILAR predicate, 232
• simple databases, building
  • with DDL
    • altering tables, 105–106
    • creating tables, 101–105
    • deleting tables, 106
    • index, 105, 106
    • overview, 98
    • SQL with Microsoft Access, 99–101
  • overview, 85–86
  • portability, 107
  • with RAD tool, 86–98
    • creating tables, 87–95
    • deleting tables, 97–98
    • index, 95–97
    • tracking information, 86–87
  • with SQL DDL, 98–106
• SIN function, 204
• single-table view, 59–60
• SINH function, 205
• SMALLINT data type, 30
• social engineering, 81
• SOME qualifier
  • reducing subquery to single value with, 292–293
  • WHERE clause, 234–236
• source type, 45
• special variables, 184
• SQL
  • in client/server system, 50–52
  • common mistakes, 465–468
  • constraints, 50
  • data types
    • approximate numerics, 31–33
    • binary strings, 35–36
    • Booleans, 36
    • character strings, 33–35
    • collection types, 42–44
    • datetimes, 36–38
    • exact numerics, 29–31
    • intervals, 38
    • literals that conform to, 48–49
    • overview, 28–29
    • REF types, 44
    • ROW types, 41–42
    • UDTs, 44–48
    • XML, 38–41
  • history of, 25–26
  • on Internet-based database system, 52–53
  • null values, 49–50
  • overview, 23–25
  • reserved words, 28, 473–477
  • statements, 26–28
• SQL injection attacks, 350
• SQL/JSON data model
  • parsing JSON, 402
  • serializing JSON, 402
  • SQL/JSON items, 401–402
  • SQL/JSON sequences, 402
  • uses for, 400
• SQL/JSON functions
  • API common syntax, 403–404
  • constructor functions, 408–411
  • IS JSON predicate, 411
  • JSON nulls and SQL nulls, 411
  • query functions, 404–408
• SQL/JSON null, 401
• SQL/JSON object, 402
• SQL/JSON Path language, 411–412
• SQL/JSON scalar, 401
• SQL/JSON sequences, 402
• SQLSTATE status parameter
  • interpreting the information returned by, 454–455
  • overview, 445–447
• SQRT function, 206
• statements, defined, 26–28
• static SQL, 350
• stored functions, 442
• stored modules, 443–444
• stored procedures, 440–441
• string value expressions, 69–70, 186–187
• string value functions
  • CONVERT function, 198
  • LOWER function, 198
  • OVERLAY function, 197
  • overview, 193
  • SUBSTRING function, 194–195
  • SUBSTRING_REGEX function, 195–196
  • TRANSLATE function, 198
  • TRANSLATE_REGEX function, 196–197
  • TRIM function, 198
  • UPPER function, 198
• strings
  • binary, 35–36
  • character, 33–35
• structured data types
  • constructors, 46
  • example, 47
  • leaf types, 325
  • mutators, 46
  • observers, 46
  • subtypes, 46, 325
  • supertypes, 46, 325
  • typed table, 325–326
• subqueries, 76.See also nested queries
• SUBSTRING function, 194–195
• SUBSTRING_REGEX function, 195–196
• subtransactions, 344–345
• subtypes, 46
• SUM function, 74, 192
• super users, 317
• supertypes, 46
• system administrators, 317
• system-versioned tables
  • applying referential integrity constraints to, 174
  • designating primary keys in, 173
  • overview, 171–173
  • querying, 174–175

T

• table constraints, 131–133
• table functions, 208
• tables
  • altering with DDL, 105–106
  • altering with RAD tool, 93–95
  • collecting into schemas, 64–65
  • creating views from, 146–147
  • creating with DDL, 101–105
  • creating with RAD tool, 87–93
  • DDL, 57–59
  • deleting with DDL, 106
  • deleting with RAD tool, 97–98
  • in multi-table relational databases, 110–115
• TAN function, 204
• TANH function, 205
• temporal data
  • application-period time tables, 165–171
  • bitemporal tables, 175–176
  • formatting and parsing dates and times, 176–177
  • overview, 163–164
  • system-versioned tables, 171–175
  • times and periods, 164–165
• termination condition, 305
• third normal form (3NF), 138–139
• third-generation languages, 85
• TIME WITH TIME ZONE data type, 38
• TIME WITHOUT TIME ZONE data type, 37
• times
  • formatting and parsing, 176–177
  • temporal data, 164–165
• TIMESTAMP WITH TIME ZONE data type, 38
• TIMESTAMP WITHOUT TIME ZONE data type, 37
• transaction processing
  • concurrent access, 333–335
  • defined, 333
  • SQL transactions, 336–338
• transaction time, 164
• transactions
  • Data Control Language, 76–77
  • defined, 333
• transaction-starting statement, 341
• transferring data, 158–161
• transitive dependency, 138
• TRANSLATE function, 198
• TRANSLATE_REGEX function, 196–197
• translation tables, 67
• translations, 116
• triggers
  • applications of, 457–458
  • defined, 324
  • example trigger definition, 459–460
  • firing multiple on single table, 462
  • firing succession of, 460
  • general discussion, 457
  • referencing old and new values, 461–462
  • row triggers, 459
  • specifying when triggers fire, 459
  • statement triggers, 459
  • trigger action time, 325
  • trigger events, 325
  • triggered actions, 325
  • triggered SQL statement, 459
• TRIM function, 198
• TRIM_ARRAY function, 203
• tuples, 14–15
• two-dimensional arrays, 14–15

U

• UDTs (user-defined types)
  • from collection types, 48
  • distinct types, 45–46
  • overview, 44–45
  • structured types, 46–47
• underscore (_) character, 231
• UNION ALL operation, 261
• union joins, 276–282
• UNION operator
  • CORRESPONDING operation, 262
  • overview, 259–261
  • UNION ALL operation, 261
• unique keys, 240–241
• UNIQUE predicate, 237–238
• update anomalies, 124–127
• UPDATE statements
  • nested queries and, 299–301
  • positioned, 424
• updating data, 155–158
• UPPER function, 198
• user access levels
  • database administrator, 317
  • database object owners, 317–318
  • overview, 316
  • public, 318
• user names, 320
• user privileges. See privileges
• user-defined types. See UDTs
• user-defined value expressions, 71

V

• VALID predicate, 391
• valid time, 164
• value expressions
  • advanced
    • CASE conditional expressions, 210–217
    • CAST expression, 217–220
    • overview, 209
    • row value expressions, 221–222
  • conditional value expression, 189
  • datetime value expressions, 187–188
  • DML, 68–71
  • interval value expressions, 188
  • numeric value expressions, 187
  • string value expressions, 186–187
• value functions
  • datetime, 207–208
  • interval, 208
  • numeric, 199–206
  • string, 193–199
• values
  • column references, 185
  • literal values, 180–182
  • overview, 179–180
  • row values, 180
  • special variables, 184
  • using CASE conditional expressions with, 212–214
  • variables, 182–183
• VARBINARY (BINARY VARYING) data type, 36
• variables, 182–183
• views (virtual tables)
  • creating
    • with modified attribute, 148–149
    • overview, 145–146
    • with selection condition, 147–148
    • from tables, 146–147
  • DDL
    • multi-table view, 60–64
    • single-table view, 59–60
  • relational databases, 16–18
  • updating, 149–150

W

• what-if processing, 312
• WHENEVER clause, 447–448
• WHERE clause
  • ALL qualifier, 234–236
  • ANY qualifier, 234–236
  • comparison predicates, 227–228
  • defined, 224
  • DISTINCT predicate, 238
  • EXISTS predicate, 236–237
  • joins and, 282
  • BETWEEN keyword, 228–229
  • LIKE predicate, 231
  • MATCH predicate, 239–242
  • NOT IN predicate, 230
  • NOT LIKE predicate, 231
  • NULL predicate, 232–233
  • OVERLAPS predicate, 238–239
  • overview, 226–227
  • IN predicate, 229–230
  • referential integrity, 240–242
  • SIMILAR predicate, 232
  • SOME qualifier, 234–236
  • UNIQUE predicate, 237–238
• WHILE…DO…END WHILE statement, 438
• windows
  • FIRST_VALUE function, 255
  • groups of rows, 256
  • LAG function, 253–254
  • LAST_VALUE function, 255
  • LEAD function, 254
  • MATCH_RECOGNIZE function, 257
  • nesting functions, 255–256
  • NTH_VALUE function, 254–255
  • overview, 251–252
  • partitioning into bucket with NTILE function, 252–253
  • row pattern recognition, 257
• WITH TIES modifier, 250
• workgroup (departmental) database, 10

X

• XML (eXtensible Markup Language) data
  • data type, 38–41
  • mapping between SQL and, 380–385
  • mapping non-predefined data types to, 393–398
  • predicates, 390–391
  • relationship to SQL, 377–378
  • SQL functions that operate on, 385–390
  • transforming into SQL tables, 392–393
  • XML data type, 378–380
• XMLAGG function, 386–387
• XMLCAST function, 389–390
• XMLCOMMENT function, 388
• XMLCONCAT function, 386–387
• XMLDOCUMENT function, 385
• XMLELEMENT function, 385–386
• XMLEXISTS predicate, 390–391
• XMLFOREST function, 386
• XMLPARSE function, 388
• XMLPI function, 388
• XMLQUERY function, 389

Y

• year-month interval, 38