Robert Sheldon0072228857, 9780072228854, 9780072252859
Table of contents :
SQL
A Beginner’s Guide……Page 5
About the Author……Page 7
Contents at a Glance……Page 9
Contents……Page 11
Acknowledgments……Page 19
Introduction……Page 21
What Content the Book Covers……Page 22
Description of the Book¡¯s Content……Page 23
Module Content……Page 26
I SQL Databases……Page 31
1 Introduction to Relational Databases and SQL……Page 33
Critical Skill 1.1 Understand Relational Databases……Page 34
Project 1-1 Normalizing Data and
Identifying Relationships……Page 42
The Relational Model……Page 35
Progress Check……Page 40
The SQL Evolution……Page 44
Critical Skill 1.3 Use a Relational Database Management System……Page 49
Types of SQL Statements……Page 46
Types of Execution……Page 47
Progress Check……Page 48
SQL Standard Versus Product Implementations……Page 50
Project 1-2 Connecting to a Database……Page 52
Module 1 Mastery Check……Page 55
2 Working with the SQL Environment……Page 57
Critical Skill 2.1 Understand the SQL Environment……Page 58
Critical Skill 2.2 Understand SQL Catalogs……Page 60
Schemas……Page 61
Schema Objects……Page 62
Ask the Expert……Page 64
Then What Is a Database?……Page 65
Progress Check……Page 66
Critical Skill 2.3 Name Objects in an SQL Environment……Page 67
Qualified Names……Page 68
Critical Skill 2.4 Create a Schema……Page 69
Progress Check……Page 71
Critical Skill 2.5 Create a Database……Page 72
Project 2-1 Creating a Database and a Schema……Page 73
Module 2 Mastery Check……Page 74
3 Creating and Altering Tables……Page 77
Critical Skill 3.1 Create SQL Tables……Page 78
Critical Skill 3.2 Specify Column Data Types……Page 82
Progress Check……Page 79
String Data Types……Page 83
Numeric Data Types……Page 85
Datetime Data Types……Page 86
Interval Data Type……Page 87
Boolean Data Type……Page 89
Ask the Expert……Page 90
Progress Check……Page 91
Critical Skill 3.3 Create User-Defined Types……Page 92
Critical Skill 3.4 Specify Column Default Values……Page 93
Progress Check……Page 94
Project 3-1 Creating SQL Tables……Page 95
Critical Skill 3.5 Alter SQL Tables……Page 97
Critical Skill 3.6 Delete SQL Tables……Page 98
Ask the Expert……Page 99
Project 3-2 Altering and Deleting SQL Tables……Page 100
Module 3 Mastery Check……Page 101
4 Enforcing Data Integrity……Page 103
Critical Skill 4.1 Understand Integrity Constraints……Page 104
Critical Skill 4.2 Use NOT NULL Constraints……Page 106
Critical Skill 4.3 Add UNIQUE Constraints……Page 107
Critical Skill 4.4 Add PRIMARY KEY Constraints……Page 110
Critical Skill 4.5 Add FOREIGN KEY Constraints……Page 113
The MATCH Clause……Page 118
The < referential triggered action> Clause……Page 120
Progress Check……Page 122
Critical Skill 4.6 Define CHECK Constraints……Page 126
Creating Domains and Domain Constraints……Page 129
Progress Check……Page 130
Project 4-2 Adding a CHECK Constraint……Page 131
Module 4 Mastery Check……Page 132
5 Creating SQL Views……Page 135
Critical Skill 5.1 Add Views to the Database……Page 136
Defining SQL Views……Page 142
Critical Skill 5.2 Create Updateable Views……Page 146
Using the WITH CHECK OPTION Clause……Page 149
Progress Check……Page 150
Ask the Expert……Page 151
Project 5-1 Adding Views to Your Database……Page 152
Module 5 Mastery Check……Page 153
6 Managing Database Security……Page 155
Critical Skill 6.1 Understand the SQL Security Model……Page 156
SQL Sessions……Page 157
Accessing Database Objects……Page 160
Critical Skill 6.2 Create and Delete Roles……Page 163
Critical Skill 6.3 Grant and Revoke Privileges……Page 164
Revoking Privileges……Page 168
Critical Skill 6.4 Grant and Revoke Roles……Page 171
Revoking Roles……Page 172
Project 6-1 Managing Roles and Privileges……Page 173
Project Summary……Page 174
Module 6 Mastery Check……Page 175
II Data Access and Modification……Page 177
7 Querying SQL Data……Page 179
Critical Skill 7.1 Use a SELECT Statement to Retrieve Data……Page 180
The SELECT Clause and FROM Clause……Page 181
Ask the Expert……Page 182
Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions……Page 187
Defining the WHERE Clause……Page 191
Progress Check……Page 194
Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions……Page 195
Critical Skill 7.4 Use the HAVING Clause to Specify Group Search Conditions……Page 200
Critical Skill 7.5 Use the ORDER BY Clause to Sort Query Results……Page 202
Ask the Expert……Page 204
Project 7-1 Querying the Inventory Database……Page 205
Module 7 Mastery Check……Page 208
8 Modifying SQL Data……Page 211
Critical Skill 8.1 Insert SQL Data……Page 212
Inserting Values from a SELECT Statement……Page 216
Critical Skill 8.2 Update SQL Data……Page 218
Updating Values from a SELECT Statement……Page 221
Ask the Expert……Page 222
Progress Check……Page 223
Critical Skill 8.3 Delete SQL Data……Page 224
Project 8-1 Modifying SQL Data……Page 225
Module 8 Mastery Check……Page 228
9 Using Predicates……Page 231
Critical Skill 9.1 Compare SQL Data……Page 232
Using the BETWEEN Predicate……Page 236
Critical Skill 9.2 Return Null Values……Page 238
Critical Skill 9.3 Return Similar Values……Page 241
Progress Check……Page 244
Project 9-1 Using Predicates in SQL Statements……Page 245
Critical Skill 9.4 Reference Additional Sources of Data……Page 247
Using the IN Predicate……Page 248
Using the EXISTS Predicate……Page 251
Ask the Expert……Page 253
Critical Skill 9.5 Quantify Comparison Predicates……Page 254
Using the SOME and ANY Predicates……Page 255
Ask the Expert……Page 257
Project 9-2 Using Subqueries in Predicates……Page 258
Module 9 Mastery Check……Page 260
10 Working with Functions and Value Expressions……Page 263
Using the COUNT Function……Page 264
Using the MAX and MIN Functions……Page 266
Using the AVG Function……Page 269
Progress Check……Page 270
Working with String Value Functions……Page 271
Working with Datetime Value Functions……Page 274
Progress Check……Page 275
Working with Numeric Value Expressions……Page 276
Using the CASE Value Expression……Page 279
Ask the Expert……Page 281
Using the CAST Value Expression……Page 282
Progress Check……Page 283
Critical Skill 10.4 Use Special Values……Page 284
Project 10-1 Using Functions and Value
Expressions……Page 285
Module 10 Mastery Check……Page 288
11 Accessing Multiple Tables……Page 291
Critical Skill 11.1 Perform Basic Join Operations……Page 292
Using Correlation Names……Page 295
Creating Joins with More than Two Tables……Page 296
Creating the Cross Join……Page 297
Creating the Self- Join……Page 298
Critical Skill 11.2 Join Tables with Shared Column Names……Page 300
Creating the Natural Join……Page 301
Critical Skill 11.3 Use the Condition Join……Page 302
Creating the Inner Join……Page 303
Creating the Outer Join……Page 305
Progress Check……Page 308
Critical Skill 11.4 Perform Union Operations……Page 309
Project 11-1 Querying Multiple Tables……Page 311
Module 11 Mastery Check……Page 313
12 Using Subqueries to Access and Modify Data……Page 315
Critical Skill 12.1 Create Subqueries That Return Multiple Rows……Page 316
Using the IN Predicate……Page 317
Using the EXISTS Predicate……Page 318
Using Quantified Comparison Predicates……Page 319
Critical Skill 12.2 Create Subqueries That Return One Value……Page 321
Progress Check……Page 322
Critical Skill 12.4 Use Nested Subqueries……Page 324
Critical Skill 12.3 Work with Correlated Subqueries……Page 323
Critical Skill 12.5 Use Subqueries to Modify Data……Page 326
Using Subqueries to Insert Data……Page 327
Using Subqueries to Update Data……Page 328
Project 12-1 Working with Subqueries……Page 329
Module 12 Mastery Check……Page 333
III Advanced Data Access……Page 335
13 Creating SQL-Invoked Routines……Page 337
Critical Skill 13.1 Understand SQL-Invoked Routines……Page 338
SQL- Invoked Procedures and Functions……Page 339
Working with the Basic Syntax……Page 340
Progress Check……Page 341
Critical Skill 13.2 Create SQL-Invoked Procedures……Page 342
Invoking SQL- Invoked Procedures……Page 343
Progress Check……Page 344
Critical Skill 13.3 Add Input Parameters to Your Procedures……Page 345
Using Procedures to Modify Data……Page 348
Critical Skill 13.4 Add Local Variables to Your Procedures……Page 349
Create Compound Statements……Page 351
Create Conditional Statements……Page 352
Ask the Expert……Page 354
Project 13-1 Creating SQL-Invoked Procedures……Page 356
Critical Skill 13.6 Add Output Parameters to Your Procedures……Page 358
Progress Check……Page 359
Critical Skill 13.7 Create SQL-Invoked Functions……Page 360
Project 13-2 Creating SQL-Invoked Functions……Page 362
Module 13 Mastery Check……Page 363
14 Creating SQL Triggers……Page 367
Critical Skill 14.1 Understand SQL Triggers……Page 368
Trigger Execution Context……Page 369
Progress Check……Page 370
Critical Skill 14.2 Create SQL Triggers……Page 371
Referencing Old and New Values……Page 372
Critical Skill 14.3 Create Insert Triggers……Page 374
Critical Skill 14.4 Create Update Triggers……Page 377
Ask the Expert……Page 381
Critical Skill 14.5 Create Delete Triggers……Page 382
Project 14-1 Creating SQL Triggers……Page 384
Module 14 Mastery Check……Page 387
15 Using SQL Cursors……Page 391
Critical Skill 15.1 Understand SQL Cursors……Page 392
Declaring and Opening SQL Cursors……Page 393
Progress Check……Page 395
Working with Optional Syntax Elements……Page 396
Creating a Cursor Declaration……Page 400
Progress Check……Page 403
Critical Skill 15.4 Retrieve Data from a Cursor……Page 404
Ask the Expert……Page 409
Using the Positioned UPDATE Statement……Page 410
Using the Positioned DELETE Statement……Page 412
Project 15-1 Working with SQL Cursors……Page 413
Module 15 Mastery Check……Page 416
16 Managing SQL Transactions……Page 419
Critical Skill 16.1 Understand SQL Transactions……Page 420
Critical Skill 16.2 Set Transaction Properties……Page 423
Specifying an Isolation Level……Page 424
Specifying a Diagnostics Size……Page 429
Creating a SET TRANSACTION Statement……Page 430
Critical Skill 16.3 Start a Transaction……Page 431
Critical Skill 16.4 Set Constraint Deferrability……Page 432
Ask the Expert……Page 433
Progress Check……Page 435
Critical Skill 16.5 Create Savepoints in a Transaction……Page 436
Critical Skill 16.6 Terminate a Transaction……Page 438
Committing a Transaction……Page 439
Rolling Back a Transaction……Page 440
Project 16-1 Working with Transactions……Page 441
Module 16 Mastery Check……Page 444
17 Accessing SQL Data from Your Host Program……Page 447
Critical Skill 17.1 Invoke SQL Directly……Page 448
Critical Skill 17.2 Embed SQL Statements in Your Program……Page 449
Creating an Embedded SQL Statement……Page 451
Using Host Variables in Your SQL Statements……Page 453
Retrieving SQL Data……Page 454
Ask the Expert……Page 455
Error Handling……Page 457
Project 17-1 Embedding SQL Statements……Page 459
Critical Skill 17.3 Create SQL Client Modules……Page 462
Defining SQL Client Modules……Page 463
Critical Skill 17.4 Use an SQL Call-Level Interface……Page 465
Allocating Handles……Page 467
Executing SQL Statements……Page 470
Working with Host Variables……Page 471
Retrieving SQL Data……Page 472
Project 17-2 Using the SQL Call-Level Interface……Page 473
Module 17 Mastery Check……Page 476
IV Appendixes……Page 479
Module 1: Introduction to Relational
Databases and SQL……Page 482
Module 2: Working with the SQL Environment……Page 483
Module 3: Creating and Altering Tables……Page 485
Module 4: Enforcing Data Integrity……Page 488
Module 5: Creating SQL Views……Page 490
Module 6: Managing Database Security……Page 492
Module 7: Querying SQL Data
1…….Page 494
Module 8: Modifying SQL Data……Page 496
Module 9: Using Predicates……Page 499
Module 10: Working with Functions
and Value Expressions……Page 501
Module 11: Accessing Multiple Tables……Page 503
Module 12: Using Subqueries to Access
and Modify Data
……Page 505
Module 13: Creating SQL-Invoked Routines……Page 506
Module 14: Creating SQL Triggers……Page 508
Module 15: Using SQL Cursors……Page 511
Index……Page 545
Reviews
There are no reviews yet.