SQL The Practical Guide (Kerem Koseoglu) (Z-Library)

Rheinwerk Computing Explore more of the Rheinwerk Computing library! The Rheinwerk Computing series off ers new and established professionals comprehen- sive guidance to enrich their skillsets and enhance their career prospects. Our publica- tions are written by the leading experts in their fi elds. Each book is detailed and hands-on to help readers develop essential, practical skills that they can apply to their daily work. Philip Ackermann JavaScript: The Comprehensive Guide 2022, 1292 pages, paperback and e-book www.rheinwerk-computing.com/5554 Jürgen Wolf HTML and CSS: The Comprehensive Guide 2023, 814 pages, paperback and e-book www.rheinwerk-computing.com/5695 Philip Ackermann Full Stack Web Development: The Comprehensive Guide 2023, 740 pages, paperback and e-book www.rheinwerk-computing.com/5704 Christian Wenz, Tobias Hauser PHP and MySQL: The Comprehensive Guide 2025, approx. 1081 pp, paperback and e-book www.rheinwerk-computing.com/6022 Johannes Ernesti, Peter Kaiser Python 3: The Comprehensive Guide 2022, 1036 pages, paperback and e-book www.rheinwerk-computing.com/5566 www.rheinwerk-computing.com

Kerem Koseoglu SQL The Practical Guide

Imprint This e-book is a publication many contributed to, specifically: Editor Rachel Gibson Acquisitions Editor Hareem Shafi Copyeditor Doug McNair Cover Design Graham Geary Photo Credit iStockphoto: 184339322/© t_kimura; 157329060/© enot-poloskun Layout Design Vera Brauner Production E-Book Eric Wyche Typesetting E-Book SatzPro, Germany We hope that you liked this e-book. Please share your feedback with us and read the Service Pages to find out how to contact us. Library o f Congress Cataloging-in-Publication Control Number: 2024057966 ISBN 978-1-4932-2679-5 (print) ISBN 978-1-4932-2680-1 (e-book) ISBN 978-1-4932-2681-8 (print and e-book) 1st edition 2025 © 2025 by: Rheinwerk Publishing, Inc. 2 Heritage Drive, Suite 305 Quincy, MA 02171 USA info@rheinwerk-publishing.com Represented in the E.U. by: Rheinwerk Verlag GmbH Rheinwerkallee 4 53227 Bonn Germany service@rheinwerk-verlag.de

Dedication To my dearest wife, Ozge, for your endless love and unwavering support. This is my first book since I met you. In Loving Memory Of my parents, Peyman and Yaman, who are no longer with us— I know you would be proud to see this book published. This is my first book since I lost you.

Contents 1 Introduction 15 1.1 Relational Databases ........................................................................................................... 17 1.2 What Is SQL? ............................................................................................................................ 20 1.3 Target Audience ..................................................................................................................... 23 1.4 What You’ll Learn from This Book ................................................................................. 24 1.5 Tips for Reading This Book ................................................................................................ 26 2 Basic Elements of Relational Databases 29 2.1 Fundamentals ......................................................................................................................... 29 2.1.1 Databases ................................................................................................................. 30 2.1.2 Database Products ................................................................................................. 30 2.1.3 Schemas ..................................................................................................................... 34 2.2 Structures .................................................................................................................................. 38 2.2.1 Tables ......................................................................................................................... 38 2.2.2 Data Types ................................................................................................................ 43 2.2.3 Views .......................................................................................................................... 46 2.3 Keys and Indexing ................................................................................................................. 49 2.3.1 Primary Keys ............................................................................................................. 49 2.3.2 Foreign Keys ............................................................................................................. 52 2.3.3 Indexes ....................................................................................................................... 55 2.4 Normalization ......................................................................................................................... 59 2.4.1 First Normal Form .................................................................................................. 59 2.4.2 Second Normal Form ............................................................................................. 60 2.4.3 Third Normal Form ................................................................................................. 61 2.4.4 Results ........................................................................................................................ 61 2.5 Operations ................................................................................................................................ 62 2.5.1 Stored Procedures .................................................................................................. 63 2.5.2 Triggers ...................................................................................................................... 65 2.6 Authorization .......................................................................................................................... 68 2.7 Summary ................................................................................................................................... 709

