SQL in a Nutshell A Desktop Quick Reference, 4th Edition (Kevin Kline, Regina O. Obe, Leo S. Hsu) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.7 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

SQL in a Nutshell A Desktop Quick Reference Fourth Edition Kevin Kline, Regina O. Obe & Leo S. Hsu

📄 Page 2

DATA SQL in a Nutshell Written for developers, analysts, and database administrators, this Nutshell guide is the essential reference for the SQL language used in today’s most popular database products. The updated fourth edition clearly documents SQL commands according to the latest ANSI/ISO standard and details how those commands are implemented in various relational databases. You’ll also get a concise overview of the relational database management system (RDBMS) model and a clear-cut explanation of foundational RDBMS concepts —all packed into a succinct, comprehensive, and easy-to-use format. Sections include: • Background on the relational database model, including current and previous SQL standards • Fundamental concepts for understanding relational databases and SQL commands • A comprehensive command reference of SQL statements according to the SQL 2019 standard • Implementation of each command using MariaDB 10.5, MySQL 8, Oracle 19c, PostgreSQL 14, SQL Server 2019 • Platform-specific functions unique to each implementation Kevin Kline is a renowned database expert and software industry veteran. He was a founder and former president of the Professional Association for SQL Server. Regina O. Obe is on the development team of PostGIS, a spatial extension for PostgreSQL, and is a coauthor of several PostgreSQL-related books. Leo S. Hsu has 20 years of professional experience developing databases for organizations large and small. He coauthored PostGIS in Action, PostgreSQL: Up and Running, and pgRouting: A Practical Guide. “Developers who have to write queries for several different relational systems will find this book very useful.” —Bruce Momjian cofounder and core team member of the PostgreSQL Global Development Group Twitter: @oreillymedia linkedin.com/company/oreilly-media youtube.com/oreillymedia US $69.99 CAN $87.99 ISBN: 978-1-492-08886-8

📄 Page 3

SQL IN A NUTSHELL A DESKTOP QUICK REFERENCE Kevin Kline, Regine O. Obe, and Leo S. Hsu

📄 Page 4

978-1-492-08886-8 [LSI] SQL in a Nutshell by Kevin Kline, Regina O. Obe, and Leo S. Hsu Copyright © 2022 Kevin Kline, Regina O. Obe, and Leo S. Hsu. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editor: Rita Fernando Production Editor: Beth Kelly Copyeditor: Rachel Head Proofreader: Tom Sullivan Indexer: WordCo Indexing Services, Inc. Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea June 2022: Fourth Edition Revision History for the Fourth Edition 2022-06-14: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781492088868 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. SQL in a Nutshell, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the authors and do not represent the publisher’s views. While the publisher and the authors have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

📄 Page 5

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v 1. SQL History and Implementations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 The Relational Model and ANSI SQL 2 History of the SQL Standard 11 SQL Dialects 16 NoSQL 17 2. Foundational Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Database Platforms Described in This Book 19 Categories of Syntax 20 SQL and Platform-Specific Data Types 35 Constraints 60 3. Structuring Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 How to Use This Chapter 69 SQL Platform Support 70 SQL Command Reference 72 4. Reading Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 How to Use This Chapter 249 SQL Platform Support 250 SQL Command Reference 251 5. Manipulating Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 How to Use This Chapter 359 iii

📄 Page 6

SQL Platform Support 360 SQL Command Reference 361 6. Securing Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 How to Use This Chapter 439 SQL Platform Support 440 SQL Command Reference 441 7. SQL Built-in Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503 How to Use This Chapter 504 Types of Functions 504 SQL Functions 505 Platform-Specific Extensions 538 8. SQL Built-in Aggregate and Window Functions. . . . . . . . . . . . . . . . . . . . . . . 593 How to Use This Chapter 593 SQL Aggregate Functions 594 Complementary Functions 623 SQL Window Functions 625 Platform-Specific Extensions 640 9. Storing Logic in the Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 How to Use This Chapter 647 SQL Platform Support 648 SQL Command Reference 650 10. Flexible and Schemaless. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745 Why JSON? 747 JSON Support 749 Why XML? 777 Appendix. Shared and Platform-Specific Keywords. . . . . . . . . . . . . . . . . . . . . . 791 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799 iv | Table of Contents

