Learning SQL (Alan Beaulieu) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.2 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

Alan Beaulieu Learning SQL Generate, Manipulate, and Retrieve Data Third Edition

📄 Page 2

(This page has no text content)

📄 Page 3

Alan Beaulieu Learning SQL Generate, Manipulate, and Retrieve Data THIRD EDITION Boston Farnham Sebastopol TokyoBeijing

📄 Page 4

978-1-492-05761-1 [MBP] Learning SQL by Alan Beaulieu Copyright © 2020 Alan Beaulieu. 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: Jessica Haberman Development Editor: Jeff Bleiel Production Editor: Deborah Baker Copyeditor: Charles Roumeliotis Proofreader: Chris Morris Indexer: Angela Howard Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Rebecca Demarest August 2005: First Edition April 2009: Second Edition April 2020: Third Edition Revision History for the Third Edition 2020-03-04: First Release 2020-09-04: Second Release See http://oreilly.com/catalog/errata.csp?isbn=9781492057611 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Learning SQL, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author, and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi 1. A Little Background. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction to Databases 1 Nonrelational Database Systems 2 The Relational Model 5 Some Terminology 7 What Is SQL? 8 SQL Statement Classes 9 SQL: A Nonprocedural Language 10 SQL Examples 11 What Is MySQL? 13 SQL Unplugged 14 What’s in Store 15 2. Creating and Populating a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Creating a MySQL Database 17 Using the mysql Command-Line Tool 18 MySQL Data Types 20 Character Data 20 Numeric Data 23 Temporal Data 25 Table Creation 27 Step 1: Design 27 Step 2: Refinement 28 Step 3: Building SQL Schema Statements 30 Populating and Modifying Tables 33 Inserting Data 33 iii

📄 Page 6

Updating Data 38 Deleting Data 39 When Good Statements Go Bad 39 Nonunique Primary Key 39 Nonexistent Foreign Key 40 Column Value Violations 40 Invalid Date Conversions 40 The Sakila Database 41 3. Query Primer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Query Mechanics 45 Query Clauses 47 The select Clause 48 Column Aliases 50 Removing Duplicates 51 The from Clause 53 Tables 53 Table Links 56 Defining Table Aliases 57 The where Clause 58 The group by and having Clauses 60 The order by Clause 61 Ascending Versus Descending Sort Order 63 Sorting via Numeric Placeholders 64 Test Your Knowledge 65 Exercise 3-1 65 Exercise 3-2 65 Exercise 3-3 65 Exercise 3-4 65 4. Filtering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Condition Evaluation 67 Using Parentheses 68 Using the not Operator 69 Building a Condition 70 Condition Types 71 Equality Conditions 71 Range Conditions 73 Membership Conditions 77 Matching Conditions 79 Null: That Four-Letter Word 82 Test Your Knowledge 85 iv | Table of Contents

📄 Page 7

Exercise 4-1 86 Exercise 4-2 86 Exercise 4-3 86 Exercise 4-4 86 5. Querying Multiple Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 What Is a Join? 87 Cartesian Product 88 Inner Joins 89 The ANSI Join Syntax 91 Joining Three or More Tables 93 Using Subqueries as Tables 95 Using the Same Table Twice 96 Self-Joins 98 Test Your Knowledge 99 Exercise 5-1 99 Exercise 5-2 99 Exercise 5-3 100 6. Working with Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Set Theory Primer 101 Set Theory in Practice 104 Set Operators 105 The union Operator 106 The intersect Operator 108 The except Operator 109 Set Operation Rules 111 Sorting Compound Query Results 111 Set Operation Precedence 112 Test Your Knowledge 114 Exercise 6-1 114 Exercise 6-2 114 Exercise 6-3 114 7. Data Generation, Manipulation, and Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Working with String Data 115 String Generation 116 String Manipulation 121 Working with Numeric Data 129 Performing Arithmetic Functions 129 Controlling Number Precision 131 Handling Signed Data 133 Table of Contents | v

📄 Page 8