Contents3 Setting Up the Environment 71 3.1 Our Database of Choice ...................................................................................................... 73 3.2 Local Database Setup ........................................................................................................... 75 3.2.1 Windows Installation ............................................................................................ 75 3.2.2 macOS Installation ................................................................................................. 81 3.3 SQL IDE Setup .......................................................................................................................... 83 3.3.1 pgAdmin .................................................................................................................... 84 3.3.2 Visual Studio Code ................................................................................................. 92 3.4 Summary ................................................................................................................................... 97 4 Data Definition Language 99 4.1 Database Manipulation ...................................................................................................... 100 4.1.1 Database Creation ................................................................................................. 100 4.1.2 Database Modification ......................................................................................... 101 4.1.3 Database Deletion ................................................................................................. 106 4.2 Schema Manipulation ......................................................................................................... 107 4.2.1 Schema Creation .................................................................................................... 107 4.2.2 Schema Modification ............................................................................................ 108 4.2.3 Schema Deletion ..................................................................................................... 112 4.3 Table Manipulation .............................................................................................................. 115 4.3.1 Table Creation ......................................................................................................... 115 4.3.2 Table Modification ................................................................................................. 134 4.3.3 Table Deletion ......................................................................................................... 145 4.4 View Manipulation ............................................................................................................... 147 4.4.1 View Creation .......................................................................................................... 149 4.4.2 View Modification .................................................................................................. 151 4.4.3 View Deletion .......................................................................................................... 152 4.5 Common Pitfalls of Data Definition Language ........................................................ 153 4.5.1 Renaming Objects .................................................................................................. 153 4.5.2 Deleting Objects ..................................................................................................... 153 4.6 Summary ................................................................................................................................... 15410

Contents5 Data Manipulation Language 155 5.1 Building a Data Manipulation Language Playground ........................................... 156 5.2 Data Manipulation Language Operations .................................................................. 162 5.2.1 Insert ........................................................................................................................... 162 5.2.2 Update ........................................................................................................................ 174 5.2.3 Delete ......................................................................................................................... 185 5.2.4 Common Pitfalls of Data Manipulation Language Operations .............. 190 5.3 Data Manipulation Language Automation ............................................................... 191 5.3.1 Stored Procedures .................................................................................................. 192 5.3.2 Triggers ...................................................................................................................... 199 5.4 Summary ................................................................................................................................... 219 6 Transaction Control Language 221 6.1 Building a Transaction Control Language Playground ......................................... 222 6.2 Why Is Transaction Control Language Necessary? ................................................. 228 6.3 COMMIT and ROLLBACK ..................................................................................................... 230 6.4 Save Points ............................................................................................................................... 235 6.5 Locks and Concurrency ........................................................................................................ 241 6.5.1 Locks ............................................................................................................................ 242 6.5.2 Transaction Isolation ............................................................................................. 248 6.6 Common Pitfalls of Transaction Control Language ............................................... 251 6.7 Summary ................................................................................................................................... 251 7 Data Query Language 253 7.1 Building a Data Query Language Playground ........................................................... 254 7.1.1 Master Data Tables ................................................................................................ 255 7.1.2 Order Tables ............................................................................................................. 258 7.1.3 Delivery Tables ........................................................................................................ 260 7.1.4 Invoice Tables .......................................................................................................... 264 7.1.5 Complaint Tables .................................................................................................... 269 7.1.6 Mock Data ................................................................................................................. 27011