📄 Page 7

Preface Since its first incarnation in the 1970s, the Structured Query Language (SQL) has evolved hand-in-hand with the information boom. As a result, it is the most widely used language to administer and query relational databases. Many software compa‐ nies and developers, including those in the open source and NoSQL movements, have developed their own SQL dialects in response to specific needs. All the while, standards bodies have codified a growing list of features. SQL in a Nutshell, 4th edition, describes the latest SQL standard—SQL:2016—ver‐ sion of many SQL commands, then details how different platforms implement that particular command. (Although the standards bodies have released SQL:2019, the new specifications mostly lie in the area of multidimensional arrays, which have not been implemented by the major vendors. You can read more about how SQL can be used in conjunction with these arrays on the ISO website). In this book, you will find a brief overview of the relational database model, which undergirds all relational database management systems (RDBMSs), followed by a more thor‐ ough treatment of frequently used SQL syntax and commands. New in the fourth edition are expanded explanations of programming concepts used in each vendor’s offerings, such as stored procedures and user-defined functions. And, of course, we have added popular commands that have arrived since the publication of the third edition. SQL in a Nutshell, 4th edition, provides a concise guide to the two most commonly installed commercial database packages on the market, Oracle and Microsoft SQL Server, and to the three most commonly installed open source databases: MySQL, PostgreSQL, and MariaDB (a fork of MySQL). The language and database versions covered in this book include: • SQL:2016 standard syntax • MySQL 8 and MariaDB 10.5 v

📄 Page 8

• Oracle Database 19c • PostgreSQL 14 • Microsoft SQL Server 2019 Why This Book? The primary and definitive source of information for any given relational database package is the official documentation, along with help files, tutorials, advisories, and other tidbits provided by the vendors themselves. But while each vendor’s documentation should be the resource that developers and database administrators turn to first, official documentation has a number of limitations: • It describes the vendor’s implementation of SQL without providing an indica‐ tion of how well that implementation meets the SQL standard. • It covers only the vendor’s specific product, without discussing potential trans‐ lation, migration, or integration issues across different vendors. • It covers individual commands in often unrelenting detail, thereby obscuring the most common use cases. • It typically describes programming methods in an overwhelming number of disconnected articles or help files. A vendor’s official documentation can be expected to provide an exhaustive explan‐ ation of every aspect of their offering. It will describe every command, including each obscure variant, along with some implementation guidance. Subjective com‐ mentaries are off-limits. However, if you move between data platforms and need to be productive quickly, you will rarely need this level of detail on all the obscure command variations. Instead, you’re looking for the most applicable usage found in real-life situations. This book begins where the vendor documentation ends by distilling the experien‐ ces of professional database administrators and developers who have used these SQL products to support complex enterprise applications. It offers you the benefit of their decades of experience in a compact and easily usable format. Whether you’re new to SQL or you have been using SQL since its earliest days, there are always new tips and techniques to learn. And when you’re moving between different data platforms, it’s always important to uncover compatibility issues before they bite you. Who Should Read This Book? SQL in a Nutshell, 4th edition, targets several groups of users. It will be useful for developers who require a concise and handy SQL reference tool; for developers who need to migrate from one SQL dialect to another; and for database administra‐ tors (DBAs) who need to both execute a myriad of SQL statements to keep their vi | Preface

📄 Page 9

enterprise databases up and running, and to create and manage objects such as tables, indexes, and views. This book is a reference work, not a tutorial. The writing is not expository. For example, we won’t explain the concept of an elementary nested loop algorithm. Instead, we will explain the workings of the cursor as implemented in the standards, then special capabilities of cursors on each database platform. We include advice, based on experience, on how to avoid and circumvent pitfalls when using cursors. While we don’t intend this book to serve as a tutorial on SQL or a handbook for the database architect, we do provide some coverage of introductory design topics. Chapters 1 and 2 provide a concise introduction to the language itself, covering essential structures, basic usage, and some history. If you’re new to SQL, these two chapters will help you get going. How This Book Is Organized The book is divided into 10 chapters and an appendix: Chapter 1, “SQL History and Implementations” Discusses the relational database model, describes the current and previous SQL standards, and introduces the SQL implementations covered in this book. Chapter 2, “Foundational Concepts” Describes the fundamental concepts necessary for understanding relational databases and SQL commands, including different syntax elements, data types, and constraints. Chapter 3, “Structuring Your Data” Provides the command reference for SQL statements that CREATE, ALTER, and DROP the various objects you might find in a relational database, such as tables, views, and schemas. The commands described here for defining data structures are collectively referred to as Data Definition Language (DDL) statements. Chapter 4, “Reading Your Data” Discusses the glorious SELECT statement and all of the optional clauses and keywords relevant to reading data. From subqueries and joins to cursors and common table expressions (CTEs), this chapter goes deep into the single most frequently used SQL statement. Chapter 5, “Manipulating Your Data” Provides details on SQL statements used to manipulate data within a given database. These are collectively referred to as Data Manipulation Language (DML) statements; they include INSERT, UPDATE, DELETE, MERGE, and TRUNCATE as well as SELECT (discussed in Chapter 4). Preface | vii

