SQL : a beginner's guide

SQL: A Beginner's Guide 2nd......Page 2
About the Author......Page 3
Contents at a Glance......Page 4
Contents......Page 6
Acknowledgments......Page 14
Introduction......Page 15
What Content the Book Covers......Page 16
Description of the Book¡¯s Content......Page 17
Module Content......Page 20
I SQL Databases......Page 24
1 Introduction to Relational Databases and SQL......Page 25
Critical Skill 1.1 Understand Relational Databases......Page 26
Project 1-1 Normalizing Data and 
Identifying Relationships......Page 34
The Relational Model......Page 27
Progress Check......Page 32
The SQL Evolution......Page 36
Critical Skill 1.3 Use a Relational Database Management System......Page 41
Types of SQL Statements......Page 38
Types of Execution......Page 39
Progress Check......Page 40
SQL Standard Versus Product Implementations......Page 42
Project 1-2 Connecting to a Database......Page 44
Module 1 Mastery Check......Page 47
2 Working with the SQL Environment......Page 48
Critical Skill 2.1 Understand the SQL Environment......Page 49
Critical Skill 2.2 Understand SQL Catalogs......Page 51
Schemas......Page 52
Schema Objects......Page 53
Ask the Expert......Page 55
Then What Is a Database?......Page 56
Progress Check......Page 57
Critical Skill 2.3 Name Objects in an SQL Environment......Page 58
Qualified Names......Page 59
Critical Skill 2.4 Create a Schema......Page 60
Progress Check......Page 62
Critical Skill 2.5 Create a Database......Page 63
Project 2-1 Creating a Database and a Schema......Page 64
Module 2 Mastery Check......Page 65
3 Creating and Altering Tables......Page 67
Critical Skill 3.1 Create SQL Tables......Page 68
Critical Skill 3.2 Specify Column Data Types......Page 72
Progress Check......Page 69
String Data Types......Page 73
Numeric Data Types......Page 75
Datetime Data Types......Page 76
Interval Data Type......Page 77
Boolean Data Type......Page 79
Ask the Expert......Page 80
Progress Check......Page 81
Critical Skill 3.3 Create User-Defined Types......Page 82
Critical Skill 3.4 Specify Column Default Values......Page 83
Progress Check......Page 84
Project 3-1 Creating SQL Tables......Page 85
Critical Skill 3.5 Alter SQL Tables......Page 87
Critical Skill 3.6 Delete SQL Tables......Page 88
Ask the Expert......Page 89
Project 3-2 Altering and Deleting SQL Tables......Page 90
Module 3 Mastery Check......Page 91
4 Enforcing Data Integrity......Page 93
Critical Skill 4.1 Understand Integrity Constraints......Page 94
Critical Skill 4.2 Use NOT NULL Constraints......Page 96
Critical Skill 4.3 Add UNIQUE Constraints......Page 97
Critical Skill 4.4 Add PRIMARY KEY Constraints......Page 100
Critical Skill 4.5 Add FOREIGN KEY Constraints......Page 103
The MATCH Clause......Page 108
The < referential triggered action> Clause......Page 110
Progress Check......Page 112
Critical Skill 4.6 Define CHECK Constraints......Page 116
Creating Domains and Domain Constraints......Page 119
Progress Check......Page 120
Project 4-2 Adding a CHECK Constraint......Page 121
Module 4 Mastery Check......Page 122
5 Creating SQL Views......Page 124
Critical Skill 5.1 Add Views to the Database......Page 125
Defining SQL Views......Page 131
Critical Skill 5.2 Create Updateable Views......Page 135
Using the WITH CHECK OPTION Clause......Page 138
Progress Check......Page 139
Ask the Expert......Page 140
Project 5-1 Adding Views to Your Database......Page 141
Module 5 Mastery Check......Page 142
6 Managing Database Security......Page 144
Critical Skill 6.1 Understand the SQL Security Model......Page 145
SQL Sessions......Page 146
Accessing Database Objects......Page 149
Critical Skill 6.2 Create and Delete Roles......Page 152
Critical Skill 6.3 Grant and Revoke Privileges......Page 153
Revoking Privileges......Page 157
Critical Skill 6.4 Grant and Revoke Roles......Page 160
Revoking Roles......Page 161
Project 6-1 Managing Roles and Privileges......Page 162
Project Summary......Page 163
Module 6 Mastery Check......Page 164
II Data Access and Modification......Page 166
7 Querying SQL Data......Page 167
Critical Skill 7.1 Use a SELECT Statement to Retrieve Data......Page 168
The SELECT Clause and FROM Clause......Page 169
Ask the Expert......Page 170
Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions......Page 175
Defining the WHERE Clause......Page 179
Progress Check......Page 182
Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions......Page 183
Critical Skill 7.4 Use the HAVING Clause to Specify Group Search Conditions......Page 188
Critical Skill 7.5 Use the ORDER BY Clause to Sort Query Results......Page 190
Ask the Expert......Page 192
Project 7-1 Querying the Inventory Database......Page 193
Module 7 Mastery Check......Page 196
8 Modifying SQL Data......Page 199
Critical Skill 8.1 Insert SQL Data......Page 200
Inserting Values from a SELECT Statement......Page 204
Critical Skill 8.2 Update SQL Data......Page 206
Updating Values from a SELECT Statement......Page 209
Ask the Expert......Page 210
Progress Check......Page 211
Critical Skill 8.3 Delete SQL Data......Page 212
Project 8-1 Modifying SQL Data......Page 213
Module 8 Mastery Check......Page 216
9 Using Predicates......Page 218
Critical Skill 9.1 Compare SQL Data......Page 219
Using the BETWEEN Predicate......Page 223
Critical Skill 9.2 Return Null Values......Page 225
Critical Skill 9.3 Return Similar Values......Page 228
Progress Check......Page 231
Project 9-1 Using Predicates in SQL Statements......Page 232
Critical Skill 9.4 Reference Additional Sources of Data......Page 234
Using the IN Predicate......Page 235
Using the EXISTS Predicate......Page 238
Ask the Expert......Page 240
Critical Skill 9.5 Quantify Comparison Predicates......Page 241
Using the SOME and ANY Predicates......Page 242
Ask the Expert......Page 244
Project 9-2 Using Subqueries in Predicates......Page 245
Module 9 Mastery Check......Page 247
10 Working with Functions and Value Expressions......Page 250
Using the COUNT Function......Page 251
Using the MAX and MIN Functions......Page 253
Using the AVG Function......Page 256
Progress Check......Page 257
Working with String Value Functions......Page 258
Working with Datetime Value Functions......Page 261
Progress Check......Page 262
Working with Numeric Value Expressions......Page 263
Using the CASE Value Expression......Page 266
Ask the Expert......Page 268
Using the CAST Value Expression......Page 269
Progress Check......Page 270
Critical Skill 10.4 Use Special Values......Page 271
Project 10-1 Using Functions and Value
 Expressions......Page 272