Contents7.2 Single Table Queries ............................................................................................................. 273 7.2.1 Selecting All Columns ........................................................................................... 274 7.2.2 Selecting Some Columns ..................................................................................... 276 7.2.3 Filtering Rows with WHERE Conditions .......................................................... 278 7.2.4 Ordering Results ..................................................................................................... 293 7.2.5 Partial Selection ...................................................................................................... 297 7.2.6 Unique Selection ..................................................................................................... 299 7.2.7 Null Values ................................................................................................................ 301 7.2.8 Aggregate Functions ............................................................................................. 303 7.3 Multitable Queries ................................................................................................................ 312 7.3.1 Refresher on Relationships between Tables ................................................. 312 7.3.2 INNER JOIN ................................................................................................................ 315 7.3.3 Using Aliases ............................................................................................................ 322 7.3.4 OUTER JOIN .............................................................................................................. 325 7.3.5 Self-Join ..................................................................................................................... 334 7.3.6 Subqueries ................................................................................................................ 337 7.3.7 Set Operations ......................................................................................................... 349 7.4 String Functions ..................................................................................................................... 358 7.4.1 Concatenation ......................................................................................................... 358 7.4.2 LEFT and RIGHT ....................................................................................................... 361 7.4.3 Length ........................................................................................................................ 363 7.4.4 LOWER and UPPER ................................................................................................. 365 7.4.5 LTRIM, RTRIM, and TRIM ....................................................................................... 366 7.4.6 REVERSE ..................................................................................................................... 368 7.4.7 SUBSTRING ............................................................................................................... 368 7.4.8 Regular Expressions ............................................................................................... 369 7.5 Math and Numeric Functions ........................................................................................... 373 7.5.1 Math Operators ....................................................................................................... 373 7.5.2 ABS ............................................................................................................................... 376 7.5.3 RANDOM ................................................................................................................... 379 7.5.4 ROUND, FLOOR, and CEILING ............................................................................. 380 7.5.5 SIGN ............................................................................................................................ 383 7.6 Temporal Functions .............................................................................................................. 383 7.6.1 Interval Calculations .............................................................................................. 384 7.6.2 Current Date and Time ......................................................................................... 385 7.6.3 Date Differences ..................................................................................................... 385 7.6.4 Extracting Date Parts ............................................................................................ 387 7.6.5 Time Zones ............................................................................................................... 38812

Contents7.7 Window Functions ................................................................................................................ 389 7.7.1 RANK ........................................................................................................................... 390 7.7.2 DENSE_RANK ........................................................................................................... 393 7.7.3 ROW NUMBER ......................................................................................................... 395 7.7.4 LEAD ............................................................................................................................ 401 7.7.5 LAG .............................................................................................................................. 404 7.7.6 NTILE ........................................................................................................................... 405 7.7.7 FIRST_VALUE ............................................................................................................ 407 7.8 Miscellaneous Functions .................................................................................................... 410 7.8.1 CAST ............................................................................................................................ 410 7.8.2 CASE ............................................................................................................................ 411 7.8.3 COALESCE .................................................................................................................. 414 7.8.4 Common Table Expressions ................................................................................ 415 7.9 Summary ................................................................................................................................... 418 8 Data Control Language 421 8.1 Building a Data Control Language Playground ........................................................ 422 8.2 User and Role Manipulation ............................................................................................. 424 8.2.1 Users ........................................................................................................................... 424 8.2.2 Roles ............................................................................................................................ 426 8.2.3 User Manipulation ................................................................................................. 429 8.2.4 Role Manipulation .................................................................................................. 430 8.3 Granting Access ...................................................................................................................... 431 8.3.1 Granting Access Directly ...................................................................................... 432 8.3.2 Granting Access Through Roles ......................................................................... 436 8.3.3 Granting Access through Role Hierarchies .................................................... 439 8.4 Revoking Access ..................................................................................................................... 440 8.5 Reporting Privileges ............................................................................................................. 441 8.6 Summary ................................................................................................................................... 446 9 Conclusion 44713

ContentsAppendices 449 A Entity-Relationship Diagrams .......................................................................................... 451 A.1 Entity-Relationship Diagrams and Their Significance ............................... 452 A.2 Entities ....................................................................................................................... 453 A.3 Relationships Among Entities ............................................................................ 457 A.4 Entity-Relationship Diagrams Exercises ......................................................... 465 A.5 Summary ................................................................................................................... 470 B Tips and Tricks ......................................................................................................................... 471 B.1 SQL Injection ............................................................................................................ 471 B.2 Application-Level Authorization ........................................................................ 473 B.3 Sensitive Data .......................................................................................................... 476 B.4 Logical Deletion ....................................................................................................... 477 B.5 Indexes versus Summary Tables ....................................................................... 478 B.6 Legal Concerns ......................................................................................................... 479 B.7 Primary Key Determination ................................................................................ 480 B.8 Logging ....................................................................................................................... 482 B.9 Backup and Recovery ............................................................................................. 482 C About the Author ................................................................................................................... 485 Index .......................................................................................................................................................... 487 Service Pages .....................................................................................................................................  I Legal Notes .........................................................................................................................................  II14

