Sumário Itens Encontrados: 204ACKNOWLEDGMENTS xiINTRODUCTION xiPART I Relational Databases and SQL1 Introduction to Relational Databases and SQL 3Understand Relational Databases 4The Relational Model 5Learn About SQL 15The SQL Evolution 15Types of SQL Statements 18Types of Execution 19SQL Standard versus Product Implementations 212 Working with the SQL Environment 29Understand the SQL Environment 30Understand SQL Catalogs 32Schemas 34Schema Objects 35Then What Is a Database? 37Name Objects in an SQL Environment 40Qualified Names 41For more information about this title, click herevi SQL: A Beginnerâs GuideCreate a Schema 42Create a Database 443 Creating and Altering Tables 49Create SQL Tables 50Specify Column Data Types 54String Data Types 55Numeric Data Types 57Datetime Data Types 58Interval Data Type 60Boolean Data Type 61Using SQL Data Types 62Create User-Defined Types 63Specify Column Default Values 64Delete SQL Tables 694 Enforcing Data Integrity 73Understand Integrity Constraints 74Use NOT NULL Constraints 76Add UNIQUE Constraints 77Add PRIMARY KEY Constraints 79Add FOREIGN KEY Constraints 83The MATCH Clause 88The Clause 89Define CHECK Constraints 95Defining Assertions 97Creating Domains and Domain Constraints 985 Creating SQL Views 103Add Views to the Database 104Defining SQL Views 108Create Updateable Views 114Using the WITH CHECK OPTION Clause 116Drop Views from the Database 1176 Managing Database Security 123Understand the SQL Security Model 124SQL Sessions 126Accessing Database Objects 128Create and Delete Roles 130Grant and Revoke Privileges 131Revoking Privileges 135Grant and Revoke Roles 137Revoking Roles 138PART II Data Access and Modification7 Querying SQL Data 145Use a SELECT Statement to Retrieve Data 146The SELECT Clause and FROM Clause 147Use the WHERE Clause to Define Search Conditions 152Defining the WHERE Clause 156Use the GROUP BY Clause to Group Query Results 159Use the HAVING Clause to Specify Group Search Conditions 164Use the ORDER BY Clause to Sort Query Results 1668 Modifying SQL Data 175Insert SQL Data 176Inserting Values from a SELECT Statement 180Update SQL Data 182Updating Values from a SELECT Statement 185Delete SQL Data 1869 Using Predicates 193Compare SQL Data 194Using the BETWEEN Predicate 199Return Null Values 200Return Similar Values 203Reference Additional Sources of Data 209Using the IN Predicate 209Using the EXISTS Predicate 213Quantify Comparison Predicates 216Using the SOME and ANY Predicates 216Using the ALL Predicate 21810 Working with Functions and Value Expressions 225Use Set Functions 226Using the COUNT Function 227Using the MAX and MIN Functions 229Using the SUM Function 231Using the AVG Function 232Use Value Functions 232Working with String Value Functions 233Working with Datetime Value Functions 236Use Value Expressions 238Working with Numeric Value Expressions 238Using the CASE Value Expression 241Using the CAST Value Expression 244Use Special Values 24511 Accessing Multiple Tables 253Perform Basic Join Operations 254Using Correlation Names 257Creating Joins with More than Two Tables 258Creating the Cross Join 259Creating the Self-Join 260Join Tables with Shared Column Names 261Creating the Natural Join 262Creating the Named Column Join 263Use the Condition Join 263Creating the Inner Join 264Creating the Outer Join 266Perform Union Operations 26912 Using Subqueries to Access and Modify Data 277Create Subqueries That Return Multiple Rows 278Using the IN Predicate 279Using the EXISTS Predicate 281Using Quantified Comparison Predicates 282Create Subqueries That Return One Value 283Work with Correlated Subqueries 284Use Nested Subqueries 286Use Subqueries to Modify Data 288Using Subqueries to Insert Data 288Using Subqueries to Update Data 290Using Subqueries to Delete Data 291PART III Advanced Data Access13 Creating SQL-Invoked Routines 299Understand SQL-Invoked Routines 300SQL-Invoked Procedures and Functions 301Working with the Basic Syntax 301Create SQL-Invoked Procedures 303Invoking SQL-Invoked Procedures 305Add Input Parameters to Your Procedures 306Using Procedures to Modify Data 309Add Local Variables to Your Procedures 311Work with Control Statements 313Create Compound Statements 313Create Conditional Statements 314Create Looping Statements 316Add Output Parameters to Your Procedures 320Create SQL-Invoked Functions 32114 Creating SQL Triggers 329Understand SQL Triggers 330Trigger Execution Context 331Create SQL Triggers 333Referencing Old and New Values 334Dropping SQL Triggers 335Create Insert Triggers 336Create Update Triggers 338Create Delete Triggers 34315 Using SQL Cursors 351Understand SQL Cursors 352Declaring and Opening SQL Cursors 353Declare a Cursor 355Working with Optional Syntax Elements 356Creating a Cursor Declaration 360Open and Close a Cursor 363Retrieve Data from a Cursor 363Use Positioned UPDATE and DELETE Statements 368Using the Positioned UPDATE Statement 368Using the Positioned DELETE Statement 37016 Managing SQL Transactions 377Understand SQL Transactions 378Set Transaction Properties 381Specifying an Isolation Level 382Specifying a Diagnostics Size 387Creating a SET TRANSACTION Statement 388Start a Transaction 389Set Constraint Deferability 390Create Savepoints in a Transaction 392Releasing a Savepoint 394Terminate a Transaction 395Committing a Transaction 395Rolling Back a Transaction 39617 Accessing SQL Data from Your Host Program 403Invoke SQL Directly 404Embed SQL Statements in Your Program 406Creating an Embedded SQL Statement 407Using Host Variables in Your SQL Statements 408Retrieving SQL Data 411Error Handling 413Create SQL Client Modules 417Defining SQL Client Modules 418Use an SQL Call-Level Interface 419Allocating Handles 421Executing SQL Statements 423Working with Host Variables 424Retrieving SQL Data 42618 Working with XML Data 433Learn the Basics of XML 434Learn About SQL/XML 437The XML Data Type 437SQL/XML Functions 439SQL/XML Mapping Rule 441PART IV AppendicesA Answers to Self Test 449B SQL:2006 Keywords 491SQL Reserved Keywords 492SQL Nonreserved Keywords 494C SQL Code Used in Try This Exercises 497SQL Code by Try This Exercise 498The INVENTORY Database 514Index 519