Learn SQL in a Month of Lunches (Jeff Iannucci) (Z-Library)

M A N N I N G JEFF IANNUCCI LEARN SQL IN A MONTH OF LUNCHES • Manipulating data • Using functions • Creating constrains • Making decisions • Using cursors • Controlling transactions • Storing data in tables • Reusing queries • Logical operators • Grouping data • Using variables • Converting data • Wildcards and null values • Querying multiple tables • Filtering and sorting • Set operators

Review of WHERE clause comparison operators Operator Description = Equality <> Inequality != Inequality* < Less than > Greater than !< Not less than* !> Not greater than* <= Less than or equal to >= Greater than or equal to BETWEEN Between two values, including those values IN Equality to a list of multiple values NOT IN Inequality to a list of multiple values NOT Inequality to stated condition *May not be supported by every RDBMS

MANN I NG Shelter ISland Learn SQL in a Month of Lunches Jeff Iannucci

For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on this book when ordered in quantity. For more information, please contact Special Sales Department Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964 Email: orders@manning.com © 2025 Manning Publications Co. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps. Recognizing the importance of preserving what has been written, it is Manning’s policy to have the books we publish printed on acid- free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine. ∞ Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964 ISBN 9781633438576 Printed in the United States of America The author and publisher have made every effort to ensure that the information in this book was correct at press time. The author and publisher do not assume and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause, or from any usage of the information herein. Development editor: Becky Whitney Technical editor: Mike Shepard Review editor: Kishor Rit Production editor: Deirdre Blanchfield-Hiam Copy editor: Keir Simpson Proofreader: Melody Dolab Technical proofreader: Trevoir Williams Typesetter: Tamara ŠveliÊ SabljiÊ Cover designer: Monica Kamswaag

For you, dear reader. May this book help you on your journey through our data-driven world.

iv brief contents 1 ■ Before you begin 1 2 ■ Your first SQL query 6 3 ■ Querying data 18 4 ■ Sorting, skipping, and commenting data 28 5 ■ Filtering on specific values 39 6 ■ Filtering with multiple values, ranges, and exclusions 50 7 ■ Filtering with wildcards and null values 60 8 ■ Querying multiple tables 71 9 ■ Using different kinds of joins 85 10 ■ Combining queries with set operators 96 11 ■ Using subqueries and logical operators 108 12 ■ Grouping data 119 13 ■ Using variables 131 14 ■ Querying with functions 143 15 ■ Combining or calculating values with functions 153 16 ■ Inserting data 164 17 ■ Updating and deleting data 176 18 ■ Storing data in tables 188 19 ■ Creating constraints and indexes 204 20 ■ Reusing queries with views and stored procedures 217 21 ■ Making decisions in queries 230

vbrief contents 22 ■ Using cursors 244 23 ■ Using someone else’s script 259 24 ■ Never the end 271

vi contents preface xv acknowledgments xvii about this book xix about the author xxii 1 Before you begin 1 1.1 Why SQL matters 1 1.2 Is this book for you? 2 The many uses for SQL 2  ■  The many flavors of SQL 3 A word about AI and SQL 3 1.3 How to use this book 3 The main chapters 4  ■  Hands-on labs 4  ■  Further exploration 4 1.4 Setting up your lab environment 4 Installing MySQL and MySQL Workbench 4  ■  Executing the lab scripts 5 1.5 Online resources 5 1.6 Being immediately effective with SQL 5

viicontents 2 Your first SQL query 6 2.1 You know tables if you already know spreadsheets 6 2.2 Learning SQL is like taking an English class 9 2.3 Writing your first SQL query 10 2.4 Key terms and keywords 15 2.5 Lab 16 2.6 Lab answers 17 3 Querying data 18 3.1 Rules for the SELECT statement 18 SELECT requirements 19  ■  Keywords and reserved words 19 Case insensitivity 20  ■  Formatting and whitespace 20 3.2 Retrieving data from a table 21 Retrieving an individual column 22  ■  Retrieving multiple columns 23  ■  Renaming output columns using aliases 24 Retrieving all columns 25 3.3 Lab 26 3.4 Lab answers 27 4 Sorting, skipping, and commenting data 28 4.1 Sorting data 28 Sorting by one column 29  ■  Sorting by multiple columns 30 Specifying sort direction 31  ■  Sorting by hidden columns 31 Sorting by position 32 4.2 Skipping data 33 Using LIMIT to reduce results 33  ■  Using OFFSET to select a different limited set 34  ■  Limiting data in another RDBMS 35 4.3 Commenting data 35 4.4 Lab 37 4.5 Lab answers 37 5 Filtering on specific values 39 5.1 Filtering on a single condition 39 Filtering on numeric values 40  ■  Filtering on string values 41 Filtering on date values 42

