Leveling Up with SQL Advanced Techniques for Transforming Data into Insights (Mark Simon) (Z-Library)

Page 1

Leveling Up with SQL Advanced Techniques for Transforming Data into Insights — Mark Simon

Page 2

Leveling Up with SQL: Advanced Techniques for Transforming Data into Insights ISBN-13 (pbk): 978-1-4842-9684-4 ISBN-13 (electronic): 978-1-4842-9685-1 https://doi.org/10.1007/978-1-4842-9685-1 Copyright © 2023 by Mark Simon This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Smriti Srivastava Development Editor: Laura Berendson Editorial Assistant: Mark Powers Cover designed by eStudioCalamar Cover image by Jigar Panchal on Unsplash (www.unsplash.com) Distributed to the book trade worldwide by Springer Science+Business Media New York, 1 New York Plaza, Suite 4600, New York, NY 10004-1562, USA. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@ springer-sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub (github.com/apress). For more detailed information, please visit https://www.apress. com/gp/services/source-code. Paper in this product is recyclable Mark Simon Ivanhoe VIC, VIC, Australia

Page 3

To Brian. You’re part of what I am today.

Page 4

(This page has no text content)

Page 5

(This page has no text content)

Page 6

(This page has no text content)

Page 7

(This page has no text content)

Page 8

(This page has no text content)

Page 9

(This page has no text content)

Page 10

(This page has no text content)

Page 11

(This page has no text content)

Page 12

xiii About the Author Mark Simon has been involved in training and education since the beginning of his career. He started as a teacher of mathematics, but quickly pivoted into IT consultancy and training because computers are much easier to work with than high school students. He has worked with and trained in several programming and coding languages and currently focuses mainly on web development and database languages. When not involved in work, you will generally find him listening to or playing music, reading, or just wandering about.

Page 13

xv About the Technical Reviewer Aaditya Pokkunuri is an experienced senior cloud database engineer with a demonstrated history of working in the information technology and services industry with 13 years of experience. He is skilled in performance tuning, MS SQL Database Server Administration, SSIS, SSRS, PowerBI, and SQL development. He possesses in-depth knowledge of replication, clustering, SQL Server high availability options, and ITIL processes. His expertise lies in Windows administration tasks, Active Directory, and Microsoft Azure technologies. He also has extensive knowledge of MySQL, MariaDB, and MySQL Aurora database engines. He has expertise in AWS Cloud and is an AWS Solution Architect Associate and AWS Database Specialty. Aaditya is a strong information technology professional with a Bachelor of Technology in Computer Science and Engineering from Sastra University, Tamil Nadu.

Page 14

xvii Acknowledgments The sample data includes information about books and authors from Goodreads (www.goodreads.com/), particularly from their lists of classical literature over the past centuries. Additional author information was obtained, of course, from Wikipedia (www.wikipedia.org/). The author makes no guarantees about whether the information was correct or even copied correctly. Certainly, the list of books should not in any way be interpreted as an endorsement or even an indication of personal taste. After all, it’s just sample data.

Page 15

xix Introduction In the early 1970s, a new design for managing databases was being developed based on the original work of E. F. Codd. The underlying model was known as the relational model and described a way of collecting data and accessing and manipulating data using mathematical principles. Over the decade, the SQL language was developed, and, though it doesn’t follow the relational model completely, it attempts to make the database accessible using a simple language. The SQL language has been improved, enhanced, and further developed over the years, and in the late 1980s, the language was developed into a standard of both ANSI (the American National Standards Institute) and ISO (the International Organization for Standardization, and, that’s right, it doesn’t spell ISO). The takeaways from this very brief history are • SQL has been around for some time. • SQL is based on some solid mathematical principles. • There is an official standard, even if nobody quite sticks to it. • SQL is a developing language, and there are new features and new techniques being added all the time. The second half of the third point is worth stressing. Nobody quite sticks to the SQL standards. There are many reasons for this, some good, some bad. But you’ll probably find that the various dialects of SQL are about 80–90% compatible, and the rest we’ll fill you in on as we go. In this book, you’ll learn about using SQL to a level which goes beyond the basics. Some things you’ll learn about are newer features in SQL; some are older features that you may not have known about. We’ll look at a few non-standard features, and we’ll also look at using features that you already know about, but in more powerful ways. This book is not for the raw beginner—we assume you have some knowledge and experience in SQL. If you are a raw beginner, then you will get more from my previous

Page 16

