Getting Started with SQL A Hands-On Approach for Beginners (Thomas Nield) (Z-Library)

Thomas Nield Getting Started with SQL A HANDS-ON APPROACH FOR BEGINNERS

Thomas Nield Boston Getting Started with SQL A Hands-on Approach for Beginners

978-1-491-93861-4 [LSI] Getting Started with SQL by Thomas Nield Copyright © 2016 Thomas Nield. 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://safaribooksonline.com). For more information, contact our corporate/ institutional sales department: 800-998-9938 or corporate@oreilly.com. Editor: Shannon Cutt Production Editor: Shiny Kalapurakkel Copyeditor: Jasmine Kwityn Proofreader: Rachel Head Indexer: Ellen Troutman-Zaig Interior Designer: David Futato Cover Designer: Randy Comer Illustrator: Rebecca Demarest February 2016: First Edition Revision History for the First Edition 2016-02-08: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781491938614 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Getting Started with SQL, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. 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.

Table of Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix 1. Why Learn SQL?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 What Is SQL and Why Is It Marketable? 1 Who Is SQL For? 2 2. Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 What Is a Database? 3 Exploring Relational Databases 3 Why Separate Tables? 4 Choosing a Database Solution 5 Lightweight Databases 5 Centralized Databases 6 3. SQLite. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 What Is SQLite? 9 SQLiteStudio 9 Importing and Navigating Databases 10 4. SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Retrieving Data with SQL 19 Expressions in SELECT Statements 23 Text Concatenation 27 Summary 28 iii

5. WHERE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Filtering Records 29 Using WHERE on Numbers 30 AND, OR, and IN Statements 31 Using WHERE on Text 32 Using WHERE on Booleans 34 Handling NULL 34 Grouping Conditions 36 Summary 37 6. GROUP BY and ORDER BY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Grouping Records 39 Ordering Records 41 Aggregate Functions 42 The HAVING Statement 45 Getting Distinct Records 46 Summary 46 7. CASE Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 The CASE Statement 47 Grouping CASE Statements 48 The “Zero/Null” CASE Trick 49 Summary 52 8. JOIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Stitching Tables Together 53 INNER JOIN 55 LEFT JOIN 58 Other JOIN Types 61 Joining Multiple Tables 61 Grouping JOINs 63 Summary 66 9. Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Planning a Database 67 The SurgeTech Conference 69 ATTENDEE 69 COMPANY 69 PRESENTATION 70 ROOM 70 PRESENTATION_ATTENDANCE 70 Primary and Foreign Keys 70 iv | Table of Contents

The Schema 71 Creating a New Database 73 CREATE TABLE 76 Setting the Foreign Keys 84 Creating Views 86 Summary 89 10. Managing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 INSERT 91 Multiple INSERTs 93 Testing the Foreign Keys 93 DELETE 94 TRUNCATE TABLE 94 UPDATE 95 DROP TABLE 95 Summary 95 11. Going Forward. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 A. Operators and Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 B. Supplementary Topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Table of Contents | v

Foreword Over the past three decades, computers have taken over the world. Twenty-five years ago, we lived analog. We communicated using an analog POTS telephone, we tuned in to analog FM radio stations, and we went to the library and browsed the stacks for information. Buildings were constructed using hand-drawn blueprints; graphic artists worked with pen, brush, and ink; musicians plucked strings and blew into horns and recorded on analog tape; and airplanes were controlled by physical cables connecting the yoke to the control surfaces. But now everything is computerized and digital. Consequently, every member of society needs to be familiar with computers. That does not mean having the deep knowledge of a techie, but just as poets need to study a little math and physics, and just as mathematicians need to read a little poetry, so too does everybody today need to know something about computers. I think that this book really helps to address the knowledge gap between techies and laypeople, by providing an accessible and easy-to-read discussion of SQL—a core database technology. —Richard Hipp, Creator of SQLite vii

Preface Nobody needs to learn how a car engine works in order to drive a car. The whole point of technologies like SQL is to allow you to focus on the business problem, and not worry about how the technical details are executed. This book will give you a practical focus on using SQL, and will steer away from unnecessary technical details that are likely not pertinent to your immediate needs. Much of the content revolves around hands-on exercises with real databases you can download so you see how concepts are applied. When you finish this book you will have practical knowledge to work with databases, as well as use them to overcome your business challenges. How to Use This Book This book is designed to teach the fundamentals of SQL and working with databases. Readers who have experience using Excel spreadsheets should find this material accessible but still challenging. Individuals who have not worked with Excel may be more challenged. It is helpful to be familiar with concepts used in Excel, such as rows, columns, tables, mathematical expressions (e.g., Excel formulas), and aggregate calcu‐ lations (e.g., SUM, AVG, MIN, MAX, COUNT). These concepts will still be taught here, but some practical Excel experience will help expedite understanding. Basic computer literacy is required, and readers should know how to navigate folders and copy/paste files, as well as download and save files from the Web. As you go through the material, have a computer on hand to practice the examples. While some people can learn by just reading, it is best to practice the material at some point to reinforce the knowledge. Proficiency comes through repeated use and practice. In your job, it is likely that you will use some SQL functionalities heavily and others not as much. That is OK. It is more important to become proficient in what your job requires, and consult this book (or Google) as a reference when you need answers about an unfamiliar topic. ix

