Table Of Content

www.allitebooks.com www.allitebooks.com by Allen G. Taylor Author of Database Development For Dummies, SQL All-in-One For Dummies, and Crystal Reports 2008 For Dummies SQL 8th Edition www.allitebooks.com SQL For Dummies®, 8th Edition Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit- ted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permis- sion of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748- 6008, or online at http://www.wiley.com/go/permissions. Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITH- OUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA- TION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For tech- nical support, please visit www.wiley.com/techsupport. Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com. Library of Congress Control Number: 2013942771 ISBN 978-1-118-60796-1 (pbk); 978-1-118-65711-9 (ebk); ISBN 978-1-118-62783-9 (ebk); ISBN 978-1-118-65718-8 (ebk) Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 www.allitebooks.com Contents at a Glance Introduction ................................................................ 1 Part I: Getting Started with SQL ................................... 3 Chapter 1: Relational Database Fundamentals ..............................................................5 Chapter 2: SQL Fundamentals ........................................................................................21 Chapter 3: The Components of SQL ..............................................................................51 Part II: Using SQL to Build Databases ......................... 79 Chapter 4: Building and Maintaining a Simple Database Structure...........................81 Chapter 5: Building a Multitable Relational Database...............................................105 Part III: Storing and Retrieving Data......................... 137 Chapter 6: Manipulating Database Data .....................................................................139 Chapter 7: Handling Temporal Data ............................................................................157 Chapter 8: Specifying Values ........................................................................................171 Chapter 9: Using Advanced SQL Value Expressions .................................................197 Chapter 10: Zeroing In on the Data You Want............................................................211 Chapter 11: Using Relational Operators .....................................................................243 Chapter 12: Delving Deep with Nested Queries .........................................................267 Chapter 13: Recursive Queries .....................................................................................285 Part IV: Controlling Operations ................................. 295 Chapter 14: Providing Database Security ...................................................................297 Chapter 15: Protecting Data .........................................................................................313 Chapter 16: Using SQL within Applications ................................................................333 Part V: Taking SQL to the Real World ........................ 347 Chapter 17: Accessing Data with ODBC and JDBC ....................................................349 Chapter 18: Operating on XML Data with SQL ...........................................................359 Part VI: Advanced Topics ......................................... 381 Chapter 19: Stepping through a Dataset with Cursors .............................................383 Chapter 20: Adding Procedural Capabilities with Persistent Stored Modules ......393 Chapter 21: Handling Errors .........................................................................................411 Chapter 22: Triggers ......................................................................................................423 www.allitebooks.com SQL For Dummies, 8th Edition iv Part VII: The Part of Tens ......................................... 429 Chapter 23: Ten Common Mistakes .............................................................................431 Chapter 24: Ten Retrieval Tips ....................................................................................435 Appendix: SQL: 2011 Reserved Words ....................... 439 Index ...................................................................... 443 www.allitebooks.com Table of Contents 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 www.allitebooks.com SQL For Dummies, 8th Edition vi 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 www.allitebooks.com vii Table of Contents 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 www.allitebooks.com SQL For Dummies, 8th Edition viii 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 www.allitebooks.com ix Table of Contents 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 SQL For Dummies, 8th Edition x 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 xi Table of Contents 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 SQL For Dummies, 8th Edition xii 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 xiii Table of Contents 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

ebook img

SQL For Dummies, 8th Edition PDF

483 Pages·2013·43.87 MB·English
by  Taylor, Allen G.
#for dummies #allen #sql
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview SQL For Dummies, 8th Edition

www.allitebooks.com www.allitebooks.com by Allen G. Taylor Author of Database Development For Dummies, SQL All-in-One For Dummies, and Crystal Reports 2008 For Dummies SQL 8th Edition www.allitebooks.com SQL For Dummies®, 8th Edition Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit- ted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permis- sion of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748- 6008, or online at http://www.wiley.com/go/permissions. Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITH- OUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA- TION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For tech- nical support, please visit www.wiley.com/techsupport. Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com. Library of Congress Control Number: 2013942771 ISBN 978-1-118-60796-1 (pbk); 978-1-118-65711-9 (ebk); ISBN 978-1-118-62783-9 (ebk); ISBN 978-1-118-65718-8 (ebk) Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 www.allitebooks.com Contents at a Glance Introduction ................................................................ 1 Part I: Getting Started with SQL ................................... 3 Chapter 1: Relational Database Fundamentals ..............................................................5 Chapter 2: SQL Fundamentals ........................................................................................21 Chapter 3: The Components of SQL ..............................................................................51 Part II: Using SQL to Build Databases ......................... 79 Chapter 4: Building and Maintaining a Simple Database Structure...........................81 Chapter 5: Building a Multitable Relational Database...............................................105 Part III: Storing and Retrieving Data......................... 137 Chapter 6: Manipulating Database Data .....................................................................139 Chapter 7: Handling Temporal Data ............................................................................157 Chapter 8: Specifying Values ........................................................................................171 Chapter 9: Using Advanced SQL Value Expressions .................................................197 Chapter 10: Zeroing In on the Data You Want............................................................211 Chapter 11: Using Relational Operators .....................................................................243 Chapter 12: Delving Deep with Nested Queries .........................................................267 Chapter 13: Recursive Queries .....................................................................................285 Part IV: Controlling Operations ................................. 295 Chapter 14: Providing Database Security ...................................................................297 Chapter 15: Protecting Data .........................................................................................313 Chapter 16: Using SQL within Applications ................................................................333 Part V: Taking SQL to the Real World ........................ 347 Chapter 17: Accessing Data with ODBC and JDBC ....................................................349 Chapter 18: Operating on XML Data with SQL ...........................................................359 Part VI: Advanced Topics ......................................... 381 Chapter 19: Stepping through a Dataset with Cursors .............................................383 Chapter 20: Adding Procedural Capabilities with Persistent Stored Modules ......393 Chapter 21: Handling Errors .........................................................................................411 Chapter 22: Triggers ......................................................................................................423 www.allitebooks.com SQL For Dummies, 8th Edition iv Part VII: The Part of Tens ......................................... 429 Chapter 23: Ten Common Mistakes .............................................................................431 Chapter 24: Ten Retrieval Tips ....................................................................................435 Appendix: SQL: 2011 Reserved Words ....................... 439 Index ...................................................................... 443 www.allitebooks.com Table of Contents 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 www.allitebooks.com SQL For Dummies, 8th Edition vi 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 www.allitebooks.com vii Table of Contents 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 www.allitebooks.com SQL For Dummies, 8th Edition viii 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 www.allitebooks.com ix Table of Contents 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 SQL For Dummies, 8th Edition x 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 xi Table of Contents 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 SQL For Dummies, 8th Edition xii 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 xiii Table of Contents 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

See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users