SQL Pocket Guide A Guide to SQL Usage (Alice Zhao) (Z-Library)

📄 File Format: PDF

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

Alice Zhao SQL A Guide to SQL Usage Pocket Guide 4th Edition

📄 Page 2

(This page has no text content)

📄 Page 3

Alice Zhao SQL Pocket Guide FOURTH EDITION

📄 Page 4

978-1-492-09040-3 [LSI] SQL Pocket Guide by Alice Zhao Copyright © 2021 Alice Zhao. 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 promo‐ tional 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: Amelia Blevins and Jeff Bleiel Production Editor: Caitlin Ghegan Copyeditor: Piper Editorial Consulting, LLC Proofreader: James Fraleigh Indexer: Ellen Troutman-Zaig Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea September 2021: Fourth Edition Revision History for the Fourth Edition 2021-08-26: First Release See https://oreil.ly/sqlpocketerrata for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. SQL Pocket Guide, 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 result‐ ing 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 Chapter 1: SQL Crash Course 1 What Is a Database? 1 SQL 1 NoSQL 2 Database Management Systems (DBMS) 3 A SQL Query 6 The SELECT Statement 7 Order of Execution 9 A Data Model 10 Chapter 2: Where Can I Write SQL Code? 13 RDBMS Software 14 SQLite 15 MySQL 17 Oracle 17 PostgreSQL 18 SQL Server 19 iii

📄 Page 6

Database Tools 20 Connect a Database Tool to a Database 22 Other Programming Languages 24 Connect Python to a Database 25 Connect R to a Database 31 Chapter 3: The SQL Language 37 Comparison to Other Languages 37 ANSI Standards 39 SQL Terms 41 Keywords and Functions 42 Identifiers and Aliases 43 Statements and Clauses 45 Expressions and Predicates 47 Comments, Quotes, and Whitespace 48 Sublanguages 50 Chapter 4: Querying Basics 53 The SELECT Clause 55 Aliasing Columns 57 Qualifying Columns 59 Selecting Subqueries 61 DISTINCT 63 The FROM Clause 66 From Multiple Tables 66 From Subqueries 69 The WHERE Clause 73 Filtering on Subqueries 75 The GROUP BY Clause 78 The HAVING Clause 83 The ORDER BY Clause 85 iv | Table of Contents

📄 Page 7

The LIMIT Clause 88 Chapter 5: Creating, Updating, and Deleting 91 Databases 91 Display Names of Existing Databases 93 Display Name of Current Database 94 Switch to Another Database 95 Create a Database 95 Delete a Database 96 Creating Tables 97 Create a Simple Table 98 Display Names of Existing Tables 100 Create a Table That Does Not Already Exist 100 Create a Table with Constraints 101 Create a Table with Primary and Foreign Keys 105 Create a Table with an Automatically Generated Field 108 Insert the Results of a Query into a Table 110 Insert Data from a Text File into a Table 112 Modifying Tables 115 Rename a Table or Column 115 Display, Add, and Delete Columns 117 Display, Add, and Delete Rows 119 Display, Add, Modify, and Delete Constraints 120 Update a Column of Data 124 Update Rows of Data 125 Update Rows of Data with the Results of a Query 126 Delete a Table 128 Indexes 129 Create an Index to Speed Up Queries 131 Views 133 Create a View to Save the Results of a Query 135 Table of Contents | v

📄 Page 8

Transaction Management 138 Double-Check Changes Before a COMMIT 139 Undo Changes with a ROLLBACK 141 Chapter 6: Data Types 143 How to Choose a Data Type 145 Numeric Data 147 Numeric Values 147 Integer Data Types 148 Decimal Data Types 150 Floating Point Data Types 151 String Data 154 String Values 154 Character Data Types 156 Unicode Data Types 159 Datetime Data 161 Datetime Values 161 Datetime Data Types 165 Other Data 172 Boolean Data 172 External Files (Images, Documents, etc.) 173 Chapter 7: Operators and Functions 179 Operators 180 Logical Operators 181 Comparison Operators 182 Math Operators 189 Aggregate Functions 191 Numeric Functions 193 Apply Math Functions 194 Generate Random Numbers 196 vi | Table of Contents