📄 Page 10

Chapter 6, “Securing Your Data” Details the ways to control access to and provide security for data using the Data Control Language (DCL) statements of the SQL standard: GRANT, REVOKE, SET, and a few others. Chapter 7, “SQL Built-in Functions” Covers the full scope of built-in functions, both those detailed in the SQL standard and those provided by the database platforms covered in the book. From data-handling functions to data type conversion functions to string manipulation functions, they’re all here. Chapter 8, “SQL Built-in Aggregate and Window Functions” Details the use and handling of two of the most important and powerful components of SQL code. Aggregate functions provide the capability to sum, average, find minimum and maximum values, and more. Window functions are functions that use values from one or more records, returning a value for each row, and are characterized by the use of the OVER clause. This is in contrast to aggregate functions, which return a single value for multiple rows. Chapter 9, “Storing Logic in the Database” Gives an overview of important aspects of SQL code used to write stored procedures, triggers, and user-defined functions. In this chapter you will find details on conditional control, variable handling, subroutines, and other aspects of developing database-dependent code. Chapter 10, “Flexible and Schemaless” Offers a quick introduction to handling JSON and XML data in relational databases. Appendix Provides a comprehensive list of keywords declared by the SQL standard and by the various database platforms covered in this book. You can use this as a reference to look for words that you should not use for object or variable names. How to Use This Book SQL in a Nutshell, 4th edition, is primarily a command reference. As a consequence, you’ll probably use it to look up a variety of SQL commands and functions. How‐ ever, with documentation for the SQL standard itself plus four database platforms, the description for each command has the potential to get very large. In an attempt to reduce redundant verbiage describing each command and its variants across the various database platforms, the book compares each platform’s implementation to the SQL standard that is current at the time of writing. If the platform supports a clause as described in the SQL discussion, we won’t repeat that clause again in the section(s) describing the vendor implementations of the SQL standard. viii | Preface

📄 Page 11

Generic and transportable examples are provided for each SQL statement. Platform- specific examples are given only in the event that at least one RDBMS discussed in the book supports the command, which is not always the case as the SQL standard usually precedes implementation by database platforms. Examples that highlight unique extensions and enhancements of the different platforms, of which there are many, are provided. We recognize that our approach may necessitate jumping from a description of a platform’s implementation of a command back to the description of the SQL stan‐ dard syntax and implementation details. However, we felt that this was preferable to bulking up the book with hundreds of pages of redundant content. Resources This book strives to deliver an up-to-date and comprehensive discussion of the SQL standard and its implementation by the leading database platforms. This, however, is a big task, and it’s a lot like shooting at a moving target. While the book contains decades’ worth of useful information, you may wish to check with a specific database platform vendor for the latest details. The following websites provide additional information about the various platforms covered in this book: MySQL and MariaDB The MySQL website and MySQL 8.0 Reference Manual are both good resources, as is the MariaDB documentation. PostgreSQL The PostgreSQL website makes a great deal of useful information available for download, and also maintains mailing lists for PostgreSQL users. The documentation for the latest major stable release of PostgreSQL and the docu‐ mentation for the upcoming release are both good resources. Oracle Oracle’s website has a great resource for hardcore Oracle users, as well as all the Oracle documentation. SQL Server The official Microsoft SQL Server website and the SQL Server documentation includes full documentation for SQL Server and its cloud counterpart, Azure SQL. Changes in the Fourth Edition One of the biggest reasons to release a new edition of a technology book is because the technology has progressed. Since the third edition of this book was published, three new versions of the ANSI/ISO standard have been published, and the database platforms it covers have each delivered at least two major releases. Consequently, our readers want fresh content on the latest versions of SQL in the marketplace today. Preface | ix