When working with technology, you are never expected to know everything. As a matter of fact, technology topics are so vast in number it would be impossible. So it is helpful to develop a degree of tunnel vision and learn only enough to fulfill the task at hand. Otherwise, you can get overwhelmed or distracted learning irrelevant topics. Hopefully this book will give you a foundation of knowledge, and afterward you can continue to learn about topics that are pertinent to you. You are always welcome to reach out to me at tmnield@outlook.com, and I will answer any questions to the best of my ability. If you have questions about positioning your career with technical skillsets or have a SQL question, I might be able to help. I hope that this material not only augments your skillset and career opportunities, but also sparks new interests that excite you like it did for me. 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. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. This element signifies a general note. Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/thomasnield/oreilly_getting_started_with_sql. 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 x | Preface

the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a signifi‐ cant amount of example code from this book into your product’s documentation does require permission. We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Getting Started with SQL by Thomas Nield (O’Reilly). Copyright 2016 Thomas Nield, 978-1-4919-3861-4.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. Safari® Books Online Safari Books Online is an on-demand digital library that deliv‐ ers expert content in both book and video form from the world’s leading authors in technology and business. Technology professionals, software developers, web designers, and business and crea‐ tive professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training. Safari Books Online offers a range of plans and pricing for enterprise, government, education, and individuals. Members have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kauf‐ mann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and hundreds more. For more information about Safari Books Online, please visit us online. Preface | xi

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 http://bit.ly/getting-started-with-sql. To comment or ask technical questions about this book, send email to bookques‐ tions@oreilly.com. For more information about our books, courses, conferences, and news, see our web‐ site 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 am blessed to have amazing people surrounding me, and I realize how central they have been in my life and everything I do. If it was not for them, this book would probably not have happened. First and foremost, I would like to thank my mom and dad. They have given every‐ thing to secure my future. I know for a fact that I would not have the opportunities I have today if it was not for them. My dad worked hard to provide a better education for my brothers and me, and my mother always pushed me forward, even when I resisted. She taught me to never settle and always struggle through my limits. I cannot express enough gratitude toward my leaders, managers, and colleagues at Southwest Airlines Revenue Management. Justin Jones and Timothy Keeney have a warrior spirit and zeal for innovation that few possess. They truly define the leader‐ ship and spirit of Southwest Airlines, but more importantly they are good guys. They will always be my friends and they’ve made it hard to imagine a life without South‐ west Airlines. xii | Preface

Robert Haun, Brice Taylor, and Allison Russell continuously work to make our team the forefront of innovation and continuously pursue new ideas, and I am blessed to work in the environment they have helped create. I also have to thank Matt Louis for bringing me on board at Revenue Management, and Steven Barsalou who made me realize how little I really knew about SQL. Steven is the first person who came to mind when I needed a reviewer for this book, and I am grateful he came on board this project. Then there is the project team I work with every day: Brian Denholm, Paul Zigler, Bridget Green, Todd Randolph, and Chris Solomon. As a team, the feats we pull off never cease to amaze me. Brian is the kind of project manager that can effectively bridge technology and business jargon together, and he will not hesitate to get his hands dirty with SQL and the occasional code review. I want to give a special thanks to Chris Solomon for helping me with everything I do every day. He not only has a rare talent to absorb high volumes of technical knowledge and maintain it in a busi‐ ness perspective, but he is also a nice guy that I am privileged to be friends with. Chris is always a key player in any project, and I was thrilled when he agreed to review this book. I cannot forget the great people who worked at Southwest Airlines Ground Ops Safety Regulatory Compliance, including Marc Stank, Reuben Miller, Mary Noel Hennes, and everybody else I had the privilege of working with. I interned and con‐ tracted with that department a few years back and some of my fondest memories are there. It was there I discovered my passion for technology, and they provided many opportunities for me to pursue that, whether it was throwing together databases or prototyping an iPad app. When I announced I was publishing this book I did not expect Richard Hipp, the founder and creator of SQLite, to reach out to me. Richard graciously stepped up to be the technical reviewer for this book and it has been a tremendous honor to have him on board. The technology community continues to amaze me, and the fact Richard Hipp joined this project shows how unique and close-knit the community really is. Shannon Cutt has been my editor at O’Reilly for this book. This is my first book and I was uncertain what the publishing experience would be like. But Shannon made pub‐ lishing such a pleasant experience that I am eager to write again. Thanks Shannon, you have been awesome! Last but not least, I want to thank Watermark Church and the volunteers at Careers in Motion for creating the vehicle that made this book happen. I initially wrote this “book” as a public service to help unemployed professionals in the Dallas area. It was at their encouragement that I decided to publish it, and I want to give a special thanks to Martha Garza for her insistence. I have learned remarkable things can happen when you give your time to help others. Preface | xiii

