SQL For Dummies 5th Edition

Many database management system (DBMS) tools run on a variety of hardware platforms. The differences among the tools can be many, but all serious products have one thing in common: They support data access and manipulation in SQL, the industry-standard language specifically designed to enable people to create and manage databases. If you know SQL, you can build relational databases and get useful information out of them.Relational database management systems are vital to many organizations. People often think that creating and maintaining these systems are extremely complex activities - the domain of database gurus who possess enlightenment beyond that of ordinary mortals. Well, SQL for Dummies, 5th Edition, sweeps away the database mystique.Written in easy-to-understand terms and updated with the latest information on SQL, this handy reference shows you step-by-step how to make your database designs a reality using SQL:2003. But you don't have to be a database newbie to find value in SQL for Dummies, 5th Edition; even if you have some experience designing and creating databases, you may discover a few things you didn't know about.Here's just a sampling of some of the things you'll find covered in SQL for Dummies, 5th Edition:# Exploring relational database and SQL fundamentals# Building and maintaining a simple database structure# Building a multiple relational database# Manipulating database data# Examining SQL nuts and bolts: values, expressions, operators, and queries# Providing database security and protecting your data# Exploring ODBC and JDBC, and using SQL with XML# Moving beyond the basics: cursors, persistent stored modules, and error-handling# Top Ten lists on common SQL mistakes and retrieval tipsPlain and simply, databases are the best tools ever invented for keeping track of the things you care about. After you understand databases and can use SQL to make them do your bidding, you wield tremendous power. SQL for Dummies, 5th Edition, can get you well on your way to harnessing the power of databases. SQL For Dummies 5th Edition -1 About the Author 5 Dedication 7 Acknowledgments 7 Contents at a Glance 9 Table of Contents 11 Introduction 21 About This Book 21 Who Should Read This Book? 22 How This Book Is Organized 22 Icons Used in This Book 24 Getting Started 24 Part I: Basic Concepts 25 Chapter 1: Relational Database Fundamentals 27 Keeping Track of Things 27 What Is a Database? 29 Database Size and Complexity 29 What Is a Database Management System? 30 Flat Files 31 Database Models 32 Database Design Considerations 40 Chapter 2: SQL Fundamentals 41 What SQL Is and Isn’t 41 A ( Very) Little History 43 SQL Commands 44 Reserved Words 45 Data Types 45 Null Values 60 Constraints 60 Using SQL in a Client/Server System 61 Using SQL on the Internet/Intranet 63 Chapter 3: The Components of SQL 65 Data Definition Language 66 Data Manipulation Language 76 Data Control Language 83 Part II: Using SQL to Build Databases 91 Chapter 4: Building and Maintaining a Simple Database Structure 93 Building a Simple Database Using a RAD Tool 94 Building PowerDesign with SQL’s DDL 105 Portability Considerations 108 Chapter 5: Building a Multitable Relational Database 111 Designing the Database 111 Working with Indexes 120 Maintaining Integrity 123 Normalizing the Database 132 Part III: Retrieving Data 139 Chapter 6: Manipulating Database Data 141 Retrieving Data 141 Creating Views 143 Updating Views 147 Adding New Data 147 Updating Existing Data 152 Transferring Data 155 Deleting Obsolete Data 157 Chapter 7: Specifying Values 159 Values 159 Value Expressions 165 Functions 169 Chapter 8: Advanced SQL Value Expressions 181 CASE Conditional Expressions 181 CAST Data-Type Conversions 188 Row Value Expressions 191 Chapter 9: Zeroing In on the Data You Want 193 Modifying Clauses 193 FROM Clauses 195 WHERE Clauses 195 Logical Connectives 211 GROUP BY Clauses 213 HAVING Clauses 215 ORDER BY Clauses 216 Chapter 10: Relational Operators 219 UNION 219 INTERSECT 222 EXCEPT 223 JOINS 224 ON versus WHERE 241 Chapter 11: Delving Deep with Nested Queries 243 Why Use a Subquery? 244 What Subqueries Do 245 Chapter 12: Recursive Queries 261 What Is Recursion? 261 What Is a Recursive Query? 264 Where Might I Use a Recursive Query? 264 Where Else Might I Use a Recursive Query? 269 Chapter 13: Providing Database Security 271 The SQL Data Control Language 272 User Access Levels 272 Granting Privileges to Users 275 Granting the Power to Grant Privileges 281 Taking Privileges Away 282 Using GRANT and REVOKE Together Saves Time and Effort 283 Part IV: Controlling Operations 285 Chapter 14: Protecting Data 287 Threats to Data Integrity 287 Reducing Vulnerability to Data Corruption 291 Constraints within Transactions 300 Chapter 15: Using SQL Within Applications 305 SQL in an Application 306 Hooking SQL into Procedural Languages 308 Part V: SQL in the Real World 315 Chapter 16: ODBC and JDBC 317 ODBC 317 ODBC in a Client/Server Environment 319 ODBC and the Internet 320 ODBC and an Intranet 324 JDBC 324 Chapter 17: SQL: 2003 and XML 327 How XML Relates to SQL 327 The XML Data Type 328 Mapping SQL to XML and XML to SQL 329 SQL Operators That Produce an XML Result 333 Mapping Non-Predefined Data Types to XML 336 Part VI: Advanced Topics 341 Chapter 18: Cursors 343 Declaring a Cursor 344 Opening a Cursor 348 Fetching Data from a Single Row 349 Closing a Cursor 351 Chapter 19: Persistent Stored Modules 353 Compound Statements 353 Flow of Control Statements 361 Stored Procedures 366 Stored Functions 367 Privileges 368 Stored Modules 368 Chapter 20: Error-Handling 371 SQLSTATE 371 WHENEVER Clause 373 Diagnostics Areas 374 Handling Exceptions 380 Part VII: The Part of Tens 383 Chapter 21: Ten Common Mistakes 385 Assuming That Your Clients Know What They Need 385 Ignoring Project Scope 386 Considering Only Technical Factors 386 Not Asking for Client Feedback 386 Always Using Your Favorite Development Environment 387 Using Your Favorite System Architecture Exclusively 387 Designing Database Tables in Isolation 387 Neglecting Design Reviews 388 Skipping Beta Testing 388 Not Documenting 388 Chapter 22: Ten Retrieval Tips 389 Verify the Database Structure 389 Try Queries on a Test Database 390 Double-Check Queries with JOINs 390 Triple-Check Queries with Subselects 390 Summarize Data with GROUP BY 390 Watch GROUP BY Clause Restrictions 391 Use Parentheses with AND, OR, and NOT 391 Control Retrieval Privileges 391 Back Up Your Databases Regularly 392 Handle Error Conditions Gracefully 392 Part VIII: Appendixes 393 Appendix A: SQL: 2003 Reserved Words 395 Appendix B: Glossary 399 Index 407 Many database management system (DBMS) tools run on a variety of hardware platforms. The differences among the tools can be many, but all serious products have one thing in common: They support data access and manipulation in SQL, the industry-standard language specifically designed to enable people to create and manage databases. If you know SQL, you can build relational databases and get useful information out of them. Relational database management systems are vital to many organizations. People often think that creating and maintaining these systems are extremely complex activities – the domain of database gurus who possess enlightenment beyond that of ordinary mortals. Well, SQL For Dummies, 5th Edition, sweeps away the database mystique. Written in easy-to-understand terms and updated with the latest information on SQL, this handy reference shows you step-by-step how to make your database designs a reality using SQL:2003. But you don't have to be a database newbie to find value in SQL For Dummies, 5th Edition; even if you have some experience designing and creating databases, you may discover a few things you didn't know about. Here's just a sampling of some of the things you'll find covered in SQL For Dummies, 5th Edition: Exploring relational database and SQL fundamentals Building and maintaining a simple database structure Building a multiple relational database Manipulating database data Examining SQL nuts and bolts: values, expressions, operators, and queries Providing database security and protecting your data Exploring ODBC and JDBC, and using SQL with XML Moving beyond the basics: cursors, persistent stored modules, and error-handling Top Ten lists on common SQL mistakes and retrieval tips Plain and simply, databases are the best tools ever invented for keeping track of the things you care about. After you understand databases and can use SQL to make them do your bidding, you wield tremendous power. SQL For Dummies, 5th Edition, can get you well on your way to harnessing the power of databases. Learn SQL the fun and easy way -- no programming experience needed! SQL For Dummies, 7th Edition, shows programmers and web developers how to use SQL to build relational databases and get valuable information from them. This book covers the core features of the SQL language. Topics covered include how to use SQL to structure a DBMS and implement a database design; secure a database; and retrieve information from a database. This edition will be revised to reflect the new enhancements of SQL/XML:2008. The latest update of the perennial bestselling SQL For Dummies, which has sold more than 210,000 units in six editions. SQL is the most popular database language in the world Covers creating, accessing, manipulating, maintaining, and storing information in relational database management systems (DBMS) such as Access, Oracle, SQL Server, and MySQL. Includes new content on using SQL with XML to power data-driven web sites and using SQL with Access 2010 Whether you're daunted by database development or a system admin star, SQL For Dummies, 7th Dummies will have you working your web site in no time at all.The EPUB format of this title may not be compatible for use on all handheld devices. Relationships are everything, especially when it comes to databases -- so if you work with them, form a relationship with this book. It helps you use SQL to build databases, protect them from corruption, store and retrieve what you need, handle nonrelational data, and even swap information with nondatabase applications using XML.