📄 Page 12

Here are some additional details about changes in this fourth edition: Improved navigation Previous editions of this book grouped all SQL commands into a single chap‐ ter. As the book grew over the years, the chapter covering SQL commands became difficult to navigate. As a result, this latest edition now features multi‐ ple chapters broken out by traditional categories of the SQL standard to make navigation easier. Programming basics While previous editions showed you how to program a stored procedure, trigger, or other SQL module, the fourth edition includes a full chapter dedi‐ cated to writing structured procedural code on Oracle, SQL Server, MySQL, MariaDB, and PostgreSQL. The syntax for keywords like IF statements and WHILE loops is not actually part of the SQL standard, but these are part of the everyday life of DBAs and developers. Therefore, it made sense to give readers a good overview of how to perform this aspect of SQL coding. Greater breadth SQL is the lingua franca of data and databases. In some ways, it is analogous to the way in which the English language is the most popular second language in the world. Across the globe, English is often the first language a person learns after their native tongue. In the same way, there are a multitude of popular programming languages that allow developers to do great work, yet they all embed SQL code within to communicate with the database backend. More examples It’s impossible to have too many examples. We’ve added to our already large set of basic examples, including more sample code that highlights the unique and powerful capabilities of the SQL standard and the extensions offered by each database platform. In addition, this version includes examples that show‐ case the functionality introduced in the newer SQL standards, such as JSON support. Conventions Used in This Book The following typographical conventions are used in this book: Italic Used to introduce new terms, for emphasis, for filenames and file extensions, and for user/group/role names; used to indicate programming elements such as commands, functions, data types, variables, and values; and used to display the names of database objects such as tables, columns, and schemas. UPPERCASE CONSTANT WIDTH Used to indicate SQL keywords when they appear in the text. Constant width Used to indicate programming syntax, code fragments, and examples. x | Preface

📄 Page 13

Constant width italic Used to indicate variables in code that should be replaced with user-supplied values. Constant width bold Used in code sections to highlight portions of the code and in examples to distinguish input from results. This element signifies a tip or suggestion. This element signifies a general note. This element indicates a warning or caution. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit http://oreilly.com. Preface | xi

📄 Page 14

How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/sql-nutshell-4e. Email bookquestions@oreilly.com to comment or ask technical questions about this book. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media Follow us on Twitter: https://twitter.com/oreillymedia Watch us on YouTube: https://youtube.com/oreillymedia Acknowledgments We’d like to take a moment to thank a few special individuals at O’Reilly Media. First, we owe a huge debt of gratitude to Rita Fernando, the editor of this fourth edition, and to Gary O’Brien, who helped us as our initial editor. All of the people at O’Reilly are extraordinary, but Rita is exceptional. She helped keep our noses to the grindstone, provided encouragement and advice, and ensured that we finished what we started. With her helpful, collaborative, and relaxed work style, Rita is a pleasure to work with. Thank you for all you’ve done for us! We also owe a debt to our fine technical reviewers and helpers. We owe a hearty thank you to our stellar technical reviewers: Robert de Graaf, Keith Hare, Evelyn Nunez, Caitlin O’Nan, and Bert Scalzo. Your contributions have greatly improved the accuracy, readability, and value of this book. Without your assistance, we would have had many more issues of accuracy and quality to remedy. In addition, we’d like to tip our hats to Daniel Mainle, Faisal Azeem, and Stella Zhou, who helped conduct important research groundwork for us. Kevin E. Kline’s Acknowledgments Many people helped deliver the big, thick book you hold in your hands. This note expresses my appreciation to those who helped make this book a reality. First of all, a big thanks to my two awesome coauthors, Regina and Leo. It has been such a pleasure to work with you on this massive project. Your expertise, tenacity, xii | Preface

📄 Page 15