xx book, Getting Started with SQL and Databases;1 you can then return to this book full of confidence and enthusiasm with a good solid grounding in SQL. If you have the knowledge and experience, the first chapter will give you a quick overview of the sort of knowledge you should have. The Sample Database To work through the exercises, you’ll need the following: • A database server and a suitable database client. • Permissions to do anything you like on the database. If you’ve installed the software locally, you probably have all the permissions you need, but if you’re doing this on somebody else’s system, you need to check. • The script which produces the sample database. The first chapter will go into the details of getting your DBMS software and sample database ready. It will also give you an overview of the story behind the sample database. Notes While you’re writing SQL to work with the data, there’s a piece of software at the other end responding to the SQL. That software is referred to generically as a database server, and, more specifically, as a DataBase Management System, or DBMS to its friends. We’ll be using that term throughout the book. The DBMSs we’ll be covering are PostgreSQL, MariaDB, MySQL, Microsoft SQL Server, SQLite, and Oracle. We’ll assume that you’re working with reasonably current versions of the DBMSs. Chapter 1 will go into more details on setting up your DBMS, as well as downloading and installing the sample database. Source Code All source code used in this book can be downloaded from github.com/apress/ leveling-up-sql. 1 https://link.springer.com/book/978148429494. InTroduCTIon

Page 17

1 CHAPTER 1 Getting Ready If you’re reading this book, you’ll already know some SQL, either through previous study or through bitter experience, or, more likely, a little of both. In the process, there may be a few bits that you’ve missed, or forgotten, or couldn’t see the point. We’ll assume that you’re comfortable enough with SQL to get the basic things done, which mostly involves fetching data from one or more tables. You may even have manipulated some of that data or even the tables themselves. We won’t assume that you consider yourself an expert in all of this. Have a look in the section “What You Probably Know Already” to check the sort of experience we think you already have. If there are some areas you’re not completely sure about, don’t panic. Each chapter will include some of the background concepts which should take you to the next level. If all of this is a bit new to you, perhaps we can recommend an introductory book. It’s called Getting Started with SQL and Databases by Mark Simon, and you can learn more about it at https://link.springer.com/book/10.1007/978-1-4842-9493-2. About the Sample Database For the sample database, we’re going to suppose that we’re running an online bookshop: BookWorks. In this scenario • Customers visit the website. • At some point, customers will have registered with their details. • They then add one or more copies of one or more books to a shopping cart. • Hopefully, they then check out and pay. • BookWorks will then procure the books and ship them to customers at some point. © Mark Simon 2023 M. Simon, Leveling Up with SQL, https://doi.org/10.1007/978-1-4842-9685-1_1

Page 18

2 To manage all of this, the database tables look something like Figure 1-1. Figure 1-1. The BookWorks Schema In real life, there’s more to the story. For example, we haven’t included payment or shipping methods, and we haven’t included login credentials. There’s no stock either, although we’ll presume that the books are ordered on demand. But there’s enough in this database for us to work with as we develop and improve our SQL skills. Chapter 1 GettinG ready

Page 19

3 Setting Up You can sit in a comfortable chair with a glass of your favorite refreshment and a box of nice chocolates and read this book from cover to cover. However, you’ll get more from this book if you join in on the samples. Database Management Software First, you’ll need access to Database Management Software (DBMS). The five and a half DBMSs we work with in the book are • PostgreSQL • MariaDB/MySQL • Microsoft SQL Server • SQLite • Oracle PostgreSQL, MariaDB/MySQL, and SQLite are all free. Microsoft SQL Server and Oracle are paid products, but have free versions. MariaDB is a spin-off of MySQL, which is why they’re treated together. They are almost identical in features, but you’ll find a few places where they’re not identical. if you’re using MariadB/MySQL, we’re going to assume that you’re running it in ANSI mode. it’s easily done if you start the session with SET SESSION sql_mode = 'ANSI'; you’ll probably see this message a few times throughout the book. the appendix will tell you why. Chapter 1 GettinG ready

Page 20

4 It’s possible—even likely—that you already have the DBMS installed. Just make sure that • It’s a fairly recent version. Some of the features you’ll learn about aren’t available in some older versions of some DBMSs. In particular, watch out for MySQL: you’ll need version 8 which was released in 2018 for some of the more sophisticated features. • You have enough privileges to create a database and to create and modify tables. Most of the book won’t require that, but Chapter 2 definitely will. At the very least, you’ll need to be able to install the sample database. If you can’t make changes to the database, you can still work with most of the book, and you’ll just have to nod your head politely as you’re reading Chapter 2, in which we make a few changes to the database. You might also have some difficulty in creating views, which we cover in Chapter 6 and in other chapters. Database Client You’ll also need a database client. All the major DBMS vendors have their own free client, and there are plenty of free and paid third-party alternatives. The Sample Database And, of course, you’ll need to install the sample database. The sample database and additional code files for this book are available on GitHub via the book’s product page, located at www.apress.com/ISBN. You can also directly download a script by visiting www.sample-db.net/ and clicking a few buttons. You’ll need to do the following: Chapter 1 GettinG ready