Chapter 1 Introduction This chapter introduces readers to relational databases and the Struc- tured Query Language (SQL) programming language. It sets the stage for the rest of the book by explaining who the book is for, what readers will learn, and how readers might approach the topics in the book. Hello there, and welcome to this exciting book on SQL: an ever-viable subject! We’ll journey together through the pages, learning the most essential features of SQL, learn- ing new skills, and gaining insights into how to apply those skills to everyday technical requirements. As the author, I am thrilled to have the honor of accompanying you on this journey. In today’s world, you can find countless resources and much documentation on any technical subject. It’s even possible to use AI for assistance in learning, but nothing can replace the wisdom gained from years of hands-on experience. Being a professional developer who’s worked with databases for many decades, I intend to provide not only technical guidance on SQL but also certain insights into how to approach technical requirements and apply the appropriate techniques to gain the best results. Examples in this book will be based on real-world scenarios as well as typical cases that you’re likely to encounter at some point during your development journey. We may simplify some cases because we don’t want to shift our focus from learning SQL to understanding a complex example. Human attention is a limited resource, and it’s best to spend it on what really matters. The realistic nature of our examples will enable you to grasp the logic and purpose of SQL features in such a way that you’ll be able to quickly apply them to your daily requirements. That was a personal frustration of mine during my novicehood: some resources provided sterile and unrealistic cartoon examples—involving cats, dogs, tomatoes, potatoes, and all. While the resources explained the technical topics at hand, it required significant effort on my part to apply the examples to the practical cases in my projects. Learning and Application Learning the theory of a feature is one thing, but the ability to apply the feature in real life is what makes it worthwhile. Going over realistic case studies typically helps read- ers in both respects.15Personal Copy for Luis Fernando de Paula, wemeke5983@elygifts.com

1 IntroductionReal-world examples will certainly be cleansed of any company specific process or con- tent due to privacy concerns. Sample contents of database tables will mostly be ran- dom mock data, not test or live data from any party. It’s unavoidable, though, that some mock data will resemble real-life people or companies. If you encounter such a case, be aware that this is purely coincidental and not intentional. Speaking of examples, all code samples in this book are available online! You can find all SQL code in this book at https://github.com/keremkoseoglu/sql, which can be useful throughout the book. Instead of manually writing code samples into your editor, you can simply copy and paste from the repository. SQL has some resemblance to board games like chess or go, in which the rules are sim- ple but game play can get very complex. This book will guide you through the syntax, features, and typical applications of SQL, which you’ll surely learn well and apply to your daily requirements. But be aware that business scenarios can get really complex in some cases where experience matters. In such cases, we recommend that you con- sult with senior developers to learn best practices honed by experience instead of attempting to reinvent the wheel. It will help you immensely on your learning journey and prevent you from developing some bad habits. Obviously, we need a playground in which to develop examples, create database objects, and execute queries against them. We’ve picked PostgreSQL as our database of choice. It’s a community-owned database that’s free and open source. It’s very popular, works across platforms, and has advanced features to future-proof your journey. PostgreSQL also supports all the functionality covered by this book. No worries, though—the SQL knowledge you’ll gain in PostgreSQL won’t lock you into it. Although SQL syntax varies from database to database in some cases, PostgreSQL is known to have a very portable SQL syntax that hews very closely to ANSI SQL standards and is identical to them in many cases. American National Standards Institute Structured Query Language The American National Standards Institute Structured Query Language (ANSI SQL) is a standardized database query language that provides the general standards of SQL, meaning it defines the de facto SQL syntax. Although vendors are expected to support ANSI SQL in their database products, some deviate from it and add new features, including special syntax elements that are unsupported by other databases. Therefore, portability of SQL code between database systems is not always a simple matter of copying and pasting code. Some adaptation might be required. Most of the case studies in this book will avoid database-specific features and focus on universal features expected to be supported by any relational database. That brings us to the next topic: what is a relational database?16 © 2025 by Rheinwerk Publishing Inc., Boston (MA)