and professionalism have made this entire endeavor possible. I am grateful for you and appreciate you so much! Here’s a dedication to my beloved—Rachel. From a small seed planted so many years ago, our tree of love and joy has grown taller and broader than I could have ever expected. More precious than jewels and more valuable than rubies, your love restores my heart and my faith daily. Regina Obe and Leo Hsu’s Acknowledgments We thank our two development editors: Gary O’Brien, who carried us through the worst of the pandemic, and Rita Fernando, for having brought our manuscript to completion. They were our guiding angels, but devilish when necessary. A tremendous thanks to Evelyn Nunez, who painstakingly reviewed some of the new chapters, correcting our numerous mistakes and adding in missing information. We thank our own Caitlin O’Nan, who has been our employee for countless years. She reviewed the manuscript, bringing her SQL and programming expertise to the book. If anyone can catch our flaws, Caitlin is the one. Finally, we thank Kevin for inviting us to coauthor with him. The quote “We stood on the shoulders of a giant” comes to mind. Needless to say, this fourth edition would not have been possible without his work on the prior three editions. Preface | xiii

📄 Page 16

(This page has no text content)

📄 Page 17

1 SQL History and Implementations In the early 1970s, the seminal work of IBM research fellow Dr. E. F. Codd led to the development of a relational data model product called SEQUEL, or Structured English Query Language. SEQUEL ultimately became SQL, the Structured Query Language. Ironically, the SQL standard treats “SQL” as the name of this language and not as an acronym. Jim Melton, longtime editor of the SQL standard, claims that if SQL is an acronym, it stands for “SQL Query Language.” IBM, along with other relational database vendors, wanted a standardized method for accessing and manipulating data in a relational database. Although IBM was the first to develop relational database theory, Oracle was first to market the technology. Over time, SQL proved popular enough in the marketplace to attract the attention of the American National Standards Institute (ANSI) in cooperation with the Inter‐ national Standards Organization (ISO), which released standards for SQL in 1986, 1989, 1992, 1999, 2003, 2008, 2011, 2016, and 2019. Since 1986, various competing languages have allowed developers to access and manipulate relational data. However, few were as easy to learn or as universally accepted as SQL. Developers and administrators now have the benefit of being able to learn a single language that, with minor adjustments, is applicable to a wide variety of database platforms, applications, and products. SQL in a Nutshell, 4th edition, provides the syntax for five common implementa‐ tions of SQL: • The ANSI/ISO SQL standard (SQL:2016) • MySQL 8 and MariaDB 10.5 • Oracle Database 19c 1

📄 Page 18

• PostgreSQL 14 • Microsoft SQL Server 2019 The Relational Model and ANSI SQL Relational database management systems (RDBMSs) such as those covered in this book are the primary engines of information systems worldwide, and particularly of web applications and distributed client/server computing systems. They enable a multitude of users to quickly and simultaneously access, create, edit, and manipu‐ late data without impacting other users. They also allow developers to write useful applications to access their resources and provide administrators with the capabili‐ ties they need to maintain, secure, and optimize organizational data resources. An RDBMS is defined as a system whose users view data as a collection of tables related to each other through common data values. Data is stored in tables, which are composed of rows and columns. Tables of independent data can be linked (or related) to one another if they each have unique, identifying columns of data (called keys) that represent data values held in common. E. F. Codd first described rela‐ tional database theory in his landmark paper “A Relational Model of Data for Large Shared Data Banks,” published in the Communications of the ACM (Association for Computing Machinery) in June 1970. Under Codd’s new relational data model, data was structured (into tables of rows and columns); manageable using operations such as selections, projections, and joins; and consistent as the result of integrity rules such as keys and referential integrity. These terms and their definitions were based upon earlier mathematical concepts—in particular, relational algebra—and as such are fully provable theorems. Codd also articulated rules that governed how a relational database should be designed. The process for applying these rules is now known as normalization. Codd’s Rules for Relational Database Systems Codd applied rigorous mathematical theories (primarily relational algebra and set theory) to the management of data, from which he compiled a list of criteria a database must meet to be considered relational. Cobb, at least in part, set forth his 12 principles to constrain the overenthusiastic marketing verbiage of the many vendors of every remotely DBMS-like product who wanted to claim to be “rela‐ tional.” In fact, Cobb was so emphatic about these principles of relational databases as a mechanism of anti-hype that he further laid out an important foundational principle he called Rule 0: Rule 0: For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities. 2 | Chapter 1: SQL History and Implementations

