SQL for Dummies [9th ed.]

Get ready to make SQL easy! Updated for the latest version of SQL, the new edition of this perennial bestseller shows programmers and web developers how to use SQL to build relational databases and get valuable information from them. Covering everything you need to know to make working with SQL easier than ever, topics include how to use SQL to structure a DBMS and implement a database design; secure a database; and retrieve information from a database; and much more. SQL is the international standard database language used to create, access, manipulate, maintain, and store information in relational database management systems (DBMS) such as Access, Oracle, SQL Server, and MySQL. SQL adds powerful data manipulation and retrieval capabilities to conventional languages—and this book shows you how to harness the core element of relational databases with ease. Server platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems Find great examples on the use of temporal data Jump right in—without previous knowledge of database programming or SQL As database-driven websites continue to grow in popularity—and complexity— SQL For Dummies is the easy-to-understand, go-to resource you need to use it seamlessly. Brief Contents......Page 3 Contents......Page 5 Intro......Page 16 Start with SQL......Page 19 Relational Database Fundamentals......Page 20 Keeping Track of Things......Page 21 What Is a Database?......Page 22 What Is a Database Management System?......Page 23 Flat Files......Page 25 Database Models......Page 26 Database Design Considerations......Page 33 What SQL Is and Isn’t......Page 35 A (Very) Little History......Page 37 SQL Statements......Page 38 Data Types......Page 40 Null Values......Page 61 Using SQL in a Client/Server System......Page 62 Using SQL on the Internet or an Intranet......Page 64 SQL Components......Page 66 Data Definition Languag......Page 67 Data Manipulation Language......Page 79 Data Control Language......Page 87 Build Databases......Page 94 Simple Database Structure......Page 95 Using a RAD Tool to Build a Simple Database......Page 96 Building POWER with SQL’s DDL......Page 108 Portability Considerations......Page 117 Multi-Table Relational Database......Page 118 Designing a Database......Page 119 Working with Indexes......Page 128 Maintaining Data Integrity......Page 131 Normalizing the Database......Page 143 Storing & Retrieving Data......Page 150 Manipulating Database Data......Page 151 Retrieving Data......Page 152 Creating Views......Page 153 Updating Views......Page 157 Adding New Data......Page 158 Updating Existing Data......Page 163 Transferring Data......Page 166 Deleting Obsolete Data......Page 169 Handling Temporal Data......Page 170 Understanding Times and Periods......Page 171 Working with Application-Time Period Tables......Page 172 Working with System-Versioned Tables......Page 178 Tracking Even More Time Data with Bitemporal Tables......Page 182 Formatting and Parsing Dates and Times......Page 183 Values......Page 185 Value Expressions......Page 192 Functions......Page 195 Advanced SQL Value Expressions......Page 215 CASE Conditional Expressions......Page 216 CAST Data-Type Conversions......Page 223 Row Value Expressions......Page 227 Zeroing in on the Data you want......Page 229 Modifying Clauses......Page 230 FROM Clauses......Page 231 WHERE Clauses......Page 232 Logical Connectives......Page 249 GROUP BY Clauses......Page 251 HAVING Clauses......Page 253 ORDER BY Clauses......Page 254 Limited FETCH......Page 256 Peering through a Window to Create a Result Set......Page 257 UNION......Page 264 INTERSECT......Page 267 EXCEPT......Page 269 Join Operators......Page 270 ON versus WHERE......Page 287 Nested Queries......Page 288 What Subqueries Do......Page 290 What Is Recursion?......Page 308 Where Might You Use a Recursive Query?......Page 311 Where Else Might You Use a Recursive Query?......Page 316 Controlling Operations......Page 318 Database Security......Page 319 User Access Levels......Page 320 Granting Privileges to Users......Page 322 Granting Privileges across Levels......Page 329 Granting the Power to Grant Privileges......Page 331 Taking Privileges Away......Page 332 Using GRANT and REVOKE Together to Save Time and Effor......Page 333 Protecting Data......Page 335 Threats to Data Integrity......Page 336 Reducing Vulnerability to Data Corruption......Page 340 Constraints Within Transactions......Page 349 Avoiding SQL Injection Attacks......Page 354 SQL within Applications......Page 355 SQL in an Application......Page 356 Hooking SQL into Procedural Languages......Page 358 Real World......Page 368 Accessing Data with ODBC & JDBC......Page 369 ODBC......Page 370 ODBC and the Internet......Page 372 ODBC and an Intranet JDBC......Page 375 How XML Relates to SQL......Page 378 The XML Data Type......Page 379 Mapping SQL to XML and XML to SQL......Page 381 SQL Functions That Operate on XML Data......Page 386 Predicates......Page 391 Transforming XML Data into SQL Tables......Page 393 Mapping Non-Predefine Data Types to XML......Page 394 The Marriage of SQL and XML......Page 399 SQL & JSON......Page 400 Using JSON with SQL......Page 401 The SQL/JSON Data Model......Page 402 SQL/JSON Functions......Page 404 SQL/JSON Path Language......Page 412 There’s More......Page 413 Advanced Topics......Page 414 Stepping through Dataset with Cursors......Page 415 Declaring a Cursor......Page 416 Opening a Cursor......Page 421 Fetching Data from a Single Row......Page 422 Closing a Cursor......Page 425 Adding Procedural Capabilities with Persistent Stored Modules......Page 426 Compound Statements......Page 427 Flow of Control Statements......Page 434 Stored Procedures......Page 439 Stored Functions Privileges......Page 441 Stored Modules......Page 442 SQLSTATE......Page 444 WHENEVER Clause......Page 446 Diagnostics Areas......Page 447 Handling Exceptions......Page 454 Examining Some Applications of Triggers......Page 456 Creating a Trigger......Page 457 Firing a Succession of Triggers......Page 459 Referencing Old Values and New Values......Page 460 Firing Multiple Triggers on a Single Table......Page 461 Parts of Tens......Page 462 Assuming That Your Clients Know What They Need......Page 463 Not Asking for Client Feedback......Page 464 Designing Database Tables in Isolation......Page 465 Not Documenting Your Process......Page 466 Retrieval Tips......Page 467 Triple-Check Queries with Subselects......Page 468 Use Parentheses with AND, OR, and NOT......Page 469 Handle Error Conditions Gracefully......Page 470 SQL 2016 Reserved Words......Page 471 Index......Page 476 Get ready to make SQL easy!Updated for the latest version of SQL, the new edition of this perennial bestseller shows programmers and web developers how to use SQL to build relational databases and get valuable information from them. Covering everything you need to know to make working with SQL easier than ever, topics include how to use SQL to structure a DBMS and implement a database design; secure a database; and retrieve information from a database; and much more. SQL is the international standard database language used to create, access, manipulate, maintain, and store information in relational database management systems (DBMS) such as Access, Oracle, SQL Server, and MySQL. SQL adds powerful data manipulation and retrieval capabilities to conventional languages & mdash;and this book shows you how to harness the core element of relational databases with ease. Server platform that gives you choices of development languages, data types, on-premises or cloud, and operating systemsFind great examples on the use of temporal dataJump right in & mdash;without previous knowledge of database programming or SQLAs database-driven websites continue to grow in popularity & mdash;and complexity & mdash;SQL For Dummies is the easy-to-understand, go-to resource you need to use it seamlessly 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.