📄 Page 9

Round and Truncate Numbers 197 Convert Data to a Numeric Data Type 198 String Functions 199 Find the Length of a String 199 Change the Case of a String 200 Trim Unwanted Characters Around a String 201 Concatenate Strings 203 Search for Text in a String 203 Extract a Portion of a String 206 Replace Text in a String 207 Delete Text from a String 208 Use Regular Expressions 209 Convert Data to a String Data Type 217 Datetime Functions 218 Return the Current Date or Time 218 Add or Subtract a Date or Time Interval 220 Find the Difference Between Two Dates or Times 221 Extract a Part of a Date or Time 226 Determine the Day of the Week of a Date 228 Round a Date to the Nearest Time Unit 229 Convert a String to a Datetime Data Type 230 Null Functions 234 Return an Alternative Value if There Is a Null Value 235 Chapter 8: Advanced Querying Concepts 237 Case Statements 238 Display Values Based on If-Then Logic for a Single Column 239 Display Values Based on If-Then Logic for Multiple Columns 240 Grouping and Summarizing 242 Table of Contents | vii

📄 Page 10

GROUP BY Basics 242 Aggregate Rows into a Single Value or List 245 ROLLUP, CUBE, and GROUPING SETS 247 Window Functions 250 Rank the Rows in a Table 252 Return the First Value in Each Group 255 Return the Second Value in Each Group 256 Return the First Two Values in Each Group 257 Return the Prior Row Value 258 Calculate the Moving Average 259 Calculate the Running Total 261 Pivoting and Unpivoting 263 Break Up the Values of a Column into Multiple Columns 263 List the Values of Multiple Columns in a Single Column 265 Chapter 9: Working with Multiple Tables and Queries 269 Joining Tables 270 Join Basics and INNER JOIN 274 LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN 277 USING and NATURAL JOIN 279 CROSS JOIN and Self Join 281 Union Operators 284 UNION 285 EXCEPT and INTERSECT 289 Common Table Expressions 291 CTEs Versus Subqueries 293 Recursive CTEs 295 viii | Table of Contents

📄 Page 11

Chapter 10: How Do I…? 303 Find the Rows Containing Duplicate Values 303 Select Rows with the Max Value for Another Column 306 Concatenate Text from Multiple Fields into a Single Field 308 Find All Tables Containing a Specific Column Name 311 Update a Table Where the ID Matches Another Table 313 Index 317 Table of Contents | ix

📄 Page 12

(This page has no text content)

📄 Page 13

Preface Why SQL? Since the last edition of SQL Pocket Guide was published, a lot has changed in the data world. The amount of data generated and collected has exploded, and a number of tools and jobs have been created to handle the influx of data. Through all of the changes, SQL has remained an integral part of the data landscape. Over the past 15 years, I have worked as an engineer, consul‐ tant, analyst, and data scientist, and I have used SQL in every one of my roles. Even if my main responsibilities were focused on another tool or skill, I had to know SQL in order to access data at a company. If there was a programming language award for best supporting actor, SQL would take home the prize. As new technologies emerge, SQL is still top of mind when it comes to working with data. Cloud-based storage solutions like Amazon Redshift and Google BigQuery require users to write SQL queries to pull data. Distributed data processing frame‐ works like Hadoop and Spark have sidekicks Hive and Spark SQL, respectively, which provide SQL-like interfaces for users to analyze data. xi

📄 Page 14