1.1 Relational Databases1.1 Relational Databases SQL is the de facto language targeting relational databases, so it’s appropriate to give an overview of the latter. In a nutshell, a relational database is a type of database that orga- nizes data into tables with rows and columns, which we can imagine as a set of spread- sheet pages. Those “sheets” refer to each other and become “related” as needed to prevent data duplication. You’ll encounter these concepts and much more in due course. As an early sneak peek, you can refer to Figure 1.1, which gives an example of the basic structure of a relational database with sales data and the following other characteristics:  The spreadsheet-like customer table contains master data on all customers, including their IDs, names, phone numbers, and addresses.  The spreadsheet-like invoice table contains data on invoices issued by the company, including invoice IDs, dates, amounts, and currencies. This table contains the ID of each corresponding customer as well. When we need to see the details on an invoice customer, we can simply peek inside the customer table and look for their ID. There’s no need to duplicate customer data in the invoice table.  The spreadsheet-like table payment table contains payment data, including transac- tion IDs, payment amounts, and currencies. This table contains the invoice ID for each payment, and when we need to see the details of the corresponding invoice, we can simply peek inside the invoice table with the invoice ID in hand. There’s no need to duplicate invoice data in the payment table. Figure 1.1 Sample Structure of a Sales Relational Database Sales Database Refer To Refer To customer invoice payment17Personal Copy for Luis Fernando de Paula, wemeke5983@elygifts.com

1 IntroductionEasy, right? It’s just like a set of Excel spreadsheets with superb VLOOKUP functionality. The concept of relational database was first defined by E. F. Codd in “A Relational Model of Data for Large Shared Data Banks,” which was a research paper he published in 1970. His raw ideas evolved over time and were turned into many different ACID-compliant relational database products. Atomicity, Consistency, Isolation, and Durability Atomicity, consistency, isolation, and durability (ACID) are the expected features of any competent database product. They ensure that database transactions are processed correctly and reliably. The meaning of each of the four components is as follows:  Atomicity The entire transaction completes successfully or doesn’t happen at all.  Consistency Datasets must be consistent before and after the transaction.  Isolation Multiple transactions can occur in parallel, without interference.  Durability Once a transaction is committed, changes are permanently stored, even in the event of a system crash. Data storage used to be a straightforward topic. There were a few options to store data, and developers and architects would pick the appropriate solution. Relational data- bases were one of the few solid options back then. Today, we are in different circumstances. We are in the age of the cloud, big data, and AI, and as time has gone by, new requirements have given way to new types of databases. Some examples are listed in Table 1.1. Database Types Descriptions Examples Relational Organizes data into tables with rows and columns PostgreSQL MySQL Microsoft SQL Server Oracle Database NoSQL Handles unstructured data MongoDB (document stores) Redis (key-value stores) Time-series Optimized for time-stamped data InfluxDB TimescaleDB Table 1.1 Various Database Types18 © 2025 by Rheinwerk Publishing Inc., Boston (MA)