viii contents 5.2 Filtering on multiple conditions 43 Filtering that requires all conditions 43  ■  Filtering that requires any one of many conditions 44  ■  Controlling the order of multiple filters 46  ■  Filtering and using ORDER BY 47 5.3 Lab 48 5.4 Lab answers 49 6 Filtering with multiple values, ranges, and exclusions 50 6.1 Filtering on specific values 50 6.2 Filtering on a range of values 52 Filtering on an open-ended range 52  ■  Filtering a defined range 53 6.3 Negating filter conditions 55 Negating a specific value 55  ■  Negating any filter condition 56 6.4 Combining types of filter conditions 57 6.5 Reviewing comparison operators 58 6.6 Lab 59 6.7 Lab answers 59 7 Filtering with wildcards and null values 60 7.1 Filtering with wildcards 60 Filtering with the percent sign 61  ■  Filtering with an underscore 63 7.2 Filtering with null values 64 How not to search for null values 65  ■  How to search for null values correctly 66  ■  How to search for values that are not null 67 7.3 Lab 68 7.4 Lab answers 68 8 Querying multiple tables 71 8.1 The rules of data relationships 71 Data without relationships 72  ■  Data with relationships 73 8.2 The way to join data 76 Joining two tables 76  ■  Joining more tables 78

ixcontents 8.3 Table aliases 80 8.4 The other way to join data 81 8.5 Lab 82 8.6 Lab answers 83 9 Using different kinds of joins 85 9.1 Inner joins 85 9.2 Outer joins 87 Left outer joins 87  ■  Right outer joins 89  ■  Using outer joins to find rows without matching values 90  ■  Interchanging left and right joins 91  ■  The USING keyword 92  ■  Natural joins 92 9.3 Cross joins 93 9.4 Lab 94 9.5 Lab answers 94 10 Combining queries with set operators 96 10.1 Using set operators 96 10.2 UNION 97 10.3 UNION ALL 100 10.4 Emulating FULL OUTER JOIN in MySQL 101 10.5 INTERSECT 103 10.6 EXCEPT 104 10.7 Lab 105 10.8 Lab answers 106 11 Using subqueries and logical operators 108 11.1 A simple subquery 108 11.2 Logical operators and subqueries 110 The ANY and IN operators 111  ■  The ALL and NOT IN operators 112  ■  The EXISTS and NOT EXISTS operators 114 11.3 Subqueries in other parts of a query 115 Subqueries in the FROM clause 115  ■  Subqueries in the SELECT clause 116 11.4 Lab 117 11.5 Lab answers 117

x contents 12 Grouping data 119 12.1 Aggregate functions 119 The SUM function 120  ■  The COUNT function 120 The MIN function 121  ■  The MAX function 122 The AVG function 122  ■  Filtering and aggregating combined values 122 12.2 Aggregating data with GROUP BY 123 GROUP BY requirements 123  ■  GROUP BY and null values 124 12.3 Filtering with HAVING 125 12.4 Logical query processing 126 12.5 The DISTINCT keyword 127 12.6 Lab 128 12.7 Lab answers 129 13 Using variables 131 13.1 User-defined variables 131 Declaring your first user-defined variable 132  ■  Understanding rules for user-defined variables 133  ■  Using your first user-defined variable 133 13.2 Filtering with variables in FROM and HAVING clauses 134 13.3 Assigning an unknown value to a variable 136 Reviewing how a query works 136  ■  Assigning an unknown variable with SELECT 137  ■  Considering performance with variables 138  ■  Troubleshooting considerations with variables 139 13.4 Other notes about variables 140 Assigning a literal value using SELECT 140  ■  Assigning a value of NULL to a variable 140  ■  Changing the type of data used by a variable 141 13.5 Lab 141 13.6 Lab answers 142 14 Querying with functions 143 14.1 The problems with functions 143 Function commands vary for each RDBMS 143  ■  Function commands can be inefficient 144