CHAPTER 1 Why Learn SQL? What Is SQL and Why Is It Marketable? It is an obvious statement that the business landscape is shifting rapidly. A lot of this is enabled by technology and the explosion of business data. Companies are investing vast amounts of capital to gather and warehouse data. But what many business lead‐ ers and managers currently struggle with is how to make sense of this data and use it. This is where SQL, which stands for Structured Query Language, comes in. It provides a means to access and manipulate this data in meaningful ways and provide business insights not possible before. Businesses are gathering data at exponential rates, and there is an equally growing need for people who know how to analyze and manage it. Stack Overflow, the most active programming community in the world, performed a comprehensive survey on its members in 2015. Apple coding was the most in-demand technology and had an average salary nearing six figures. But SQL came in in fifth place, with a salary that was not far behind. In recent years, data has suddenly become ubiquitous—yet few people know how to access it meaningfully, which has put SQL talent in high demand. 1

Who Is SQL For? One misperception about SQL is that it is an IT skill and therefore only applicable to technology (not business) professionals. In the world as it exists today, this is hardly the truth. Businesspeople, managers, IT professionals, and engineers can all reap ben‐ efits from learning SQL to better position their careers. SQL can open many career paths because it enables individuals to know their businesses better through the data that is driving them. On the business side, interest in SQL can lead to roles that are analytical, managerial, strategic, and research- or project-based. On the IT front, it can lead to roles in database design, database administration, systems engineering, IT project management, and even software development. 2 | Chapter 1: Why Learn SQL?

CHAPTER 2 Databases What Is a Database? In the broadest definition, a database is anything that collects and organizes data. A spreadsheet holding customer bookings is a database, and so is a plain-text file con‐ taining flight schedule data. Plain-text data itself can be stored in a variety of formats, including XML and CSV. Professionally, however, when one refers to a “database” they likely are referring to a relational database management system (RDBMS). This term may sound technical and intimidating, but an RDBMS is simply a type of database that holds one or more tables that may have relationships to each other. Exploring Relational Databases A table should be a familiar concept. It has columns and rows to store data, much like a spreadsheet. These tables can have relationships to each other, such as an ORDER table that refers to a CUSTOMER table for customer information. For example, suppose we have an ORDER table with a field called CUSTOMER_ID (Figure 2-1). Figure 2-1. An ORDER table with a CUSTOMER_ID 3

We can reasonably expect there to be another table, maybe called CUSTOMER (Figure 2-2), which holds the customer information for each CUSTOMER_ID. Figure 2-2. A CUSTOMER table When we go through the ORDER table, we can use the CUSTOMER_ID to look up the cus‐ tomer information in the CUSTOMER table. This is the fundamental idea behind a “rela‐ tional database,” where tables may have fields that point to information in other tables. This concept may sound familiar if you’ve used VLOOKUP in Excel to retrieve information in one sheet from another sheet in a workbook. Why Separate Tables? But why are these tables separated and designed this way? The motivation is normal‐ ization, which is separating the different types of data into their own tables rather than putting them in one table. If we had all information in a single table, it would be redundant, bloated, and very difficult to maintain. Imagine if we stored customer information in the ORDER table. Figure 2-3 shows what it would look like. Figure 2-3. A table that is not normalized Notice that for the Re-Barre Construction orders someone had to populate the cus‐ tomer information three times for all three orders (the name, region, street address, city, state, and zip). This is very redundant, takes up unnecessary storage space, and is difficult to maintain. Imagine if a customer had an address change and you had to update all the orders to reflect that. This is why it is better to separate CUSTOMERS and ORDERS into two separate tables. If you need to change a customer’s address, you only need to change one record in the CUSTOMER table (Figure 2-4). 4 | Chapter 2: Databases