SQL has been around for almost five decades, and it is not going away anytime soon. It is one of the oldest programming languages still being used widely today, and I am excited to share the latest and greatest with you in this book. Goals of This Book There are many existing SQL books out there, ranging from ones that teach beginners how to code in SQL to detailed tech‐ nical specifications for database administrators. This book is not intended to cover all SQL concepts in depth, but rather to be a simple reference for when: • You’ve forgotten some SQL syntax and need to look it up quickly • You’ve come across a slightly different set of database tools at a new job and need to look up the nuanced differences • You’ve been focusing on another coding language for a while and need a quick refresher on how SQL works If SQL plays a large supporting role in your job, then this is the perfect pocket guide for you. Updates to the Fourth Edition The third edition of the SQL Pocket Guide by Jonathan Gennick was published in 2010, and it was well received by readers. I’ve made the following updates to the fourth edition: • The syntax has been updated for Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL. IBM’s Db2 has been removed due to its decrease in popularity, and SQLite has been added due to its increase in popularity. • The third edition of this book was organized alphabeti‐ cally. I’ve rearranged the sections in the fourth edition so that similar concepts are grouped together. There is still an xii | Preface

📄 Page 15

index at the end of this book that lists concepts alphabetically. • Due to the number of data analysts and data scientists who are now using SQL in their jobs, I’ve added sections on how to use SQL with Python and R (popular open source programming languages), as well as a SQL crash course for those who need a quick refresher. Frequently Asked (SQL) Questions The last chapter of this book is called "How Do I…?" and it includes frequently asked questions by SQL beginners or those who haven’t used SQL in a while. It’s a good place to start if you don’t remember the exact key‐ word or concept that you’re looking for. Example questions include: • How do I find the rows containing duplicate values? • How do I select rows with the max value for another column? • How do I concatenate text from multiple fields into a single field? Navigating This Book This book is organized into three sections. I. Basic Concepts • Chapters 1 through 3 introduce basic keywords, concepts, and tools for writing SQL code. • Chapter 4 breaks down each clause of a SQL query. Preface | xiii

📄 Page 16

II. Database Objects, Data Types, and Functions • Chapter 5 lists common ways to create and modify objects within a database. • Chapter 6 lists common data types that are used in SQL. • Chapter 7 lists common operators and functions in SQL. III. Advanced Concepts • Chapters 8 and 9 explain advanced querying concepts including joins, case statements, window functions, etc. • Chapter 10 walks through solutions to some of the most commonly searched for SQL questions. 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 elements such as variable or function names, databases, data types, environment variables, state‐ ments, and keywords. Constant width bold Shows commands or other text that should be typed liter‐ ally by the user, or values determined by context. xiv | Preface

📄 Page 17

TIP This element signifies a tip or suggestion. NOTE This element signifies a general note. WARNING This element indicates a warning or caution. Using Code Examples If you have a technical question or a problem using the code examples, please send email to bookquestions@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incor‐ porating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “SQL Pocket Guide, 4th ed. by Alice Zhao (O’Reilly). Copyright 2021 Alice Zhao, 978-1-492-209040-3.” Preface | xv

📄 Page 18

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permis‐ sions@oreilly.com. 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 col‐ lection of text and video from O’Reilly and 200+ other publish‐ ers. For more information, 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, exam‐ ples, and any additional information. You can access this page at https://oreil.ly/jreAj. Email bookquestions@oreilly.com to comment or ask technical questions about this book. For news and information about our books and courses, visit http://oreilly.com. xvi | Preface

📄 Page 19

Find us on Facebook: http://facebook.com/oreilly. Follow us on Twitter: http://twitter.com/oreillymedia. Watch us on YouTube: http://youtube.com/oreillymedia. Acknowledgments Thank you to Jonathan Gennick for creating this pocket guide from scratch and writing the first three editions, and to Andy Kwan for trusting me to continue on with the publication. I couldn’t have completed this book without the help of my edi‐ tors Amelia Blevins, Jeff Bleiel, and Caitlin Ghegan, and my technical reviewers Alicia Nevels, Joan Wang, Scott Haines, and Thomas Nield. I truly appreciate the time you’ve spent reading each page of this book. Your feedback has been invaluable. To my parents—thank you for fostering my love for learning and creating. To my kids Henry and Lily—your excitement for this book warms my heart. Finally, to my husband, Ali—thank you for all of your notes on this book, for your encouragement, and for being my biggest fan. Preface | xvii

📄 Page 20

(This page has no text content)