SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK SQL FOR DUMMIES, 8TH EDITION
SUMMARY
-
Items Found: 405
- Introduction 1
- About This Book 1
- Who Should Read This Book? 2
- Icons Used in This Book 2
- Where to Go from Here 2
- Part I: Getting Started with SQL 3
- Chapter 1: Relational Database Fundamentals 5
- Keeping Track of Things 6
- What Is a Database? 7
- Database Size and Complexity 7
- What Is a Database Management System? 8
- Flat Files 9
- Database Models 11
- Relational model 11
- Components of a relational database 12
- Dealing with your relations 12
- Enjoy the view 14
- Schemas, domains, and constraints 16
- The object model challenged the relational model 18
- The object-relational model 18
- Database Design Considerations 19
- Chapter 2: SQL Fundamentals 21
- What SQL Is and Isn’t 21
- A (Very) Little History 23
- SQL Statements 24
- Reserved Words 26
- Data Types 26
- Exact numerics 27
- Approximate numerics 29
- Character strings 30
- Binary strings 32
- Booleans 33
- Datetimes 33
- Intervals 35
- XML type 35
- ROW types 38
- Collection types 39
- REF types 41
- vi SQL For Dummies, 8th Edition
- User-defined types 41
- Data type summary 44
- Null Values 46
- Constraints 46
- Using SQL in a Client/Server System 47
- The server 47
- The client 48
- Using SQL on the Internet or an Intranet 49
- Chapter 3: The Components of SQL 51
- Data Definition Language 52
- When “Just do it!” is not good advice 52
- Creating tables 53
- A room with a view 55
- Collecting tables into schemas 61
- Ordering by catalog 61
- Getting familiar with DDL statements 62
- Data Manipulation Language 64
- Value expressions 64
- Predicates 68
- Logical connectives 69
- Set functions 69
- Subqueries 71
- Data Control Language 71
- Transactions 71
- Users and privileges 73
- Referential integrity constraints can jeopardize your data 75
- Delegating responsibility for security 77
- Part II: Using SQL to Build Databases 79
- Chapter 4: Building and Maintaining a Simple Database Structure 81
- Using a RAD Tool to Build a Simple Database 82
- Deciding what to track 82
- Creating a database table 83
- Altering the table structure 90
- Creating an index 92
- Deleting a table 94
- Building POWER with SQL’s DDL 95
- Using SQL with Microsoft Access 95
- Creating a table 97
- Creating an index 101
- Altering the table structure 102
- Deleting a table 102
- Deleting an index 103
- Portability Considerations 103
- Chapter 5: Building a Multitable Relational Database 105
- Designing a Database 105
- Step 1: Defining objects 106
- Step 2: Identifying tables and columns 106
- Step 3: Defining tables 107
- Domains, character sets, collations, and translations 111
- Getting into your database fast with keys 112
- Working with Indexes 114
- What’s an index, anyway? 115
- Why you should want an index 116
- Maintaining an index 117
- Maintaining Data Integrity 118
- Entity integrity 118
- Domain integrity 119
- Referential integrity 120
- Just when you thought it was safe 123
- Potential problem areas 124
- Constraints 126
- Normalizing the Database 129
- Modification anomalies and normal forms 129
- First normal form 132
- Second normal form 132
- Third normal form 134
- Domain-key normal form (DK/NF) 134
- Abnormal form 135
- Part III: Storing and Retrieving Data 137
- Chapter 6: Manipulating Database Data 139
- Retrieving Data 139
- Creating Views 141
- From tables 142
- With a selection condition 143
- With a modified attribute 144
- Updating Views 145
- Adding New Data 146
- Adding data one row at a time 146
- Adding data only to selected columns 148
- Adding a block of rows to a table 148
- Updating Existing Data 151
- Transferring Data 154
- Deleting Obsolete Data 156
- Chapter 7: Handling Temporal Data 157
- Understanding Times and Periods in SQL:2011 158
- Working with Application-Time Period Tables 159
- Designating primary keys in application-time period tables 162
- Applying referential constraints to application-time
- period tables 163
- Querying application-time period tables 164
- Working with System-Versioned Tables 165
- Designating primary keys in system-versioned tables 167
- Applying referential constraints to system-versioned tables 168
- Querying system-versioned tables 168
- Tracking Even More Time Data with Bitemporal Tables 169
- Chapter 8: Specifying Values 171
- Values 171
- Row values 172
- Literal values 172
- Variables 174
- Special variables 176
- Column references 176
- Value Expressions 177
- String value expressions 178
- Numeric value expressions 179
- Datetime value expressions 179
- Interval value expressions 180
- Conditional value expressions 180
- Functions 181
- Summarizing by using set functions 181
- Value functions 184
- Chapter 9: Using Advanced SQL Value Expressions 197
- CASE Conditional Expressions 197
- Using CASE with search conditions 198
- Using CASE with values 200
- A special CASE — NULLIF 202
- Another special CASE — COALESCE 204
- CAST Data-Type Conversions 205
- Using CAST within SQL 206
- Using CAST between SQL and the host language 206
- Row Value Expressions 207
- Chapter 10: Zeroing In on the Data You Want 211
- Modifying Clauses 211
- FROM Clauses 213
- WHERE Clauses 213
- Comparison predicates 215
- BETWEEN 215
- IN and NOT IN 217
- LIKE and NOT LIKE 218
- SIMILAR 220
- NULL 220
- ALL, SOME, ANY 221
- EXISTS 224
- UNIQUE 225
- DISTINCT 225
- OVERLAPS 226
- MATCH 226
- Referential integrity rules and the MATCH predicate 228
- Logical Connectives 230
- AND 230
- OR 231
- NOT 232
- GROUP BY Clauses 232
- HAVING Clauses 234
- ORDER BY Clauses 235
- Limited FETCH 236
- Peering through a Window to Create a Result Set 238
- Partitioning a window into buckets with NTILE 239
- Navigating within a window 239
- Nesting window functions 241
- Evaluating groups of rows 242
- Chapter 11: Using Relational Operators 243
- UNION 243
- The UNION ALL operation 245
- The CORRESPONDING operation 245
- INTERSECT 246
- EXCEPT 248
- Join Operators 249
- Basic join 249
- Equi-join 251
- Cross join 253
- Natural join 253
- Condition join 254
- Column-name join 254
- Inner join 255
- Outer join 256
- Union join 259
- ON versus WHERE 266
- x SQL For Dummies, 8th Edition
- Chapter 12: Delving Deep with Nested Queries 267
- What Subqueries Do 268
- Nested queries that return sets of rows 269
- Nested queries that return a single value 272
- The ALL, SOME, and ANY quantifiers 275
- Nested queries that are an existence test 277
- Other correlated subqueries 278
- UPDATE, DELETE, and INSERT 282
- Retrieving changes with pipelined DML 284
- Chapter 13: Recursive Queries 285
- What Is Recursion? 285
- Houston, we have a problem 287
- Failure is not an option 287
- What Is a Recursive Query? 288
- Where Might You Use a Recursive Query? 289
- Querying the hard way 290
- Saving time with a recursive query 291
- Where Else Might You Use a Recursive Query? 293
- Part IV: Controlling Operations 295
- Chapter 14: Providing Database Security 297
- The SQL Data Control Language 298
- User Access Levels 298
- The database administrator 298
- Database object owners 299
- The public 300
- Granting Privileges to Users 300
- Roles 301
- Inserting data 302
- Looking at data 302
- Modifying table data 303
- Deleting obsolete rows from a table 304
- Referencing related tables 304
- Using domains 305
- Causing SQL statements to be executed 306
- Granting Privileges across Levels 307
- Granting the Power to Grant Privileges 309
- Taking Privileges Away 310
- Using GRANT and REVOKE Together to Save Time and Effort 311
- Chapter 15: Protecting Data 313
- Threats to Data Integrity 313
- Platform instability 314
- Equipment failure 314
- Concurrent access 315
- Reducing Vulnerability to Data Corruption 317
- Using SQL transactions 318
- The default transaction 319
- Isolation levels 320
- The implicit transaction-starting statement 322
- SET TRANSACTION 323
- COMMIT 324
- ROLLBACK 324
- Locking database objects 324
- Backing up your data 325
- Savepoints and subtransactions 325
- Constraints Within Transactions 327
- Chapter 16: Using SQL within Applications 333
- SQL in an Application 333
- Keeping an eye out for the asterisk 334
- SQL strengths and weaknesses 334
- Procedural languages’ strengths and weaknesses 335
- Problems in combining SQL with a procedural language 335
- Hooking SQL into Procedural Languages 336
- Embedded SQL 336
- Module language 339
- Object-oriented RAD tools 342
- Using SQL with Microsoft Access 343
- Part V: Taking SQL to the Real World 347
- Chapter 17: Accessing Data with ODBC and JDBC 349
- ODBC 350
- The ODBC interface 350
- Components of ODBC 351
- ODBC in a Client/Server Environment 352
- ODBC and the Internet 352
- Server extensions 353
- Client extensions 354
- ODBC and an Intranet 355
- JDBC 355
- Chapter 18: Operating on XML Data with SQL 359
- How XML Relates to SQL 359
- The XML Data Type 360
- When to use the XML type 361
- When not to use the XML type 362
- Mapping SQL to XML and XML to SQL 362
- Mapping character sets 362
- Mapping identifiers 363
- Mapping data types 364
- Mapping tables 364
- Handling null values 365
- Generating the XML Schema 366
- SQL Functions That Operate on XML Data 367
- XMLDOCUMENT 367
- XMLELEMENT 367
- XMLFOREST 368
- XMLCONCAT 368
- XMLAGG 369
- XMLCOMMENT 369
- XMLPARSE 370
- XMLPI 370
- XMLQUERY 370
- XMLCAST 371
- Predicates 371
- DOCUMENT 371
- CONTENT 372
- XMLEXISTS 372
- VALID 372
- Transforming XML Data into SQL Tables 373
- Mapping Non-Predefined Data Types to XML 375
- Domain 375
- Distinct UDT 376
- Row 377
- Array 378
- Multiset 379
- The Marriage of SQL and XML 379
- Part VI: Advanced Topics 381
- Chapter 19: Stepping through a Dataset with Cursors 383
- Declaring a Cursor 384
- Query expression 385
- ORDER BY clause 385
- Updatability clause 387
- Sensitivity 387
- Scrollability 388
- Opening a Cursor 388
- Fetching Data from a Single Row 390
- Syntax 390
- Orientation of a scrollable cursor 391
- Positioned DELETE and UPDATE statements 391
- Closing a Cursor 392
- Chapter 20: Adding Procedural Capabilities with Persistent
- Stored Modules 393
- Compound Statements 393
- Atomicity 394
- Variables 395
- Cursors 396
- Conditions 396
- Handling conditions 397
- Conditions that aren’t handled 400
- Assignment 400
- Flow of Control Statements 400
- IF…THEN…ELSE…END IF 401
- CASE…END CASE 401
- LOOP…ENDLOOP 402
- LEAVE 403
- WHILE…DO…END WHILE 404
- REPEAT…UNTIL…END REPEAT 404
- FOR…DO…END FOR 405
- ITERATE 405
- Stored Procedures 406
- Stored Functions 407
- Privileges 408
- Stored Modules 409
- Chapter 21: Handling Errors 411
- SQLSTATE 411
- WHENEVER Clause 413
- Diagnostics Areas 414
- Diagnostics header area 414
- Diagnostics detail area 416
- Constraint violation example 418
- Adding constraints to an existing table 419
- Interpreting the information returned by SQLSTATE 419
- Handling Exceptions 420
- xiv SQL For Dummies, 8th Edition
- Chapter 22: Triggers 423
- Examining Some Applications of Triggers 423
- Creating a Trigger 424
- Statement and row triggers 425
- When a trigger fires 425
- The triggered SQL statement 425
- An example trigger definition 426
- Firing a Succession of Triggers 426
- Referencing Old Values and New Values 427
- Firing Multiple Triggers on a Single Table 428
- Part VII: The Part of Tens 429
- Chapter 23: Ten Common Mistakes 431
- Assuming That Your Clients Know What They Need 431
- Ignoring Project Scope 432
- Considering Only Technical Factors 432
- Not Asking for Client Feedback 432
- Always Using Your Favorite Development Environment 433
- Using Your Favorite System Architecture Exclusively 433
- Designing Database Tables in Isolation 433
- Neglecting Design Reviews 434
- Skipping Beta Testing 434
- Not Documenting Your Process 434
- Chapter 24: Ten Retrieval Tips 435
- Verify the Database Structure 435
- Try Queries on a Test Database 436
- Double-Check Queries That Include Joins 436
- Triple-Check Queries with Subselects 436
- Summarize Data with GROUP BY 436
- Watch GROUP BY Clause Restrictions 437
- Use Parentheses with AND, OR, and NOT 437
- Control Retrieval Privileges 437
- Back Up Your Databases Regularly 438
- Handle Error Conditions Gracefully 438
- Appendix: SQL: 2011 Reserved Words 439
- Index 443
- Introduction