xicontents 14.2 String functions 144 Case functions 144  ■  Trim functions 145  ■  Other string functions 147 14.3 Date and time functions 147 Date functions that return numeric values 147  ■  Date functions that return string values 148  ■  Other date and time functions 149 14.4 Informational functions 149 Date and time information 150  ■  Connection information 151 14.5 Lab 151 14.6 Lab answers 152 15 Combining or calculating values with functions 153 15.1 Combining string values 153 CONCAT 154  ■  CONCAT_WS 156  ■  COALESCE 157 15.2 Converting values 158 REPLACE 158  ■  CONVERT and CAST 159 15.3 Numeric calculations with functions 160 15.4 Lab 162 15.5 Lab answers 162 16 Inserting data 164 16.1 Inserting specific values 164 Inserting a new row 165  ■  Inserting multiple new rows 167 Inserting a partial row 168  ■  A word of caution about omitting columns 170 16.2 Inserting a row with a query 171 16.3 Inserting a row with variables 173 16.4 Lab 174 16.5 Lab answers 175 17 Updating and deleting data 176 17.1 Updating values 176 Working with data manipulation in real time 176 Requirements for updates 177  ■  Updating values in one or more columns 179  ■  Updating values with a multitable query 180

xii contents 17.2 Deleting rows 183 Deleting one or more rows 183  ■  Deleting a row with a multitable query 184  ■  Deleting all rows in a table 184 17.3 One big tip for data manipulation 185 17.4 Lab 186 17.5 Lab answers 186 18 Storing data in tables 188 18.1 Creating a table 188 Considerations before creating a table 188  ■  Creating a table 190  ■  Adding values to an empty table 190 18.2 Altering a table 191 Adding a column to a table 191  ■  Considerations before adding a column 194 18.3 Primary keys 194 Considerations for primary keys 195  ■  Adding a primary key 195 18.4 Foreign keys and constraints 197 Data diagrams 197  ■  Adding a foreign-key constraint 197 18.5 Deleting a table, column, or constraint 199 Deleting a constraint 199  ■  Deleting a column 200 Deleting a table 200 18.6 Lab 200 18.7 Lab answers 201 19 Creating constraints and indexes 203 19.1 Constraints 203 NOT NULL constraints 204  ■  DEFAULT constraints 206 UNIQUE constraints 207  ■  CHECK constraints 208 19.2 Automatically incrementing values for a column 209 19.3 Indexes 210 Clustered indexes 210  ■  Nonclustered indexes 212 19.4 Lab 214 19.5 Lab answers 215

xiiicontents 20 Reusing queries with views and stored procedures 217 20.1 Views 217 Creating views 218  ■  Filtering with views 219  ■  Joining views 219  ■  Considerations for views 221 20.2 Stored procedures 221 Creating stored procedures 222  ■  Using variables with stored procedures 223  ■  Considerations for stored procedures 226 20.3 Differences between views and stored procedures 227 20.4 Lab 227 20.5 Lab answers 228 21 Making decisions in queries 230 21.1 Conditional functions and expressions 230 COALESCE function 230  ■  IFNULL function 231 CASE expression 233 21.2 Decision structures 235 IF and THEN 236  ■  ELSE 239  ■  Multiple conditions 240 21.3 Lab 242 21.4 Lab answers 242 22 Using cursors 244 22.1 Reviewing variables and parameters 244 Variables inside stored procedures 245  ■  Output parameters 245 22.2 Cursors 246 Anatomy of a cursor 246  ■  Creating a cursor 247 22.3 Alternatives to cursors 251 Using WHILE 251  ■  Temporary tables 253 22.4 Considerations for using cursors 255 Thinking in sets 255  ■  Thinking about cursor use 256 22.5 Lab 256 22.6 Lab answers 256

xiv contents 23 Using someone else’s script 259 23.1 Someone else’s script: Creating a table 260 The CREATE TABLE script 260  ■  Reviewing the CREATE TABLE script 260  ■  Improving the CREATE TABLE script 261 23.2 Someone else’s script: Inserting data 262 The INSERT stored procedure 262  ■  Reviewing the INSERT stored procedure 263  ■  Improving the INSERT stored procedure 267  ■  Improving the INSERT stored procedure even more 269 24 Never the end 271 24.1 More SQL 271 24.2 Other SQL resources 272 24.3 Farewell 273 index 274