Working with Temporal Data 134 Dealing with Time Zones 134 Generating Temporal Data 136 Manipulating Temporal Data 140 Conversion Functions 144 Test Your Knowledge 145 Exercise 7-1 145 Exercise 7-2 145 Exercise 7-3 145 8. Grouping and Aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Grouping Concepts 147 Aggregate Functions 150 Implicit Versus Explicit Groups 151 Counting Distinct Values 152 Using Expressions 153 How Nulls Are Handled 153 Generating Groups 155 Single-Column Grouping 155 Multicolumn Grouping 156 Grouping via Expressions 157 Generating Rollups 157 Group Filter Conditions 159 Test Your Knowledge 160 Exercise 8-1 160 Exercise 8-2 160 Exercise 8-3 160 9. Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 What Is a Subquery? 161 Subquery Types 163 Noncorrelated Subqueries 163 Multiple-Row, Single-Column Subqueries 164 Multicolumn Subqueries 169 Correlated Subqueries 171 The exists Operator 173 Data Manipulation Using Correlated Subqueries 174 When to Use Subqueries 175 Subqueries as Data Sources 176 Subqueries as Expression Generators 182 Subquery Wrap-Up 184 Test Your Knowledge 185 vi | Table of Contents

📄 Page 9

Exercise 9-1 185 Exercise 9-2 185 Exercise 9-3 185 10. Joins Revisited. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Outer Joins 187 Left Versus Right Outer Joins 190 Three-Way Outer Joins 191 Cross Joins 192 Natural Joins 198 Test Your Knowledge 199 Exercise 10-1 200 Exercise 10-2 200 Exercise 10-3 (Extra Credit) 200 11. Conditional Logic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 What Is Conditional Logic? 201 The case Expression 202 Searched case Expressions 202 Simple case Expressions 204 Examples of case Expressions 205 Result Set Transformations 205 Checking for Existence 206 Division-by-Zero Errors 208 Conditional Updates 209 Handling Null Values 210 Test Your Knowledge 211 Exercise 11-1 211 Exercise 11-2 211 12. Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 Multiuser Databases 213 Locking 214 Lock Granularities 214 What Is a Transaction? 215 Starting a Transaction 217 Ending a Transaction 218 Transaction Savepoints 219 Test Your Knowledge 222 Exercise 12-1 222 Table of Contents | vii

📄 Page 10

13. Indexes and Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Indexes 223 Index Creation 224 Types of Indexes 229 How Indexes Are Used 231 The Downside of Indexes 232 Constraints 233 Constraint Creation 234 Test Your Knowledge 237 Exercise 13-1 237 Exercise 13-2 237 14. Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 What Are Views? 239 Why Use Views? 242 Data Security 242 Data Aggregation 243 Hiding Complexity 244 Joining Partitioned Data 244 Updatable Views 245 Updating Simple Views 246 Updating Complex Views 247 Test Your Knowledge 249 Exercise 14-1 249 Exercise 14-2 250 15. Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 Data About Data 251 information_schema 252 Working with Metadata 257 Schema Generation Scripts 257 Deployment Verification 260 Dynamic SQL Generation 261 Test Your Knowledge 265 Exercise 15-1 265 Exercise 15-2 265 16. Analytic Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 Analytic Function Concepts 267 Data Windows 268 Localized Sorting 269 Ranking 270 viii | Table of Contents

📄 Page 11

Ranking Functions 271 Generating Multiple Rankings 274 Reporting Functions 277 Window Frames 279 Lag and Lead 281 Column Value Concatenation 283 Test Your Knowledge 284 Exercise 16-1 284 Exercise 16-2 285 Exercise 16-3 285 17. Working with Large Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 Partitioning 287 Partitioning Concepts 288 Table Partitioning 288 Index Partitioning 289 Partitioning Methods 289 Partitioning Benefits 297 Clustering 297 Sharding 298 Big Data 299 Hadoop 299 NoSQL and Document Databases 300 Cloud Computing 300 Conclusion 301 18. SQL and Big Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Introduction to Apache Drill 303 Querying Files Using Drill 304 Querying MySQL Using Drill 306 Querying MongoDB Using Drill 309 Drill with Multiple Data Sources 315 Future of SQL 317 A. ER Diagram for Example Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319 B. Solutions to Exercises. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349 Table of Contents | ix

📄 Page 12

(This page has no text content)

📄 Page 13