1.1 Relational DatabasesDelving into the details of each database type is beyond the scope of this book. The pur- pose of Table 1.1 is to demonstrate the breadth of alternatives for handling data. But does this mean that relational database management systems (RDBMS) are out of fash- ion? Not at all. Old but Gold Don’t let the gray hair fool you! Although a relational database is an aged concept, it has aged well and is still a solid and valid technology used throughout the industry. Classics are bound to be old, anyway—they can’t become classics until they age and stand the test of time. According to multiple sources, relational databases are still the most widely used data- bases in the entire industry. They are still relevant, going strong, and powering count- less systems on the planet—such as ERPs, websites, mobile applications, flight systems, financial institutions, health care systems, telecommunications, and social media plat- forms. If you can imagine your data as a set of structured spreadsheets, then a rela- tional database is one of the most solid alternatives for you to store your data, due to its benefits. In Table 1.2, you can see some of the significant benefits of relational databases—and having SQL as a reliable industry-standard language is one of them. Learning how to use SQL to query and manipulate data in relational databases is still a solid career investment. Object-oriented Stores data in objects ObjectDB Actian Graph Focuses on relationships between data points Neo4j ArangoDB Cloud Hosted on cloud platforms, focuses on scalability Amazon RDS Azure SQL DB In-Memory Stores data in RAM SAP HANA Spatial Optimized for spatial data, such as geographic information systems PostGIS Oracle Spatial Database Types Descriptions Examples Table 1.1 Various Database Types (Cont.)19Personal Copy for Luis Fernando de Paula, wemeke5983@elygifts.com

1 IntroductionBesides, many other database types support SQL in enabling developers to reuse their existing skills, so an investment in SQL can open doors for you into other databases as well. Now, let’s put SQL under the magnifying glass. 1.2 What Is SQL? Since you’re holding this book in your hands (or viewing it on your screen), you already have an idea of what SQL is all about that made you pursue the goal of learning about it in detail. We’ve already gone over SQL “informally,” so it’s time for a formal introduc- tion. Structured Query Language (SQL) is a programming language that’s used to manage, manipulate, and query contents of relational database systems. SQL was one of the first languages to target E. F. Codd’s relational model, which we mentioned in Section 1.1. It was initially developed in the 1970s by IBM, and it evolved into an American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standard in the 1980s. The language is obviously a success—even today, it’s easily the most widely used database language. Mind you that SQL is not a general-purpose language like Python, Java, or Rust. It’s rather a domain-specific language (DSL) tailored to a specific goal. In our case, the goal of SQL is to manage and query relational databases, nothing more. But since SQL is the Benefits of Relational Databases Descriptions Structured Relational databases provide well-defined and easy-to- understand schemata, which organize data into rows and columns. Data integrity You can enforce data integrity in multiple tables through constraints and transactions. Standardized language You can use SQL throughout the system for operations like data manipulation and queries. Scalability As data grows over time, databases can scale accord- ingly. Mature ecosystem Documentation, best practices, forums, experts, com- munity support, etc., are widely available. Security You can maintain data security with user authentica- tion and authorizations. Table 1.2 Some Benefits of Relational Databases20 © 2025 by Rheinwerk Publishing Inc., Boston (MA)

1.2 What Is SQL?de facto standard for that purpose, it’s widely used in conjunction with general-pur- pose languages. For example, a developer might code the business logic of their app in Python while coding the data manipulation part in SQL. In the typical approach (depicted in Figure 1.2), Python code is the central executable of the app. As data manipulation is needed, the appropriate SQL statements are be gener- ated by the Python code and executed against the database. Figure 1.2 SQL as the “Middleman” between Database and Application However, you don’t need to be a programmer to enjoy SQL! Many nonprogrammers simply use a database app or tool to connect to a database and execute handwritten SQL code to achieve the results they want. For instance, business users and data scien- tists often query databases to extract meaningful data to support their tasks (as sketched in Figure 1.3). The declarative nature of SQL makes it approachable by a wide audience. Figure 1.3 SQL Written Manually by the User Declarative versus Imperative Programming Languages Declarative programming languages enable the programmer to specify “what” the pro- gram should accomplish, without explicitly coding the steps for “how” to achieve it. SQL and HTML are examples of such languages, which provide a high level of abstrac- tion that makes them arguably more approachable. The programmer simply says what they want, using relatively simple statements, and the target framework does the heavy lifting for them. Imperative programming languages, on the other hand, enable the programmer to explicitly code “how” the program should accomplish the desired task. Python and Rust are examples of such languages, which enable a high level of control and flexibility at the cost of a steeper learning curve. The programmer can enjoy an increased level of technical granularity in their algorithms, which is necessary in many cases. Database Python Code SQL Code (Generated) Database Database Tool SQL Code (Handwritten)21Personal Copy for Luis Fernando de Paula, wemeke5983@elygifts.com