xv preface My experience with the SQL language started in the late 20th century when I first worked with relational databases. I wasn’t a software developer, and my programming skills were limited mostly to typing a few DOS commands on a home computer. Yet even though I was thrust into learning how to use SQL to perform my job, I quickly found how easy it was to write and execute scripts in this intuitive programming language. In the years since, I’ve shared my knowledge of SQL with hundreds of colleagues. What I’ve found interesting is that most of these colleagues weren’t software developers either; they were folks in other departments, such as finance, marketing, or sales, who needed to use SQL to directly access data that was vital to the organization. They didn’t have time to learn concepts like third normal form and tuples. They just needed to learn a few basic commands to get started. If you think about it, we learn a lot of things in life this way. Most of us didn’t learn how to build a car before we learned how to drive. We didn’t go to culinary school before we started cooking meals. We didn’t learn how hard drives stored data and how processors managed CPU threads before we started using computers. Rather, we learned a few necessary concepts and methods to get going and continued learning more as we progressed. This is the main reason why I was excited to write this book. As someone who has read several books in the Month of Lunches series, I appreciate the fact that one of the main goals is to help the reader become “immediately effective” with the subject. Like others in the series, this book was designed and written to help you start using SQL as quickly as possible. I hope that before you reach the end of chapter 2, you’ll already be excited about writing queries in SQL, learning more about the language, and using it with almost any brand of relational database.

xvi preface Although no single book can cover every concept in the SQL language, I believe that by the end of this book, you’ll be able to query and manipulate data successfully for nearly any task required of you. That said, finishing this book should by no means be the end of your journey. I encourage you not only to seek out other books, blog posts, and videos to expand your knowledge of SQL, but also to attend regional events and local user groups to learn from others who use this popular language. For now, though, enjoy the book. Take heart in the fact that a world full of data will soon be available to you!

xvii acknowledgments The process of creating this book was the most unexpected adventure of my life. I’d never considered writing a tech book, yet from the moment I was contacted by the folks at Manning Publications about this opportunity, I was excited to be encouraged by so many people. This book has my name on the front, but many others helped get it ready for you. First, I’m eternally grateful to my development editors, Karen Miller and Becky Whit- ney, who patiently helped me create this book, as well as Mike Shepard for his outstand- ing technical reviews and recommendations. Mike is a solutions architect at Jack Henry and Associates who studied math and computer science at Missouri State University. In his 27-year IT career, he has been a developer, a DBA, a sysadmin, and a solutions archi- tect. He specializes in process improvement with PowerShell and SQL Server. Many oth- ers behind the scenes helped immensely with organization and promotion, including Mike Stephens, Eleonor Gardner, Malena Selic, Aria Ducic, Paul Spratley, Matko Hrva- tin, Adriano Sabo, Ana Romac, Susan Honeywell, and Stjepan Jurekovic. Thank you also to my project editor, Deirdre Blanchfield-Hiam; my copyeditor, Keir Simpson; and my proofer, Melody Dolab. I’m blessed to have worked with such an awesome group of folks! Many thanks also to my friend Mike Walsh and all my colleagues at Straight Path Solutions for their support and encouragement while I was working on this book, as well as the wisdom of those who have mentored me over the years, including Martin Grant, Curt Johnson, Chris Rose, Chris Hinson, Andy Yun, Ginger Grant, Buck Woody, Grant Fritchey, and Kevin Kline. Thanks to all the reviewers: Ali Shakiba, Andres Sacco, Cristian Antonioli, Dave Corun, Eder Andres Avila Niño, Foster Haines, Giampiero Granatella, Grant Colley, Greg Grimes, Harlan Brewer, Helen Mary Barrameda, Iyabo Sindiku, Jane Noesgaard

xviii acknowledgments Larsen, José Alberto Reyes Quevedo, Malisa Middlebrooks, Mary Anne Thygesen, Mat- thias Lein, Mike Baran, Oliver Korten, Paolo Brunasti, Paul Love, Peter Schott, Rajesh- kumar Muthaiah, Ravichandran Raja, Rohini Uppuluri, Simon Tschoeke, Sleiman Salameh, Steven Joseph Herrera Corrales, and Sveta Natu. Your suggestions helped make this book better. Finally, I can’t possibly thank my beautiful wife, Amy, enough. She not only helped with most of the formatting but also served as my “pre-editor,” checking each chapter before I submitted it. She never complained as I locked myself in my office to write chapters, and she devoted countless hours to reviewing my messy drafts. There’s no way that this book would have been finished without her. Thank you, Amy. I love you always.