Preface Programming languages come and go constantly, and very few languages in use today have roots going back more than a decade or so. Some examples are COBOL, which is still used quite heavily in mainframe environments; Java, which was born in the mid-1990s and has become one of the most popular programming languages; and C, which is still quite popular for operating systems and server development and for embedded systems. In the database arena, we have SQL, whose roots go all the way back to the 1970s. SQL was initially created to be the language for generating, manipulating, and retriev‐ ing data from relational databases, which have been around for more than 40 years. Over the past decade or so, however, other data platforms such as Hadoop, Spark, and NoSQL have gained a great deal of traction, eating away at the relational database market. As will be discussed in the last few chapters of this book, however, the SQL language has been evolving to facilitate the retrieval of data from various platforms, regardless of whether the data is stored in tables, documents, or flat files. Why Learn SQL? Whether you will be using a relational database or not, if you are working in data sci‐ ence, business intelligence, or some other facet of data analysis, you will likely need to know SQL, along with other languages/platforms such as Python and R. Data is everywhere, in huge quantities, and arriving at a rapid pace, and people who can extract meaningful information from all this data are in big demand. Why Use This Book to Do It? There are plenty of books out there that treat you like a dummy, idiot, or some other flavor of simpleton, but these books tend to just skim the surface. At the other end of the spectrum are reference guides that detail every permutation of every statement in a language, which can be useful if you already have a good idea of what you want to xi

📄 Page 14

do but just need the syntax. This book strives to find the middle ground, starting with some background of the SQL language, moving through the basics, and then pro‐ gressing into some of the more advanced features that will allow you to really shine. Additionally, this book ends with a chapter showing how to query data in nonrela‐ tional databases, which is a topic rarely covered in introductory books. Structure of This Book This book is divided into 18 chapters and 2 appendixes: Chapter 1, A Little Background Explores the history of computerized databases, including the rise of the rela‐ tional model and the SQL language. Chapter 2, Creating and Populating a Database Demonstrates how to create a MySQL database, create the tables used for the examples in this book, and populate the tables with data. Chapter 3, Query Primer Introduces the select statement and further demonstrates the most common clauses (select, from, where). Chapter 4, Filtering Demonstrates the different types of conditions that can be used in the where clause of a select, update, or delete statement. Chapter 5, Querying Multiple Tables Shows how queries can utilize multiple tables via table joins. Chapter 6, Working with Sets This chapter is all about data sets and how they can interact within queries. Chapter 7, Data Generation, Manipulation, and Conversion Demonstrates several built-in functions used for manipulating or converting data. Chapter 8, Grouping and Aggregates Shows how data can be aggregated. Chapter 9, Subqueries Introduces subqueries (a personal favorite) and shows how and where they can be utilized. Chapter 10, Joins Revisited Further explores the various types of table joins. xii | Preface

📄 Page 15

Chapter 11, Conditional Logic Explores how conditional logic (i.e., if-then-else) can be utilized in select, insert, update, and delete statements. Chapter 12, Transactions Introduces transactions and shows how to use them. Chapter 13, Indexes and Constraints Explores indexes and constraints. Chapter 14, Views Shows how to build an interface to shield users from data complexities. Chapter 15, Metadata Demonstrates the utility of the data dictionary. Chapter 16, Analytic Functions Covers functionality used to generate rankings, subtotals, and other values used heavily in reporting and analysis. Chapter 17, Working with Large Databases Demonstrates techniques for making very large databases easier to manage and traverse. Chapter 18, SQL and Big Data Explores the transformation of the SQL language to allow retrieval of data from nonrelational data platforms. Appendix A, ER Diagram for Example Database Shows the database schema used for all examples in the book. Appendix B, Solutions to Exercises Shows solutions to the chapter exercises. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. Preface | xiii

📄 Page 16

Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. Constant width bold Shows commands or other text that should be typed literally by the user. Indicates a tip, suggestion, or general note. For example, I use notes to point you to useful new features in Oracle9i. Indicates a warning or caution. For example, I’ll tell you if a certain SQL clause might have unintended consequences if not used care‐ fully. Using the Examples in This Book To experiment with the data used for the examples in this book, you have two options: • Download and install the MySQL server version 8.0 (or later) and load the Sakila example database from https://dev.mysql.com/doc/index-other.html. • Go to https://www.katacoda.com/mysql-db-sandbox/scenarios/mysql-sandbox to access the MySQL Sandbox, which has the Sakila sample database loaded in a MySQL instance. You’ll have to set up a (free) Katacoda account. Then, click the Start Scenario button. If you choose the second option, once you start the scenario, a MySQL server is installed and started, and then the Sakila schema and data are loaded. When it’s ready, a standard mysql> prompt appears, and you can then start querying the sample data‐ base. This is certainly the easiest option, and I anticipate that most readers will choose this option; if this sounds good to you, feel free to skip ahead to the next section. If you prefer to have your own copy of the data and want any changes you have made to be permanent, or if you are just interested in installing the MySQL server on your own machine, you may prefer the first option. You may also opt to use a MySQL server hosted in an environment such as Amazon Web Services or Google Cloud. In either case, you will need to perform the installation/configuration yourself, as it is beyond the scope of this book. Once your database is available, you will need to fol‐ low a few steps to load the Sakila sample database. xiv | Preface

