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