Module 10 Mastery Check......Page 275
11 Accessing Multiple Tables......Page 277
Critical Skill 11.1 Perform Basic Join Operations......Page 278
Using Correlation Names......Page 281
Creating Joins with More than Two Tables......Page 282
Creating the Cross Join......Page 283
Creating the Self- Join......Page 284
Critical Skill 11.2 Join Tables with Shared Column Names......Page 286
Creating the Natural Join......Page 287
Critical Skill 11.3 Use the Condition Join......Page 288
Creating the Inner Join......Page 289
Creating the Outer Join......Page 291
Progress Check......Page 294
Critical Skill 11.4 Perform Union Operations......Page 295
Project 11-1 Querying Multiple Tables......Page 297
Module 11 Mastery Check......Page 299
12 Using Subqueries to Access and Modify Data......Page 301
Critical Skill 12.1 Create Subqueries That Return Multiple Rows......Page 302
Using the IN Predicate......Page 303
Using the EXISTS Predicate......Page 304
Using Quantified Comparison Predicates......Page 305
Critical Skill 12.2 Create Subqueries That Return One Value......Page 307
Progress Check......Page 308
Critical Skill 12.4 Use Nested Subqueries......Page 310
Critical Skill 12.3 Work with Correlated Subqueries......Page 309
Critical Skill 12.5 Use Subqueries to Modify Data......Page 312
Using Subqueries to Insert Data......Page 313
Using Subqueries to Update Data......Page 314
Project 12-1 Working with Subqueries......Page 315
Module 12 Mastery Check......Page 319
III Advanced Data Access......Page 321
13 Creating SQL-Invoked Routines......Page 322
Critical Skill 13.1 Understand SQL-Invoked Routines......Page 323
SQL- Invoked Procedures and Functions......Page 324
Working with the Basic Syntax......Page 325
Progress Check......Page 326
Critical Skill 13.2 Create SQL-Invoked Procedures......Page 327
Invoking SQL- Invoked Procedures......Page 328
Progress Check......Page 329
Critical Skill 13.3 Add Input Parameters to Your Procedures......Page 330
Using Procedures to Modify Data......Page 333
Critical Skill 13.4 Add Local Variables to Your Procedures......Page 334
Create Compound Statements......Page 336
Create Conditional Statements......Page 337
Ask the Expert......Page 339
Project 13-1 Creating SQL-Invoked Procedures......Page 341
Critical Skill 13.6 Add Output Parameters to Your Procedures......Page 343
Progress Check......Page 344
Critical Skill 13.7 Create SQL-Invoked Functions......Page 345
Project 13-2 Creating SQL-Invoked Functions......Page 347
Module 13 Mastery Check......Page 348
14 Creating SQL Triggers......Page 351
Critical Skill 14.1 Understand SQL Triggers......Page 352
Trigger Execution Context......Page 353
Progress Check......Page 354
Critical Skill 14.2 Create SQL Triggers......Page 355
Referencing Old and New Values......Page 356
Critical Skill 14.3 Create Insert Triggers......Page 358
Critical Skill 14.4 Create Update Triggers......Page 361
Ask the Expert......Page 365
Critical Skill 14.5 Create Delete Triggers......Page 366
Project 14-1 Creating SQL Triggers......Page 368
Module 14 Mastery Check......Page 371
15 Using SQL Cursors......Page 374
Critical Skill 15.1 Understand SQL Cursors......Page 375
Declaring and Opening SQL Cursors......Page 376
Progress Check......Page 378
Working with Optional Syntax Elements......Page 379
Creating a Cursor Declaration......Page 383
Progress Check......Page 386
Critical Skill 15.4 Retrieve Data from a Cursor......Page 387
Ask the Expert......Page 392
Using the Positioned UPDATE Statement......Page 393
Using the Positioned DELETE Statement......Page 395
Project 15-1 Working with SQL Cursors......Page 396
Module 15 Mastery Check......Page 399
16 Managing SQL Transactions......Page 401
Critical Skill 16.1 Understand SQL Transactions......Page 402
Critical Skill 16.2 Set Transaction Properties......Page 405
Specifying an Isolation Level......Page 406
Specifying a Diagnostics Size......Page 411
Creating a SET TRANSACTION Statement......Page 412
Critical Skill 16.3 Start a Transaction......Page 413
Critical Skill 16.4 Set Constraint Deferrability......Page 414
Ask the Expert......Page 415
Progress Check......Page 417
Critical Skill 16.5 Create Savepoints in a Transaction......Page 418
Critical Skill 16.6 Terminate a Transaction......Page 420
Committing a Transaction......Page 421
Rolling Back a Transaction......Page 422
Project 16-1 Working with Transactions......Page 423
Module 16 Mastery Check......Page 426
17 Accessing SQL Data from Your Host Program......Page 429
Critical Skill 17.1 Invoke SQL Directly......Page 430
Critical Skill 17.2 Embed SQL Statements in Your Program......Page 431
Creating an Embedded SQL Statement......Page 433
Using Host Variables in Your SQL Statements......Page 435
Retrieving SQL Data......Page 436
Ask the Expert......Page 437
Error Handling......Page 439
Project 17-1 Embedding SQL Statements......Page 441
Critical Skill 17.3 Create SQL Client Modules......Page 444
Defining SQL Client Modules......Page 445
Critical Skill 17.4 Use an SQL Call-Level Interface......Page 447
Allocating Handles......Page 449
Executing SQL Statements......Page 452
Working with Host Variables......Page 453
Retrieving SQL Data......Page 454
Project 17-2 Using the SQL Call-Level Interface......Page 455
Module 17 Mastery Check......Page 458
IV Appendixes......Page 461
Module 1: Introduction to Relational 
Databases and SQL......Page 463
Module 2: Working with the SQL Environment......Page 464
Module 3: Creating and Altering Tables......Page 466
Module 4: Enforcing Data Integrity......Page 469
Module 5: Creating SQL Views......Page 471
Module 6: Managing Database Security......Page 473
Module 7: Querying SQL Data
1.......Page 475
Module 8: Modifying SQL Data......Page 477
Module 9: Using Predicates......Page 480
Module 10: Working with Functions 
and Value Expressions......Page 482
Module 11: Accessing Multiple Tables......Page 484
Module 12: Using Subqueries to Access
 and Modify Data
......Page 486
Module 13: Creating SQL-Invoked Routines......Page 487
Module 14: Creating SQL Triggers......Page 489
Module 15: Using SQL Cursors......Page 492
Index......Page 525