Sumário Itens Encontrados: 428INTRODUCTION. 1About This Book. 1Foolish Assumptions. 2Icons Used in This Book. 2Beyond the Book. 3Where to Go from Here. 3PART 1: GETTING STARTED WITH SQL. 5CHAPTER 1: Relational Database Fundamentals. 7Keeping Track of Things. 8What Is a Database?. 9Database Size and Complexity 10What Is a Database Management System?. 10Flat Files 12Database Models. 13Relational model. 13Components of a relational database 14Dealing with your relations 14Enjoy the view 16Schemas, domains, and constraints. 18The object model challenged the relational model. 19The object-relational model. 20Database Design Considerations 20CHAPTER 2: SQL Fundamentals. 23What SQL Is and Isnât 23A (Very) Little History. 25SQL Statements. 26Reserved Words. 28Data Types. 28Exact numerics. 29Approximate numerics. 31Character strings. 33Binary strings. 35Booleans. 36Datetimes. 36Intervals 38XML type. 38ROW types 41Collection types. 42vi SQL For DummiesREF types. 44User-defined types 44Data type summary. 48Null Values. 49Constraints. 50Using SQL in a Client/Server System 50The server. 51The client. 52Using SQL on the Internet or an Intranet 52CHAPTER 3: The Components of SQL. 55Data Definition Language. 56When âJust do it!â is not good advice 56Creating tables. 57A room with a view. 59Collecting tables into schemas 64Ordering by catalog. 65Getting familiar with DDL statements 66Data Manipulation Language. 68Value expressions. 68Predicates. 72Logical connectives. 73Set functions. 73Subqueries. 76Data Control Language. 76Transactions. 76Users and privileges. 77Referential integrity constraints can jeopardize your data. 80Delegating responsibility for security. 82PART 2: USING SQL TO BUILD DATABASES. 83CHAPTER 4: Building and Maintaining a SimpleDatabase Structure. 85Using a RAD Tool to Build a Simple Database. 86Deciding what to track 86Creating a database table 87Altering the table structure. 93Creating an index. 95Deleting a table. 97Building POWER with SQLâs DDL. 98Using SQL with Microsoft Access 99Creating a table. 101Creating an index. 105Altering the table structure. 105Deleting a table. 106Deleting an index. 106Portability Considerations. 107CHAPTER 5: Building a Multi-table Relational Database. 109Designing a Database. 110Step 1: Defining objects 110Step 2: Identifying tables and columns 110Step 3: Defining tables 111Domains, character sets, collations, and translations. 115Getting into your database fast with keys. 116Working with Indexes. 119Whatâs an index, anyway?. 119Why you should want an index. 121Maintaining an index 121Maintaining Data Integrity. 122Entity integrity 122Domain integrity. 124Referential integrity. 124Just when you thought it was safe 127Potential problem areas. 128Constraints. 130Normalizing the Database. 134Modification anomalies and normal forms. 134First normal form. 136Second normal form. 137Third normal form. 138Domain-key normal form (DK/NF) 139Abnormal form 140PART 3: STORING AND RETRIEVING DATA 141CHAPTER 6: Manipulating Database Data. 143Retrieving Data 144Creating Views. 145From tables. 146With a selection condition. 147With a modified attribute. 148Updating Views 149Adding New Data. 150Adding data one row at a time 151Adding data only to selected columns. 152Adding a block of rows to a table. 152Updating Existing Data. 155Transferring Data 158Deleting Obsolete Data. 161CHAPTER 7: Handling Temporal Data. 163Understanding Times and Periods. 164Working with Application-Time Period Tables 165Designating primary keys in application-time period tables. 168Applying referential integrity constraints toapplication-time period tables. 169Querying application-time period tables. 170Working with System-Versioned Tables. 171Designating primary keys in system-versioned tables. 173system-versioned tables. 174Querying system-versioned tables. 174Tracking Even More Time Data with Bitemporal Tables. 175Formatting and Parsing Dates and Times. 176CHAPTER 8: Specifying Values. 179Values. 179Row values. 180Literal values 180Variables. 182Special variables 184Column references. 185Value Expressions. 186String value expressions. 186Numeric value expressions. 187Datetime value expressions. 187Interval value expressions. 188Conditional value expressions. 189Functions 189Set functions. 189Value functions 193Table functions 208CHAPTER 9: Using Advanced SQL Value Expressions. 209CASE Conditional Expressions. 210Using CASE with search conditions. 211Using CASE with values. 212A special CASE â NULLIF. 215Another special CASE â COALESCE. 216CAST Data-Type Conversions. 217Using CAST within SQL. 219Using CAST between SQL and the host language. 220Row Value Expressions. 221CHAPTER 10: Zeroing In on the Data You Want. 223Modifying Clauses. 224FROM Clauses 225WHERE Clauses 226Comparison predicates 227BETWEEN 228IN and NOT IN 229LIKE and NOT LIKE. 231SIMILAR. 232NULL. 232ALL, SOME, ANY. 234EXISTS. 236UNIQUE. 237DISTINCT. 238OVERLAPS. 238MATCH 239Referential integrity rules and the MATCH predicate 240Logical Connectives. 243AND. 243OR 244NOT. 244GROUP BY Clauses 245HAVING Clauses. 247ORDER BY Clauses. 248Limited FETCH. 250Peering through a Window to Create a Result Set. 251Partitioning a window into buckets with NTILE. 252Navigating within a window. 253Nesting window functions. 255Evaluating groups of rows. 256Row pattern recognition. 257CHAPTER 11: Using Relational Operators. 259UNION. 259The UNION ALL operation. 261The CORRESPONDING operation. 262INTERSECT 262EXCEPT 264x SQL For DummiesJoin Operators. 265Basic join. 265Equi-join 267Cross join 269Natural join. 270Condition join. 270Column-name join. 271Inner join. 272Outer join. 272Union join. 276ON versus WHERE. 282CHAPTER 12: Delving Deep with Nested Queries. 283What Subqueries Do. 285Nested queries that return sets of rows 285Nested queries that return a single value. 289The ALL, SOME, and ANY quantifiers. 292Nested queries that are an existence test. 293Other correlated subqueries. 295UPDATE, DELETE, and INSERT. 299Retrieving changes with pipelined DML. 301CHAPTER 13: Recursive Queries. 303What Is Recursion? 303Houston, we have a problem. 305Failure is not an option. 305What Is a Recursive Query?. 306Where Might You Use a Recursive Query?. 306Querying the hard way. 308Saving time with a recursive query. 309Where Else Might You Use a Recursive Query?. 311PART 4: CONTROLLING OPERATIONS. 313CHAPTER 14: Providing Database Security 315The SQL Data Control Language. 316User Access Levels. 316The database administrator. 317Database object owners. 317The public. 318Granting Privileges to Users. 318Roles 320Inserting data. 320Looking at data 321Modifying table data. 321Deleting obsolete rows from a table 322Referencing related tables. 322Using domains. 323Causing SQL statements to be executed. 325Granting Privileges across Levels. 325Granting the Power to Grant Privileges. 327Taking Privileges Away. 328Using GRANT and REVOKE Together to Save Time and Effort 329CHAPTER 15: Protecting Data. 331Threats to Data Integrity 332Platform instability 332Equipment failure. 332Concurrent access. 333Reducing Vulnerability to Data Corruption. 336Using SQL transactions. 336The default transaction. 338Isolation levels. 338The implicit transaction-starting statement 341SET TRANSACTION. 341COMMIT 342ROLLBACK. 342Locking database objects. 343Backing up your data 343Savepoints and subtransactions. 344Constraints Within Transactions. 345Avoiding SQL Injection Attacks 350CHAPTER 16: Using SQL within Applications. 351SQL in an Application. 352Keeping an eye out for the asterisk 352SQL strengths and weaknesses. 353Procedural languagesâ strengths and weaknesses. 353Problems in combining SQL with a procedural language. 353Hooking SQL into Procedural Languages 354Embedded SQL 355Module language. 358Object-oriented RAD tools. 360Using SQL with Microsoft Access 361PART 5: TAKING SQL TO THE REAL WORLD. 365CHAPTER 17: Accessing Data with ODBC and JDBC. 367ODBC. 368The ODBC interface. 368Components of ODBC. 369ODBC in a Client/Server Environment. 370ODBC and the Internet. 370Server extensions 371Client extensions. 372ODBC and an Intranet. 373JDBC. 373CHAPTER 18: Operating on XML Data with SQL. 377How XML Relates to SQL 377The XML Data Type. 378When to use the XML type. 379When not to use the XML type 380Mapping SQL to XML and XML to SQL. 380Mapping character sets 381Mapping identifiers. 381Mapping data types. 382Mapping tables 382Handling null values. 383Generating the XML Schema. 384SQL Functions That Operate on XML Data 385XMLDOCUMENT. 385XMLELEMENT. 385XMLFOREST 386XMLCONCAT. 386XMLAGG 387XMLCOMMENT. 388XMLPARSE. 388XMLPI. 388XMLQUERY. 389XMLCAST. 389Predicates. 390DOCUMENT. 390CONTENT 390XMLEXISTS 390VALID. 391Transforming XML Data into SQL Tables. 392Mapping Non-Predefined Data Types to XML. 393Domain. 393Distinct UDT. 394Row. 395Array 396Multiset. 397The Marriage of SQL and XML. 398CHAPTER 19: SQL and JSON. 399Using JSON with SQL. 400Ingesting and storing JSON data into a relational database. 400Generating JSON data from relational data. 400Querying JSON data stored in relational tables 400The SQL/JSON Data Model. 401SQL/JSON items. 401SQL/JSON sequences 402Parsing JSON. 402Serializing JSON. 402SQL/JSON Functions. 403JSON API common syntax. 403Query functions. 404Constructor functions. 408IS JSON predicate. 411JSON nulls and SQL nulls 411SQL/JSON Path Language. 411Thereâs More 412PART 6: ADVANCED TOPICS. 413CHAPTER 20: Stepping through a Dataset with Cursors. 415Declaring a Cursor. 416Query expression 417ORDER BY clause. 417Updatability clause. 419Sensitivity. 419Scrollability. 420Opening a Cursor 421Fetching Data from a Single Row 422Syntax. 423Orientation of a scrollable cursor. 424Positioned DELETE and UPDATE statements. 424Closing a Cursor. 425xiv SQL For DummiesCHAPTER 21: Adding Procedural Capabilities withPersistent Stored Modules. 427Compound Statements 428Atomicity. 429Variables. 430Cursors. 430Conditions. 431Handling conditions 431Conditions that arenât handled. 434Assignment. 434Flow of Control Statements. 435IF. THEN. ELSE. END IF. 435CASE. END CASE. 435LOOP. ENDLOOP 437LEAVE. 437WHILE. DO. END WHILE 438REPEAT. UNTIL. END REPEAT. 438FOR. DO. END FOR. 439ITERATE. 439Stored Procedures. 440Stored Functions. 442Privileges. 442Stored Modules. 443CHAPTER 22: Handling Errors. 445SQLSTATE. 445WHENEVER Clause. 447Diagnostics Areas 448Diagnostics header area. 449Diagnostics detail area. 450Constraint violation example. 452Adding constraints to an existing table. 453Interpreting the information returned by SQLSTATE. 454Handling Exceptions. 455CHAPTER 23: Triggers. 457Examining Some Applications of Triggers. 457Creating a Trigger 458Statement and row triggers. 459When a trigger fires. 459The triggered SQL statement. 459An example trigger definition 460Firing a Succession of Triggers 460Referencing Old Values and New Values. 461Firing Multiple Triggers on a Single Table 462PART 7: THE PARTS OF TENS. 463CHAPTER 24: Ten Common Mistakes. 465Assuming That Your Clients Know What They Need 465Ignoring Project Scope. 466Considering Only Technical Factors. 466Not Asking for Client Feedback. 466Always Using Your Favorite Development Environment. 467Using Your Favorite System Architecture Exclusively. 467Designing Database Tables in Isolation. 467Neglecting Design Reviews 468Skipping Beta Testing. 468Not Documenting Your Process 468CHAPTER 25: Ten Retrieval Tips. 469Verify the Database Structure. 470Try Queries on a Test Database 470Double-Check Queries That Include Joins. 470Triple-Check Queries with Subselects 470Summarize Data with GROUP BY. 471Watch GROUP BY Clause Restrictions 471Use Parentheses with AND, OR, and NOT. 471Control Retrieval Privileges 472Back Up Your Databases Regularly. 472Handle Error Conditions Gracefully. 472APPENDIX: ISO/IEC SQL: 2016 RESERVED WORDS 473INDEX 479