📄 Page 17

First, you will need to launch the mysql command-line client and provide a password, and then perform the following steps: 1. Go to https://dev.mysql.com/doc/index-other.html and download the files for “sakila database” under the Example Databases section. 2. Put the files in a local directory such as C:\temp\sakila-db (used for the next two steps, but overwrite with your directory path). 3. Type source c:\temp\sakila-db\sakila-schema.sql; and press Enter. 4. Type source c:\temp\sakila-db\sakila-data.sql; and press Enter. You should now have a working database populated with all the data needed for the examples in this book. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy 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, please visit http:// oreilly.com. 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 and any additional informa‐ tion. You can access this page at https://oreil.ly/Learning_SQL3. Email bookquestions@oreilly.com to comment or ask technical questions about this book. Preface | xv

📄 Page 18

For news and more information about our books and courses, see our website at http://www.oreilly.com. Find us on Facebook: http://facebook.com/oreilly Follow us on Twitter: http://twitter.com/oreillymedia Watch us on YouTube: http://www.youtube.com/oreillymedia Acknowledgments I would like to thank my editor, Jeff Bleiel, for helping to make this third edition a reality, along with Thomas Nield, Ann White-Watkins, and Charles Givre, who were kind enough to review the book for me. Thanks also go to Deb Baker, Jess Haberman, and all the other folks at O’Reilly Media who were involved. Lastly, I thank my wife, Nancy, and my daughters, Michelle and Nicole, for their encouragement and inspiration. xvi | Preface

📄 Page 19

CHAPTER 1 A Little Background Before we roll up our sleeves and get to work, it would be helpful to survey the his‐ tory of database technology in order to better understand how relational databases and the SQL language evolved. Therefore, I’d like to start by introducing some basic database concepts and looking at the history of computerized data storage and retrieval. For those readers anxious to start writing queries, feel free to skip ahead to Chapter 3, but I recommend returning later to the first two chapters in order to better understand the history and utility of the SQL language. Introduction to Databases A database is nothing more than a set of related information. A telephone book, for example, is a database of the names, phone numbers, and addresses of all people liv‐ ing in a particular region. While a telephone book is certainly a ubiquitous and fre‐ quently used database, it suffers from the following: • Finding a person’s telephone number can be time consuming, especially if the tel‐ ephone book contains a large number of entries. • A telephone book is indexed only by last/first names, so finding the names of the people living at a particular address, while possible in theory, is not a practical use for this database. • From the moment the telephone book is printed, the information becomes less and less accurate as people move into or out of a region, change their telephone numbers, or move to another location within the same region. 1

📄 Page 20

The same drawbacks attributed to telephone books can also apply to any manual data storage system, such as patient records stored in a filing cabinet. Because of the cum‐ bersome nature of paper databases, some of the first computer applications developed were database systems, which are computerized data storage and retrieval mecha‐ nisms. Because a database system stores data electronically rather than on paper, a database system is able to retrieve data more quickly, index data in multiple ways, and deliver up-to-the-minute information to its user community. Early database systems managed data stored on magnetic tapes. Because there were generally far more tapes than tape readers, technicians were tasked with loading and unloading tapes as specific data was requested. Because the computers of that era had very little memory, multiple requests for the same data generally required the data to be read from the tape multiple times. While these database systems were a significant improvement over paper databases, they are a far cry from what is possible with today’s technology. (Modern database systems can manage petabytes of data, accessed by clusters of servers each caching tens of gigabytes of that data in high-speed mem‐ ory, but I’m getting a bit ahead of myself.) Nonrelational Database Systems This section contains some background information about pre- relational database systems. For those readers eager to dive into SQL, feel free to skip ahead a couple of pages to the next section. Over the first several decades of computerized database systems, data was stored and represented to users in various ways. In a hierarchical database system, for example, data is represented as one or more tree structures. Figure 1-1 shows how data relating to George Blake’s and Sue Smith’s bank accounts might be represented via tree structures. 2 | Chapter 1: A Little Background