+612 9045 4394
 
CHECKOUT
Troubleshooting SQL : Application Development S. - Forrest Houlette

Troubleshooting SQL

Application Development S.

Paperback

Published: 1st September 2001
Ships: 7 to 10 business days
7 to 10 business days
RRP $98.00
$73.50
25%
OFF

This programmer's resource explains how to optimize SQL statements and procedures and create a customized SQL debugging environment for error-free applications. You'll get programming tips, best practices, plus coverage of Oracle SQL and PL/SQL and T-SQL for SQL Server.

Laying the Groundwork
Choosing a Troubleshooting Environmentp. 3
Query Analyzersp. 8
Graphical Toolsp. 12
Development Environmentsp. 16
Facing Realityp. 17
Summaryp. 19
Using Best Practicesp. 21
A Guided Tour of Best Practicesp. 24
Make Your Code Self-Documentingp. 24
Make Your Code Readablep. 36
Undertake All Actions Explicitlyp. 38
Include Narrative Commentsp. 40
Stick to Supported Optionsp. 40
Optimizing Queriesp. 41
Protecting Datap. 44
Protecting Data Integrityp. 45
Summaryp. 47
Preparing Your Troubleshooting Environmentp. 49
What to Have Handyp. 50
What to Make Readyp. 53
What to Set Upp. 54
What to Runp. 55
How to Run Your Environmentp. 57
Summaryp. 58
Analyzing Problems
Creating Databasesp. 61
Troubleshooting Setup Problemsp. 62
Database Objects and User Relationshipsp. 65
Organizing Tablesp. 68
Putting the Database on Diskp. 78
Protecting Response Timep. 82
Creating Indexesp. 82
Archiving Datap. 84
Managing Logs and Filesp. 85
Securing the Datap. 88
Defining Usersp. 90
Defining Rolesp. 94
Summaryp. 97
Normalizing Tablesp. 99
Thirteen Rulesp. 101
First Normal Formp. 105
Further Normal Formsp. 108
Second Normal Formp. 109
Third Normal Formp. 110
Boyce-Codd Normal Formp. 111
Fourth Normal Formp. 112
Fifth Normal Form and Beyondp. 113
Optimizing Tablesp. 115
Normalizing to Optimizep. 115
Considering Denormalizationp. 116
Summaryp. 116
Using Data Typesp. 117
Using Data Typesp. 118
Numeric Data Typesp. 120
Time-Related Data Typesp. 123
Character Data Typesp. 126
Converting Data Types from One Database to Anotherp. 129
Summaryp. 131
Selecting Datap. 133
The Basic SELECT Statementp. 135
Aggregates as Complicating Factorsp. 136
Joins as Complicating Factorsp. 139
WHERE Clauses as Complicating Factorsp. 141
A Look at a Complex Queryp. 142
A Few Practical Suggestionsp. 147
Summaryp. 148
Inserting Datap. 149
The Basic INSERT Statementp. 151
Inserting into Multiple Tablesp. 152
Common Complicating Factorsp. 154
Database Designp. 154
Constraintsp. 156
Null Valuesp. 158
Missing Valuesp. 159
Multiple Valuesp. 159
Examining a Complex INSERT Statementp. 160
A Few Practical Suggestionsp. 167
Summaryp. 167
Updating Datap. 169
Transactional Integrityp. 170
The ACID Testp. 171
Types of Locksp. 174
Lock Granularityp. 175
Optimistic and Pessimistic Lockingp. 176
Making Optimistic Locking Workp. 176
Deadlocksp. 178
Transaction Suggestionsp. 180
The Basic UPDATE Statementp. 180
WHERE Is Your Best Friendp. 181
Using the FROM Clausep. 181
Updating to Calculated Valuesp. 182
Things That Prevent Updatesp. 183
Undocumented Schemasp. 184
Data Type Incompatibilityp. 184
Unique Primary Key Constraintsp. 185
Foreign Key Constraintsp. 187
Unique Index Constraintsp. 187
Allow Nulls and Defaultsp. 187
Check Constraintsp. 188
Triggersp. 188
Views, with Checkp. 189
Security Settingsp. 189
Summaryp. 190
Deleting Datap. 191
The Basic DELETE Statementp. 192
Just in Casep. 192
The Data Saving WHERE Clausep. 193
FROM-FROMp. 193
Things That Prevent Deletionsp. 194
Referential Integrityp. 195
Optional Foreign Keysp. 195
Cascade Deletesp. 196
Cascade Delete Triggersp. 197
Logical Deletionp. 198
Logical Delete Flagp. 198
Logical Delete Triggersp. 199
Cascading Logical Deletesp. 201
Truncating a Tablep. 202
Summaryp. 202
Grouping and Aggregating Datap. 203
Common Aggregate Functionsp. 205
The COUNT() Functionp. 205
The SUM() Functionp. 208
The AVG() Functionp. 209
The MIN() and MAX() Functionsp. 209
GROUP BYp. 210
Cleaning Up a GROUP BY Queryp. 211
The SQL Order with Aggregatesp. 213
Generating Cube Subtotalsp. 214
Recapp. 215
Summaryp. 216
Using Joinsp. 217
The Join Within the SQL Statementp. 219
Inner Joinsp. 219
Changes in the Resulting Row Countp. 219
Working with Graphic Query Toolsp. 220
Execution Order of the SQL Statementp. 221
Self Joinsp. 222
Outer Joinsp. 225
Right Outer Joinsp. 226
Data Scrubbing with Nullsp. 226
Full Outer Joinsp. 227
An Eighteenth-Century Analogyp. 227
Legacy Joinsp. 228
Cross Joinsp. 229
Union Joinsp. 229
Complex Joinsp. 230
Multiple Tablesp. 230
Multiple Join Conditionsp. 231
Non-Equal Joinp. 231
A Readable Stylep. 232
Summaryp. 232
Using Subqueriesp. 233
Subquery Basicsp. 234
Substituting Subqueriesp. 235
Substituting a Column Namep. 236
Substituting a Column Valuep. 239
Dynamically Setting the Top Row Countp. 241
Referencing a Derived Tablep. 242
Building a Dynamic WHERE Clausep. 243
Altering the GROUP BY and ORDER BYp. 245
Correlated Subqueriesp. 246
Summaryp. 247
Using Viewsp. 249
Using Viewsp. 251
Views to Project Columnsp. 252
Nesting Viewsp. 252
Partitioned Views and Federated Databasesp. 255
Views as Securityp. 259
Using the WITH CHECK Optionp. 259
Problems with Viewsp. 260
Speaking of Locks, Updates, and Viewsp. 260
Performancep. 261
Views Are Often Nonupdatablep. 262
Schema Changesp. 262
Debugging Difficultiesp. 263
Multiple Table Referencesp. 264
Editing Viewsp. 264
Rebuilding Database Objects from Scriptsp. 265
Summaryp. 265
Solving Complex Problems
Triggers, Stored Procedures, and Parametersp. 269
Why Use Triggers and Stored Procedures?p. 271
Eliminating Code Troubles with Triggers and Stored Proceduresp. 271
Scenariop. 272
Option 1: Modifying the Codep. 276
Triggersp. 277
Option 2: Using a Triggerp. 279
Option 3: Using Stored Proceduresp. 279
Syntax and Types of Stored Proceduresp. 282
Debugging Stored Proceduresp. 284
Using Parametersp. 286
What Are Parameters?p. 286
Parameter Fundamentalsp. 288
Summaryp. 290
Transactionsp. 291
Transaction Processing Requirementsp. 293
Atomicp. 293
Consistentp. 293
Isolatedp. 294
Durablep. 294
Transaction Fundamentalsp. 294
Database Locksp. 295
Understanding Locksp. 295
Using Locksp. 297
Using Transactionsp. 298
Transactions in Oraclep. 298
Controlling Transactionsp. 301
Transactions in Transact-SQLp. 301
Transactions and Stored Proceduresp. 302
Monitoring Transactions: Using the Transaction Logp. 303
Summaryp. 304
Using Cursors and Exceptionsp. 305
Understanding Cursorsp. 306
Creating and Using Cursorsp. 307
Cursors for Transact-SQLp. 308
Creating SQL Cursorsp. 308
Opening Cursorsp. 309
Updating and Deleting Cursorsp. 311
Closing Cursorsp. 312
Understanding Cursors for PL/SQLp. 313
Declaring a Cursorp. 313
Exceptions in PL/SQLp. 317
Exception Typesp. 317
Exception Handlingp. 319
RAISE and RAISE_APPLICATION_ERROR Statementsp. 320
Summaryp. 323
Treesp. 325
Introducing Treesp. 326
Understanding Trees and Hierarchiesp. 327
Rules for Treesp. 332
Limits of the CONNECT BY Clausep. 333
Extracting Information Within Treesp. 335
Tree Operationsp. 336
Deleting a Subtreep. 336
Subtree Incorporationp. 337
Summaryp. 337
Indexp. 339
Table of Contents provided by Syndetics. All Rights Reserved.

ISBN: 9780072134896
ISBN-10: 0072134895
Series: Application Development S.
Audience: Professional
Format: Paperback
Language: English
Number Of Pages: 384
Published: 1st September 2001
Publisher: McGraw-Hill Education - Europe
Country of Publication: US
Dimensions (cm): 22.23 x 17.78  x 1.91
Weight (kg): 0.77