📄 Page 19

At its core, the relational database concept centers around storing data in tables. This concept is now so common as to seem trivial; however, it’s only since the 1980s that the idea of designing a system capable of sustaining the relational model has not been considered a long shot with limited usefulness. Following are Codd’s Twelve Principles of Relational Databases: 1. Information is represented logically in tables. 2. Data must be logically accessible by table, primary key, and column. 3. Null values must be uniformly treated as “missing information,” not as empty strings, blanks, or zeros. 4. Metadata (data about the database) must be stored in the database just as regular data is. 5. A single language must be able to define data, views, integrity constraints, authorization, transactions, and data manipulation. 6. Views must show the updates of their base tables and vice versa. 7. A single operation must be available to do each of the following operations: retrieve data, insert data, update data, or delete data. 8. Batch and end-user operations are logically separate from physical storage and access methods. 9. Batch and end-user operations can change the database schema without having to re-create it or the applications built upon it. 10. Integrity constraints must be available and stored in the metadata, not in an application program. 11. The data manipulation language of the relational system should not care where or how the physical data is distributed and should not require alteration if the physical data is centralized or distributed. 12. Any row processing done in the system must obey the same integrity rules and constraints that set-processing operations do. While these rules do not apply to application development, they continue to be the litmus test used to validate the “relational” characteristics of a database platform; a database that does not meet all of these rules is not fully relational. Currently, most commercial RDBMS products pass Codd’s test, and all the platforms discussed in the reference material of SQL in a Nutshell, 4th edition, satisfy these requirements. The standard also specifies support for handling non-relational formats like JSON and XML; we’ll discuss this in Chapter 10. Understanding Codd’s principles assists developers in the proper development and design of relational databases (RDBs). The following sections detail how some of these requirements are met within SQL using RDBs. The Relational Model and ANSI SQL | 3 SQ L H isto ry

📄 Page 20

Data structures (rules 1, 2, 8, and 9) Codd’s rules 1 and 2 state that “information is represented logically in tables” and that “data must be logically accessible by table, primary key, and column.” So, the process of defining a table for a relational database does not require that programs instruct the database how to interact with the underlying physical data structures. Furthermore, SQL logically isolates the processes of accessing data and physically maintaining that data, as required by rules 8 and 9, which state that batch and end-user operations “are logically separate from physical storage and access methods” and “can change the database schema without having to re-create it or the applications built upon it.” In the relational model, data is shown logically as a two-dimensional table that describes a single entity (for example, business expenses). Academics refer to tables as entities and to columns as attributes. Tables are composed of rows, or records (academics call them tuples), and columns (called attributes, since each column of a table describes a specific attribute of the entity). The intersection of a record and a column provides a single value. However, it is quite common to hear this referred to as a field, from spreadsheet parlance. The column or columns whose values uniquely identify each record can act as a primary key. These days this representation seems elementary, but it was actually quite innovative when it was first proposed. The SQL standard defines a whole data structure hierarchy beyond simple tables, though tables are the core data structure. Relational design handles data on a table- by-table basis, not on a record-by-record basis. This table-centric orientation is the heart of set programming. Consequently, almost all SQL commands operate much more efficiently against sets of data within or across tables than against individual records. Said another way, effective SQL programming requires that you think in terms of sets of data, rather than of individual rows. Figure 1-1 is a description of SQL’s terminology used to describe the hierarchical data structures used by a relational database: catalogs contain sets of schemas; schemas contain sets of objects, such as tables and views; and tables are composed of sets of columns and records. For example, in a Business_Expense table, a column called Expense_Date might show when an expense was incurred. Each record in the table describes a specific entity; in this case, everything that makes up a business expense (when it happened, how much it cost, who incurred the expense, what it was for, and so on). Each attribute of an expense—in other words, each column—is supposed to be atomic; that is, each column is supposed to contain one, and only one, value. If a table is constructed in which the intersection of a row and column can contain more than one distinct value, one of SQL’s primary design guidelines has been violated. (That said, some of the database platforms discussed in this book do allow you to place more than one value into a column, via the VARRAY or TABLE data types or, more commonly in the last several years, XML or JSON data types.) 4 | Chapter 1: SQL History and Implementations